SnowPro Associate: Platform Question 215
Single answer○ Semi-structured dataA retail company loads clickstream events from JSON files into a Snowflake table named RAW_EVENTS with a single VARIANT column called EVENT_DATA. Analysts need a query that returns the customer ID as text and the numeric order total for only those events where the event type is "purchase" and the order total is greater than 100. Which query correctly handles the semi-structured data in Snowflake?
- A
SELECT EVENT_DATA:customer.id::STRING AS customer_id, EVENT_DATA:order.total::NUMBER AS order_total FROM RAW_EVENTS WHERE EVENT_DATA:event_type::STRING = 'purchase' AND EVENT_DATA:order.total::NUMBER > 100;
- B
SELECT PARSE_JSON(EVENT_DATA):customer.id AS customer_id, PARSE_JSON(EVENT_DATA):order.total AS order_total FROM RAW_EVENTS WHERE PARSE_JSON(EVENT_DATA):event_type = 'purchase' AND PARSE_JSON(EVENT_DATA):order.total > 100;
- C
SELECT GET_PATH(EVENT_DATA, 'customer.id') AS customer_id, GET_PATH(EVENT_DATA, 'order.total') AS order_total FROM RAW_EVENTS WHERE GET(EVENT_DATA, 'event_type') = 'purchase' AND GET_PATH(EVENT_DATA, 'order.total') > 100;
- D
SELECT EVENT_DATA.customer.id AS customer_id, EVENT_DATA.order.total AS order_total FROM RAW_EVENTS WHERE EVENT_DATA.event_type = 'purchase' AND EVENT_DATA.order.total > 100;
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores semi-structured data such as JSON in VARIANT columns and allows querying nested attributes with path notation. The standard pattern is COLUMN_NAME:path.to.element, for example EVENT_DATA:order.total. Because extracted values are VARIANT by default, best practice is to cast them explicitly when returning or filtering on a specific type, such as ::STRING or ::NUMBER. This is especially important for comparisons, since numeric comparisons should be performed on numeric types rather than raw VARIANT values. Snowflake documentation on querying semi-structured data and casting VARIANT values supports this approach. The key applied skill being tested is choosing the query pattern that correctly traverses JSON fields and safely converts them into relational types for analytics.
- A. Correct.
Correct. In Snowflake, semi-structured data stored in a VARIANT column is commonly accessed with path notation using a colon for the first level and dots for nested attributes, such as EVENT_DATA:customer.id. Because values extracted from VARIANT should typically be cast to the desired SQL type for reliable comparison and presentation, casting customer_id to STRING and order_total to NUMBER is the correct approach. Filtering on EVENT_DATA:event_type::STRING = 'purchase' and EVENT_DATA:order.total::NUMBER > 100 correctly applies typed comparisons.
- B. Incorrect.
Incorrect. PARSE_JSON is used to convert a string containing JSON into a VARIANT value. In this scenario, EVENT_DATA is already stored as VARIANT, so reparsing is unnecessary and not the correct pattern. In addition, leaving extracted values uncast can lead to less precise or unintended comparisons because the results remain VARIANT values.
- C. Incorrect.
Incorrect. GET and GET_PATH can be used with semi-structured data, so this option looks plausible. However, the returned values are still VARIANT, and this query does not cast them to STRING or NUMBER before comparing to 'purchase' and 100. That makes it a poor and potentially incorrect choice for typed filtering. The requirement specifically calls for customer ID as text and order total as numeric output.
- D. Incorrect.
Incorrect. Snowflake does not use pure dot notation directly on a VARIANT column the way some programming languages or SQL dialects do. Snowflake's standard syntax for semi-structured traversal starts with a colon after the column name, for example EVENT_DATA:event_type or EVENT_DATA:order.total. This option uses invalid access syntax for Snowflake SQL.