ADA-C01 Question 330
Single answerLocal disk (warehouse) cacheA BI team reports that a dashboard query is fast when run repeatedly during the morning, but after the virtual warehouse is suspended during lunch and resumes later, the same query becomes noticeably slower on its first execution. The underlying tables have not changed, and the query text is identical. As the Snowflake administrator, which action is most likely to preserve the performance benefit seen before suspension?
- A
Prevent the warehouse from suspending so the warehouse's local disk cache remains available
- B
Enable the query result cache at the warehouse level so results survive suspension
- C
Convert the warehouse to a multi-cluster warehouse so cache is shared across clusters after resume
- D
Increase the STATEMENT_TIMEOUT_IN_SECONDS setting so cached micro-partitions are retained longer
- E
Recluster the underlying tables immediately before lunch so the cache is rebuilt automatically on resume
Show answer and explanation
Correct answer: A
Explanation
This scenario tests the distinction between Snowflake caching layers and how warehouse lifecycle affects performance. Snowflake uses a local disk cache on virtual warehouse compute nodes to store data read from table storage. Repeated queries that scan the same data can benefit from this cache, but the cache is lost when the warehouse is suspended because the compute resources are deprovisioned. Therefore, if the goal is to preserve the warm-cache performance benefit, the practical administrative action is to avoid suspending the warehouse during the period when fast repeat performance is needed. Administrators must balance this against additional compute cost.
This is different from persisted query results, which are not controlled at the warehouse level and are separate from local disk cache. It is also different from metadata-based optimizations such as pruning, clustering, or statement timeout settings. Snowflake documentation and performance best practices distinguish persisted query results, metadata caching, and warehouse local disk cache, and note that suspending a warehouse clears the local disk cache.
- A. Correct.
Correct. Snowflake's local disk cache is tied to the virtual warehouse compute resources. When a warehouse is suspended, the local disk cache is cleared because the compute resources are released. Keeping the warehouse running preserves that cache and can improve performance for repeated scans of the same data. This is a common tradeoff between cost savings from auto-suspend and performance benefits from warm warehouse cache.
- B. Incorrect.
Incorrect. Query result cache is not enabled at the warehouse level, and it is distinct from the warehouse local disk cache. Persisted query results are managed by Snowflake separately from warehouse compute and can be reused only when the query and underlying conditions qualify. The scenario specifically points to the first query after warehouse resume being slower due to loss of local disk cache, not result cache settings.
- C. Incorrect.
Incorrect. A multi-cluster warehouse does not provide a shared local disk cache across clusters in a way that preserves cache through suspension. Each cluster has its own compute resources and associated local cache behavior, and suspending the warehouse still releases those resources.
- D. Incorrect.
Incorrect. STATEMENT_TIMEOUT_IN_SECONDS controls how long a statement may run before timing out. It has nothing to do with retention of warehouse local disk cache or micro-partitions in cache. This option reflects a misunderstanding between execution controls and caching behavior.
- E. Incorrect.
Incorrect. Reclustering can improve pruning and scan efficiency for some workloads, but it does not rebuild or preserve the warehouse local disk cache across suspension and resume. The cache is populated as data is read by the warehouse, and suspension removes that cached data.