ADA-C01 Question 325
Single answerUse an explain planA Snowflake administrator is helping a data engineering team troubleshoot a slow reporting query before it is moved into production. The team wants to understand how Snowflake plans to execute the SQL and identify whether the query is likely to scan large amounts of data or perform expensive joins, but they do not want to actually run the query against the warehouse yet. Which action should the administrator take?
- A
Run EXPLAIN on the SQL statement to review the logical execution plan without executing the query
- B
Run the query once, then open QUERY_HISTORY to see the execution plan that Snowflake would have used before execution
- C
Use DESCRIBE RESULT on the SQL statement to view join order, partition pruning, and scan estimates without execution
- D
Use SHOW QUERIES to display the optimizer's predicted plan and cost metrics before the query runs
Show answer and explanation
Correct answer: A
Explanation
When a team needs to understand how Snowflake will execute a SQL statement without actually running it, the correct tool is EXPLAIN. This command provides the logical execution plan so administrators can review operators and processing flow before warehouse compute is consumed. That makes it especially useful for pre-production validation, tuning discussions, and identifying likely problem areas such as large scans or complex joins. By contrast, QUERY_HISTORY is oriented toward statements that have already executed, DESCRIBE RESULT returns result-set metadata rather than optimizer details, and SHOW commands do not replace EXPLAIN for plan analysis. This aligns with Snowflake best practice: use EXPLAIN for pre-execution plan inspection and use post-execution monitoring tools separately when analyzing queries that have already run.
- A. Correct.
Correct. The EXPLAIN command returns the query execution plan without actually running the statement. This is the appropriate way to inspect how Snowflake intends to process the SQL, including plan operators such as scans, joins, and aggregations, before consuming warehouse resources for execution. In practice, administrators and engineers use EXPLAIN during tuning to understand whether the optimizer is likely to perform broad scans or complex join operations.
- B. Incorrect.
Incorrect. QUERY_HISTORY is useful after a query has run, and it provides information about executed statements. It is not the right tool when the team explicitly wants to inspect the plan before execution. This option reflects a common misconception that all plan analysis in Snowflake requires prior execution; however, EXPLAIN is specifically designed for pre-execution plan review.
- C. Incorrect.
Incorrect. DESCRIBE RESULT returns metadata about the result set columns of a query, such as names and data types, but it does not provide the execution plan, join strategy, or scan details. Someone might choose this if they confuse result metadata inspection with optimizer plan inspection.
- D. Incorrect.
Incorrect. SHOW QUERIES is not the mechanism for retrieving an optimizer plan prior to execution. SHOW commands expose account objects or activity listings depending on the command, but they do not produce the detailed logical execution plan that EXPLAIN provides. This distractor targets the misconception that SHOW commands are general-purpose inspection tools for all internal planning details.