DAA-C01 Question 75
Single answerTabular data/structured dataA retail analytics team stores daily sales transactions in a Snowflake table with columns such as ORDER_ID, STORE_ID, PRODUCT_ID, QUANTITY, UNIT_PRICE, and ORDER_TS. Analysts frequently run dashboards that aggregate sales by day, store, and product category for the most recent 90 days. Query performance has become inconsistent as the table has grown to several billion rows. The team wants to improve performance for these structured, tabular workloads without rewriting all dashboard queries. Which action is the MOST appropriate?
- A
Create a materialized view that pre-aggregates sales by day, store, and product category for the dashboard workload
- B
Convert the structured sales table into a VARIANT column so Snowflake can automatically optimize semi-structured access paths
- C
Replace the base table with an external table over the same data in cloud storage to reduce storage overhead
- D
Use a recursive CTE in each dashboard query so Snowflake can prune historical rows more efficiently
Show answer and explanation
Correct answer: A
Explanation
For large structured fact tables that support repetitive dashboard aggregations, a materialized view is often an appropriate optimization when the query patterns are stable and commonly reused. Snowflake materialized views can store and maintain precomputed results for eligible query patterns, which can significantly reduce compute for repeated aggregations. This is especially relevant in tabular/structured data scenarios where analysts group by a known set of dimensions. By contrast, VARIANT is intended for semi-structured data and is not a best practice replacement for a strongly typed relational schema. External tables are useful for querying files in cloud object storage but are not generally the best fit for interactive BI performance on large structured transactional datasets. Recursive CTEs solve hierarchical query problems, not fact-table performance problems. Snowflake documentation and best practices around materialized views, structured relational modeling, and external tables support this choice.
- A. Correct.
Correct. A materialized view is well suited for repeated, predictable aggregations over structured/tabular data when many users run similar dashboard queries. In this scenario, the workload repeatedly summarizes sales by day, store, and product category over recent data. A materialized view can persist the precomputed result and allow Snowflake to use it transparently when queries are compatible, reducing repeated computation on a very large fact table. This is a practical optimization when the organization wants better performance without rewriting every dashboard query extensively.
- B. Incorrect.
Incorrect. Converting a well-defined structured table into a single VARIANT column would generally make analytical SQL on relational attributes harder, not easier. VARIANT is useful for semi-structured data such as JSON, but for tabular data with stable schema and frequent aggregations, native relational columns are the recommended design. This option reflects the misconception that semi-structured storage is inherently more optimized than structured storage for all workloads.
- C. Incorrect.
Incorrect. External tables are typically used to query data stored outside Snowflake, often for data lake scenarios. They are not a performance optimization for high-frequency dashboarding on structured transactional data. In most analytics scenarios like this one, native Snowflake tables provide better performance and broader optimization opportunities than external tables. This distractor targets the misconception that lower storage cost automatically improves query performance.
- D. Incorrect.
Incorrect. Recursive CTEs are designed for hierarchical or graph-like traversals, such as organizational charts or bill-of-materials structures. They do not help Snowflake prune rows in a standard time-based fact table aggregation workload. Adding recursive CTE logic to every dashboard query would complicate the SQL and would not address the root performance issue. This option reflects confusion between SQL constructs for hierarchy processing and physical/query optimization techniques.