DAA-C01 Question 142
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":"s1","items":[{"sku":"A1","qty":2,"price":10.5},{"sku":"B2","qty":1,"price":5.0}],"attributes":{"channel":"email","device":"mobile"}}. The team needs a query that returns one row per item with SESSION_ID, SKU, QTY, PRICE, and CHANNEL. The solution must correctly traverse nested attributes and expand the items array without losing rows for sessions that contain multiple items. Which query best meets the requirement?
- A
SELECT EVENT_DATA:session_id::STRING AS session_id, f.value:sku::STRING AS sku, f.value:qty::NUMBER AS qty, f.value:price::FLOAT AS price, EVENT_DATA:attributes:channel::STRING AS channel FROM RAW_EVENTS, LATERAL FLATTEN(INPUT => EVENT_DATA:items) f;
- B
SELECT EVENT_DATA.session_id AS session_id, EVENT_DATA.items.sku AS sku, EVENT_DATA.items.qty AS qty, EVENT_DATA.items.price AS price, EVENT_DATA.attributes.channel AS channel FROM RAW_EVENTS;
- C
SELECT EVENT_DATA:session_id::STRING AS session_id, ARRAY_TO_STRING(EVENT_DATA:items, ',') AS sku, EVENT_DATA:items[0]:qty::NUMBER AS qty, EVENT_DATA:items[0]:price::FLOAT AS price, EVENT_DATA:attributes:channel::STRING AS channel FROM RAW_EVENTS;
- D
SELECT r.EVENT_DATA:session_id::STRING AS session_id, r.EVENT_DATA:items:sku::STRING AS sku, r.EVENT_DATA:items:qty::NUMBER AS qty, r.EVENT_DATA:items:price::FLOAT AS price, r.EVENT_DATA:attributes:channel::STRING AS channel FROM RAW_EVENTS r JOIN TABLE(FLATTEN(r.EVENT_DATA)) f ON TRUE;
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use LATERAL FLATTEN on the specific array path that must be expanded: EVENT_DATA:items. In Snowflake, semi-structured data stored in VARIANT can be traversed with path notation such as column:path and nested path segments like EVENT_DATA:attributes:channel. However, when a path points to an array, you must flatten that array to produce one relational row per element. The FLATTEN table function returns columns including VALUE, which contains each array element; this is then traversed further to extract sku, qty, and price. This pattern aligns with Snowflake documentation and best practices for querying semi-structured data: use path traversal for object access, explicit casting for typed output, and LATERAL FLATTEN for arrays that need row-wise expansion. This avoids common mistakes such as selecting only the first array element or trying to read scalar properties directly from an array.
- A. Correct.
Correct. This query uses Snowflake's path traversal syntax with colons to access semi-structured fields inside a VARIANT column and casts them to relational types. It also uses LATERAL FLATTEN(INPUT => EVENT_DATA:items) to expand the items array so that each array element becomes a separate row. Referencing f.value accesses each item object, from which sku, qty, and price are extracted. This is the standard and correct pattern for returning one row per array element while also projecting sibling attributes such as session_id and attributes.channel.
- B. Incorrect.
Incorrect. This option treats the VARIANT column as if nested array fields can be directly projected into scalar columns without flattening. While Snowflake supports traversal of VARIANT data, EVENT_DATA.items is an array, and accessing item fields like sku, qty, and price directly from the array does not produce one row per item. The result would not satisfy the requirement to expand multiple items into separate rows.
- C. Incorrect.
Incorrect. This option does not flatten the items array. ARRAY_TO_STRING(EVENT_DATA:items, ',') is not an appropriate way to extract individual SKU values from an array of objects, and using EVENT_DATA:items[0] only returns the first item from each session. That means sessions with multiple items are not fully represented, which violates the requirement to avoid losing rows.
- D. Incorrect.
Incorrect. Although this option attempts to use FLATTEN, it flattens the entire EVENT_DATA object rather than the items array specifically. Flattening the top-level object would iterate over keys like session_id, items, and attributes, not over individual item objects. In addition, the projected paths r.EVENT_DATA:items:sku, qty, and price are invalid for extracting fields from each array element because the query never references f.value from a flatten operation on EVENT_DATA:items.