ADA-C01 Question 272
Single answerUse persisted query resultsA BI team runs the same dashboard query every few minutes against a large FACT_SALES table. The Snowflake administrator notices repeated executions with identical SQL text, but warehouse credits are still being consumed for many of those refreshes. The team wants to maximize reuse of persisted query results so repeated dashboard refreshes return instantly without reprocessing data. Which action is the BEST recommendation?
- A
Ensure the dashboard issues the exact same deterministic query text and avoid changes to the underlying tables between executions.
- B
Increase the warehouse size so Snowflake can cache more result sets for repeated queries.
- C
Rewrite the query to use SELECT * so Snowflake recognizes it as the same result regardless of column changes.
- D
Disable the warehouse cache so Snowflake is forced to use persisted query results instead.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to keep the query text exactly the same and ensure the query remains eligible for result reuse by avoiding underlying data changes that invalidate the result. In Snowflake, persisted query results allow identical, deterministic queries to return previously computed results rather than re-executing. This is distinct from the virtual warehouse data cache. A common misconception is that bigger warehouses or warehouse caching settings control persisted result reuse; they do not. Another misconception is that broadening the query with SELECT * helps matching, when in reality exact text matching and unchanged underlying data are what matter. Snowflake documentation on persisted query results and result reuse emphasizes identical queries, deterministic behavior, and unchanged contributing data as key requirements.
- A. Correct.
Correct. Persisted query results can be reused when Snowflake determines the new query matches a previous query and the underlying data has not changed in a way that invalidates the cached result. In practice, this means the SQL text must be identical and the query must be deterministic. If the base objects change, or if the query includes non-reusable elements, Snowflake will re-execute the statement instead of returning the persisted result.
- B. Incorrect.
Incorrect. Persisted query results are not controlled by warehouse size. They are part of Snowflake's result reuse behavior, separate from increasing compute capacity. A larger warehouse may make execution faster, but it does not improve eligibility for persisted result reuse.
- C. Incorrect.
Incorrect. Using SELECT * is not a best practice for maximizing persisted result reuse. It can make queries more fragile because schema changes or column-order considerations may affect the result shape. Persisted result reuse depends on matching query text and valid underlying data state, not on using SELECT *.
- D. Incorrect.
Incorrect. Warehouse cache and persisted query results are different mechanisms. The warehouse cache relates to local disk caching of table data by a virtual warehouse, while persisted query results are stored separately and can be reused without scanning data again when conditions are met. Disabling warehouse cache does not force Snowflake to use persisted query results.