DAA-C01 Question 173
Single answerUnderstand how to view and analyze the query execution planA data analyst is troubleshooting a dashboard query in Snowflake that recently slowed from 8 seconds to more than 2 minutes. The query joins a large fact table to two dimensions and applies several filters. The analyst wants to determine which part of the statement is consuming the most time and whether the slowdown is caused by scan volume, join processing, or data movement between operators. Which action will best help the analyst view and analyze the query execution plan for this specific query?
- A
Open the Query Profile for the completed query in Snowsight or Classic Console and inspect the operator graph, including node-level statistics such as elapsed time, rows produced, and bytes scanned
- B
Run DESCRIBE TABLE on each table in the query to identify which columns participate in joins and filters, because DESCRIBE TABLE returns the physical execution order used by Snowflake
- C
Query INFORMATION_SCHEMA.COLUMNS for the participating tables to infer the execution plan, because column metadata includes the join strategy selected by the optimizer
- D
Use SHOW WAREHOUSES to verify warehouse size, because warehouse metadata displays the full execution plan and highlights the slowest join step
Show answer and explanation
Correct answer: A
Explanation
The best answer is to inspect the Query Profile for the completed query. In Snowflake, the Query Profile is the primary interface for viewing and analyzing the execution plan of an executed statement. It presents the plan as a graph of operators and provides runtime statistics that help analysts isolate bottlenecks, including expensive scans, skewed joins, repartitioning or data exchange, and long-running aggregation or sort steps. This makes it far more useful than schema metadata commands when diagnosing a real performance regression.
From a best-practice perspective, analysts should start with the Query History entry for the slow query and then open its Query Profile to compare operator costs and row/byte volumes across steps. Snowflake documentation on query history and query profiling emphasizes using the profile to understand how a query executed and where time was spent. Metadata commands such as DESCRIBE TABLE, INFORMATION_SCHEMA views, or SHOW WAREHOUSES can provide useful context, but they do not replace the execution plan analysis available in the Query Profile.
- A. Correct.
Correct. For a completed query, the most direct and useful way to analyze execution behavior in Snowflake is to open the Query Profile. The profile visualizes the execution plan as an operator graph and exposes node-level metrics such as processing time, rows, bytes scanned, and data exchanged between steps. This is exactly what an analyst needs to determine whether the bottleneck is in scanning, joins, aggregation, or data redistribution.
- B. Incorrect.
Incorrect. DESCRIBE TABLE returns table metadata such as column definitions, types, and defaults. It does not provide the query execution plan or the optimizer's execution order. A candidate might choose this because join and filter columns matter for performance, but DESCRIBE TABLE is not the tool used to inspect how Snowflake actually executed a specific query.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA.COLUMNS contains schema metadata about columns, not runtime execution details. It cannot reveal the join strategy, scan cost, operator timing, or data movement used for a particular query. This distractor reflects a common misconception that metadata views can substitute for execution diagnostics.
- D. Incorrect.
Incorrect. SHOW WAREHOUSES helps review warehouse properties and status, such as size or whether a warehouse is running, but it does not expose a query's execution plan. Warehouse sizing can affect performance, yet this command does not identify the slowest operator or explain how Snowflake executed the joins and filters.