DAA-C01 Question 141
Single answerUse built-in functions for traversing, flattening, transforming, and nesting semi-structured dataA retail analytics team stores clickstream events in a VARIANT column named EVENT_DATA in table RAW_EVENTS. Each row contains JSON like: {"session_id":"S1001","customer":{"id":123,"segment":"gold"},"items":[{"sku":"A1","qty":2,"tags":["promo","mobile"]},{"sku":"B2","qty":1,"tags":["clearance"]}]}. The team needs a query that returns one row per item tag with the session ID, customer segment, SKU, quantity, and tag value. Which query pattern is the MOST appropriate in Snowflake?
- A
SELECT EVENT_DATA:session_id::STRING AS session_id, EVENT_DATA:customer.segment::STRING AS segment, item.value:sku::STRING AS sku, item.value:qty::NUMBER AS qty, tag.value::STRING AS tag FROM RAW_EVENTS, LATERAL FLATTEN(INPUT => EVENT_DATA:items) item, LATERAL FLATTEN(INPUT => item.value:tags) tag;
- B
SELECT EVENT_DATA.session_id AS session_id, EVENT_DATA.customer.segment AS segment, FLATTEN(EVENT_DATA.items).sku AS sku, FLATTEN(EVENT_DATA.items).qty AS qty, FLATTEN(EVENT_DATA.items.tags) AS tag FROM RAW_EVENTS;
- C
SELECT r.EVENT_DATA:session_id::STRING AS session_id, r.EVENT_DATA:customer.segment::STRING AS segment, ARRAY_AGG(i.value:sku::STRING) AS sku, ARRAY_AGG(i.value:qty::NUMBER) AS qty, ARRAY_AGG(t.value::STRING) AS tag FROM RAW_EVENTS r, LATERAL FLATTEN(INPUT => r.EVENT_DATA:items) i, LATERAL FLATTEN(INPUT => i.value:tags) t GROUP BY 1,2;
- D
SELECT EVENT_DATA:session_id::STRING AS session_id, EVENT_DATA:customer.segment::STRING AS segment, OBJECT_CONSTRUCT('sku', EVENT_DATA:items[0]:sku, 'qty', EVENT_DATA:items[0]:qty, 'tag', EVENT_DATA:items[0]:tags[0]) AS item_detail FROM RAW_EVENTS;
Show answer and explanation
Correct answer: A
Explanation
To return one row per nested array element in Snowflake, use the FLATTEN table function in the FROM clause, usually with LATERAL so each input row can be correlated to its expanded children. For nested arrays, chain multiple LATERAL FLATTEN operations, such as flattening EVENT_DATA:items and then flattening item.value:tags. Use JSON path traversal on VARIANT data with colon notation and cast results as needed, for example ::STRING or ::NUMBER. ARRAY_AGG and OBJECT_CONSTRUCT are useful for transforming and re-nesting semi-structured data, but they are not appropriate when the target result must be fully flattened to row-level output. This aligns with Snowflake documentation and best practices for querying semi-structured data, especially the sections on querying VARIANT values, path traversal, and the FLATTEN table function.
- A. Correct.
Correct. This is the standard Snowflake pattern for traversing and flattening nested semi-structured data. The query uses colon notation to traverse JSON paths in a VARIANT column, casts values to relational types, and applies LATERAL FLATTEN twice: first to expand the items array into one row per item, then again to expand each item's tags array into one row per tag. This produces the required row grain: one row per item tag, while preserving session and customer attributes from the parent object.
- B. Incorrect.
Incorrect. This reflects a common misconception that JSON fields in VARIANT can be traversed with standard dot notation everywhere and that FLATTEN can be invoked as a scalar accessor inline. In Snowflake, semi-structured traversal is typically done with path notation such as EVENT_DATA:session_id and EVENT_DATA:customer.segment, and FLATTEN is a table function that must be used in the FROM clause, usually with LATERAL, not as FLATTEN(...).sku in the SELECT list.
- C. Incorrect.
Incorrect. Although this option correctly uses nested LATERAL FLATTEN calls, it aggregates the flattened values back into arrays with ARRAY_AGG and groups only by session and segment. That changes the required output grain from one row per item tag to one row per session/segment with nested arrays of SKUs, quantities, and tags. This pattern is useful for re-nesting transformed results, but it does not satisfy the reporting requirement.
- D. Incorrect.
Incorrect. This option traverses semi-structured data and uses OBJECT_CONSTRUCT to build a nested object, but it only references the first item and first tag using array indexes [0]. It does not flatten all items and tags, so it misses most of the data. This is a plausible mistake when someone confuses direct indexing for iterative expansion.