ARA-C01 Question 413
Single answerCache expirationA data engineering team uses Snowflake to serve a dashboard that runs the same SELECT statements every 10 minutes against a SALES_SUMMARY table. The warehouse remains running all day. To reduce compute cost, the architect wants the dashboard queries to reuse cached results whenever possible, but the SALES_SUMMARY table is refreshed by a scheduled ETL process every 4 hours. During testing, users noticed that immediately after the ETL completes, the dashboard queries stop using cached results even though the SQL text is unchanged. Which action best explains this behavior and should be considered when designing for cache expiration?
- A
The persisted query result cache is invalidated when the underlying table data changes, so queries executed after the ETL refresh cannot reuse the previous result cache.
- B
The virtual warehouse local disk cache is automatically preserved across all table reload operations, so the issue must be caused by warehouse auto-suspend settings.
- C
Snowflake expires cached query results only when the SQL text changes; DML against referenced tables does not affect result cache reuse.
- D
Result cache reuse depends on micro-partitions remaining in the same physical order, so a reclustering operation would be required to restore cache hits.
Show answer and explanation
Correct answer: A
Explanation
The correct answer is Option 1. Snowflake provides multiple caching layers, and architects need to distinguish among them: persisted query results, virtual warehouse local disk cache, and metadata-related optimizations. For dashboards that repeatedly issue identical SELECT statements, persisted query results can eliminate compute for qualifying repeat executions. However, this cache is reused only when the query and execution context meet Snowflake's reuse conditions and the underlying data has not changed. When the ETL refresh updates SALES_SUMMARY, Snowflake treats the prior result as no longer valid for reuse, so the next dashboard run must execute again.
This is an important architecture consideration for cache expiration: repeated queries can benefit significantly from result caching during stable periods between data refreshes, but architects should expect cache invalidation after DML or data refresh operations on referenced objects. By contrast, the virtual warehouse cache helps avoid re-reading data files for subsequent scans on the same running warehouse, but it does not substitute for persisted query result reuse and can be affected by suspension, resizing, or warehouse restart events.
This aligns with Snowflake documentation on persisted query results and warehouse caching best practices: result reuse requires unchanged underlying data and matching query conditions, while warehouse cache behavior is separate and tied to the warehouse lifecycle.
- A. Correct.
Correct. Snowflake's persisted query results are reused only when the underlying data contributing to the result has not changed and other reuse conditions are met. If the ETL refresh modifies the SALES_SUMMARY table, previously cached query results referencing that table are no longer valid for reuse, even when the SQL text is identical. This is a key cache expiration and invalidation behavior architects must account for when balancing freshness and cost.
- B. Incorrect.
Incorrect. This mixes up two different caches. The warehouse local disk cache is used for data read optimization at the virtual warehouse level, not for returning a prior query result set directly to the user. Also, local disk cache can be lost if the warehouse is suspended or resized and does not explain why identical dashboard queries stop reusing persisted query results immediately after table data changes.
- C. Incorrect.
Incorrect. Identical SQL text is necessary but not sufficient for persisted query result reuse. Snowflake also requires that referenced data has not changed and that other session and query conditions are compatible. A common misconception is that result caching is purely text-based, but underlying table changes invalidate reuse.
- D. Incorrect.
Incorrect. Result cache reuse is not based on preserving micro-partition physical order. Reclustering can improve pruning and scan efficiency, but it does not restore reuse of a persisted query result that became invalid because referenced table data changed. This distractor reflects confusion between storage optimization and result caching behavior.