SnowPro Associate: Platform Question 201
Single answer○ Semi-structured dataA retail company loads clickstream events into a Snowflake table named RAW_EVENTS. The table has one column, EVENT_DATA, of type VARIANT. Each row contains JSON similar to: {"eventType":"purchase","customer":{"id":123,"tier":"gold"},"items":[{"sku":"A1","qty":2},{"sku":"B9","qty":1}]}. An analyst needs a query that returns one row per purchased item, including the customer ID and item SKU, but only for rows where eventType = 'purchase'. Which query best meets this requirement?
- A
SELECT EVENT_DATA:customer:id::NUMBER AS customer_id, f.value:sku::STRING AS sku FROM RAW_EVENTS, LATERAL FLATTEN(INPUT => EVENT_DATA:items) f WHERE EVENT_DATA:eventType::STRING = 'purchase';
- B
SELECT EVENT_DATA.customer.id AS customer_id, EVENT_DATA.items.sku AS sku FROM RAW_EVENTS WHERE EVENT_DATA.eventType = 'purchase';
- C
SELECT customer.id::NUMBER AS customer_id, items[*].sku::STRING AS sku FROM RAW_EVENTS WHERE eventType = 'purchase';
- D
SELECT PARSE_JSON(EVENT_DATA):customer:id::NUMBER AS customer_id, FLATTEN(EVENT_DATA:items):sku::STRING AS sku FROM RAW_EVENTS WHERE EVENT_DATA:eventType = 'purchase';
Show answer and explanation
Correct answer: A
Explanation
The best answer is the query that combines VARIANT path traversal with LATERAL FLATTEN. In Snowflake, semi-structured data such as JSON is commonly stored in VARIANT columns and queried using path notation like column_name:key or column_name:path.to.element. When a JSON field contains an array, FLATTEN is required to explode that array into separate rows. Using LATERAL allows each input row from RAW_EVENTS to be joined to the rows produced by FLATTEN for that specific row's items array. This is the standard Snowflake approach for querying nested arrays in semi-structured data. Snowflake documentation on querying semi-structured data and the FLATTEN table function describes these patterns, including extracting scalar values with path notation and casting them to expected SQL types.
- A. Correct.
Correct. In Snowflake, semi-structured data stored in a VARIANT column is commonly accessed with path notation using colons, such as EVENT_DATA:customer:id. Because items is an array, LATERAL FLATTEN is needed to produce one output row per array element. The alias f exposes each array element in f.value, so f.value:sku returns each SKU. Casting with ::NUMBER and ::STRING is also appropriate when returning typed scalar values.
- B. Incorrect.
Incorrect. This option treats the JSON structure like standard object-property access without proper Snowflake semi-structured syntax. Snowflake does support dot notation after a VARIANT path in some contexts, but EVENT_DATA.items.sku would not correctly expand the items array into one row per element. Without FLATTEN, the query cannot return one row per purchased item.
- C. Incorrect.
Incorrect. This option references customer, items, and eventType as if they were top-level relational columns rather than fields inside the VARIANT column EVENT_DATA. In the given table, only EVENT_DATA exists, so these references are invalid. It also incorrectly assumes array wildcard syntax can directly return one row per item in a standard SELECT without FLATTEN.
- D. Incorrect.
Incorrect. PARSE_JSON is used to convert a string into VARIANT, but EVENT_DATA is already a VARIANT column, so parsing is unnecessary. Also, FLATTEN is a table function and must be used in the FROM clause, typically with LATERAL FLATTEN, not inline as a scalar expression in the SELECT list as shown here. The filter expression also omits explicit casting to STRING, which is a best practice for comparing scalar JSON values.