DAA-C01 Question 143
Single answerUse native data typesA retail analytics team stores clickstream events in a Snowflake table named RAW_EVENTS with a VARIANT column called EVENT_DATA. Analysts frequently query customer actions using filters such as EVENT_DATA:userId, aggregate purchase amounts from EVENT_DATA:order.total, and join on product identifiers from EVENT_DATA:product.id. Query performance is inconsistent, and BI developers report frequent casting logic in dashboards. The team wants to redesign the table to improve usability and support analytics best practices while preserving semi-structured flexibility for less common attributes. Which approach is the BEST choice?
- A
Keep all attributes in the VARIANT column and create views that cast values at query time, because Snowflake automatically optimizes semi-structured data access as effectively as native relational columns.
- B
Create a new table that extracts frequently used attributes into native columns such as USER_ID VARCHAR, ORDER_TOTAL NUMBER, PRODUCT_ID VARCHAR, and EVENT_TS TIMESTAMP, while retaining the original VARIANT column for less commonly used fields.
- C
Convert the entire VARIANT payload into a single VARCHAR column so BI tools can parse the JSON themselves and avoid Snowflake casting overhead.
- D
Split the JSON payload into multiple OBJECT columns by domain area, such as customer, order, and product, because OBJECT columns provide the same analytical usability as native NUMBER, VARCHAR, and TIMESTAMP columns.
Show answer and explanation
Correct answer: B
Explanation
The best practice in Snowflake is to use native relational data types for stable, frequently queried attributes and reserve semi-structured types such as VARIANT, OBJECT, and ARRAY for attributes that are sparse, evolving, or not central to common analytics. In this scenario, fields like user IDs, product IDs, order totals, and timestamps are core analytical dimensions and measures, so modeling them as VARCHAR, NUMBER, and TIMESTAMP columns improves SQL usability, BI compatibility, and maintainability. Snowflake documentation on semi-structured data and data loading patterns supports using VARIANT for flexible ingestion while extracting important elements into typed columns when needed for reporting and analytics. This hybrid modeling approach balances flexibility with performance and analytical clarity.
- A. Incorrect.
Incorrect. While Snowflake supports querying semi-structured data in VARIANT efficiently, repeatedly extracting and casting commonly used fields at query time adds complexity for analysts and BI tools. Native relational columns are generally preferable for heavily queried, well-defined attributes because they improve schema clarity, reduce repetitive casting, and better align with standard SQL analytics patterns. The misconception is assuming VARIANT access is equivalent in usability and design quality to modeling stable fields with native types.
- B. Correct.
Correct. This is the recommended design for a mixed workload: promote frequently accessed, business-critical attributes into strongly typed native columns and keep the original VARIANT column for sparse or evolving fields. Using NUMBER for measures, VARCHAR for identifiers, and TIMESTAMP for event time improves query readability, data quality enforcement, and downstream BI compatibility. It also reduces repetitive casts and makes analytical logic more maintainable while preserving flexibility for semi-structured data.
- C. Incorrect.
Incorrect. Converting JSON to VARCHAR reduces, rather than improves, analytical usability in Snowflake. VARIANT is the native data type for semi-structured JSON and supports path traversal, automatic handling of JSON structures, and functions designed for semi-structured querying. Storing the payload as plain text would push parsing complexity to every consumer and remove many built-in capabilities. The misconception is treating raw text as simpler when it actually weakens analytical functionality.
- D. Incorrect.
Incorrect. OBJECT is useful for representing hierarchical semi-structured content, but it does not replace the benefits of native scalar types for common analytical fields. BI tools, joins, filters, and aggregations are typically easier and clearer with explicit native columns such as NUMBER and TIMESTAMP. Breaking one VARIANT into several OBJECT columns may organize payloads, but it does not solve the core problem of repeated extraction and casting for frequently analyzed attributes.