ARA-C01 Question 410
Single answerDifferent cache layersA retail company uses Snowflake to serve a dashboard that runs the same SELECT statement every 5 minutes against a SALES_FACT table. The dashboard uses a dedicated virtual warehouse. After a schema migration, the architect notices that the first dashboard refresh is slow, but the next several refreshes are much faster even though no result set is being returned from the persisted query results cache. The architect wants to explain which Snowflake cache layer is most likely improving performance for the repeated dashboard queries and under which condition that benefit would be lost.
Which option best answers this scenario?
- A
The metadata cache is improving performance, and the benefit is lost only if the table gains new micro-partitions.
- B
The virtual warehouse local disk cache is improving performance, and the benefit is lost if the warehouse is suspended or resized, causing cached data on the warehouse nodes to be cleared.
- C
The persisted query results cache is improving performance, and the benefit is lost whenever the SQL text contains fully qualified object names.
- D
The result cache in the cloud services layer is improving performance, and the benefit is lost only when the query is run by a different role.
Show answer and explanation
Correct answer: B
Explanation
Snowflake has multiple performance-related cache layers that architects should distinguish carefully:
- Persisted query results: If the same query is rerun and the underlying data has not changed, Snowflake may return the stored result set instead of re-executing the query. In this scenario, that cache is explicitly ruled out.
- Virtual warehouse local disk cache: When a warehouse reads table data, the data can be cached on local SSD attached to the warehouse compute nodes. Subsequent queries using the same warehouse can scan less remote storage and complete faster. This is the most likely reason the first run is slower and later runs are faster.
- Metadata services: Snowflake also uses metadata about micro-partitions for pruning and planning, but that is not the primary cache layer being described here.
A key architectural consideration is that the warehouse cache is tied to the running compute resources. Suspending the warehouse clears that cache because the nodes are deprovisioned. Resizing may also replace nodes and reduce cache locality. This is why dashboards with frequent repeated access patterns sometimes benefit from keeping a warehouse warm, balanced against cost.
This aligns with Snowflake documentation on optimizing warehouse performance, persisted query results, and the impact of warehouse suspension on local disk cache behavior.
- A. Incorrect.
Incorrect. Snowflake does use metadata for partition pruning and optimization decisions, but that is not the best explanation for a large improvement between the first and subsequent executions when persisted query results are explicitly not being used. Metadata does not represent the main cache layer responsible for reusing previously fetched table data blocks across repeated scans by the same warehouse.
- B. Correct.
Correct. When persisted query results are not used, repeated queries can still run faster because the virtual warehouse caches table data in local SSD storage on the compute nodes. This is often called the warehouse cache or local disk cache. If the warehouse is suspended, the cache is lost because the compute resources are released. Resizing can also change the cluster nodes, which can reduce or eliminate the benefit of the existing local cache.
- C. Incorrect.
Incorrect. Persisted query results can accelerate repeated identical queries, but the scenario explicitly states that this cache is not being used. Also, using fully qualified object names does not by itself invalidate persisted query results. The reuse of persisted results depends on factors such as unchanged underlying data and matching query text and session settings where applicable, not on whether object names are fully qualified.
- D. Incorrect.
Incorrect. This option mixes concepts. Persisted query results are the relevant cache for returning a stored result set, but the scenario says no result set is being returned from that cache. A different role can affect reuse of persisted results due to privilege checks, but that is not the best explanation here. The observed speedup after the initial run is more consistent with the virtual warehouse local disk cache.