ADA-C01 Question 294
Single answerEvaluate and interpret Query Profiles to improve performanceA Snowflake administrator is investigating a dashboard query that has become slow after a large historical data load. The query joins a 12 TB SALES_FACT table to a 50 MB REGION_DIM table and filters on SALES_DATE for the last 7 days. In the Query Profile, the administrator notices that most elapsed time is spent in a table scan on SALES_FACT, with a very high percentage of micro-partitions scanned compared to total partitions. The join itself consumes relatively little time, and there is no significant spill to local or remote storage. Which action is the BEST recommendation to improve performance based on this Query Profile?
- A
Increase the warehouse size so the join completes faster
- B
Define a clustering key on SALES_FACT aligned to SALES_DATE so partition pruning improves for the date filter
- C
Convert REGION_DIM into a temporary table so Snowflake can cache it more effectively
- D
Rewrite the query to use a CROSS JOIN so Snowflake can reorder joins more aggressively
Show answer and explanation
Correct answer: B
Explanation
This scenario tests whether the candidate can interpret Query Profile evidence rather than applying generic tuning steps. In Snowflake, Query Profile often reveals whether time is spent on scanning, joins, aggregation, or spilling. Here, the critical signals are: 1) most time is spent scanning SALES_FACT, 2) the percentage of scanned micro-partitions is high, 3) the query has a selective date filter, and 4) the join and spill metrics are not the issue. These indicators point to insufficient micro-partition pruning. Snowflake best practices recommend improving pruning by aligning clustering with common selective filter predicates for very large tables when natural clustering is no longer effective after data growth or load pattern changes. Increasing warehouse size is more appropriate when the workload is compute-bound, such as heavy joins, aggregations, or spills, but it does not solve data access inefficiency caused by poor pruning. This aligns with Snowflake documentation and best practices for using Query Profile, micro-partition pruning, and clustering to improve large-table query performance.
- A. Incorrect.
Incorrect. The Query Profile indicates the main bottleneck is the large table scan on SALES_FACT, not the join operator itself. Increasing warehouse size may add compute, but it does not directly fix poor micro-partition pruning. If the engine still has to scan a large percentage of the table, performance gains may be limited and cost will increase.
- B. Correct.
Correct. A high percentage of scanned micro-partitions on a very large table, combined with a selective date predicate, strongly suggests poor pruning. Clustering SALES_FACT on SALES_DATE can improve micro-partition organization and make the filter on the last 7 days more selective at scan time. This is a practical optimization when Query Profile shows scan-heavy behavior and limited partition pruning.
- C. Incorrect.
Incorrect. REGION_DIM is already small relative to SALES_FACT, and the Query Profile shows the join is not the dominant cost. Making the dimension table temporary does not address the expensive scan on the fact table, nor is temporary-table usage a standard technique for improving scan pruning on a large permanent table.
- D. Incorrect.
Incorrect. A CROSS JOIN would change query semantics unless additional logic is added, and it is not a performance tuning method for this scenario. Snowflake's optimizer already determines efficient join strategies. The profile shows the bottleneck is scanning too much of SALES_FACT, not join ordering.