ADA-C01 Question 303
Single answerOperator nodesA Snowflake administrator is investigating why a critical reporting query is slow even though it scans a modest amount of data and runs on a sufficiently sized virtual warehouse. The Query Profile shows that most elapsed time is concentrated in an AGGREGATE operator node, and the node indicates a large volume of data was received from upstream operators before producing a very small result set. Which action is MOST likely to reduce the runtime of this query?
- A
Rewrite the query so filtering and joins reduce rows earlier, before the AGGREGATE operator processes them
- B
Increase the Time Travel retention period so the AGGREGATE operator can access historical micro-partitions more efficiently
- C
Convert the target table to a temporary table so the AGGREGATE operator can avoid remote disk I/O
- D
Disable result caching for the session so the AGGREGATE operator recalculates intermediate results more efficiently
Show answer and explanation
Correct answer: A
Explanation
This question tests the ability to interpret operator nodes in Snowflake Query Profile and apply that information to tuning decisions. Operator nodes such as AGGREGATE, JOIN, FILTER, and TABLE SCAN help administrators identify where time and resources are spent during execution. When an AGGREGATE node receives a large input and produces a tiny output, the practical tuning approach is to reduce row volume before that operator executes. This can involve pushing predicates earlier, restructuring joins, removing unnecessary columns/rows, or using pre-aggregation patterns. Snowflake documentation and performance best practices emphasize using Query Profile to identify expensive operators and then minimizing scanned and processed data rather than relying on unrelated settings such as Time Travel, object type changes, or cache disabling.
- A. Correct.
Correct. In Query Profile, an AGGREGATE operator that consumes a large number of rows but emits a very small result often indicates that too much data is flowing into the aggregation step. The most effective optimization is usually to reduce data volume earlier in the plan by applying selective filters sooner, avoiding unnecessary joins, or pre-aggregating where appropriate. This aligns with Snowflake performance tuning guidance: reduce the amount of data scanned and processed, and examine heavy operator nodes in Query Profile to identify where row reduction should occur earlier.
- B. Incorrect.
Incorrect. Time Travel retention affects how long historical data versions are available, not the efficiency of an AGGREGATE operator in a current query execution. It does not optimize operator-node processing or reduce the amount of data sent into aggregation.
- C. Incorrect.
Incorrect. Table type does not make an AGGREGATE operator inherently faster. Temporary tables are session-scoped objects and may be useful in some ETL patterns, but simply converting a table to temporary does not address the root cause shown in the operator node: excessive rows reaching the aggregation step.
- D. Incorrect.
Incorrect. Result cache usage can make repeated queries return instantly when eligible, but disabling it does not improve operator execution efficiency. In fact, disabling result caching removes a potential performance benefit. It does not address the underlying issue of too much data being processed before aggregation.