DAA-C01 Question 156
Single answerEnrich the dataA retail analytics team stores clickstream events in a VARIANT column and wants to enrich each event with product attributes from a relational PRODUCT_DIM table. Each event contains an array of product IDs under payload:items. Analysts need one row per event-item combination, including event timestamp, product_id, product_name, and category, while preserving events that contain an empty items array for downstream quality checks. Which approach best meets the requirement?
- A
Use LATERAL FLATTEN on payload:items with OUTER => TRUE, then LEFT JOIN the flattened product_id values to PRODUCT_DIM.
- B
Use CROSS JOIN PRODUCT_DIM and filter where PRODUCT_DIM.PRODUCT_ID = payload:items, because Snowflake will automatically expand the array into rows.
- C
Use ARRAY_TO_STRING(payload:items, ',') and join the resulting string to PRODUCT_DIM.PRODUCT_ID to avoid flattening semi-structured data.
- D
Use INNER JOIN TABLE(FLATTEN(input => payload:items)) and then INNER JOIN PRODUCT_DIM so that only matched product IDs are returned.
Show answer and explanation
Correct answer: A
Explanation
To enrich semi-structured event data with relational reference data in Snowflake, the recommended pattern is to first normalize the array elements using FLATTEN and then join those elements to the dimension table. Because the requirement includes preserving events whose items array is empty, FLATTEN should be used with OUTER => TRUE so the source event is retained even when there are no array elements. After flattening, a LEFT JOIN to PRODUCT_DIM adds product attributes without excluding unmatched product IDs. This pattern aligns with Snowflake best practices for querying semi-structured data, using LATERAL joins with FLATTEN, and combining semi-structured data with structured tables for enrichment.
- A. Correct.
Correct. TABLE(FLATTEN(...)) is the standard Snowflake method to explode an array in a VARIANT column into separate rows. Using it laterally allows each source event row to be expanded by its own payload:items array. Setting OUTER => TRUE preserves source rows even when the array is empty or missing, which satisfies the requirement to retain such events for quality checks. A subsequent LEFT JOIN to PRODUCT_DIM enriches each event-item row with product attributes while still retaining rows when a product ID is not found in the dimension.
- B. Incorrect.
Incorrect. A regular CROSS JOIN to PRODUCT_DIM would create a Cartesian product and is not how Snowflake expands arrays stored in VARIANT. Snowflake does not automatically turn an array into multiple relational rows during a standard join predicate. The correct pattern is to use FLATTEN to normalize the array elements before joining.
- C. Incorrect.
Incorrect. Converting the array to a comma-delimited string loses the one-row-per-item structure required by the analysts. It also creates an invalid join pattern because PRODUCT_DIM.PRODUCT_ID is typically a single scalar value, not a concatenated list. This approach prevents proper relational enrichment and would be error-prone for matching and downstream analysis.
- D. Incorrect.
Incorrect. Flattening is necessary, but using INNER JOIN to PRODUCT_DIM would drop event-item rows where the product ID is missing from the dimension, and using an inner-style pattern also would not preserve events with empty arrays. The scenario explicitly requires preserving events with empty items arrays for quality checks, so OUTER => TRUE and a LEFT JOIN are the better fit.