DAA-C01 Question 77
Single answerSemi-structured dataA retail analytics team stores clickstream events in a Snowflake table named RAW_EVENTS with a VARIANT column called EVENT_DATA. The JSON structure varies by event type, and analysts frequently query nested fields such as EVENT_DATA:user.id, EVENT_DATA:device.os, and array elements under EVENT_DATA:items. Query performance has become inconsistent as data volume grows. The team wants to improve analyst usability and performance for common queries without changing the raw landing pattern. Which approach should the data analyst recommend?
- A
Create a curated relational layer using a view or dynamic table that extracts frequently used attributes from the VARIANT column into typed columns, while keeping the raw VARIANT data available for less common attributes.
- B
Convert the VARIANT column to VARCHAR so Snowflake can compress it more efficiently and analysts can use string functions for all nested access patterns.
- C
Replace the VARIANT column with separate columns for every possible JSON attribute across all event types, including rare and sparsely populated fields, to avoid any semi-structured processing at query time.
- D
Require analysts to use FLATTEN in every query against RAW_EVENTS, even when accessing scalar attributes, because FLATTEN is the recommended way to query all semi-structured data in Snowflake.
Show answer and explanation
Correct answer: A
Explanation
The best recommendation is to keep the raw semi-structured landing table intact and add a curated consumption layer that extracts high-value JSON attributes into typed columns. In Snowflake, VARIANT is the native type for semi-structured data such as JSON, and analysts can access elements with path notation. However, when the same paths are queried repeatedly at scale, a curated relational projection improves consistency, readability, and often performance for common analytics workloads. FLATTEN should be reserved for cases where arrays or nested objects must be expanded into rows, not for all semi-structured access. Converting VARIANT to VARCHAR discards native semi-structured capabilities, and creating columns for every possible attribute is a poor fit for evolving event schemas. This recommendation is consistent with Snowflake guidance on using VARIANT for raw ingestion and building downstream curated models for analytics.
- A. Correct.
Correct. A curated layer that projects commonly used JSON paths into strongly typed relational columns is a common Snowflake design pattern for semi-structured data. It preserves schema-on-read flexibility in the raw layer while improving usability, consistency, and performance for common analytical queries. Analysts can query simple typed columns for high-value attributes such as user ID or OS, while the original VARIANT remains available for evolving or less frequent fields. This approach aligns with best practices for separating raw ingestion from curated consumption.
- B. Incorrect.
Incorrect. Converting VARIANT to VARCHAR removes Snowflake's native semi-structured awareness and makes querying nested data harder, not easier. Analysts would lose direct JSON path traversal and would need string parsing logic, which is more error-prone and typically worse for both maintainability and performance. Snowflake is designed to store semi-structured data in VARIANT specifically so nested access can be queried directly.
- C. Incorrect.
Incorrect. Fully normalizing every possible attribute from all event types into physical columns is usually impractical for evolving clickstream schemas. It creates wide, sparse tables and increases maintenance overhead whenever new attributes appear. Snowflake supports semi-structured storage precisely to handle schema drift and sparse attributes efficiently. A curated subset of frequently queried fields is typically better than materializing every possible path.
- D. Incorrect.
Incorrect. FLATTEN is useful when working with arrays or objects that need to be expanded into rows, such as EVENT_DATA:items. However, it is not required for accessing scalar attributes like EVENT_DATA:user.id or EVENT_DATA:device.os. Forcing FLATTEN in every query adds unnecessary complexity and can increase processing cost. It should be used selectively when the business question requires exploding nested collections.