DAA-C01 Question 96
Single answerTable functionsA retail analytics team stores clickstream events in a table named RAW_EVENTS with the columns SESSION_ID VARCHAR, EVENT_TIME TIMESTAMP_NTZ, and ATTRIBUTES VARIANT. The ATTRIBUTES column contains a JSON array called "products" for product-detail page views, where each array element has keys "sku" and "qty". Analysts need a query that returns one row per product viewed, while preserving SESSION_ID and EVENT_TIME from the base table. Which SQL pattern best solves this requirement using a Snowflake table function?
- A
SELECT r.SESSION_ID, r.EVENT_TIME, f.value:sku::STRING AS SKU, f.value:qty::NUMBER AS QTY FROM RAW_EVENTS r, LATERAL FLATTEN(input => r.ATTRIBUTES:products) f;
- B
SELECT SESSION_ID, EVENT_TIME, FLATTEN(ATTRIBUTES:products):sku::STRING AS SKU, FLATTEN(ATTRIBUTES:products):qty::NUMBER AS QTY FROM RAW_EVENTS;
- C
SELECT r.SESSION_ID, r.EVENT_TIME, GET(r.ATTRIBUTES, 'products')[0]:sku::STRING AS SKU, GET(r.ATTRIBUTES, 'products')[0]:qty::NUMBER AS QTY FROM RAW_EVENTS r;
- D
SELECT r.SESSION_ID, r.EVENT_TIME, p.sku, p.qty FROM RAW_EVENTS r CROSS JOIN TABLE(PARSE_JSON(r.ATTRIBUTES):products) p;
Show answer and explanation
Correct answer: A
Explanation
The correct solution is to use the FLATTEN table function with a LATERAL join. In Snowflake, FLATTEN takes semi-structured input such as an array in a VARIANT column and returns a set of rows with metadata columns including VALUE, INDEX, KEY, and others. Because the products array is stored per event row, LATERAL is the appropriate construct to correlate each RAW_EVENTS row with the rows produced by FLATTEN for that specific event. This is the standard pattern for exploding JSON arrays while retaining columns from the original table. Best practice is to use syntax like FROM base_table, LATERAL FLATTEN(input => base_table.variant_path) alias or an equivalent CROSS JOIN LATERAL form. Snowflake documentation for table functions and FLATTEN describes this usage and the structure of the returned columns.
- A. Correct.
Correct. FLATTEN is a Snowflake table function that expands arrays or objects stored in VARIANT into a set of rows. Using it with LATERAL allows each row from RAW_EVENTS to be correlated with the output rows produced from that row's ATTRIBUTES:products array. The VALUE column returned by FLATTEN contains each array element, so extracting f.value:sku and f.value:qty preserves SESSION_ID and EVENT_TIME while returning one row per product.
- B. Incorrect.
Incorrect. FLATTEN is a table function, not a scalar function that can be invoked directly in the SELECT list like a simple expression. In Snowflake, table functions such as FLATTEN must appear in the FROM clause, typically with LATERAL when they reference columns from a preceding table expression.
- C. Incorrect.
Incorrect. This pattern only accesses the first array element at index 0, so it does not produce one row per product. It is a common mistake to use direct indexing when the requirement is to unnest all array elements. This would lose additional products in sessions where multiple items appear in the products array.
- D. Incorrect.
Incorrect. TABLE(...) is used to invoke a table function, but PARSE_JSON(...):products is not itself a table function. Also, ATTRIBUTES is already a VARIANT column in the scenario, so PARSE_JSON is unnecessary. To expand the products array into rows, Snowflake requires a table function such as FLATTEN in the FROM clause.