ADA-C01 Question 332
Single answerExplain the impact on the local disc cache when a warehouse is suspended or resumedA data engineering team runs a MEDIUM virtual warehouse every morning to execute the same set of large reporting queries against stable data. To reduce cost, the warehouse is configured with AUTO_SUSPEND after 5 minutes of inactivity. The team notices that when the warehouse resumes after being suspended, the first query run is often slower than subsequent runs, even though the SQL text is unchanged. Which explanation best describes what is happening?
- A
When the warehouse is suspended, its local disk cache is cleared, so resumed compute nodes must repopulate cached micro-partition data from remote storage.
- B
Suspending a warehouse preserves the local disk cache for 24 hours, but the cache is unavailable until a manual refresh command is issued after resume.
- C
The warehouse resume process automatically restores the prior local disk cache from cloud services, so the slower first run must be caused by result cache expiration only.
- D
Suspending a warehouse invalidates the metadata cache in the Cloud Services layer, which forces recompilation of all SQL and causes slower performance after resume.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests understanding of Snowflake virtual warehouse caching behavior. The key point is that the local disk cache belongs to the warehouse's compute resources. When a warehouse is suspended, those resources are deprovisioned, and the local disk cache is lost. After the warehouse resumes, queries may run more slowly at first because data must be read again from remote storage before the cache is warmed. This is a common real-world reason why the first execution after resume is slower than later executions. Candidates should also distinguish local disk cache from the result cache and from Cloud Services metadata/optimization layers. Snowflake documentation on warehouse caching and virtual warehouse behavior explains that local disk cache improves performance for repeated data access, but it is not preserved across warehouse suspension.
- A. Correct.
Correct. Snowflake's virtual warehouse uses a local disk cache on the compute resources to store frequently accessed table data (for example, micro-partitions). When the warehouse is suspended, those compute resources are released, and the local disk cache does not persist across the suspend/resume cycle. After resume, the warehouse starts with a cold local cache, so the first query may need to fetch data again from remote storage. Subsequent queries can be faster once the local cache is warmed.
- B. Incorrect.
Incorrect. There is no requirement or capability to manually refresh a preserved local disk cache after resume. The misconception is that suspension behaves like a paused VM whose attached local cache remains intact. In Snowflake, suspending a warehouse releases the compute resources, so the local disk cache is not retained for reuse after resume.
- C. Incorrect.
Incorrect. Snowflake does not restore the warehouse's previous local disk cache from cloud services when a warehouse resumes. Result cache can make repeated queries fast in some cases, but this option is wrong because it ignores the loss of the local disk cache across suspension. Also, result cache and local disk cache are different mechanisms with different behaviors.
- D. Incorrect.
Incorrect. This confuses warehouse local disk cache with other caching and optimization layers. Suspending a warehouse does not broadly invalidate metadata in Cloud Services in the way described, nor does it inherently force recompilation of all SQL. The practical performance impact after resume is typically due to loss of the warehouse's local disk cache, not a mandatory metadata-cache reset.