DAA-C01 Question 182
Single answerLeverage result, metadata, and virtual warehouse cachingA data analyst runs the same dashboard query against a SALES_FACT table every few minutes during a business review. The first run takes 40 seconds, but subsequent runs are usually much faster. However, after the analyst suspends the virtual warehouse for 20 minutes and resumes it, the next execution is slower again. The underlying table data has not changed, and the SQL text remains identical. Which statement best explains this behavior?
- A
The slower run after resume occurs because suspending the warehouse clears the virtual warehouse's local disk cache, while persisted query results can still be reused only when all result cache conditions are met.
- B
Suspending the warehouse clears both the metadata cache and the persisted query result cache, so Snowflake must fully recompute the query after resume.
- C
The slower run after resume occurs because result caching requires the warehouse to remain continuously running; once suspended, result cache is invalidated.
- D
The slower run after resume occurs because Snowflake does not cache query results for large fact tables, only for small dimension tables and views.
Show answer and explanation
Correct answer: A
Explanation
Snowflake performance improvements for repeated queries can come from different caching mechanisms, and candidates are expected to distinguish among them. Persisted query results can allow Snowflake to return a prior result for the same query without recomputation when eligibility requirements are met, including unchanged underlying data and identical query text. Metadata caching helps Snowflake avoid repeated metadata lookups and is handled by the services layer. Virtual warehouse caching stores data retrieved from table storage on the warehouse's local disk, which speeds up repeated scans while the warehouse remains running. When a warehouse is suspended, that local disk cache is removed, so after resume, scans may take longer until the cache is warmed again. This behavior is consistent with Snowflake documentation on optimizing query performance using persisted query results, metadata optimization, and virtual warehouse cache behavior.
- A. Correct.
Correct. Snowflake uses multiple caching layers. The virtual warehouse cache stores table data on local disk for the running warehouse, improving repeated scans. When the warehouse is suspended, that local disk cache is lost. By contrast, persisted query results are stored separately and can still be reused if the query text is identical and other reuse conditions are satisfied, such as no relevant underlying data changes and compatible session settings. Therefore, after resume, the query may be slower if it must re-read data because the warehouse cache was cleared, even though result cache may still help only in scenarios where Snowflake can return the exact prior result.
- B. Incorrect.
Incorrect. This confuses the different cache types. Suspending a virtual warehouse clears the warehouse's local data cache, but it does not inherently clear persisted query results. Metadata caching is managed by Snowflake's cloud services layer and is not simply wiped because a specific warehouse is suspended. Someone might choose this option if they assume all caches are tied to warehouse lifecycle, which is not how Snowflake's caching architecture works.
- C. Incorrect.
Incorrect. Persisted query result reuse does not require the warehouse to stay continuously running. Result cache is independent of a warehouse remaining active. If the SQL text is identical and the query is still eligible for result reuse, Snowflake can return persisted results without recomputing. This option reflects a common misconception that all performance benefits in Snowflake come from warehouse-local caching.
- D. Incorrect.
Incorrect. Snowflake does not limit result caching based on whether an object is a fact table versus a dimension table. Persisted query results depend on eligibility conditions such as identical query text and unchanged contributing data, not table modeling patterns. This distractor is plausible because large fact-table scans often benefit noticeably from warehouse caching, but the platform does not restrict result caching by table type.