ADA-C01 Question 300
Single answerOperator treeA Snowflake administrator is investigating a dashboard query that suddenly became much slower after a schema change. In Snowsight, the Query Profile shows the operator tree for the statement. One branch contains a TABLE SCAN operator reading far more micro-partitions than expected, followed by a FILTER operator that removes most of the rows. The administrator wants to identify the most likely reason for the slowdown and the best next step.
Which action should the administrator take?
- A
Review whether the query predicates can be made more selective and aligned with columns used for micro-partition pruning, because the operator tree suggests excessive scanning before filtering.
- B
Increase the warehouse size immediately, because a FILTER operator after a TABLE SCAN indicates insufficient compute rather than a data access issue.
- C
Recreate the table as a temporary table, because temporary tables use a different operator tree that reduces scan cost for filtered queries.
- D
Disable result caching for the session, because the operator tree indicates cached results are preventing predicate pushdown.
Show answer and explanation
Correct answer: A
Explanation
The operator tree in Query Profile helps administrators identify where time and resources are spent during execution. A TABLE SCAN operator reading substantially more data than expected, followed by a FILTER operator that eliminates most rows, typically indicates that Snowflake could not prune enough micro-partitions or could not apply filtering as efficiently as intended. In practice, administrators should inspect the filter predicates, confirm that they are sargable for pruning purposes, review clustering depth or clustering keys if relevant, and check whether recent schema or query changes introduced expressions/casts on filter columns that reduced pruning effectiveness.
This aligns with Snowflake best practices around using Query Profile to analyze execution plans and focusing on data scanned, partition pruning, and selective predicates before simply resizing warehouses. Warehouse scaling can help throughput and execution speed, but the operator tree here points first to an access-path inefficiency rather than a pure compute bottleneck.
- A. Correct.
Correct. In the operator tree, a TABLE SCAN that reads many micro-partitions followed by a FILTER that discards most rows is a classic sign that pruning was ineffective or that predicate filtering was not applied as early/selectively as expected. The administrator should examine the query predicates, clustering characteristics, and whether expressions on filter columns are limiting pruning. This is the most likely cause of the slowdown after a schema or query-pattern change.
- B. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed time in some cases, but it does not address the root cause shown by the operator tree. When the plan shows excessive data being read and then filtered out, the issue is usually inefficient data access or poor pruning, not simply lack of compute. Scaling up can mask the symptom while leaving unnecessary scanning in place.
- C. Incorrect.
Incorrect. Temporary tables do not use a special operator tree that inherently improves filtered query performance. Query execution operators are determined by the optimizer and physical data access patterns, not by converting a permanent table to a temporary one. This option reflects a misconception about table type and execution profiling.
- D. Incorrect.
Incorrect. Result cache is used to return a previous query result without re-executing the query. If a full operator tree is shown for the current execution, Snowflake has executed the statement rather than serving it entirely from result cache. Disabling result caching would not improve predicate pushdown or micro-partition pruning and could worsen performance for repeat queries.