DAA-C01 Question 175
Single answerTroubleshoot query performanceA data analyst investigates a dashboard query that used to finish in under 10 seconds but now takes more than 4 minutes. The query filters a large FACT_SALES table on ORDER_DATE and REGION, then joins to a small DIM_STORE table. The analyst checks Query Profile and sees that most elapsed time is spent in the scan of FACT_SALES, with a very high percentage of micro-partitions scanned compared to total micro-partitions. There is no significant queueing time, and the join itself is not the main bottleneck. Which action would most directly address the root cause of the slowdown?
- A
Increase the warehouse size so the same scan can be processed by more compute resources
- B
Define a clustering key on FACT_SALES using the frequently filtered columns, such as ORDER_DATE and REGION
- C
Convert DIM_STORE into a temporary table so the join can complete faster
- D
Rewrite the query to use a CROSS JOIN and filter rows afterward
Show answer and explanation
Correct answer: B
Explanation
The key diagnostic clue is in Query Profile: the scan of FACT_SALES dominates elapsed time, and a very high percentage of micro-partitions are being scanned. In Snowflake, this usually indicates poor micro-partition pruning. For large tables with repeated filtering patterns, clustering can improve pruning efficiency by co-locating related rows in micro-partitions based on commonly filtered columns. In this scenario, a clustering key on ORDER_DATE and REGION is the most direct fix because those are the columns used in the filter predicate. By contrast, resizing the warehouse helps parallelize work but does not solve poor pruning. Since there is no significant queueing, concurrency is not the issue. Also, the join is not the bottleneck, so changing the dimension table type or rewriting to a CROSS JOIN would not be appropriate. This aligns with Snowflake best practices for troubleshooting query performance using Query Profile, partition pruning, and clustering guidance in Snowflake documentation.
- A. Incorrect.
Increasing warehouse size can reduce execution time for compute-bound workloads, but the scenario specifically shows poor pruning on FACT_SALES, indicated by a high percentage of micro-partitions scanned. When the bottleneck is excessive scanning due to ineffective partition pruning, simply adding compute does not directly fix the root cause. It may improve runtime somewhat, but it does not address the underlying table organization problem.
- B. Correct.
This is correct. Snowflake stores data in micro-partitions and uses metadata for partition pruning. If queries frequently filter on ORDER_DATE and REGION, and Query Profile shows too many micro-partitions are being scanned, improving clustering on those columns can help Snowflake prune more partitions and reduce scan volume. This directly targets the observed root cause in the profile.
- C. Incorrect.
The scenario states that the join to the small dimension table is not the main bottleneck. Making DIM_STORE temporary does not improve pruning or reduce scanning of FACT_SALES. This option reflects a misconception that table type alone improves performance for unrelated scan issues.
- D. Incorrect.
A CROSS JOIN followed by filtering would typically increase intermediate row volume and make performance worse, not better. It does not address the excessive scan of FACT_SALES and is contrary to good query design for selective joins.