COF-C03 Question 257
Single answerQuery Profile/Query insightsA data engineer notices that a reporting query that usually finishes in under 20 seconds is now taking more than 4 minutes. The warehouse size and data volume have not changed significantly. In Snowsight, the engineer opens Query History and reviews the Query Profile for the slow run. The profile shows that most of the elapsed time is spent in a join step, and Query Insights highlights that a large amount of data was scanned and spilled to local storage during execution. Which action is the BEST next step to improve query performance?
- A
Rewrite the query to reduce the amount of data participating in the join, such as filtering earlier or joining on more selective predicates
- B
Enable Time Travel on the database for a longer retention period so the optimizer can compare more historical versions of the table
- C
Convert the tables involved in the join to temporary tables so that Query Profile can cache the join results automatically
- D
Suspend and resume the warehouse before rerunning the query so the execution graph is rebuilt with less spill
Show answer and explanation
Correct answer: A
Explanation
The key evidence is in the Query Profile and Query Insights: the slowest operator is a join, and the query scanned a large amount of data and spilled to local storage. In Snowflake, Query Profile helps identify where time is spent in the execution plan, while Query Insights surfaces common performance patterns such as excessive scanning or spilling. When a join dominates runtime and spill occurs, the best next step is usually to reduce the amount of data processed before or during the join: push predicates earlier, verify that joins are not unintentionally broad, and ensure the query is not bringing unnecessary rows or columns into the plan. This is more targeted than unrelated administrative changes. Snowflake documentation and best practices around Query Profile emphasize using operator-level details to identify bottlenecks and then tuning the SQL or data access pattern driving those operators.
- A. Correct.
Correct. Query Profile and Query Insights indicate that the bottleneck is the join step, with excessive scan volume and spill to local storage. That pattern commonly points to a join processing more rows than necessary or insufficient pruning before the join. The most effective next step is to reduce the data entering the join by applying filters earlier, validating join conditions, and using more selective predicates where appropriate. This is a practical tuning action aligned with what Query Profile is designed to reveal.
- B. Incorrect.
Incorrect. Time Travel retention affects how long historical data can be queried or recovered, not how Query Profile analyzes performance or how the optimizer resolves a current join bottleneck. Increasing retention would not address heavy scan volume or spilling during execution.
- C. Incorrect.
Incorrect. Temporary tables do not cause Query Profile to cache join results automatically. While temporary or transient tables can be useful in ETL design, they are not a direct remedy for a poorly performing join identified in Query Profile. This option reflects a misconception about how Snowflake caching and execution work.
- D. Incorrect.
Incorrect. Suspending and resuming a warehouse clears local disk cache rather than improving a spill-heavy join. Rebuilding the execution graph is not a documented tuning method for this type of issue. In many cases, clearing cache could make performance worse on the next run because cached data is lost.