ADA-C01 Question 327
Select 3Compare and contrast different caching techniques and the impact of caching on performanceA data engineering team runs the same dashboard queries every 5 minutes against a large fact table in Snowflake. The SQL text is unchanged, and the underlying table data is refreshed only once per hour. After a warehouse restart during maintenance, users report that the first dashboard run is much slower, but subsequent runs on the same warehouse return to normal performance. The administrator needs to explain which Snowflake caching behaviors are most relevant to this pattern and what actions would preserve performance benefits. Which TWO statements are correct?
- A
The slower first run after the warehouse restart is primarily due to loss of the warehouse local disk cache, which stores micro-partition data previously read by that virtual warehouse.
- B
Query results cache would not help in this scenario because it is cleared whenever the virtual warehouse is restarted.
- C
If the exact same query is rerun and the underlying data has not changed, persisted query results can allow the result to be returned without compute, even after a warehouse restart.
- D
Suspending and resuming the warehouse preserves the local disk cache, so restart events do not affect cache-warming behavior.
- E
Using a different virtual warehouse for the same dashboard queries can reduce the benefit of the warehouse data cache, because that cache is maintained per warehouse cluster rather than shared globally.
Show answer and explanation
Correct answers: A, C, E
Explanation
Snowflake performance can benefit from multiple caching mechanisms, and administrators need to distinguish them clearly. The most relevant comparison here is between persisted query results and the warehouse local disk cache.
Persisted query results: Snowflake can reuse results for an identical query if the query is eligible for reuse and the underlying data has not changed. This can avoid compute entirely and is not dependent on a specific warehouse remaining warm. That is why an unchanged dashboard query may still return quickly even after a warehouse restart, assuming result reuse conditions are satisfied.
Warehouse local disk cache: This cache stores data previously read by a virtual warehouse from cloud storage. It improves scan performance for repeated access patterns, but it is tied to the warehouse's compute resources. After restart, and commonly after suspend/resume when resources are reprovisioned, the cache may be lost, causing the first run to be slower while data is read again.
A practical implication is that workload routing matters: sending the same repeated BI queries to the same warehouse improves the chance of benefiting from warehouse cache, while moving them across warehouses can reduce those gains. This aligns with Snowflake guidance on understanding query result reuse and warehouse caching behavior when tuning performance and cost.
- A. Correct.
Correct. Snowflake uses a warehouse-level data cache (often described as local disk cache) to store table data read from remote storage. When a virtual warehouse is restarted or newly provisioned, that local cache is empty, so the first query may need to fetch data again from remote cloud storage, increasing latency. This is a common reason why repeated scans become faster after the warehouse has been warmed.
- B. Incorrect.
Incorrect. This reflects a common misconception that all caches are tied to the warehouse lifecycle. Persisted query results are not the same as the warehouse local disk cache. The result cache can still be used after a warehouse restart, provided the query text is reused and other eligibility conditions are met, such as no relevant data changes and no use of non-reusable query constructs.
- C. Correct.
Correct. Snowflake's persisted query results can return results for an identical query when the underlying data and other reuse conditions have not changed. Because this cache is not dependent on the virtual warehouse's local storage, a warehouse restart does not by itself eliminate the ability to reuse persisted results. In many cases, the query can be satisfied without re-executing on compute resources.
- D. Incorrect.
Incorrect. Suspending a warehouse typically releases compute resources, and resuming does not guarantee preservation of the warehouse local disk cache. In practice, cache warming benefits are often lost after suspend/resume or restart because new compute resources may be allocated. Administrators should not assume suspend/resume preserves the local data cache.
- E. Correct.
Correct. The warehouse data cache is associated with the specific virtual warehouse (and, in multi-cluster scenarios, with the individual compute clusters), not a single shared global cache across all warehouses. Running the same workload on a different warehouse often means the new warehouse has a cold cache, reducing the performance benefit seen from repeated scans on the original warehouse.