ADA-C01 Question 305
Single answerOperator typesA 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.