SnowPro Associate: Platform Question 204
Single answer○ Semi-structured dataA retail company stores clickstream events in a Snowflake table named EVENTS. The table has a VARIANT column called RAW_EVENT that contains JSON documents. Analysts need a query that returns the customer ID and the numeric purchase amount for only those events where the JSON field eventType equals "purchase". Which query correctly extracts the values from the VARIANT column and filters the rows as required?
- A
SELECT RAW_EVENT:customer.id::STRING AS customer_id, RAW_EVENT:amount::NUMBER AS purchase_amount FROM EVENTS WHERE RAW_EVENT:eventType::STRING = 'purchase';
- B
SELECT RAW_EVENT.customer.id AS customer_id, RAW_EVENT.amount AS purchase_amount FROM EVENTS WHERE RAW_EVENT.eventType = 'purchase';
- C
SELECT GET_PATH(RAW_EVENT, 'customer.id') AS customer_id, GET_PATH(RAW_EVENT, 'amount') AS purchase_amount FROM EVENTS WHERE GET(RAW_EVENT, 'eventType') = 'purchase';
- D
SELECT PARSE_JSON(RAW_EVENT):customer.id::STRING AS customer_id, PARSE_JSON(RAW_EVENT):amount::NUMBER AS purchase_amount FROM EVENTS WHERE PARSE_JSON(RAW_EVENT):eventType::STRING = 'purchase';
Show answer and explanation
Correct answer: A
Explanation
When querying semi-structured data in Snowflake, JSON stored in a VARIANT column is commonly accessed using path notation such as COLUMN:key or COLUMN:nested.key. Because extracted values remain semi-structured unless converted, explicit casting is a best practice when the result must be treated as a SQL data type, such as STRING or NUMBER. In this scenario, the correct approach is to extract the nested customer ID and amount from the VARIANT column and cast them appropriately, while also casting the eventType field for accurate filtering. Snowflake documentation on querying semi-structured data and using VARIANT explains path traversal, nested element access, and casting extracted values to relational types.
- A. Correct.
Correct. Snowflake uses colon notation to traverse semi-structured data stored in VARIANT columns. RAW_EVENT:customer.id accesses the nested customer.id field, and explicit casting with ::STRING and ::NUMBER converts the values to the required scalar data types. The filter RAW_EVENT:eventType::STRING = 'purchase' correctly compares the JSON field value as a string.
- B. Incorrect.
Incorrect. Dot notation by itself is not the standard syntax for navigating fields inside a VARIANT column in Snowflake SQL. Semi-structured elements are typically accessed using colon notation from the VARIANT column, such as RAW_EVENT:eventType. A candidate might choose this because dot notation is common in object-access syntax in other technologies.
- C. Incorrect.
Incorrect. GET_PATH and GET can be used with semi-structured data, but this option is incomplete and unreliable for the stated requirement. The returned values are still VARIANT unless cast, so purchase_amount is not explicitly returned as a numeric value. Also, comparing GET(RAW_EVENT, 'eventType') directly to a SQL string without casting can lead to type mismatch or unclear semantics. This reflects a common misconception that extraction functions automatically return SQL scalar types.
- D. Incorrect.
Incorrect. PARSE_JSON is used to parse a string into a VARIANT value. In this scenario, RAW_EVENT is already a VARIANT column, so calling PARSE_JSON on it is unnecessary and invalid for normal usage. This distractor targets the misconception that PARSE_JSON is required whenever querying JSON, even after the data has already been loaded into a semi-structured column.