ADA-C01 Question 344
Select 2Use data cachingA BI team runs the same dashboard query every 5 minutes against a large SALES_FACT table. The query text is identical each time and the underlying table is refreshed only once per night. During business hours, users report that dashboard response time is inconsistent: sometimes the query returns in seconds, and other times it takes much longer. As the Snowflake administrator, you want to maximize the likelihood that the repeated query benefits from Snowflake caching without changing the SQL logic. Which TWO actions are most appropriate?
- A
Keep the dashboard running on the same virtual warehouse so the warehouse cache remains available between executions.
- B
Set USE_CACHED_RESULT = FALSE at the session level to force Snowflake to reuse local disk cache instead of recomputing the query.
- C
Avoid suspending the warehouse between dashboard refreshes if low latency is more important than warehouse cost.
- D
Rewrite the query with different whitespace and alias formatting so Snowflake treats it as a fresh candidate for persisted query results.
- E
Increase the size of the warehouse because persisted query results are only available on Large or larger warehouses.
Show answer and explanation
Correct answers: A, C
Explanation
This scenario tests practical use of Snowflake caching layers. For repeated dashboard queries, two cache mechanisms are especially relevant: persisted query results and the virtual warehouse data cache. Persisted query results can return a previous result set for identical queries when the underlying data and relevant conditions have not changed. The warehouse data cache stores table data read from storage on the warehouse's local disk, but it is preserved only while the same warehouse continues running. Therefore, keeping the workload on the same warehouse and avoiding suspension between frequent executions are the best actions to maximize cache benefit when SQL cannot be changed. By contrast, setting USE_CACHED_RESULT = FALSE disables result-cache reuse, and changing query text reduces the chance of matching a cached result. Warehouse size affects compute capacity, not eligibility for persisted query results. These behaviors align with Snowflake documentation on persisted query results, warehouse cache/local disk cache, and warehouse suspend/resume behavior.
- A. Correct.
Correct. Snowflake's warehouse data cache (local disk cache) is tied to the running virtual warehouse cluster. Reusing the same warehouse increases the chance that previously read micro-partitions are still cached locally, reducing remote storage reads and improving performance for repeated queries over unchanged data.
- B. Incorrect.
Incorrect. Setting USE_CACHED_RESULT = FALSE disables retrieval of persisted query results, so it makes repeated identical queries less likely to benefit from result caching. It does not force Snowflake to use warehouse local disk cache; that cache is managed automatically and independently of this parameter.
- C. Correct.
Correct. Suspending a warehouse clears its local disk cache because the compute resources are released. If the dashboard runs frequently and consistent low latency is the priority, keeping the warehouse running between executions helps preserve the warehouse cache and improve repeat-query performance.
- D. Incorrect.
Incorrect. Persisted query results are most likely to be reused when the query text is identical and the underlying data has not changed. Changing whitespace or alias formatting can prevent result reuse because Snowflake requires the submitted statement to match the previous one for result-cache reuse. This would reduce, not improve, caching effectiveness.
- E. Incorrect.
Incorrect. Persisted query results are not limited to Large warehouses. Result-cache reuse is a service-level capability and is not dependent on warehouse size in that way. A larger warehouse may improve raw execution speed, but it does not enable persisted query results.