ARA-C01 Question 409
Single answerCachingA Snowflake architect is investigating why a dashboard query that runs every 5 minutes is not consistently returning in under 1 second. The dashboard issues the exact same SELECT statement each time against a large fact table. The warehouse remains running throughout business hours, no data is loaded into the fact table during the day, and role/privilege settings are unchanged. However, analysts report that the query is fast immediately after one run, then becomes slow after the warehouse is suspended overnight and resumed the next morning. Which Snowflake caching behavior best explains this pattern?
- A
The result cache is cleared whenever a warehouse is suspended, so the query must fully re-execute after resume.
- B
The local disk cache used by the virtual warehouse is lost when the warehouse suspends, so resumed warehouses cannot reuse previously cached micro-partition data.
- C
The metadata cache is tied to each user session, so a new morning session prevents cache reuse even when the SQL text is identical.
- D
The remote result cache is only available for 5 minutes, so the query becomes slow after that period even if no data changes.
Show answer and explanation
Correct answer: B
Explanation
This scenario distinguishes among Snowflake's caching layers. Snowflake uses multiple forms of caching, including persisted query results (result cache), warehouse local data cache, and metadata-related optimizations. Because the SQL text is unchanged and the underlying table data is not modified during the day, the architect might initially suspect result cache behavior. However, the key clue is that performance degrades specifically after the warehouse is suspended and resumed. That points to the virtual warehouse's local disk cache being lost when compute resources are shut down. During the day, repeated access can benefit from cached micro-partition data on the warehouse, improving latency. After overnight suspension, that data must be fetched again. By contrast, persisted query results are not cleared just because a warehouse suspends. Snowflake documentation describes persisted query results separately from the warehouse cache and notes that warehouse cache is removed when a warehouse is suspended. Best practice is to understand which cache layer affects a given workload: exact-repeat queries may use persisted results, while broader scan performance often depends on the warehouse data cache remaining warm.
- A. Incorrect.
Incorrect. Persisted query results (result cache) are not tied to the running state of a warehouse. If the exact same query is issued and underlying data has not changed, Snowflake can reuse persisted results even after warehouse suspension, subject to result cache eligibility and retention rules. A common misconception is that all cache types are warehouse-local; persisted query results are not.
- B. Correct.
Correct. The virtual warehouse data cache (often described as the local disk cache) stores micro-partition data that has been read from cloud storage. This cache exists on the warehouse compute resources themselves. When the warehouse suspends, those resources are released, and the local cache is lost. After resume, queries may need to read data again from remote storage, making them slower until the cache is repopulated.
- C. Incorrect.
Incorrect. Metadata caching is not primarily a per-user-session mechanism that explains this overnight pattern. While session context can affect whether a cached query result is reusable, the described behavior specifically aligns with warehouse suspension causing loss of the warehouse's local data cache, not metadata cache invalidation due to a new session.
- D. Incorrect.
Incorrect. Persisted query results are not limited to 5 minutes. Snowflake's persisted query results are generally available for up to 24 hours when eligible, and the token for large results has separate timing considerations. The 5-minute dashboard interval in the scenario does not explain the slowdown after an overnight suspend.