COF-C03 Question 293
Single answerWarehouse cacheA BI team runs the same dashboard queries every 10 minutes against a large fact table. The queries use a virtual warehouse named ANALYTICS_WH and performance is fast after the first run each morning, but much slower after lunch. Investigation shows the warehouse is configured with AUTO_SUSPEND = 300 seconds and AUTO_RESUME = TRUE. The team wants to improve query performance during business hours by taking advantage of the warehouse cache without changing the SQL. Which action would best address this requirement while balancing cost and performance?
- A
Increase the AUTO_SUSPEND setting or keep the warehouse running during active business hours so cached data in the warehouse can be reused
- B
Enable Search Optimization Service on the fact table so the warehouse cache persists even after the warehouse suspends
- C
Resize the warehouse to a larger size because warehouse cache is shared across all warehouses of the same size
- D
Rely on the persisted query results cache, because it will speed up the dashboard queries even when the underlying data changes
Show answer and explanation
Correct answer: A
Explanation
This scenario tests understanding of Snowflake's warehouse cache behavior. Snowflake uses a local disk cache at the virtual warehouse level to retain data read from table storage, which can improve performance for repeated queries. However, this cache is cleared when the warehouse suspends, so aggressive AUTO_SUSPEND settings can reduce the cache benefit for recurring workloads. A practical approach is to keep the warehouse running during periods of frequent query activity or increase AUTO_SUSPEND to reduce cache loss, while still suspending during idle periods to control cost. Candidates should distinguish warehouse cache from persisted query results and from services such as Search Optimization. Snowflake documentation and performance best practices describe that warehouse local cache is tied to the running warehouse and is not preserved across suspend/resume cycles.
- A. Correct.
Correct. Snowflake's warehouse cache (local disk cache) is associated with the running virtual warehouse and is cleared when the warehouse suspends. If the workload is repetitive throughout the day, increasing AUTO_SUSPEND or keeping the warehouse running during active hours can allow subsequent queries to benefit from cached micro-partition data already read by that warehouse. This is a common performance-versus-cost tradeoff.
- B. Incorrect.
Incorrect. Search Optimization Service can improve selective query performance for certain access patterns, but it does not make warehouse cache survive a suspend event. Warehouse cache is tied to the active warehouse cluster and is not preserved after suspension.
- C. Incorrect.
Incorrect. Resizing a warehouse may change compute capacity, but warehouse cache is not a shared cache across different warehouses or recreated simply because another warehouse has the same size. In addition, resizing does not solve the key issue that suspension clears the local cache.
- D. Incorrect.
Incorrect. Persisted query results can return results without re-executing a query only when the query text and relevant conditions match and the underlying data has not changed. The option is wrong because it claims this will help even when underlying data changes. Also, the scenario specifically asks about taking advantage of warehouse cache rather than relying on result reuse.