DAA-C01 Question 117
Single answerJSON (query and parse)A retail analytics team stores clickstream events in a Snowflake table named RAW_EVENTS. The table has a VARIANT column named EVENT_DATA loaded from JSON files. Each JSON document contains an array called items and a nested object called device, for example: {"session_id":"S1001","device":{"os":"iOS"},"items":[{"sku":"A1","qty":2},{"sku":"B2","qty":1}]}. An analyst needs a query that returns one row per item with the session_id, device OS, item SKU, and item quantity. Which query correctly parses the JSON and returns the required result?
- A
SELECT EVENT_DATA:session_id::STRING AS session_id, EVENT_DATA:device.os::STRING AS os, i.value:sku::STRING AS sku, i.value:qty::NUMBER AS qty FROM RAW_EVENTS, LATERAL FLATTEN(input => EVENT_DATA:items) i;
- B
SELECT EVENT_DATA.session_id AS session_id, EVENT_DATA.device.os AS os, items.sku AS sku, items.qty AS qty FROM RAW_EVENTS CROSS JOIN EVENT_DATA:items items;
- C
SELECT JSON_EXTRACT_PATH_TEXT(EVENT_DATA, 'session_id') AS session_id, JSON_EXTRACT_PATH_TEXT(EVENT_DATA, 'device.os') AS os, JSON_EXTRACT_PATH_TEXT(EVENT_DATA, 'items.sku') AS sku, JSON_EXTRACT_PATH_TEXT(EVENT_DATA, 'items.qty') AS qty FROM RAW_EVENTS;
- D
SELECT PARSE_JSON(EVENT_DATA):session_id::STRING AS session_id, PARSE_JSON(EVENT_DATA):device:os::STRING AS os, PARSE_JSON(EVENT_DATA):items[0]:sku::STRING AS sku, PARSE_JSON(EVENT_DATA):items[0]:qty::NUMBER AS qty FROM RAW_EVENTS;
Show answer and explanation
Correct answer: A
Explanation
To return one row per element of a JSON array stored in a VARIANT column, Snowflake best practice is to query scalar values with path notation and expand arrays with FLATTEN, typically using a lateral join. In this scenario, EVENT_DATA:session_id and EVENT_DATA:device.os access top-level and nested values, while LATERAL FLATTEN(input => EVENT_DATA:items) produces a row for each item in the items array. The current array element is then read from the VALUE column of the FLATTEN result, such as i.value:sku and i.value:qty. This approach is documented in Snowflake guidance for querying semi-structured data and using FLATTEN with arrays. PARSE_JSON is appropriate when starting with a string, not when the column is already VARIANT.
- A. Correct.
Correct. Snowflake queries semi-structured data in VARIANT columns using path notation. EVENT_DATA:session_id and EVENT_DATA:device.os correctly access scalar values, and LATERAL FLATTEN(input => EVENT_DATA:items) expands the items array into one row per array element. The flattened element is exposed through i.value, so i.value:sku and i.value:qty return the item fields for each array entry.
- B. Incorrect.
Incorrect. Dot notation without the colon path syntax is not the correct way to traverse a VARIANT value in Snowflake SQL. In addition, EVENT_DATA:items is an array and cannot be expanded with a plain CROSS JOIN in this form; Snowflake requires FLATTEN to turn array elements into rows.
- C. Incorrect.
Incorrect. JSON_EXTRACT_PATH_TEXT can extract text from JSON, but this query does not flatten the items array, so it cannot correctly return one row per item. Paths such as 'items.sku' and 'items.qty' are also problematic because items is an array of objects, not a single object field that can be directly projected into multiple rows.
- D. Incorrect.
Incorrect. PARSE_JSON is used to convert a string into VARIANT. Because EVENT_DATA is already stored as VARIANT, reparsing is unnecessary. More importantly, this query references only items[0], which returns just the first element of the array rather than one row per item, so it does not meet the requirement.