DAA-C01 Question 95
Single answerTable functionsA retail analytics team stores clickstream events in a Snowflake table named WEB_EVENTS. One column, EVENT_PAYLOAD, is VARIANT and contains JSON like {"session_id":"s123","products":[{"sku":"A1","qty":2},{"sku":"B9","qty":1}]}. Analysts need a query that returns one row per product within each event while preserving the original event row context, including SESSION_ID and EVENT_TS. Which query best meets this requirement using a Snowflake table function?
- A
SELECT EVENT_TS, EVENT_PAYLOAD:session_id::STRING AS SESSION_ID, f.value:sku::STRING AS SKU, f.value:qty::NUMBER AS QTY FROM WEB_EVENTS, LATERAL FLATTEN(INPUT => EVENT_PAYLOAD:products) f;
- B
SELECT EVENT_TS, EVENT_PAYLOAD:session_id::STRING AS SESSION_ID, FLATTEN(EVENT_PAYLOAD:products):sku::STRING AS SKU, FLATTEN(EVENT_PAYLOAD:products):qty::NUMBER AS QTY FROM WEB_EVENTS;
- C
SELECT EVENT_TS, EVENT_PAYLOAD:session_id::STRING AS SESSION_ID, p.sku, p.qty FROM WEB_EVENTS CROSS JOIN TABLE(PARSE_JSON(EVENT_PAYLOAD:products)) p;
- D
SELECT EVENT_TS, EVENT_PAYLOAD:session_id::STRING AS SESSION_ID, GET(EVENT_PAYLOAD,'products')[0]:sku::STRING AS SKU, GET(EVENT_PAYLOAD,'products')[0]:qty::NUMBER AS QTY FROM WEB_EVENTS;
Show answer and explanation
Correct answer: A
Explanation
The best answer is the query that uses LATERAL FLATTEN in the FROM clause. In Snowflake, FLATTEN is a built-in table function for expanding semi-structured arrays or objects into a set of rows. When analysts need to preserve columns from the source table while generating multiple child rows from a nested array, the standard pattern is to join the source row to the table function with LATERAL. This enables correlation so that each WEB_EVENTS row is expanded only against its own products array. The FLATTEN output includes metadata columns such as KEY, INDEX, PATH, THIS, and especially VALUE, which contains each array item. From VALUE, the query can extract SKU and QTY. This aligns with Snowflake documentation and common best practices for querying VARIANT data and using table functions in correlated row-expansion scenarios.
- A. Correct.
Correct. FLATTEN is a Snowflake table function used to explode ARRAY or OBJECT content in semi-structured data into multiple rows. Using it with LATERAL allows each row from WEB_EVENTS to be correlated with the rows produced by FLATTEN for that specific row's EVENT_PAYLOAD:products array. The VALUE column returned by FLATTEN contains each array element, so f.value:sku and f.value:qty correctly extract product attributes while preserving EVENT_TS and SESSION_ID from the base row.
- B. Incorrect.
Incorrect. FLATTEN is a table function, not a scalar function that can be referenced directly in the SELECT list this way. In Snowflake, table functions such as FLATTEN must appear in the FROM clause, typically with LATERAL when they need to reference columns from the left-side table. This option reflects a common misconception that semi-structured expansion functions behave like scalar JSON accessors.
- C. Incorrect.
Incorrect. TABLE(...) is used to invoke table functions, but PARSE_JSON is a scalar function, not a table function. Also, EVENT_PAYLOAD is already VARIANT, so parsing EVENT_PAYLOAD:products is unnecessary. Even if parsing were valid, this syntax would not automatically produce columns sku and qty as shown. The correct approach is to use FLATTEN over the array and then project fields from its VALUE output.
- D. Incorrect.
Incorrect. This query only accesses the first array element at index 0 and therefore does not return one row per product. It fails the requirement to explode all products in the array. This is a plausible mistake when someone knows how to access JSON array elements but does not recognize when a table function is needed to normalize repeated elements.