COF-C03 Question 252
Single answer4.1 Evaluate query performanceA data engineering team notices that a nightly reporting query has become significantly slower over the last two weeks. The query joins a 5 TB SALES table with several smaller dimension tables and filters on SALE_DATE for the last 7 days. The warehouse size and SQL text have not changed. The team wants to determine why the query is slower before making changes. Which Snowflake feature should they use first to evaluate the query performance and identify the main bottleneck?
- A
Review the Query Profile in Snowsight for the slow query execution
- B
Increase the warehouse size immediately to reduce execution time
- C
Run RECLUSTER on all tables involved in the query
- D
Convert the SALES table to a temporary table to improve scan speed
Show answer and explanation
Correct answer: A
Explanation
When query performance degrades and the SQL text and warehouse size have not changed, the best first step is to inspect execution details using Snowflake's Query Profile in Snowsight. Query Profile shows operator-level statistics and is commonly used to evaluate issues such as poor partition pruning, expensive joins, data skew, remote or local disk spilling, and scan-heavy execution patterns. This aligns with Snowflake best practices: diagnose before tuning. Only after identifying the bottleneck should the team consider actions such as resizing the warehouse, improving clustering on large selective tables, or rewriting the query. Relevant Snowflake documentation includes the sections on Query History and Query Profile in Snowsight, which describe how to analyze execution steps and resource usage for performance troubleshooting.
- A. Correct.
Correct. The Query Profile is the primary Snowflake tool for evaluating query performance at the execution level. It helps identify where time is spent, such as table scan, join processing, partition pruning effectiveness, spilling, or other operator-level bottlenecks. In this scenario, the team wants to understand why performance changed before modifying compute or storage design, so reviewing the Query Profile is the most appropriate first step.
- B. Incorrect.
Incorrect. Increasing warehouse size may improve runtime in some cases, but it does not help determine why the query slowed down. The question asks for the best feature to evaluate performance and identify the main bottleneck first. Scaling compute without analysis is a common but premature response.
- C. Incorrect.
Incorrect. Reclustering all tables is not an appropriate first action. Automatic Clustering may be relevant for large tables if pruning has degraded, but the team should first confirm through performance analysis whether micro-partition pruning or clustering depth is actually the issue. Reclustering every table would add cost and may not address the real bottleneck.
- D. Incorrect.
Incorrect. Temporary tables do not inherently improve scan performance for an existing workload. Table type does not make a large persisted fact table scan faster simply by changing it to temporary. This option reflects a misconception about Snowflake table types and query optimization.