SnowflakeProfessional levelADA-C01Page 4 of 6

ADA-C01 exam dumps: questions 301 to 400 of 565

Page 4 of the free ADA-C01 question bank for the SnowPro® Advanced: Administrator exam. Questions 301 to 400 are listed below, the first 5 in full with answers and explanations. Back to page 1 for the exam overview and FAQ.

Question bank last updated April 2026

Start timed practice exam Questions 301 to 400

Free ADA-C01 practice questions

Questions 301 to 305 of 565

Pick an answer before you open the explanation. Each question also has its own page with a permalink.

ADA-C01 Question 301

Single answerOperator tree

A 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.

ADA-C01 Question 302

Single answerOperator nodes

A Snowflake administrator is troubleshooting a recurring performance issue on a reporting query that joins a 12 TB SALES_FACT table to a 50 MB DIM_REGION table and then performs an aggregation. Query Profile shows most elapsed time is spent in a Join operator, and the profile indicates significant data movement between processing nodes before the aggregation begins. The warehouse is appropriately sized and is not queued. Which action is most likely to reduce the time spent in the operator nodes for this workload?

A
Define a clustering key on SALES_FACT using the join column used to match DIM_REGION
B
Convert DIM_REGION into a temporary table so Snowflake can keep it local to each operator node
C
Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter so the Join operator has more time to finish
D
Replace the join with a view on SALES_FACT so the optimizer can eliminate operator-node data exchange

Show answer and explanation

Correct answer: A

Explanation

Operator nodes in Snowflake Query Profile represent execution steps such as scans, joins, aggregations, sorts, and exchanges performed across processing nodes in the virtual warehouse. In this scenario, the key clue is significant data movement before aggregation, centered on the Join operator. That usually points to expensive repartitioning/shuffling across nodes. For a very large fact table joined to a small dimension, administrators should examine whether the large table's physical organization supports efficient pruning and join processing. A clustering key on the large table's join column can improve micro-partition organization and reduce the amount of data that must be scanned and redistributed, which can lower operator-node work. The other options reflect common misconceptions: temporary tables do not control node locality, timeouts do not improve performance, and views do not eliminate physical join costs. This aligns with Snowflake best practices around using Query Profile to identify costly operators and considering clustering for large, repeatedly accessed tables with predictable filter or join patterns.

A. Correct.
Correct. When Query Profile shows a Join operator with heavy data movement, one practical tuning approach is to improve pruning and data locality on the large table involved in the join. Clustering SALES_FACT on the join key can reduce the amount of data scanned and shuffled for the join, especially for large fact-to-small-dimension join patterns. While Snowflake automatically handles many physical optimizations, clustering can help specific large-table workloads where join/filter access patterns are stable and Query Profile indicates excessive work in join-related operator nodes.
B. Incorrect.
Incorrect. Temporary tables change object lifespan and session scope, but they do not cause Snowflake to pin data to specific operator nodes for joins. Snowflake's execution engine distributes work across processing nodes dynamically, and table type does not provide node-local placement control in the way implied here.
C. Incorrect.
Incorrect. Increasing STATEMENT_TIMEOUT_IN_SECONDS does not improve execution efficiency. It only allows a query to run longer before timing out. Because the issue is excessive work and data movement in the Join operator, extending the timeout would not reduce elapsed time or operator-node overhead.
D. Incorrect.
Incorrect. Creating or using a view does not inherently remove the need for a join or the associated data exchange between operator nodes. A standard view is a logical abstraction over the underlying query. Unless the query itself changes in a way that reduces join cost, the optimizer still must execute the join against the base data.

ADA-C01 Question 303

Single answerOperator nodes

A 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.

ADA-C01 Question 304

Single answerOperator types

A 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.

ADA-C01 Question 305

Single answerOperator types

A Snowflake administrator is investigating why a frequently executed analytical query is consuming excessive resources on a large warehouse. The administrator reviews the Query Profile and sees a HASH JOIN operator followed by a SORT operator that processes a very large intermediate result set before returning only the top 100 rows ordered by total sales. The goal is to reduce execution time and warehouse consumption without changing the business result. Which action is the MOST appropriate based on the operator types shown in the profile?

A
Rewrite the query to apply filtering and aggregation earlier, so fewer rows reach the HASH JOIN and SORT operators
B
Replace the large warehouse with a multi-cluster warehouse so the HASH JOIN operator uses more local disk
C
Convert the query to use a nested subquery because SORT operators are only expensive when used with window functions
D
Disable result caching so Snowflake can recompute the HASH JOIN with a more efficient operator path

Show answer and explanation

Correct answer: A

Explanation

Snowflake Query Profile exposes operator types such as HASH JOIN, SORT, AGGREGATE, TABLE SCAN, and others so administrators can identify where a query spends time and resources. In this scenario, the important clue is not just the presence of a HASH JOIN and SORT, but that they are operating on a very large intermediate result before the final top-100 output. Best practice is to reduce data volume as early as possible by applying selective predicates, limiting unnecessary columns, and aggregating before expensive downstream operators when the logic permits. While warehouse resizing can sometimes help with memory pressure or spilling, it does not address the root cause as effectively as rewriting the query to reduce rows entering the expensive operators. This aligns with Snowflake guidance on using Query Profile to locate high-cost operators and optimize SQL patterns accordingly.

A. Correct.
Correct. HASH JOIN and SORT are both potentially expensive operators, especially when they process large intermediate row sets. If the query only needs the top 100 rows after ordering by an aggregated metric such as total sales, the most effective optimization is often to reduce the number of rows as early as possible by pushing filters down, pre-aggregating where appropriate, and avoiding unnecessary expansion before the join and sort. This directly targets the expensive operators identified in Query Profile.
B. Incorrect.
Incorrect. A multi-cluster warehouse primarily helps with concurrency, not the performance of a single query plan. It does not change the logical operator choice in a way that specifically optimizes a large HASH JOIN followed by a SORT. Also, adding clusters is not about giving a join operator more local disk; spilled data and operator behavior are better addressed by query design and, if needed, warehouse sizing.
C. Incorrect.
Incorrect. Changing the query to a nested subquery does not inherently improve SORT performance. SORT operators can be expensive whenever they must order a large number of rows, not only in window-function scenarios. This option reflects a common misconception that syntax changes alone will force a meaningfully better operator strategy.
D. Incorrect.
Incorrect. Disabling result caching generally increases cost and execution time because it forces recomputation. Result cache settings do not make Snowflake choose a more efficient join or sort operator for this case. Query Profile analysis is meant to guide tuning of data reduction, join patterns, and ordering workload rather than turning off caching.

Timed practice exam

Take a ADA-C01 practice test under exam conditions

65 questions in 115 minutes, drawn from this bank, with a score report and a per-question review when you finish.

Start timed exam

ADA-C01 practice questions 301 to 400 of 565

Every question has a page with the answer and explanation. Numbers are stable, so you can bookmark or share them. The bank is split into 6 pages of up to 100 questions.