SnowPro Associate: Platform Question 207
Single answer● Querying dataA retail analytics team stores clickstream events in a Snowflake table named EVENTS with the columns USER_ID, EVENT_TS, and PAYLOAD. The PAYLOAD column is VARIANT and contains JSON such as {"device":"mobile","cart":{"items":3},"campaign":"spring"}. An analyst needs a query that returns only mobile events where the cart contains more than 2 items, along with USER_ID and campaign name. Which query correctly meets this requirement?
- A
SELECT USER_ID, PAYLOAD:campaign AS campaign FROM EVENTS WHERE PAYLOAD:device = 'mobile' AND PAYLOAD:cart.items > 2;
- B
SELECT USER_ID, PAYLOAD.campaign AS campaign FROM EVENTS WHERE PAYLOAD.device = 'mobile' AND PAYLOAD.cart.items > 2;
- C
SELECT USER_ID, PAYLOAD:campaign::STRING AS campaign FROM EVENTS WHERE PAYLOAD:device::STRING = 'mobile' AND PAYLOAD:cart.items::NUMBER > 2;
- D
SELECT USER_ID, GET(PAYLOAD,'campaign') AS campaign FROM EVENTS WHERE GET(PAYLOAD,'device') = 'mobile' AND GET(PAYLOAD,'cart.items') > 2;
Show answer and explanation
Correct answer: C
Explanation
When querying semi-structured data stored in a VARIANT column in Snowflake, use path notation to access JSON elements, such as COLUMN:key and COLUMN:parent.child. Because extracted values are VARIANT by default, it is best practice to cast them to the needed SQL type, for example ::STRING or ::NUMBER, especially when filtering or returning scalar values. In this scenario, the analyst must filter by a string value (device = 'mobile') and a numeric value (cart.items > 2), so explicit casting is the most correct and reliable approach. Snowflake documentation on querying semi-structured data describes using path notation for element access and casting VARIANT values to native SQL types for comparisons and output.
- A. Incorrect.
Incorrect. Snowflake supports colon notation to access elements in a VARIANT value, so PAYLOAD:campaign and PAYLOAD:device are valid path expressions. However, the comparison relies on implicit handling of VARIANT values rather than explicitly casting them to SQL data types. On the SnowPro Associate exam, the best-practice and most reliable query is the one that casts the JSON values to the appropriate scalar types before comparing or returning them.
- B. Incorrect.
Incorrect. Dot notation in this form is not valid for traversing semi-structured data from a VARIANT column in Snowflake SQL. Snowflake uses path notation such as PAYLOAD:device and PAYLOAD:cart.items, often combined with casts like ::STRING or ::NUMBER when the value is used in comparisons or projections.
- C. Correct.
Correct. This query uses valid Snowflake semi-structured data path syntax on a VARIANT column and explicitly casts values to the correct SQL types. PAYLOAD:device::STRING correctly extracts the device value for string comparison, PAYLOAD:cart.items::NUMBER correctly extracts the nested numeric item count for numeric comparison, and PAYLOAD:campaign::STRING returns the campaign name as a string.
- D. Incorrect.
Incorrect. GET can be used with semi-structured data, but GET(PAYLOAD,'cart.items') treats 'cart.items' as a single key name rather than navigating a nested path. In this JSON structure, cart is an object and items is a nested key within it, so the expression would not correctly access the nested value. Also, explicit casting would still be preferred for comparison and output.