ADA-C01 Question 336
Select 2Implement performance improvementsA retail company runs a dashboard every 5 minutes against a 4 TB SALES_FACT table. The dashboard queries typically filter on ORDER_DATE, REGION_ID, and STORE_ID, and aggregate revenue by hour. Users report that response times have increased from 3 seconds to more than 20 seconds during business hours. Query Profile shows most elapsed time is spent in table scan and scan output is much larger than the final result set. The table receives frequent micro-batch inserts throughout the day. Which TWO actions would most directly improve query performance for this workload while minimizing unnecessary compute increases?
- A
Define a clustering key on columns such as ORDER_DATE, REGION_ID, and STORE_ID, and monitor clustering depth to improve micro-partition pruning
- B
Create a materialized view that pre-aggregates revenue by hour for the commonly used dashboard dimensions and query that object instead of the base table
- C
Increase the warehouse size by two levels so every dashboard query gets more CPU, regardless of pruning efficiency
- D
Suspend and resume the warehouse more frequently to clear cache and reduce scan volume
- E
Convert the SALES_FACT table to a temporary table so Snowflake can optimize storage for short-lived dashboard queries
Show answer and explanation
Correct answers: A, B
Explanation
The strongest performance improvements here target the root cause shown in Query Profile: excessive scanning relative to the final result. In Snowflake, large scan costs are often reduced by improving micro-partition pruning and by avoiding repeated full-table aggregation work. A clustering key can help co-locate related values across micro-partitions for large, frequently filtered tables, improving pruning on predicates such as ORDER_DATE, REGION_ID, and STORE_ID. Because the table receives ongoing inserts, natural clustering can deteriorate over time, making explicit clustering more useful when justified by workload. A materialized view is also appropriate because the dashboard repeatedly requests similar grouped results; Snowflake can maintain the precomputed aggregation incrementally and serve queries more efficiently. By contrast, simply increasing warehouse size addresses compute capacity rather than data access efficiency and may raise cost without proportionate benefit. Best-practice guidance in Snowflake documentation emphasizes using Query Profile to identify scan bottlenecks, considering clustering for selective filter patterns on large tables, and using materialized views for repeated, costly query patterns.
- A. Correct.
Correct. The scenario indicates poor pruning because scan output is much larger than the final result set and predicates repeatedly target ORDER_DATE, REGION_ID, and STORE_ID. Defining an appropriate clustering key on commonly filtered columns can improve micro-partition pruning, especially for a large table with continuous inserts that may degrade natural clustering over time. Monitoring clustering information helps validate whether clustering is beneficial and whether reclustering effort is justified.
- B. Correct.
Correct. A materialized view is well suited when the same aggregation pattern is queried repeatedly and base data changes incrementally. Pre-aggregating hourly revenue by the dimensions used by the dashboard can significantly reduce scan and compute at query time. Since the dashboard runs every 5 minutes with a consistent access pattern, this can provide a direct performance improvement without simply scaling compute for every execution.
- C. Incorrect.
Incorrect. Increasing warehouse size may reduce execution time in some cases, but it does not address the main problem described: inefficient scans and poor pruning. If most time is spent scanning a large amount of unnecessary data, simply adding compute can be more expensive and less effective than improving data pruning or precomputing common aggregations.
- D. Incorrect.
Incorrect. Suspending and resuming the warehouse more frequently would discard cache rather than improve it. Warehouse cache can help repeated queries, and clearing it does not reduce scan volume from the underlying table. This option reflects a common misconception that restarting compute improves scan efficiency.
- E. Incorrect.
Incorrect. Temporary tables are session-scoped objects intended for transient workloads, not a performance optimization for a shared production fact table. Changing a permanent fact table to temporary would break persistence and multi-session availability requirements and does not inherently improve scan pruning or aggregation performance.