COF-C03 Question 292
Single answerWarehouse cacheA BI team runs the same dashboard query every 10 minutes against a large fact table. The query performance is fast during business hours, but after the virtual warehouse is suspended overnight and resumes in the morning, the first few dashboard refreshes are noticeably slower. The SQL text and underlying table data have not changed. Which action would best reduce the morning slowdown while preserving the benefit of warehouse cache?
- A
Configure the virtual warehouse with AUTO_SUSPEND set to a longer interval so it is less likely to suspend between periods of repeated use
- B
Enable multi-cluster on the warehouse so each cluster keeps a shared cache that survives suspend and resume events
- C
Increase the warehouse size by one level because resizing preserves the existing local disk cache across restarts
- D
Rely on the result cache because it is stored in the warehouse and will be warmed again when the warehouse resumes
Show answer and explanation
Correct answer: A
Explanation
This scenario tests understanding of Snowflake's warehouse cache, often called the local disk cache on a virtual warehouse. Repeated queries against the same table can run faster because previously scanned micro-partitions may already be cached on the warehouse's local storage. However, when the warehouse suspends, that cache is cleared. As a result, the first executions after resume may be slower until the cache is rebuilt. The best practical action is to keep the warehouse running longer by increasing AUTO_SUSPEND, assuming the performance benefit justifies the added compute cost. Candidates should distinguish warehouse cache from persisted query results (result cache), which is separate from the warehouse, and from metadata caching, which is also different. Snowflake documentation and best practices emphasize that warehouse cache is tied to the running warehouse and is not preserved through suspension.
- A. Correct.
Correct. Snowflake's warehouse cache refers to data cached on the local disk of the virtual warehouse. This cache improves performance for repeated access to the same micro-partitions, but it is lost when the warehouse suspends. If the workload is periodic and frequent, increasing the AUTO_SUSPEND interval can keep the warehouse running between executions so the local disk cache remains available. This is a common trade-off between compute cost and performance.
- B. Incorrect.
Incorrect. Multi-cluster warehouses help with concurrency by adding clusters, but each cluster has its own local cache. The cache is not a shared cache across clusters, and suspend/resume still clears the local disk cache for a warehouse cluster. This option confuses concurrency scaling behavior with cache persistence.
- C. Incorrect.
Incorrect. Increasing warehouse size may improve raw compute performance, but it does not preserve warehouse local disk cache across suspend/resume cycles. In fact, changes in warehouse state such as restart/suspend can result in cache loss. This option reflects the misconception that cache is a persistent warehouse property rather than tied to running compute resources.
- D. Incorrect.
Incorrect. The result cache is not stored in the warehouse local disk cache. Snowflake's persisted query results are managed separately from the virtual warehouse and can be reused when the exact same query and conditions are met. However, the scenario specifically points to slower first runs after warehouse resume despite unchanged SQL and data, which is characteristic of warehouse cache loss, not result cache warming.