SnowPro Associate: Platform Question 205
Single answer● Querying dataA retail analytics team stores raw point-of-sale events in a Snowflake table named SALES_RAW with the following columns: STORE_ID NUMBER, EVENT_TS TIMESTAMP_NTZ, and PAYLOAD VARIANT. The PAYLOAD column contains JSON such as {"items":[{"sku":"A100","qty":2},{"sku":"B200","qty":1}],"cashier":"jsmith"}. An analyst needs a query that returns one row per purchased item with the store ID, event timestamp, SKU, and quantity for all events from the last 7 days. Which query should the analyst use?
- A
SELECT STORE_ID, EVENT_TS, f.value:sku::STRING AS SKU, f.value:qty::NUMBER AS QTY FROM SALES_RAW, LATERAL FLATTEN(input => PAYLOAD:items) f WHERE EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP());
- B
SELECT STORE_ID, EVENT_TS, PAYLOAD.items.sku::STRING AS SKU, PAYLOAD.items.qty::NUMBER AS QTY FROM SALES_RAW WHERE EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP());
- C
SELECT STORE_ID, EVENT_TS, GET(PAYLOAD, 'items')[0]:sku::STRING AS SKU, GET(PAYLOAD, 'items')[0]:qty::NUMBER AS QTY FROM SALES_RAW WHERE EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP());
- D
SELECT STORE_ID, EVENT_TS, items.sku::STRING AS SKU, items.qty::NUMBER AS QTY FROM SALES_RAW CROSS JOIN PAYLOAD:items WHERE EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP());
Show answer and explanation
Correct answer: A
Explanation
The key requirement is to return one row per item from a JSON array stored in a VARIANT column. In Snowflake, querying nested arrays in semi-structured data typically requires FLATTEN, which produces a lateral view of the array elements. Each element becomes available through the VALUE column of the FLATTEN result, allowing field extraction such as value:sku and value:qty. Direct path notation works well for nested objects, but arrays must be flattened when you need one output row per element. This aligns with Snowflake best practices for querying semi-structured data using VARIANT, path notation, and LATERAL FLATTEN as documented in Snowflake's guidance for querying JSON and using table functions.
- A. Correct.
Correct. PAYLOAD is a VARIANT column containing semi-structured JSON, and the items field is an array. To return one row per array element, Snowflake uses LATERAL FLATTEN. The expression PAYLOAD:items accesses the array, FLATTEN expands it into separate rows, and f.value:sku and f.value:qty retrieve fields from each item object. The timestamp filter using DATEADD(day, -7, CURRENT_TIMESTAMP()) correctly limits results to the last 7 days.
- B. Incorrect.
Incorrect. This option treats PAYLOAD.items as if Snowflake can directly project array members into scalar columns without expanding the array. Because items is an array of objects, direct dot notation does not produce one row per item. A common misconception is that semi-structured arrays can be queried like nested objects without FLATTEN.
- C. Incorrect.
Incorrect. This query only returns the first element of the items array by referencing [0]. It does not produce one row per purchased item, so any event containing multiple items would be incomplete. This is a plausible mistake when someone knows how to access array elements but overlooks the need to expand all elements.
- D. Incorrect.
Incorrect. Snowflake does not support directly CROSS JOINing a VARIANT path such as PAYLOAD:items in this way. To turn array elements into rows, the supported approach is TABLE(FLATTEN(...)) or the shorthand comma join with LATERAL FLATTEN. This option reflects a misunderstanding of how semi-structured array expansion works in Snowflake.