DAA-C01 Question 144
Single answerUse native data typesA retail analytics team stores clickstream events in a Snowflake table named RAW_EVENTS. Each row contains a VARIANT column called EVENT_PAYLOAD with JSON such as {"customer_id":12345,"event_time":"2025-01-15T14:23:11Z","items":[{"sku":"A1","qty":2}],"order_total":149.95}. Analysts frequently filter by customer_id, aggregate order_total, and join on event_time to a calendar table. Query performance is inconsistent, and analysts often cast fields from EVENT_PAYLOAD in every query. The team wants to improve usability and align the design with Snowflake best practices for native data types while preserving the raw JSON for audit and future schema changes. Which approach should the data analyst recommend?
- A
Keep all attributes only in the VARIANT column and create views that cast values at query time, because Snowflake automatically optimizes semi-structured fields the same as native columns in all cases.
- B
Create a curated table that retains the raw VARIANT column and also materializes frequently used fields into native typed columns such as NUMBER for customer_id, TIMESTAMP_TZ for event_time, and NUMBER for order_total.
- C
Convert the entire JSON document to a single VARCHAR column so the team can use string functions consistently across all reports and avoid semi-structured parsing overhead.
- D
Store event_time as VARCHAR in the curated table to preserve the original text exactly, and let analysts cast it to a timestamp only when a time-based join is needed.
Show answer and explanation
Correct answer: B
Explanation
The best recommendation is to keep the raw JSON in VARIANT and materialize commonly used attributes into native columns in a curated layer. Snowflake supports semi-structured data through VARIANT, OBJECT, and ARRAY, which is valuable for ingestion flexibility and schema drift. However, when analysts repeatedly use the same fields for joins, filters, and aggregations, native data types improve consistency, readability, and downstream analytical behavior. In this scenario, customer_id and order_total should be stored as numeric types, while event_time should be stored as TIMESTAMP_TZ because the source timestamp includes UTC timezone information. This approach reflects Snowflake best practices: preserve raw semi-structured data, but model high-value analytical fields with appropriate native types for easier and more reliable querying. Relevant Snowflake documentation includes guidance on semi-structured data types (VARIANT, OBJECT, ARRAY), casting and converting data types, and choosing appropriate timestamp variants such as TIMESTAMP_TZ when timezone context matters.
- A. Incorrect.
Incorrect. Snowflake supports querying semi-structured data in VARIANT, but repeatedly casting JSON paths in every query adds complexity and can reduce usability. For frequently accessed attributes used in filters, joins, and aggregations, best practice is often to expose them as native typed columns. Snowflake does not guarantee that leaving everything only in VARIANT performs equivalently to using properly typed relational columns for all workloads.
- B. Correct.
Correct. This design preserves the original semi-structured payload for auditability and schema evolution while promoting commonly queried attributes into native data types. Using NUMBER for numeric identifiers and measures, and TIMESTAMP_TZ for ISO 8601 timestamps with timezone information, improves query simplicity, type safety, and interoperability with joins and aggregations. This is a common Snowflake pattern for balancing flexibility with analytic performance and maintainability.
- C. Incorrect.
Incorrect. Converting JSON into a single VARCHAR column removes Snowflake's ability to natively work with semi-structured data using VARIANT, path notation, and functions designed for JSON. It also makes type-safe querying harder, not easier, because every downstream use would require manual parsing from text.
- D. Incorrect.
Incorrect. Preserving the exact original text can be useful in the raw payload, but using VARCHAR for a field that is routinely joined and filtered as a timestamp is not a best practice in a curated analytics layer. TIMESTAMP_TZ is more appropriate here because the source value includes timezone information indicated by the trailing Z, and native timestamp typing supports correct temporal comparisons and joins.