ADA-C01 Question 296
Single answerAnalyze the components of the Query Profile:A Snowflake administrator is investigating a dashboard query that became much slower after a new release. In Snowsight Query Profile, the administrator sees that one join operator consumes most of the elapsed time. The profile for that operator shows a very large number of rows produced compared with the rows read from its inputs, while scan operators below it show normal pruning and no unusual remote disk I/O. Which conclusion is the BEST supported by the Query Profile?
- A
The query is likely suffering from join explosion caused by a low-selectivity or missing join predicate in that part of the plan.
- B
The warehouse is undersized, because Query Profile indicates that all slow joins are primarily caused by insufficient virtual warehouse compute.
- C
The slowdown is most likely due to poor micro-partition pruning, because a large join output always indicates that too many partitions were scanned.
- D
The problem is caused by result cache invalidation, because Query Profile shows execution operators instead of returning a cached result.
Show answer and explanation
Correct answer: A
Explanation
The best answer is that the Query Profile indicates a problematic join, typically join explosion. When analyzing Query Profile, administrators should compare rows in versus rows out at expensive operators, especially joins, aggregations, and sorts. A join that emits disproportionately more rows than it consumes is a strong signal of an incorrect or nonselective join condition. By contrast, pruning issues are diagnosed mainly in scan nodes, where Snowflake exposes partition statistics and scan volume. Similarly, warehouse sizing is a capacity consideration, but Query Profile evidence should drive the conclusion: if the bottleneck is row explosion in the logical plan, increasing warehouse size treats symptoms rather than the underlying query design issue. This aligns with Snowflake performance tuning guidance to use Query Profile to identify expensive operators, scan efficiency, data movement, and row cardinality growth through the plan.
- A. Correct.
Correct. In Query Profile, a join node that outputs far more rows than its inputs strongly suggests row multiplication, often called join explosion. This commonly happens when the join condition is missing, incomplete, or low selectivity, such as an accidental many-to-many join. Because the scan operators already show normal pruning and no unusual remote I/O, the evidence points to the join itself rather than table access.
- B. Incorrect.
Incorrect. Warehouse size can affect query duration, but Query Profile does not support the blanket conclusion that slow joins are primarily due to insufficient compute. If the dominant signal is excessive row production at a join operator, the more direct interpretation is a problematic join pattern. Simply scaling up compute may reduce runtime somewhat, but it does not address the root cause shown by the plan.
- C. Incorrect.
Incorrect. Poor micro-partition pruning is usually inferred from scan behavior, such as many partitions scanned relative to total partitions and large scan volume. In this scenario, the scan operators show normal pruning. A large join output does not by itself mean too many micro-partitions were scanned; it more specifically suggests row multiplication at the join step.
- D. Incorrect.
Incorrect. If a query result were served from the result cache, Snowflake would not execute the full plan in the same way, and the investigation would not center on an expensive join operator within the executed profile. Seeing execution operators does not mean result cache invalidation is the cause of the slowdown; it only indicates the query executed and produced a profile.