DAA-C01 Question 76
Single answerTabular data/structured dataA retail analytics team stores daily point-of-sale transactions in a Snowflake table named SALES_TXN with strongly typed columns such as TXN_ID NUMBER, STORE_ID NUMBER, TXN_TS TIMESTAMP_NTZ, QUANTITY NUMBER, UNIT_PRICE NUMBER(10,2), and DISCOUNT_AMT NUMBER(10,2). Analysts frequently run dashboards that aggregate revenue by calendar date and store. Query performance has degraded as the table has grown to several billion rows. Most dashboard filters are on STORE_ID and a date range derived from TXN_TS. The team wants to improve performance for these tabular queries without changing the source data model. Which action is the MOST appropriate?
- A
Create a materialized view that pre-aggregates revenue by STORE_ID and CAST(TXN_TS AS DATE) for the dashboard query pattern
- B
Convert the structured SALES_TXN table into a VARIANT-based table so Snowflake can optimize semi-structured pruning automatically
- C
Replace numeric columns with VARCHAR columns because text values compress better for repetitive transactional data
- D
Use a sequence on TXN_ID to improve scan pruning for queries filtered by STORE_ID and transaction date
Show answer and explanation
Correct answer: A
Explanation
For structured, tabular workloads in Snowflake, the best optimization depends on the query pattern. Here, the repeated BI workload aggregates by STORE_ID and transaction date from a very large fact table. A materialized view is an appropriate optimization when a stable, frequently reused aggregation pattern exists. Snowflake materialized views can improve performance by maintaining precomputed results over the base table, reducing repeated scans and aggregations. By contrast, converting a relational fact table to VARIANT is not a best practice for standard tabular analytics and would undermine the advantages of strong typing. Likewise, changing numeric columns to VARCHAR would hurt analytical performance and data quality. Sequences are useful for key generation, not for improving pruning on unrelated filter columns. This aligns with Snowflake best practices around using native structured data types for tabular analytics and applying materialized views selectively for repeated, expensive query patterns.
- A. Correct.
Correct. A materialized view that matches the common aggregation pattern can significantly reduce compute for repeated dashboard queries, especially when users repeatedly group by store and transaction date. In this scenario, the workload is highly repetitive and based on structured columns already present in the table. Pre-aggregating SUM(QUANTITY * UNIT_PRICE - DISCOUNT_AMT) or similar revenue logic by STORE_ID and transaction date allows Snowflake to maintain the results incrementally and serve common analytical queries more efficiently than scanning the full base table each time.
- B. Incorrect.
Incorrect. Moving strongly typed, structured transactional data into VARIANT would usually make the model less efficient and less governed for this use case. The issue described is repeated analytical aggregation on known columns, not schema flexibility. Snowflake supports semi-structured data well, but converting a well-modeled tabular fact table to VARIANT does not improve standard BI query performance and can complicate typing, predicate evaluation, and downstream analytics.
- C. Incorrect.
Incorrect. Replacing NUMBER columns with VARCHAR is a poor design choice for structured analytics. Snowflake works efficiently with native numeric and timestamp types for compression, storage, and query execution. Converting structured numeric data to text would increase casting overhead, reduce type safety, and likely worsen performance for aggregations and joins. This distractor reflects a common misconception that text is inherently better compressed or more flexible for analytics.
- D. Incorrect.
Incorrect. A sequence on TXN_ID only guarantees generated uniqueness and monotonic assignment behavior for that column; it does not directly improve pruning for predicates on STORE_ID and date-derived filters. Since the dashboard queries filter on STORE_ID and TXN_TS-derived date, changing TXN_ID generation does not address the actual access pattern. This option confuses surrogate key generation with physical optimization for analytical filtering.