DAA-C01 Question 10
Single answerSemi-structured (e.g., Parquet, Avro, ORC, JSON, or XML)A retail analytics team stores clickstream events as newline-delimited JSON files in an external stage. The data is loaded into a Snowflake table with a single VARIANT column named EVENT_RAW. Analysts frequently need to build reports using the customer identifier and purchase amount from deeply nested attributes. Query performance is poor because each dashboard repeatedly extracts and casts these values from EVENT_RAW at runtime. The team wants to improve analyst query performance while keeping the raw JSON intact for future schema changes. Which approach should they take?
- A
Create a relational table or view that exposes frequently used JSON paths as typed columns, such as customer_id and purchase_amount, while retaining the original VARIANT data.
- B
Convert the JSON files to CSV before loading so Snowflake can query the fields faster than VARIANT data.
- C
Store the entire JSON document in a VARCHAR column instead of VARIANT, then use string functions to parse customer_id and purchase_amount when needed.
- D
Reload the data into a temporary table each time a dashboard runs so the JSON paths are evaluated only for the current report.
Show answer and explanation
Correct answer: A
Explanation
For analytics workloads on semi-structured data, Snowflake best practice is often to keep the source document in VARIANT for flexibility while exposing frequently queried elements as relational columns for ease of use and better performance. This avoids repeated use of expressions such as EVENT_RAW:customer:id::STRING or EVENT_RAW:purchase:amount::NUMBER in every report query. Snowflake documentation on querying semi-structured data describes using path notation and casting from VARIANT, while modeling best practices for analytics favor extracting high-value fields into typed columns when they are repeatedly queried. This approach balances schema-on-read flexibility with analyst-friendly performance and maintainability.
- A. Correct.
Correct. This is the most practical Snowflake pattern for semi-structured analytics workloads: preserve the raw JSON in VARIANT, but project commonly accessed attributes into relational, typed columns through a derived table, dynamic transformation, or a view. This reduces repeated path traversal and casting in every analyst query, improves usability, and still keeps the original semi-structured payload available when the schema evolves.
- B. Incorrect.
Incorrect. Converting JSON to CSV is not the best solution here. CSV removes the flexibility of storing evolving nested structures and often requires flattening or lossy transformations before load. Snowflake is designed to ingest and query semi-structured formats such as JSON directly through VARIANT. The problem described is repeated runtime extraction, not an inability to query JSON at all.
- C. Incorrect.
Incorrect. Storing semi-structured data in VARCHAR instead of VARIANT is a common misconception. VARIANT preserves semi-structured structure and data types, enabling path notation, functions for semi-structured processing, and better optimization than raw string parsing. Using VARCHAR would make querying harder and less efficient because analysts would need to parse strings manually.
- D. Incorrect.
Incorrect. Reloading data into a temporary table for every dashboard execution adds unnecessary ingestion overhead and does not address the root issue. The performance problem comes from repeated extraction and casting from nested JSON during reporting. A modeled layer with typed columns is the appropriate optimization, not repeated reloading.