DAA-C01 Question 174
Single answerUnderstand how to view and analyze the query execution planA data analyst notices that a dashboard query against Snowflake has become significantly slower after new fact data was loaded. The query joins a large SALES fact table to two dimensions and filters on ORDER_DATE and REGION. The analyst wants to determine whether the slowdown is caused primarily by excessive scanning, poor join efficiency, or data movement between execution steps. Which action would BEST help the analyst view and analyze the query execution plan for this specific query?
- A
Open the query in Snowsight and inspect the Query Profile graph for operators such as TableScan, Join, and Aggregate, including row counts, bytes scanned, and time spent in each node.
- B
Run DESCRIBE TABLE on the SALES table to retrieve the execution plan details for the slow query and identify which join step caused the delay.
- C
Query INFORMATION_SCHEMA.COLUMNS for the SALES table to determine which execution step moved the most data between nodes.
- D
Use SHOW WAREHOUSES to review warehouse size and infer the exact execution plan bottleneck from the warehouse state.
Show answer and explanation
Correct answer: A
Explanation
To analyze a specific Snowflake query's execution behavior, the best practice is to use Query History and open the Query Profile in Snowsight. The Query Profile presents the physical execution graph and operator statistics, allowing analysts to examine where time was spent, how much data was scanned, and how joins and aggregations behaved. This is especially useful for diagnosing issues such as large scans due to poor pruning, expensive joins, or imbalanced execution steps. By contrast, metadata views such as INFORMATION_SCHEMA.COLUMNS or commands like DESCRIBE TABLE and SHOW WAREHOUSES do not provide operator-level execution details for a query. Snowflake documentation and performance-tuning guidance consistently point users to Query History and Query Profile for execution-plan analysis and query troubleshooting.
- A. Correct.
Correct. In Snowflake, the Query Profile in Snowsight is the primary tool for viewing and analyzing a query's execution plan after the query runs. It shows the execution graph and operator-level details such as scans, joins, partitions scanned, bytes processed, row counts, and timing. This is exactly the right place to investigate whether slowness is due to large table scans, expensive joins, or skewed/expensive steps.
- B. Incorrect.
Incorrect. DESCRIBE TABLE returns metadata about the table schema and properties, not the execution plan of a previously run query. A common misconception is assuming object metadata commands expose runtime plan details, but query execution analysis is performed through query history and Query Profile.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA.COLUMNS provides column metadata, not execution-plan or operator-level runtime information. While metadata can help understand schema design, it cannot identify which execution step moved the most data or consumed the most time.
- D. Incorrect.
Incorrect. SHOW WAREHOUSES can help assess warehouse status, size, and configuration, but it does not display the query execution plan. Warehouse sizing may affect performance overall, yet it cannot be used to infer the exact operator-level bottleneck for a specific query.