COF-C03 Question 256
Single answerQuery Profile/Query insightsA data engineering team notices that a nightly transformation query, which usually finishes in under 2 minutes, now takes more than 15 minutes. The SQL text has not changed. In Snowsight, the engineer opens Query Profile and sees that one join operator consumes most of the elapsed time and processes far more rows than expected before applying a later filter. Which action is the best next step to reduce the query runtime?
- A
Rewrite the SQL so selective filters are applied earlier, reducing the number of rows entering the expensive join
- B
Increase the warehouse size immediately, because Query Profile proves the query is CPU-bound
- C
Disable result caching for the session so Snowflake can generate a more efficient execution plan
- D
Convert the tables involved in the join to temporary tables so Query Profile shows fewer stages
Show answer and explanation
Correct answer: A
Explanation
Query Profile is used to analyze execution details such as operator timing, row counts, and where data volume expands unexpectedly. In this scenario, the important signal is that the expensive join processes too many rows before a later filter reduces them. The most appropriate next step is to change the query so selective predicates are applied earlier when possible, reducing intermediate result sizes and join cost. This aligns with Snowflake performance tuning best practices: use Query Profile to identify bottlenecks, then optimize SQL logic and data reduction before simply adding compute. Warehouse resizing can be useful, but it should follow understanding the bottleneck rather than being the first response. Relevant Snowflake documentation areas include Query Profile in Snowsight and general query performance optimization guidance.
- A. Correct.
Correct. Query Profile helps identify where time is spent in the execution plan. If a join is processing many more rows than expected and filtering happens later, a strong optimization step is to rewrite the query so predicates are pushed earlier or otherwise reduce the data volume before the join. This addresses the root cause shown in the profile rather than masking it with more compute.
- B. Incorrect.
Incorrect. Increasing warehouse size can sometimes reduce elapsed time, but the profile described does not by itself prove the query is specifically CPU-bound. The key issue is excessive row processing at the join before filtering. Scaling up may help somewhat, but it does not directly address inefficient query logic revealed by Query Profile.
- C. Incorrect.
Incorrect. Result cache usage is unrelated to improving the execution plan for a query that is already running slowly due to excess rows in a join. Disabling result caching would typically remove a performance benefit rather than improve the underlying execution path.
- D. Incorrect.
Incorrect. Changing permanent or transient tables to temporary tables does not inherently reduce the number of stages or improve the execution strategy shown in Query Profile. Query performance issues identified in the profile are more commonly addressed through SQL rewrites, clustering choices where appropriate, pruning, and warehouse sizing based on the actual bottleneck.