ADA-C01 Question 324
Single answerUse an explain planA Snowflake administrator is asked to help diagnose why a business-critical reporting query is slow even after the warehouse size was increased. The administrator wants to understand how Snowflake plans to execute the SQL before running more tuning experiments in production. Which action should the administrator take to get the most appropriate pre-execution insight from Snowflake?
- A
Run EXPLAIN on the SQL statement to view the logical execution plan, including operations such as scans, joins, and aggregations before execution
- B
Run the query and then inspect QUERY_HISTORY, because EXPLAIN only works after a statement has completed
- C
Use EXPLAIN to retrieve the exact runtime metrics, including spilled bytes, remote disk I/O, and final execution duration without executing the query
- D
Use RESULT_SCAN on the SQL text, because RESULT_SCAN returns the optimizer plan that Snowflake would use for the query
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use EXPLAIN. In Snowflake, EXPLAIN provides the optimizer's planned operations for a statement before it executes, which is valuable when administrators want to inspect likely scans, joins, and aggregation behavior without incurring the cost or risk of repeatedly running a production query. This is especially useful when a larger warehouse has not resolved the issue, suggesting the problem may relate more to query shape, pruning effectiveness, join patterns, or data access strategy than raw compute alone. By contrast, runtime metrics such as spill to local or remote storage, bytes scanned, and operator timings are available only after execution through the query profile and query history views/functions. Snowflake documentation and best practices distinguish clearly between pre-execution plan inspection with EXPLAIN and post-execution performance analysis with query profile and QUERY_HISTORY.
- A. Correct.
Correct. EXPLAIN returns Snowflake's planned execution steps before the query is run. It is used to inspect the logical plan and understand how the optimizer intends to process the statement, such as table scans, join strategy, and aggregation steps. This is the appropriate tool when the administrator wants pre-execution insight without repeatedly running a potentially expensive production query.
- B. Incorrect.
Incorrect. QUERY_HISTORY is useful after a query has executed and can help correlate elapsed time, warehouse usage, and query profile access, but it does not replace EXPLAIN for pre-execution planning insight. The misconception is that planning visibility is only available post-execution; in Snowflake, EXPLAIN can be used before running the statement.
- C. Incorrect.
Incorrect. EXPLAIN does not provide actual runtime metrics such as bytes spilled, remote disk usage, or final duration, because those values are only known after execution. Those details come from the executed query's profile and history. A common misunderstanding is to treat an explain plan as if it were an execution profile.
- D. Incorrect.
Incorrect. RESULT_SCAN returns the result set of a previously executed query identified by query ID or session context. It does not generate or expose the optimizer plan for arbitrary SQL text. This distractor reflects confusion between working with prior query results and analyzing a statement's execution plan.