SnowPro Associate: Platform Question 202
Single answer○ Semi-structured dataA retail company stores clickstream events in a Snowflake table named EVENT_LOG. The table has a VARIANT column called RAW_EVENT that contains JSON documents. Analysts need a query that returns the event ID and the numeric order total for only those events where the JSON attribute eventType equals "purchase" and the nested attribute order.total exists as a number. Which query best meets this requirement?
- A
SELECT RAW_EVENT:eventId::STRING AS event_id, RAW_EVENT:order.total::NUMBER AS order_total FROM EVENT_LOG WHERE RAW_EVENT:eventType::STRING = 'purchase' AND IS_NUMBER(RAW_EVENT:order.total);
- B
SELECT RAW_EVENT['eventId'] AS event_id, RAW_EVENT['order']['total'] AS order_total FROM EVENT_LOG WHERE RAW_EVENT['eventType'] = 'purchase' AND TYPEOF(RAW_EVENT['order']['total']) = 'INTEGER';
- C
SELECT eventId, order.total FROM EVENT_LOG WHERE eventType = 'purchase' AND order.total IS NOT NULL;
- D
SELECT GET_PATH(RAW_EVENT, 'eventId')::STRING AS event_id, GET_PATH(RAW_EVENT, 'order.total')::NUMBER AS order_total FROM EVENT_LOG WHERE GET_PATH(RAW_EVENT, 'eventType') = 'purchase' AND GET_PATH(RAW_EVENT, 'order.total') IS NOT NULL;
Show answer and explanation
Correct answer: A
Explanation
This question tests practical querying of semi-structured JSON data stored in a VARIANT column, a core SnowPro Associate topic. In Snowflake, JSON fields in VARIANT can be accessed with path notation such as COLUMN:key and COLUMN:key.nested_key, and values are often cast to standard SQL types with ::STRING or ::NUMBER for filtering and reporting. To validate the type of a semi-structured value, Snowflake provides functions such as IS_NUMBER. This is preferable here to simply checking for non-NULL because the scenario specifically requires order.total to exist as a number. Best practice is to cast VARIANT values when comparing or returning scalar results so query behavior is explicit and predictable. These patterns align with Snowflake documentation for querying semi-structured data, path traversal, casting VARIANT values, and using type-check functions.
- A. Correct.
Correct. Snowflake supports path traversal on VARIANT data using colon notation for the first-level element and dot notation for nested elements, such as RAW_EVENT:order.total. Casting with ::STRING and ::NUMBER is appropriate when returning scalar values from VARIANT. The predicate RAW_EVENT:eventType::STRING = 'purchase' correctly filters for purchase events, and IS_NUMBER(RAW_EVENT:order.total) verifies that the nested value exists and is numeric before casting.
- B. Incorrect.
Incorrect. Bracket notation can be used to access JSON elements in VARIANT, so parts of this option look plausible. However, TYPEOF(...) = 'INTEGER' is too restrictive because Snowflake numeric values in semi-structured data may not be stored specifically as INTEGER; they could be DECIMAL/NUMBER-like values. The requirement is to ensure the value exists as a number, not specifically as an integer. Also, the selected values are returned as VARIANT unless explicitly cast, which does not fully meet the requirement for a numeric order total.
- C. Incorrect.
Incorrect. This syntax treats eventId, eventType, and order.total as regular relational columns, but they are attributes inside the VARIANT column RAW_EVENT. Snowflake requires semi-structured path expressions or functions like GET_PATH to access nested JSON elements within VARIANT data.
- D. Incorrect.
Incorrect. GET_PATH can retrieve values from VARIANT data and using casts in the SELECT list is valid. However, the WHERE clause compares GET_PATH(RAW_EVENT, 'eventType') directly to the string 'purchase' without casting the VARIANT value to STRING, which can lead to incorrect comparison semantics. In addition, checking only IS NOT NULL does not ensure that order.total is numeric, which is part of the requirement.