DAA-C01 Question 155
Single answerEnrich the dataA retail analytics team stores online orders in a Snowflake table that includes a VARIANT column named EVENT_PAYLOAD. The JSON document contains a customer object and an array of purchased items. Analysts need a queryable enriched dataset with one row per purchased item, while preserving customer attributes from the same JSON document for downstream reporting. Which approach should the data analyst use?
- A
Use LATERAL FLATTEN on EVENT_PAYLOAD:items and select customer attributes from EVENT_PAYLOAD:customer in the same query.
- B
Create a materialized view directly on top of a query that uses FLATTEN over the VARIANT array so analysts can query the exploded rows automatically.
- C
Cast the entire VARIANT document to VARCHAR and use string functions to split the items array into rows and parse customer fields.
- D
Use Snowpipe to reload the source files into a new table with one column for each possible item attribute and customer attribute.
Show answer and explanation
Correct answer: A
Explanation
To enrich semi-structured JSON data in Snowflake, analysts typically use native VARIANT traversal with dot or bracket notation and LATERAL FLATTEN to explode arrays into row sets. In this case, the correct solution is to flatten the items array while also selecting parent-level customer attributes from the same JSON document, producing a denormalized item-level result suitable for BI and downstream reporting. This aligns with Snowflake best practices for querying semi-structured data: store nested content in VARIANT, access scalar elements directly, and use FLATTEN for arrays and repeated structures. String parsing is discouraged because it bypasses Snowflake's native JSON handling, and ingestion services such as Snowpipe do not replace query-time or transformation-time enrichment logic. Relevant Snowflake documentation includes guidance on querying semi-structured data and using the FLATTEN table function with LATERAL joins.
- A. Correct.
Correct. LATERAL FLATTEN is the standard Snowflake approach for expanding arrays stored in VARIANT into multiple rows. In this scenario, the analyst can flatten EVENT_PAYLOAD:items to create one row per item and simultaneously project scalar attributes such as EVENT_PAYLOAD:customer.id or EVENT_PAYLOAD:customer.region from the same source record. This is a practical and efficient pattern for enriching semi-structured data with related attributes from the parent JSON object.
- B. Incorrect.
Incorrect. Although materialized views can improve performance for certain repeatable transformations, Snowflake materialized views have important limitations and are not the recommended answer here. In practice, FLATTEN-based transformations are commonly implemented in views, dynamic tables, tasks, or ETL logic rather than assuming a materialized view can simply be created for this pattern. The core requirement is how to enrich and explode the data correctly, and LATERAL FLATTEN is the key mechanism.
- C. Incorrect.
Incorrect. Converting the entire VARIANT document to VARCHAR and then parsing it with string functions is brittle, error-prone, and ignores Snowflake's native semi-structured data support. This can break on nested structures, escaped characters, ordering differences, and schema changes. It is a common misconception from traditional text-processing workflows, but it is not a best practice in Snowflake.
- D. Incorrect.
Incorrect. Snowpipe is designed for continuous ingestion of data files, not for reshaping existing nested JSON into an enriched item-level analytical structure by itself. Reloading files into a wide table with columns for every possible nested attribute is inflexible and does not address the one-row-per-array-element requirement cleanly. The enrichment should be performed using semi-structured querying patterns such as FLATTEN.