ADA-C01 Question 304
Single answerOperator typesA Snowflake administrator is troubleshooting a frequently executed reporting query that has become slower after several new joins were added. In Snowsight's Query Profile, one operator accounts for most of the elapsed time and shows that a very large number of rows are being grouped to calculate SUM() and COUNT() by REGION and PRODUCT_CATEGORY. The administrator wants to identify the operator type most directly responsible for this work so they can focus tuning efforts on pre-filtering rows and reducing grouping cardinality. Which operator type should the administrator focus on?
- A
Aggregate
- B
Join
- C
Sort
- D
TableScan
- E
Result
Show answer and explanation
Correct answer: A
Explanation
In Snowflake Query Profile, operator types help administrators identify where time and resources are being spent. When a query computes grouped metrics such as SUM() and COUNT() with GROUP BY, the relevant operator type is Aggregate. In real-world tuning, administrators often examine whether joins are inflating row counts before aggregation, whether predicates can be pushed earlier to reduce input rows, and whether grouping keys create unnecessarily high cardinality. Snowflake documentation on query profiling and query operators emphasizes using the Query Profile to isolate expensive steps such as scans, joins, sorts, and aggregates. In this scenario, the operator most directly responsible for the grouped calculations is Aggregate, even if upstream joins contributed to the volume of data being aggregated.
- A. Correct.
Correct. The Aggregate operator is responsible for computing grouped and aggregate results such as SUM(), COUNT(), AVG(), MIN(), and MAX(), especially when used with GROUP BY. In this scenario, the profile specifically indicates that a large number of rows are being grouped by REGION and PRODUCT_CATEGORY, which maps directly to aggregate processing. Tuning often involves reducing rows before aggregation, lowering grouping cardinality, or restructuring the query.
- B. Incorrect.
Incorrect. Join operators are responsible for combining rows from multiple inputs based on join conditions. New joins can contribute to overall query cost and may increase the number of rows flowing into later stages, but the scenario explicitly states that the expensive work shown in the Query Profile is grouping rows to calculate SUM() and COUNT(). That is the role of an Aggregate operator, not a Join operator.
- C. Incorrect.
Incorrect. Sort operators handle ordering operations, such as ORDER BY, and may also appear for other execution requirements that need ordered data. Although sorts can be expensive, especially on large intermediate result sets, the described work is not ordering rows but grouping rows and computing aggregate functions. That points to Aggregate rather than Sort.
- D. Incorrect.
Incorrect. TableScan operators read data from underlying table storage and are often important when assessing pruning and I/O efficiency. However, the operator described is spending time grouping rows and calculating aggregate metrics. A scan may feed data into the aggregation stage, but it is not the operator type performing the grouping logic in this scenario.
- E. Incorrect.
Incorrect. Result operators represent final result production or output stages in the plan. They do not perform the core grouped calculation work described here. Candidates may choose this if they confuse the final returned dataset with the actual compute-intensive step, but the bottleneck is the aggregation stage.