DAA-C01 Question 176
Single answerTroubleshoot query performanceA data analyst notices that a dashboard query that used to finish in under 10 seconds now takes more than 3 minutes. The query joins a 4 TB SALES_FACT table to small dimension tables and filters on SALES_DATE for the last 7 days and REGION = 'WEST'. In Query Profile, most time is spent scanning SALES_FACT, and the profile shows very little partition pruning. The warehouse size has not changed, and there is no noticeable queuing. Which action is the BEST first step to improve this query's performance?
- A
Recluster SALES_FACT on columns commonly used in the filter predicates, such as SALES_DATE and possibly REGION, after confirming the table's clustering depth is poor
- B
Convert the small dimension tables to temporary tables so the optimizer can avoid scanning SALES_FACT repeatedly
- C
Increase the virtual warehouse from MEDIUM to 2XLARGE because larger warehouses automatically improve partition pruning
- D
Rewrite the query to use SELECT DISTINCT before the JOINs so fewer rows are read from SALES_FACT
Show answer and explanation
Correct answer: A
Explanation
This question tests practical troubleshooting using Snowflake Query Profile and an understanding of micro-partition pruning. When a query against a very large table becomes slower and the profile shows heavy scan time with little partition pruning, the first diagnostic and remediation path should focus on table organization and clustering, not just warehouse size. Snowflake stores table data in micro-partitions and can skip irrelevant partitions when filter predicates align well with the metadata for those partitions. If data has become less correlated with common filter columns over time, pruning degrades and scans expand. Best practice is to inspect clustering information, confirm whether the table has poor clustering for the workload, and then consider clustering on the columns most frequently used in selective predicates. Documentation areas relevant to this include Snowflake guidance on Query Profile, micro-partitions and data clustering, clustering keys, and warehouse scaling behavior. The important distinction is that compute scaling can improve throughput, but it does not by itself fix poor pruning.
- A. Correct.
Correct. The key symptom is poor micro-partition pruning on the large fact table, with most execution time spent scanning SALES_FACT. In Snowflake, clustering can improve pruning when queries frequently filter on specific columns and data has become poorly organized over time. The best first step is to verify clustering information and, if needed, define or improve a clustering key aligned to common predicates such as SALES_DATE and possibly REGION. This targets the root cause shown in Query Profile rather than just adding compute.
- B. Incorrect.
Incorrect. Temporary tables do not address the main bottleneck here, which is scanning a large fact table with poor partition pruning. The dimension tables are already small, so changing their table type is unlikely to materially reduce execution time. This option reflects the misconception that join-side changes help when the dominant issue is fact-table scan inefficiency.
- C. Incorrect.
Incorrect. A larger warehouse may reduce elapsed time by adding compute, but it does not automatically improve micro-partition pruning. If the query is scanning many unnecessary partitions, scaling up can make an inefficient scan faster, but it does not solve the underlying data layout problem. Since there is no queuing and Query Profile points to poor pruning, this is not the best first step.
- D. Incorrect.
Incorrect. Using SELECT DISTINCT before joins is not an appropriate optimization for this scenario and may add extra sort or aggregation work. It does not help Snowflake prune micro-partitions in SALES_FACT. This option represents a common but misplaced attempt to reduce row counts logically when the real issue is physical data pruning during scan.