ADA-C01 Question 316
Single answerTroubleshoot common query performance issuesA Snowflake administrator is investigating a BI dashboard query that became significantly slower over the last two weeks. The query joins a 4 TB SALES_FACT table to several small dimension tables and filters on SALES_DATE and REGION. The warehouse size and concurrency have not changed, and Query Profile shows most of the elapsed time is spent scanning SALES_FACT. The administrator also notices that the percentage of partitions scanned for SALES_FACT has increased sharply compared with earlier runs. Which action is the BEST next step to improve performance for this query pattern?
- A
Define a clustering key on SALES_FACT using commonly filtered columns such as SALES_DATE and REGION, then monitor clustering depth and partition pruning
- B
Convert the small dimension tables into temporary tables so Snowflake can cache them more aggressively during dashboard usage
- C
Increase STATEMENT_TIMEOUT_IN_SECONDS for the BI user so the optimizer has more time to generate a better execution plan
- D
Recreate the virtual warehouse to clear its cache and force Snowflake to rebuild micro-partition metadata
Show answer and explanation
Correct answer: A
Explanation
This question focuses on troubleshooting a common Snowflake query performance issue: poor micro-partition pruning on a large table. The strongest clues are that elapsed time is dominated by scanning SALES_FACT and that the percentage of partitions scanned has risen over time. In Snowflake, query performance on large tables often depends on effective partition pruning based on filter predicates. When data becomes less well ordered for common filters, more micro-partitions must be scanned, increasing latency. A practical remediation is to evaluate and implement clustering on columns frequently used in selective filters and joins when justified by workload and table size. Administrators should use Query Profile to confirm scan-heavy behavior and then review clustering-related information to determine whether reclustering will help. This aligns with Snowflake best practices around using Query Profile for diagnosis, leveraging micro-partition pruning, and applying clustering keys selectively to large tables with performance-sensitive query patterns.
- A. Correct.
Correct. The scenario indicates degraded partition pruning on a very large fact table: Query Profile shows scan time dominating execution, and the percentage of partitions scanned has increased. In Snowflake, clustering can improve pruning when large tables are frequently filtered on specific columns. Defining an appropriate clustering key on columns commonly used in selective predicates, such as SALES_DATE and possibly REGION, is the best next step. Afterward, the administrator should validate the benefit by monitoring clustering information and query profile metrics.
- B. Incorrect.
Incorrect. Temporary tables do not address the root cause described here. The bottleneck is scanning the large SALES_FACT table, not repeatedly accessing small dimension tables. While result cache, local disk cache, and metadata may help in some situations, converting dimensions to temporary tables does not improve micro-partition pruning on the fact table.
- C. Incorrect.
Incorrect. STATEMENT_TIMEOUT_IN_SECONDS controls how long a statement is allowed to run before timing out. It does not improve optimization quality or reduce scan volume. Someone might choose this option because the query is slow, but the problem is not timeout management; it is inefficient data pruning on a large table.
- D. Incorrect.
Incorrect. Recreating the warehouse would remove the local disk cache rather than improve performance, and micro-partition metadata is maintained by Snowflake independently of warehouse recreation. The issue described is not stale cache or missing metadata rebuilds; it is that more micro-partitions are being scanned, which points to clustering/pruning issues.