DAA-C01 Question 30
Single answerPerform data filtering and/or transformationA retail analytics team stores clickstream events in a Snowflake table named RAW_EVENTS with columns EVENT_ID, EVENT_TS, SESSION_ID, and PAYLOAD (VARIANT). The PAYLOAD column contains JSON such as {"eventType":"purchase","user":{"id":"U123"},"items":[{"sku":"A1","qty":2},{"sku":"B4","qty":1}],"marketing":{"campaign":"spring_sale"}}. The team needs a dataset that contains one row per purchased item, but only for purchase events from the last 7 days where the marketing campaign is present. Which approach best satisfies the requirement?
- A
Select from RAW_EVENTS, use LATERAL FLATTEN on PAYLOAD:items, filter with PAYLOAD:eventType = 'purchase', EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP()), and PAYLOAD:marketing:campaign IS NOT NULL, then project PAYLOAD:user:id and flattened item fields.
- B
Select from RAW_EVENTS and filter with WHERE PAYLOAD.items[0].sku IS NOT NULL AND EVENT_TS >= CURRENT_DATE - 7, then use ARRAY_SIZE(PAYLOAD:items) to represent one row per item without flattening.
- C
Select from RAW_EVENTS, cast PAYLOAD to VARCHAR, use LIKE '%purchase%', and split the JSON string to extract item values, because FLATTEN is only needed for XML data.
- D
Create a view that filters EVENT_TS for the last 7 days, then use DISTINCT on SESSION_ID and PAYLOAD to avoid duplicate items instead of flattening the items array.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to query the VARIANT column using Snowflake's semi-structured data support and to expand the items array with LATERAL FLATTEN. This is the canonical approach when transforming nested JSON into tabular form. The filtering logic should be applied using JSON path expressions for eventType and marketing campaign, along with a timestamp predicate such as EVENT_TS >= DATEADD(day, -7, CURRENT_TIMESTAMP()). Snowflake documentation for querying semi-structured data and the FLATTEN table function describes this pattern: use colon path notation to access nested keys, and use LATERAL FLATTEN to produce one output row per array element. Alternatives that rely on string parsing, DISTINCT, or array counts do not satisfy the item-level row requirement and are not aligned with Snowflake best practices for filtering and transforming VARIANT data.
- A. Correct.
Correct. In Snowflake, semi-structured JSON fields in a VARIANT column are accessed with path notation such as PAYLOAD:eventType and PAYLOAD:marketing:campaign. Because the requirement is one row per purchased item, the items array must be expanded with LATERAL FLATTEN(input => PAYLOAD:items). Filtering for purchase events, limiting to the last 7 days with DATEADD, and checking that the campaign exists with IS NOT NULL directly matches the scenario. Projecting nested values like PAYLOAD:user:id and flattened item attributes is the standard pattern for transforming VARIANT data into relational output.
- B. Incorrect.
Incorrect. Checking PAYLOAD.items[0].sku is not the preferred Snowflake path syntax and, more importantly, it only verifies that at least one item exists. It does not generate one row per item. ARRAY_SIZE can count items, but it cannot transform an array into separate rows. This option fails the core requirement to output one row per purchased item.
- C. Incorrect.
Incorrect. Converting VARIANT JSON to VARCHAR and parsing it with string functions is brittle, inefficient, and not a Snowflake best practice when native semi-structured querying is available. FLATTEN is specifically designed for arrays and nested structures in VARIANT, OBJECT, and ARRAY data, not XML only. This option reflects a common misconception that JSON should be handled as raw text instead of with Snowflake's native functions.
- D. Incorrect.
Incorrect. DISTINCT can remove duplicate rows but cannot expand an array into one row per element. Using DISTINCT on SESSION_ID and PAYLOAD also risks masking legitimate repeated events rather than performing the required transformation. This option addresses deduplication, not item-level explosion of nested purchase arrays.