ADA-C01 Question 273
Single answerUse persisted query resultsA BI team runs the same dashboard query every few minutes against a large SALES_FACT table and expects near-instant response when no underlying data has changed. An administrator notices that Snowflake is repeatedly scanning warehouse resources instead of returning the cached result. Which change is MOST likely to allow Snowflake to use persisted query results for this workload?
- A
Rewrite the dashboard query so that each execution uses identical SQL text and remove non-deterministic functions such as CURRENT_TIMESTAMP from the SELECT list
- B
Increase the warehouse size so Snowflake can persist more query results in the virtual warehouse local cache
- C
Create a materialized view on SALES_FACT because persisted query results are only used when a materialized view exists
- D
Disable USE_CACHED_RESULT at the session level so Snowflake can recalculate and then store a more reusable cached result
Show answer and explanation
Correct answer: A
Explanation
Snowflake can reuse persisted query results, often called the result cache, when the same query is run again and the previously produced result is still valid. For reuse, the query text must match, the underlying objects and relevant micro-partitions must not have changed in a way that invalidates the result, and session settings such as USE_CACHED_RESULT must allow reuse. Queries containing non-deterministic functions or generated SQL that changes from run to run often prevent cache hits. Administrators should distinguish persisted query results from the warehouse local disk cache: the former is a service-level optimization for eligible repeated queries, while the latter caches table data for a specific running warehouse. Snowflake documentation on persisted query results and USE_CACHED_RESULT supports these behaviors and is the relevant best-practice reference for this scenario.
- A. Correct.
Correct. Persisted query results can be reused only when Snowflake determines the new query matches a previous result and the underlying data has not changed. Reuse is most reliable when the SQL text is identical and the query does not include non-reusable constructs such as non-deterministic functions like CURRENT_TIMESTAMP. If the dashboard tool injects changing literals, aliases, comments, or timestamps into the SQL, Snowflake may not reuse the persisted result.
- B. Incorrect.
Incorrect. Persisted query results are part of Snowflake's result cache behavior, not something expanded by increasing warehouse size. Warehouse sizing affects execution performance, but persisted query result reuse is independent of warehouse local data cache capacity. Confusing the result cache with the warehouse cache is a common misconception.
- C. Incorrect.
Incorrect. Materialized views are not required for persisted query results. They are a separate optimization feature that precomputes and maintains query results for specific definitions. Persisted query results can be reused for eligible repeated queries even without any materialized view.
- D. Incorrect.
Incorrect. Setting USE_CACHED_RESULT to FALSE prevents Snowflake from reusing persisted query results for that session. It forces query re-execution rather than improving cache reuse. This option does the opposite of what the BI team wants.