ARA-C01 Question 411
Single answerDifferent cache layersA data platform team uses Snowflake to serve a BI dashboard that runs the same set of SELECT queries every 5 minutes. The dashboard points to a dedicated virtual warehouse named BI_WH. During testing, architects observe the following behavior: the first dashboard refresh after BI_WH resumes is slower, subsequent refreshes are faster, and after the dashboard is switched to a different warehouse with the same size, performance degrades again even though the underlying table data has not changed. The team wants to explain which Snowflake cache layers are contributing to these observations. Which statement BEST explains the behavior?
- A
The initial slowdown after BI_WH resumes occurs because the warehouse local disk cache is empty; subsequent runs benefit from cached micro-partition data on that warehouse, while switching to another warehouse loses that local cache even if the SQL text and data are unchanged.
- B
The slower first run occurs because the result cache is cleared whenever a warehouse is suspended; subsequent runs are faster because the result cache is rebuilt only on that warehouse, so changing warehouses prevents reuse of cached query results.
- C
The faster subsequent runs are caused only by the metadata cache in Cloud Services, which is tied to a specific warehouse; moving to a different warehouse invalidates the metadata cache and forces full table scans.
- D
The performance difference is explained by Snowflake's automatic clustering cache, which is stored per warehouse and reused only if the same warehouse processes the same query text.
Show answer and explanation
Correct answer: A
Explanation
Snowflake query performance can be influenced by different cache layers, and architects should understand how they behave operationally. In this scenario, the key distinction is between the persisted query result cache and the virtual warehouse local disk cache. Persisted query results can be reused when the exact query and underlying conditions permit, and they are not limited to a specific warehouse. By contrast, the warehouse cache is tied to the virtual warehouse's compute resources. When a warehouse is resumed, its local cache starts empty, so the first query may need to fetch data from remote storage. Subsequent queries on the same warehouse can be faster because frequently accessed micro-partition data may already be present locally. Moving the workload to another warehouse of the same size does not transfer that local cache, so the new warehouse behaves like a cold cache. This aligns with Snowflake documentation on optimizing warehouse performance, query result reuse, and understanding the distinction between persisted query results, metadata usage, and warehouse-local data caching.
- A. Correct.
Correct. Snowflake uses multiple cache layers. The warehouse cache (often described as local disk cache) is associated with a running virtual warehouse and stores data read from remote storage. After a warehouse resumes, that cache starts cold, so the first run may be slower. Repeated scans on the same warehouse can be faster because needed micro-partition data may already be cached locally. If the workload is moved to a different warehouse, that new warehouse does not inherit the previous warehouse's local disk cache, so performance can drop again even when the underlying table data has not changed.
- B. Incorrect.
Incorrect. Persisted query results are not tied to a specific warehouse, and suspending a warehouse does not by itself clear the persisted result cache. If the exact same query is rerun and the required conditions are met, Snowflake can reuse persisted results across warehouses. This option reflects a common misconception that result caching is warehouse-specific.
- C. Incorrect.
Incorrect. Cloud Services metadata is not the sole explanation for the observed pattern, and metadata caching is not primarily warehouse-specific in the way described here. Also, moving to a different warehouse does not generally force full table scans because metadata cache was 'invalidated.' The scenario specifically matches the behavior of a cold vs. warm warehouse data cache.
- D. Incorrect.
Incorrect. There is no Snowflake feature called an 'automatic clustering cache' that explains query acceleration in this way. Automatic Clustering is a service for maintaining clustering depth on tables, not a per-warehouse query cache. This distractor targets confusion between storage optimization features and runtime caching layers.