DAA-C01 Question 98
Single answerSystem functionsA data analyst is troubleshooting a dashboard query that suddenly became much slower after a recent schema change. The analyst has the SQL text of the problematic statement and wants to inspect how Snowflake executed it, including the operators used and any pruning details, without rerunning the query. Which approach should the analyst use?
- A
Call SYSTEM$EXPLAIN_PLAN_JSON with the SQL text to generate the execution plan for the already completed query
- B
Use SYSTEM$GET_QUERY_OPERATOR_STATS with the query ID to retrieve execution operator statistics for the completed query
- C
Call SYSTEM$CLUSTERING_INFORMATION with the table name to view the exact plan and runtime operators for the completed query
- D
Use GET_DDL on the affected tables to compare schema definitions and infer the runtime execution plan
Show answer and explanation
Correct answer: B
Explanation
For investigating how a query actually ran after execution, Snowflake provides SYSTEM$GET_QUERY_OPERATOR_STATS, which returns operator-level runtime statistics for a query ID. This is more appropriate than EXPLAIN-based functions when the requirement is to analyze a completed query without rerunning it. By contrast, SYSTEM$EXPLAIN_PLAN_JSON is useful for generating an explain plan from SQL text, but it reflects planned execution rather than the actual runtime profile. SYSTEM$CLUSTERING_INFORMATION is focused on table clustering characteristics, and GET_DDL is for object definition retrieval. In practice, analysts often combine the query ID from query history with SYSTEM$GET_QUERY_OPERATOR_STATS to diagnose regressions caused by schema changes, pruning issues, or join/operator inefficiencies.
- A. Incorrect.
Incorrect. SYSTEM$EXPLAIN_PLAN_JSON returns explain plan information for a SQL statement text, but it is used to generate a plan estimate rather than retrieve the actual execution details of a query that already ran. It does not provide the runtime operator statistics of the completed statement. A candidate might choose this because it sounds like the right tool for plan inspection, but the question specifically asks for how Snowflake executed the completed query without rerunning it.
- B. Correct.
Correct. SYSTEM$GET_QUERY_OPERATOR_STATS is designed to return operator-level execution statistics for a completed query when provided a query ID. This is the appropriate system function when the analyst wants to inspect actual execution behavior, such as operators involved and runtime details, after the query has already finished.
- C. Incorrect.
Incorrect. SYSTEM$CLUSTERING_INFORMATION provides information about a table's clustering depth and clustering quality. While clustering can affect pruning and performance, this function does not return the execution plan or runtime operator details for a specific completed query. Someone might choose this option because pruning issues are often related to clustering, but the function does not answer the question being asked.
- D. Incorrect.
Incorrect. GET_DDL returns the DDL used to create an object, such as a table or view. It can help identify schema changes, but it does not expose the actual execution plan or operator statistics for a completed query. This is a plausible troubleshooting step for schema comparison, but not the correct system function for execution inspection.