DAA-C01 Question 172
Single answer2.5 Optimize query performance.A retail analytics team runs a dashboard that filters a 4 TB SALES_FACT table by ORDER_DATE and REGION and joins to small dimension tables. The dashboard is queried hundreds of times per hour, but analysts report slow response times. Query Profile shows most elapsed time is spent scanning many micro-partitions from SALES_FACT, while the joins to dimensions are relatively inexpensive. The table is loaded continuously throughout the day. Which action would most directly improve query performance for this workload with the least application change?
- A
Define a clustering key on (ORDER_DATE, REGION) for SALES_FACT and enable automatic clustering if needed
- B
Convert the small dimension tables to temporary tables so the optimizer can join them faster
- C
Increase the warehouse size permanently, because micro-partition pruning does not affect scan-heavy queries
- D
Create a materialized view on the entire SALES_FACT table without filters or aggregation
Show answer and explanation
Correct answer: A
Explanation
This scenario points to a classic Snowflake performance issue: inadequate micro-partition pruning on a large fact table. Since the repeated dashboard queries filter by ORDER_DATE and REGION and Query Profile shows scan time dominating, the most direct optimization is to improve clustering around those filter columns. In Snowflake, clustering keys can improve pruning for very large tables where natural clustering from load order is not sufficient. For continuously changing tables, automatic clustering helps maintain clustering over time. Query Profile is the right tool to confirm whether scanning and partition pruning are the real bottlenecks. By contrast, changing small dimension tables to temporary tables does not address scan cost, and simply scaling the warehouse is a broader compute-based remedy rather than a data-layout optimization. Materialized views can help in some repeated-query scenarios, but a view on the entire base table without pre-aggregation or a narrower pattern usually provides limited benefit while adding maintenance overhead. This aligns with Snowflake best practices around using Query Profile, evaluating partition pruning, and applying clustering selectively to large tables with common selective filter predicates.
- A. Correct.
Correct. The Query Profile indicates the main bottleneck is scanning many micro-partitions in a large fact table, and the common filter predicates are ORDER_DATE and REGION. Defining a clustering key aligned to those selective predicates can improve micro-partition pruning so fewer partitions are scanned. Because the table is loaded continuously, automatic clustering can help maintain clustering depth over time without requiring application query changes. This is a targeted performance optimization for scan-heavy workloads on large tables.
- B. Incorrect.
Incorrect. Temporary tables do not inherently make joins faster. Join performance depends on factors such as table size, filtering, statistics, and execution strategy, not whether a table is temporary versus permanent. In this scenario, Query Profile already shows the joins are relatively inexpensive, so changing dimension table type does not address the actual bottleneck.
- C. Incorrect.
Incorrect. Increasing warehouse size may reduce execution time through more compute, but it does not directly solve poor micro-partition pruning. The statement that pruning does not affect scan-heavy queries is wrong; pruning is especially important for scan-heavy workloads. A larger warehouse can be a valid scaling choice, but it is less targeted and may increase cost without fixing the underlying data layout issue.
- D. Incorrect.
Incorrect. Materialized views are most useful when they precompute expensive results for repeated query patterns, often involving specific filters, projections, or aggregations. Creating one on the entire fact table without filters or aggregation is unlikely to reduce scan volume meaningfully and may add maintenance cost on a continuously loaded table. It also does not directly target the pruning issue shown in Query Profile.