ARA-C01 Question 377
Single answerQuery profilingA data engineering team notices that a nightly transformation query has become much slower over the past week, even though the virtual warehouse size has not changed. The query joins a 12 TB fact table to several small dimensions and applies a highly selective date filter. When you open the Snowflake Query Profile for the slow runs, most of the elapsed time is concentrated in a table scan node on the fact table, and the profile shows a very high percentage of partitions scanned compared to partitions total. The join operators and aggregation steps consume relatively little time. Which action is the BEST next step based on the query profile findings?
- A
Investigate the clustering of the fact table on the filtered columns and consider reclustering or redefining the clustering key to improve micro-partition pruning
- B
Increase the warehouse size immediately, because table scan time in the profile proves the query is compute-bound rather than storage-pruning-bound
- C
Rewrite the query to replace the joins with correlated subqueries, because Query Profile indicates the joins are the root cause of the slowdown
- D
Disable result caching for the session, because Query Profile scan nodes with high elapsed time usually indicate stale cached results
Show answer and explanation
Correct answer: A
Explanation
This question focuses on using Snowflake Query Profile to diagnose the actual performance bottleneck rather than guessing. In the scenario, the key evidence is: (1) elapsed time is concentrated in the table scan node, (2) a very high percentage of partitions are scanned, and (3) joins and aggregations are not the major contributors. In Snowflake, Query Profile helps identify where time is spent across operators such as table scans, joins, sorts, and aggregations. When a selective predicate still results in many micro-partitions being scanned, the likely issue is weak micro-partition pruning, often due to the table's clustering not aligning well with query predicates. Best practice is to evaluate clustering information for the large table and consider a clustering key that supports the filter access pattern. Relevant Snowflake documentation includes Query Profile guidance for operator-level analysis, micro-partitions and data pruning concepts, and clustering key best practices for large tables with selective filter predicates.
- A. Correct.
Correct. The Query Profile indicates that the dominant cost is the fact table scan, and the profile specifically shows a high number of micro-partitions scanned relative to total partitions. For a highly selective filter, this is a strong signal that partition pruning is ineffective. In Snowflake, improving clustering on commonly filtered columns can reduce the number of micro-partitions scanned and significantly lower scan time. Reviewing clustering depth/cardinality and aligning the clustering key with the filter pattern is the most targeted next step.
- B. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed time in some cases, but the profile evidence here points first to poor pruning rather than insufficient compute. If too many micro-partitions are being scanned, simply adding compute can be an expensive workaround instead of addressing the root cause. Query profiling should be used to identify whether the bottleneck is scan volume, join processing, spilling, or another operator-level issue before scaling the warehouse.
- C. Incorrect.
Incorrect. The scenario explicitly states that join operators consume relatively little time in the profile, so rewriting joins is not supported by the evidence. Replacing joins with correlated subqueries is not a general Snowflake performance best practice and can make execution plans less efficient depending on the query pattern. The misconception is assuming that because the query contains joins, the joins must be the primary issue.
- D. Incorrect.
Incorrect. Result cache behavior is not the issue described by the profile. A slow execution with heavy table scan activity means the query is actually being executed and scanning data. Disabling result caching would not improve pruning and could make repeated executions slower. The misconception is conflating execution-time scan costs with cache freshness or cache invalidation problems.