ADA-C01 Question 317
Single answerTroubleshoot common query performance issuesA data engineering team reports that a nightly dashboard query against a 12 TB fact table has become much slower over the past two weeks. The SQL text has not changed. In Snowsight Query Profile, the administrator sees that most elapsed time is spent on a table scan, and the query is filtering on ORDER_DATE and REGION. The warehouse has already been scaled up once, but runtime improved only slightly. Which action is the BEST next step to address the root cause of the performance issue?
- A
Review the table's clustering depth and micro-partition pruning effectiveness for ORDER_DATE and REGION, then recluster if needed
- B
Increase STATEMENT_TIMEOUT_IN_SECONDS so the query has more time to finish on the existing warehouse
- C
Convert the fact table to a temporary table so Snowflake can avoid long-term storage metadata overhead during scans
- D
Disable result caching for the session to force Snowflake to generate a more efficient execution plan
Show answer and explanation
Correct answer: A
Explanation
This scenario points to a classic query performance issue in Snowflake: poor pruning during table scans. Since the SQL has not changed, most time is in the scan operator, and predicates are on ORDER_DATE and REGION, the administrator should investigate whether the table's micro-partitions are still well organized for those filter columns. Snowflake stores data in immutable micro-partitions and relies heavily on partition metadata for pruning. As new data arrives over time, clustering can drift, causing more partitions to be scanned and increasing runtime. In this case, simply scaling the warehouse provides limited benefit because the query is doing unnecessary I/O work. Best practice is to use Query Profile together with table clustering information to confirm poor pruning, then improve clustering if justified by workload patterns. Relevant Snowflake guidance includes using Query Profile to identify scan bottlenecks and evaluating clustering depth/pruning effectiveness for large frequently filtered tables.
- A. Correct.
Correct. When Query Profile shows most time spent in the table scan and the predicates are on specific columns, the most likely root cause is poor micro-partition pruning. As data distribution changes over time, clustering quality can degrade, causing more micro-partitions to be scanned than necessary. Reviewing clustering information and reclustering, or enabling/adjusting clustering strategy where appropriate, directly addresses scan inefficiency. This is a more targeted action than simply adding warehouse compute.
- B. Incorrect.
Incorrect. Increasing STATEMENT_TIMEOUT_IN_SECONDS does not improve query performance; it only allows the query to run longer before timing out. This is a common misconception when teams treat timeout settings as a tuning mechanism. The scenario indicates a performance regression caused by scan work, not by an overly aggressive timeout value.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped objects intended for transient workloads, not a performance tuning feature for large production fact tables. Converting a permanent fact table to temporary would not improve micro-partition pruning or scan efficiency and would create significant data lifecycle and availability problems.
- D. Incorrect.
Incorrect. Disabling result caching would typically make performance worse for repeatable queries because cached results can allow eligible queries to return immediately. It also does not cause Snowflake to produce a fundamentally better execution plan for this scan-heavy workload. The problem described is excessive scanning of table data, not misuse of the result cache.