ARA-C01 Question 408
Single answerCachingA retail company uses Snowflake to power hourly operational dashboards. The same set of parameterized SELECT statements is executed every 5 minutes by a BI tool against a STAR schema. To reduce latency, the architect enabled AUTO_SUSPEND = 60 seconds on the virtual warehouse used by the dashboard. After deployment, users report that the first dashboard refresh after each idle period is much slower, while subsequent refreshes are fast. No base tables are changing during business hours. Which action would best improve performance for that first refresh after idle periods while keeping compute cost reasonably controlled?
- A
Increase the warehouse size so the local disk cache survives suspension and can be reused after resume
- B
Disable AUTO_SUSPEND or increase the auto-suspend threshold so the warehouse stays running longer and retains its local disk cache between dashboard refreshes
- C
Rely on the result cache, because identical queries will always use cached results even after the warehouse has been suspended
- D
Create a materialized view for every dashboard query, because materialized views replace the need for both warehouse cache and result cache
Show answer and explanation
Correct answer: B
Explanation
Snowflake uses multiple caching-related mechanisms that architects must distinguish: persisted query results, metadata-related optimizations, and the virtual warehouse local disk cache. In this scenario, the key clue is that the first refresh after an idle period is slow, but subsequent refreshes are fast, while the underlying tables are unchanged. That pattern typically indicates the local disk cache on the virtual warehouse was lost because AUTO_SUSPEND caused the warehouse to stop. When the warehouse resumes, it must re-read data from remote storage until the cache is warmed again.
The best architectural adjustment is to keep the warehouse running through short idle gaps by increasing the auto-suspend threshold or, if justified, disabling auto-suspend during dashboard hours. This preserves the local disk cache and improves latency for repetitive BI workloads, while still allowing cost control through an appropriately chosen threshold.
Relevant Snowflake guidance distinguishes persisted query results from warehouse caching. Persisted query results can be reused only when strict conditions are met, whereas the warehouse cache is lost on suspend. Snowflake documentation on optimizing warehouses and understanding query performance consistently notes that suspending a warehouse clears its local cache, so frequent suspend/resume cycles can increase latency for repeated workloads.
- A. Incorrect.
Incorrect. Increasing warehouse size may improve raw execution speed, but it does not preserve the warehouse local disk cache across suspension. Snowflake's warehouse cache is tied to the running warehouse cluster and is cleared when the warehouse suspends. A larger warehouse does not solve the specific problem of the first query after resume losing warmed data cache.
- B. Correct.
Correct. The behavior described is classic loss of the virtual warehouse local disk cache when the warehouse suspends. Keeping the warehouse running longer by disabling AUTO_SUSPEND or increasing the suspension threshold allows frequently accessed micro-partitions to remain in the warehouse cache, improving the first dashboard refresh after short idle periods. This is a practical tradeoff between latency and compute cost for repetitive BI workloads.
- C. Incorrect.
Incorrect. Snowflake's persisted query results cache can accelerate repeated queries, but it is not guaranteed to be used in every case. Reuse depends on conditions such as the query text being identical, no relevant underlying data changes, non-restrictive session settings, and no use of certain non-reusable constructs. Also, many BI tools generate queries with small text differences or session-level variations that prevent result cache reuse. The scenario specifically points to slow first execution after warehouse resume and fast subsequent executions, which strongly indicates dependence on warehouse local cache rather than reliable result cache hits.
- D. Incorrect.
Incorrect. Materialized views can help specific repeated query patterns, especially when they precompute expensive aggregations or projections, but they do not replace Snowflake caching layers. They also introduce maintenance cost and are not appropriate as a blanket response for every dashboard query. The problem described is directly tied to cache eviction on warehouse suspend, so adjusting warehouse suspension behavior is the most targeted and cost-conscious fix.