ADA-C01 Question 268
Select 33.5 Perform queries in Snowflake.A Snowflake administrator is troubleshooting a dashboard query that has become slow and expensive. The query runs every 5 minutes and joins a very large SALES table to several small dimension tables, then filters for the last 7 days of data. Business users only need the result set and do not require the underlying raw rows. The administrator wants to determine whether Snowflake is scanning excessive data and whether repeated runs can be optimized without changing the warehouse size. Which actions should the administrator take to investigate and improve this query? (Choose two.)
- A
Use Query Profile to inspect operator-level details such as partitions scanned, bytes scanned, join behavior, and spill indicators for the slow query.
- B
Rewrite the workload to use a materialized view on the result-producing query if it meets supported materialized view constraints and the maintenance cost is justified.
- C
Run the query with RESULT_SCAN on the previous query ID so Snowflake will avoid rescanning base tables for all future executions by any session.
- D
Increase STATEMENT_TIMEOUT_IN_SECONDS so the query has more time to complete and can reduce total credits consumed.
- E
Use QUERY_HISTORY to compare execution characteristics across runs, including elapsed time, compilation time, rows produced, and bytes scanned.
Show answer and explanation
Correct answers: A, B, E
Explanation
The best choices are to investigate with Query Profile and QUERY_HISTORY, then consider a materialized view if the repeated result pattern and SQL constraints make it appropriate. In Snowflake, query tuning often begins with execution analysis: QUERY_HISTORY helps identify trends across repeated runs, while Query Profile provides detailed internals for a specific execution. For recurring dashboard queries that repeatedly compute the same result, materialized views can improve performance by precomputing and maintaining results, though they introduce maintenance cost and have SQL limitations. RESULT_SCAN is not a persistent optimization mechanism; it only accesses a prior query result set. Adjusting statement timeout also does not improve query efficiency. These approaches align with Snowflake best practices for query troubleshooting and performance optimization using account usage/information schema history views, Query Profile, and supported acceleration techniques such as materialized views.
- A. Correct.
Correct. Query Profile is one of the primary tools for analyzing query execution in Snowflake. It provides detailed operator-level insight into how the query executed, including bytes scanned, partitions scanned, pruning effectiveness, join types, data redistribution, and whether any operations spilled to local or remote storage. For a query joining a large fact table to small dimensions, this helps determine whether excessive scanning or inefficient joins are contributing to poor performance and high cost.
- B. Correct.
Correct. If users repeatedly need the same derived result and not the raw underlying rows, a materialized view can be an effective optimization in some scenarios. Snowflake can precompute and maintain query results for supported patterns, reducing repeated computation at query time. This is particularly relevant for frequent dashboard queries, provided the SQL is compatible with materialized view limitations and the administrator evaluates the tradeoff of ongoing maintenance credits and storage.
- C. Incorrect.
Incorrect. RESULT_SCAN lets a session query the results of a previously executed statement by referencing its query ID, but it does not create a persistent optimization for all future executions. It is useful for reprocessing an existing result set, not for making recurring dashboard queries automatically avoid scanning base tables in later runs across sessions. This option reflects a common misunderstanding between querying stored results and query acceleration features.
- D. Incorrect.
Incorrect. Increasing STATEMENT_TIMEOUT_IN_SECONDS only changes how long Snowflake allows a statement to run before timing out. It does not improve query efficiency, reduce scanned data, or lower credit consumption. In fact, allowing an inefficient query to run longer may increase cost rather than reduce it.
- E. Correct.
Correct. QUERY_HISTORY is useful for comparing repeated executions over time. It allows the administrator to review elapsed time, queued time, compilation time, rows returned, and bytes scanned, which helps confirm whether the query is consistently rescanning large amounts of data and whether changes improve performance. This is a practical first step when troubleshooting recurring dashboard workloads.