DAA-C01 Question 118
Single answerJSON (query and parse)A retail analytics team stores clickstream events in a Snowflake table named RAW_EVENTS with columns EVENT_ID NUMBER and PAYLOAD VARIANT. Each PAYLOAD contains JSON similar to: {"customer":{"id":"C102","tier":"gold"},"order":{"items":[{"sku":"A1","qty":2},{"sku":"B9","qty":1}]},"event_ts":"2025-01-15T10:03:22Z"}. The team needs a query that returns one row per ordered item with these columns: EVENT_ID, CUSTOMER_ID, SKU, QTY. The query must safely parse the nested JSON already stored in VARIANT and correctly expand the items array. Which solution should the analyst use?
- A
SELECT r.EVENT_ID, r.PAYLOAD:customer.id::STRING AS CUSTOMER_ID, f.value:sku::STRING AS SKU, f.value:qty::NUMBER AS QTY FROM RAW_EVENTS r, LATERAL FLATTEN(INPUT => r.PAYLOAD:order.items) f;
- B
SELECT EVENT_ID, GET_PATH(PAYLOAD, 'customer.id') AS CUSTOMER_ID, GET_PATH(PAYLOAD, 'order.items.sku') AS SKU, GET_PATH(PAYLOAD, 'order.items.qty') AS QTY FROM RAW_EVENTS;
- C
SELECT r.EVENT_ID, PARSE_JSON(r.PAYLOAD):customer:id::STRING AS CUSTOMER_ID, PARSE_JSON(r.PAYLOAD):order:items[0]:sku::STRING AS SKU, PARSE_JSON(r.PAYLOAD):order:items[0]:qty::NUMBER AS QTY FROM RAW_EVENTS r;
- D
SELECT r.EVENT_ID, JSON_EXTRACT_PATH_TEXT(r.PAYLOAD, 'customer', 'id') AS CUSTOMER_ID, FLATTEN(r.PAYLOAD:order.items):sku AS SKU, FLATTEN(r.PAYLOAD:order.items):qty AS QTY FROM RAW_EVENTS r;
Show answer and explanation
Correct answer: A
Explanation
When JSON is stored in a VARIANT column in Snowflake, the recommended approach is to query it directly using path notation such as column:path.to.element and cast values as needed. For arrays, Snowflake requires FLATTEN as a table function to explode array elements into separate rows. In this scenario, order.items is an array of objects, so LATERAL FLATTEN(INPUT => PAYLOAD:order.items) is the correct pattern. The flattened row exposes each array entry in the VALUE column, enabling extraction like VALUE:sku and VALUE:qty. Re-parsing existing VARIANT data with PARSE_JSON is unnecessary, and direct path extraction across an array without flattening does not satisfy the one-row-per-item requirement. This aligns with Snowflake documentation and best practices for querying semi-structured data, including VARIANT traversal, explicit casting, and use of FLATTEN for arrays.
- A. Correct.
Correct. PAYLOAD is already stored as VARIANT, so it should be queried directly with Snowflake's semi-structured data path notation. r.PAYLOAD:customer.id accesses the nested customer id, and LATERAL FLATTEN(INPUT => r.PAYLOAD:order.items) expands the items array into one row per element. Each flattened element is available as f.value, so f.value:sku and f.value:qty correctly extract item attributes. Explicit casts to STRING and NUMBER are appropriate to produce typed relational columns.
- B. Incorrect.
Incorrect. GET_PATH can retrieve a nested element from VARIANT, but this option incorrectly assumes Snowflake can directly project fields like order.items.sku from an array without flattening it first. Because order.items is an array of objects, the query would not produce one row per item and would not correctly extract SKU and QTY for each array element.
- C. Incorrect.
Incorrect. PARSE_JSON is used to convert a string into VARIANT, but PAYLOAD is already a VARIANT column, so reparsing is unnecessary and invalid in typical usage. In addition, this option only accesses items[0], which returns only the first item in each order rather than one row per ordered item. It fails the core requirement to expand the full array.
- D. Incorrect.
Incorrect. JSON_EXTRACT_PATH_TEXT is intended for extracting text from JSON content, commonly from string expressions, but this option mixes it with invalid FLATTEN syntax. In Snowflake, FLATTEN must be used as a table function in the FROM clause, typically with LATERAL, and its output must be referenced through columns such as VALUE. You cannot call FLATTEN inline as if it were a scalar expression returning :sku or :qty directly.