ADA-C01 Question 301
Single answerOperator treeA Snowflake administrator is troubleshooting a reporting query that became significantly slower after a schema change. In Snowsight, the administrator opens the query profile and reviews the operator tree. One operator near the top of the tree shows a very large output row count compared to its input, and downstream operators consume substantially more data than expected. The administrator wants to identify the most likely root cause of the slowdown using the operator tree. Which conclusion is the BEST fit for this profile?
- A
A join operator is likely producing excessive intermediate rows, possibly because of low join selectivity or an unintended many-to-many join.
- B
The warehouse is undersized, because the operator tree directly reports when a virtual warehouse lacks enough compute clusters.
- C
The result cache was bypassed, because a large output row count in one operator proves the query could not use cached results.
- D
Micro-partition pruning failed globally, because any operator with a larger output than input means all tables in the query were fully scanned.
Show answer and explanation
Correct answer: A
Explanation
The operator tree in Snowflake's Query Profile is used to understand how data flows through execution operators and where cost, rows, and bytes increase unexpectedly. When one operator, especially a join, shows output that is much larger than its input, that is a classic sign of intermediate row explosion. This often happens with missing or incomplete join predicates, non-selective joins, or data model changes that introduce many-to-many relationships. Administrators should use this insight to review join conditions, cardinality, predicate placement, and whether filtering can occur earlier. By contrast, warehouse sizing, result cache usage, and micro-partition pruning are important performance topics, but the specific operator-tree symptom described most strongly indicates a problematic join producing excessive intermediate results. This aligns with Snowflake guidance on using Query Profile and the operator tree to pinpoint expensive operations and bottlenecks in query execution.
- A. Correct.
Correct. In the operator tree, a join that dramatically increases row counts is a strong indicator of join explosion, low selectivity, or an unintended many-to-many relationship. This often causes large intermediate result sets that then increase processing in downstream operators such as aggregations, sorts, or additional joins. In practice, administrators use the query profile and operator tree to identify where rows and bytes expand unexpectedly and then inspect join predicates, data cardinality, and filter placement.
- B. Incorrect.
Incorrect. Warehouse sizing can affect runtime, but the operator tree does not directly declare that the warehouse is undersized. Query Profile helps identify where time and data movement occur, but a large row expansion in a specific operator points more directly to query logic or data characteristics than to warehouse size alone. An administrator might choose this option because slow queries are often associated with compute capacity, but the operator-tree symptom described is more diagnostic of inefficient processing within the plan.
- C. Incorrect.
Incorrect. Result cache usage is not inferred from a single operator's output row count. If a query fully uses the result cache, Snowflake typically returns the cached result instead of executing the underlying plan in the same way. A large intermediate row count inside the operator tree indicates actual execution behavior, not proof about cache eligibility by itself. A candidate might pick this because cached queries are faster, but the operator tree evidence here is about row expansion during execution.
- D. Incorrect.
Incorrect. Poor micro-partition pruning can contribute to scanning more data than necessary, but an operator producing more output rows than input does not prove that all tables were fully scanned or that pruning failed globally. That pattern is more characteristic of a join or row-generating operation than of scan inefficiency alone. This distractor reflects a common misconception that any large data volume in the profile must originate from table scans rather than later operators.