DAA-C01 Question 9
Single answerSemi-structured (e.g., Parquet, Avro, ORC, JSON, or XML)A retail analytics team loads clickstream data from cloud storage into a Snowflake table named RAW_EVENTS using a single VARIANT column called EVENT. The source files are newline-delimited JSON. Analysts report that many queries are slow because they repeatedly extract nested attributes such as customer.id, device.os, and order.total from EVENT. The team wants to improve analyst query performance while preserving the raw JSON for future use and minimizing repeated JSON parsing in downstream queries. Which approach should the data analyst recommend?
- A
Create a relational reporting table or dynamic table that materializes the frequently used JSON attributes into typed columns while retaining the raw VARIANT data in RAW_EVENTS.
- B
Convert RAW_EVENTS from VARIANT to VARCHAR so analysts can use string functions instead of JSON path expressions for faster filtering.
- C
Store the JSON files as-is in an internal stage and have analysts query the staged files directly with SELECT statements whenever they need nested attributes.
- D
Create a view over RAW_EVENTS that exposes expressions such as EVENT:customer.id and EVENT:order.total, because views physically store the extracted values and eliminate repeated computation.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to keep the raw semi-structured data in a VARIANT column and create a derived relational structure for frequently accessed attributes. In Snowflake, JSON is commonly loaded into VARIANT, and analysts can access nested fields with path notation such as EVENT:customer.id. However, when the same fields are queried repeatedly at scale, projecting them into typed columns improves query simplicity and often performance, especially when business users routinely filter, join, and aggregate on those attributes. This approach also supports the requirement to preserve the original JSON for replay, governance, or future extraction of additional fields.
A key misconception is that a standard view materializes results; it does not. If physical precomputation is needed, a table populated by ELT or a dynamic table is a stronger fit. Likewise, converting VARIANT to VARCHAR removes structural advantages of semi-structured support. Snowflake documentation on querying semi-structured data and using VARIANT emphasizes path-based extraction and casting, while practical modeling guidance supports relationalizing high-value fields for repeated analytic access.
- A. Correct.
Correct. Materializing commonly queried attributes from VARIANT into typed relational columns is a practical optimization pattern in Snowflake when analysts repeatedly access the same semi-structured fields. This preserves the raw JSON for auditability and future schema evolution, while reducing repeated extraction and casting in user queries. Using a derived table, ETL/ELT pipeline, or dynamic table to project fields like EVENT:customer.id::STRING and EVENT:order.total::NUMBER into columns is aligned with Snowflake best practices for improving usability and query performance on high-value attributes.
- B. Incorrect.
Incorrect. Converting semi-structured JSON data from VARIANT to VARCHAR generally makes analysis worse, not better. Analysts lose native semi-structured querying capabilities, data type awareness, and easier path-based access. String parsing is typically more error-prone and less efficient than working with VARIANT and extracting typed values. This option reflects a common misconception that plain text is simpler or faster for analytics workloads.
- C. Incorrect.
Incorrect. Querying files directly from a stage can be useful for ad hoc inspection or external table patterns, but it is not the best recommendation here. The requirement is to preserve raw JSON while improving recurring analyst query performance and minimizing repeated parsing. Repeatedly querying staged JSON files would still require extracting nested fields during each query and is not an efficient design for frequent analytics use.
- D. Incorrect.
Incorrect. A standard view does not physically materialize or store extracted values by itself. It stores the SQL definition only, so the JSON path expressions are still evaluated when queries run against the view. While a view can improve usability by standardizing field access, it does not by itself eliminate repeated computation in the way a materialized target table or dynamic table can.