DAA-C01 Question 78
Single answerSemi-structured dataA retail analytics team stores raw clickstream events in a Snowflake table named EVENTS_RAW. Each row contains a VARIANT column called EVENT_PAYLOAD with JSON similar to: {"eventType":"product_view","customer":{"id":"C123","loyaltyTier":"gold"},"items":[{"sku":"SKU1","price":19.99},{"sku":"SKU2","price":29.99}],"eventTs":"2025-01-10T14:32:11Z"}. Analysts frequently run queries that filter on customer loyalty tier and join individual items to a product dimension table by SKU. Query performance is poor because every query repeatedly traverses the JSON and flattens the items array. Which approach would best improve analyst performance while preserving the raw semi-structured data for future use?
- A
Replace the VARIANT column with a VARCHAR column containing the original JSON text so analysts can use string functions instead of JSON path expressions.
- B
Create a derived relational table or dynamic table that extracts commonly used attributes such as customer.loyaltyTier, eventTs, and item sku/price into typed columns, while retaining the original raw table with the VARIANT payload.
- C
Create a materialized view directly over a query that uses LATERAL FLATTEN on the items array and join that materialized view to dimensions for all analyst workloads.
- D
Store each JSON attribute in a separate top-level VARIANT column in the same table so Snowflake does not need to traverse nested paths during query execution.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to keep the raw semi-structured payload in VARIANT and create a curated, analytics-friendly structure for common access patterns. In Snowflake, VARIANT is ideal for ingesting JSON and preserving schema flexibility, but repeated path traversal and LATERAL FLATTEN across large datasets can become expensive for frequent analyst queries. For recurring filters and joins, best practice is to extract commonly used fields into typed columns in a derived table, view-backed pipeline, or dynamic table at the appropriate grain. In this scenario, an item-level table containing loyalty tier, event timestamp, SKU, and price would let analysts filter and join efficiently without repeatedly flattening arrays. This aligns with Snowflake guidance on using VARIANT for raw ingestion and relationalizing semi-structured data for performant analytics workloads.
- A. Incorrect.
Incorrect. Converting structured JSON content from VARIANT to VARCHAR usually makes analytics worse, not better. Analysts would lose native semi-structured querying, type awareness, and easier path extraction. String parsing is more error-prone and typically less efficient than extracting needed fields into relational columns. This distractor reflects the misconception that plain text is simpler for performance.
- B. Correct.
Correct. A common best practice in Snowflake is to preserve the raw semi-structured data in a landing or raw table, then create a curated relational representation for frequently accessed fields. Extracting customer.loyaltyTier, event timestamp, and flattened item-level attributes into typed columns reduces repeated JSON traversal and array flattening in analyst queries. Using a derived table or dynamic table supports downstream joins and filtering more efficiently while preserving the original VARIANT for reprocessing or schema evolution.
- C. Incorrect.
Incorrect. Materialized views in Snowflake have important limitations, and using LATERAL FLATTEN in a materialized view is not a generally appropriate pattern for this use case. Even beyond feature constraints, forcing all workloads through a materialized view built on repeated flattening is not the preferred design compared to modeling frequently accessed semi-structured elements into curated tables. This distractor targets the misconception that materialized views are the universal answer for semi-structured optimization.
- D. Incorrect.
Incorrect. Splitting nested JSON into multiple top-level VARIANT columns does not solve the main issue. Analysts would still query VARIANT data and likely still need to flatten arrays such as items. The real optimization is to project commonly used attributes into typed relational columns at the grain needed for analysis, such as one row per event item. This option sounds plausible because it reduces nesting depth, but it does not meaningfully address the repeated flatten-and-join workload.