COF-C03 Question 253
Single answer4.1 Evaluate query performanceA data engineering team notices that a dashboard query, which filters a 4 TB SALES table by ORDER_DATE and REGION and runs every 15 minutes, has become much slower over the past week. The SQL text has not changed. A Snowflake administrator needs to determine why the query is slow and identify the most appropriate evidence in Snowflake to evaluate its performance. Which action should the administrator take?
- A
Review the query in Query Profile to check execution steps, bytes scanned, partition pruning, and whether time is spent in scanning, joins, or spilling
- B
Increase the Time Travel retention period on the SALES table so Snowflake can use older micro-partitions to optimize the query plan
- C
Convert the SALES table to a temporary table to reduce metadata overhead and improve pruning for repeated dashboard queries
- D
Run the query with RESULT_SCAN on the previous query ID to measure the performance of the original query execution
Show answer and explanation
Correct answer: A
Explanation
To evaluate query performance in Snowflake, administrators should use Query History and especially Query Profile. Query Profile shows how the query actually executed, including scan operators, partition pruning, bytes scanned, join operations, and any spilling to local or remote storage. In this scenario, the SQL text is unchanged, so likely causes include reduced pruning due to changing data distribution, larger underlying data volume, clustering degradation, or warehouse resource pressure. Snowflake best practices for troubleshooting performance focus on reviewing query execution details first before making tuning changes. Relevant Snowflake documentation includes Query Profile, Query History, and performance optimization guidance related to micro-partitions, partition pruning, and warehouse behavior.
- A. Correct.
Correct. Query Profile is the primary Snowflake tool for evaluating query performance. It provides operator-level details such as scan volume, partition pruning effectiveness, join behavior, remote/local disk spilling, and time spent in each execution step. For a query that suddenly becomes slower without SQL changes, Query Profile helps determine whether more micro-partitions are being scanned, whether pruning has degraded, whether data skew or join processing changed, or whether the warehouse is spilling due to memory pressure.
- B. Incorrect.
Incorrect. Time Travel retention controls how long historical table versions are available for recovery and historical queries. It does not improve query performance or allow Snowflake to optimize using older micro-partitions. A candidate might choose this because older table versions exist physically, but Time Travel is a data protection feature, not a query tuning mechanism.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped and mainly used for transient intermediate data. Converting a large production fact table used by dashboards into a temporary table is not a valid performance troubleshooting approach and would not inherently improve partition pruning. Partition pruning depends on micro-partition metadata and filter selectivity, not on the table being permanent versus temporary.
- D. Incorrect.
Incorrect. RESULT_SCAN reads a previously generated query result set if it is still available; it does not rerun the original query plan or provide insight into why the original query was slow. Someone might select this because it references a prior query ID, but it is used to process stored results, not to analyze execution performance.