ADA-C01 Question 295
Single answerEvaluate and interpret Query Profiles to improve performanceA data engineering team reports that a nightly ETL query has become significantly slower over the last two weeks. The query joins a 12 TB SALES_FACT table to a 50 MB PRODUCT_DIM table, filters on SALE_DATE for the last 30 days, and aggregates by product category. In the Snowflake Query Profile for the slow runs, the administrator observes that most elapsed time is spent in a table scan node on SALES_FACT, with a very large number of micro-partitions scanned and very low partition pruning. The join node to PRODUCT_DIM shows relatively low cost, and there is no indication of spilling to local or remote storage. Which action is the BEST recommendation to improve this query's performance based on the Query Profile findings?
- A
Define a clustering key on SALES_FACT using SALE_DATE, then recluster or allow automatic clustering so micro-partitions align better with the filter predicate
- B
Increase the warehouse size because the Query Profile shows the join operation is the main bottleneck
- C
Convert PRODUCT_DIM into a materialized view so the optimizer can avoid scanning SALES_FACT
- D
Disable result cache for the ETL session so Snowflake recompiles the query plan for each run
Show answer and explanation
Correct answer: A
Explanation
The key to this question is interpreting the Query Profile correctly. The expensive operator is the table scan on SALES_FACT, not the join, and the profile explicitly shows many micro-partitions scanned with poor pruning. In Snowflake, micro-partition pruning is a major performance mechanism for large-table scans. When a table is frequently filtered by a column such as SALE_DATE and pruning is weak, a clustering key on that column can improve physical organization over time and reduce the amount of data scanned. This aligns with Snowflake best practices for large tables with selective filters. By contrast, simply increasing warehouse size may add compute but does not correct ineffective pruning, materializing the small dimension table does not reduce the fact-table scan, and disabling result cache is unrelated to the scan bottleneck. Relevant Snowflake documentation includes topics on Query Profile interpretation, micro-partitions and data clustering, clustering keys, and automatic clustering.
- A. Correct.
Correct. The profile indicates the dominant cost is scanning SALES_FACT with poor partition pruning, while the date filter is highly selective to the last 30 days. In Snowflake, clustering can improve pruning when a large table is frequently filtered on specific columns such as dates. Defining an appropriate clustering key on SALE_DATE can reduce the number of micro-partitions scanned, which directly addresses the bottleneck shown in the Query Profile. This is a practical tuning step when scan cost is high and pruning is poor.
- B. Incorrect.
Incorrect. This option misreads the profile. The scenario explicitly states the join node is relatively low cost and that most time is spent scanning SALES_FACT. Increasing warehouse size may help some workloads through more parallelism, but it does not specifically address poor micro-partition pruning. If the main issue is unnecessary scanning caused by weak clustering relative to the filter predicate, resizing the warehouse is not the best first recommendation.
- C. Incorrect.
Incorrect. A materialized view on PRODUCT_DIM would not meaningfully reduce the cost of scanning SALES_FACT, which is where the profile shows the query spends most of its time. PRODUCT_DIM is already very small, and the join to it is inexpensive. This distractor reflects a common misconception that optimizing the small side of a join will fix performance when the real issue is on the large fact table scan.
- D. Incorrect.
Incorrect. Result cache is not the issue described in the profile. Disabling it would not improve scan efficiency and could make repeated runs slower. The problem is not query compilation or stale planning; it is excessive micro-partition scanning on SALES_FACT. Query Profile analysis should guide administrators to address the actual expensive operators, not unrelated session settings.