ADA-C01 Question 326
Select 2Compare and contrast different caching techniques and the impact of caching on performanceA BI team runs the same dashboard every morning against a 12 TB star-schema data mart in Snowflake. The dashboard issues many identical SELECT statements with unchanged underlying data between refreshes. An administrator notices that the first run after the warehouse resumes takes several minutes, but subsequent runs complete much faster. The team asks which Snowflake caching mechanisms are contributing to the faster repeat executions and what operational changes would reduce the benefit of those caches. Which TWO statements are correct?
- A
Persisted query results can allow identical queries to return from the result cache without re-executing, but this benefit is lost if the underlying table data changes or the SQL text is not effectively identical.
- B
The warehouse local disk cache can improve performance for repeated scans of the same micro-partitions, but suspending the warehouse can remove this cache because it is tied to the compute resources.
- C
The metadata cache is stored only inside a running virtual warehouse, so resizing or suspending the warehouse clears it and forces full table scans on the next query.
- D
Disabling automatic clustering on the fact table will increase the likelihood that persisted query results are reused for identical dashboard queries.
- E
Increasing the warehouse size guarantees that repeated dashboard queries will use the result cache even if the SQL text changes slightly.
Show answer and explanation
Correct answers: A, B
Explanation
This scenario tests the ability to compare Snowflake caching layers and understand their impact on performance. In practice, faster repeat executions commonly come from two different mechanisms: persisted query results and the virtual warehouse local disk cache. Persisted query results can completely avoid re-execution for repeated identical queries when the underlying data has not changed and other reuse conditions are met. The warehouse cache accelerates repeated data access by keeping previously read data close to compute resources, but it is typically lost when the warehouse suspends. By contrast, Snowflake's metadata services support optimization such as partition pruning and are not simply a warehouse-local cache cleared by suspend/resume. Best-practice guidance from Snowflake documentation distinguishes these layers clearly: result cache for reusing final query results, warehouse cache for reusing data files read by compute, and metadata-based pruning for minimizing scanned micro-partitions. Operationally, frequent suspend/resume reduces warehouse cache benefits, while query text changes and data modifications reduce persisted query result reuse.
- A. Correct.
Correct. Snowflake's persisted query results, commonly called the result cache, can return results for repeated queries without re-computation when conditions are met. Key requirements include that the query text be the same and that the underlying data not have changed in a way that invalidates the cached result. This is why repeated dashboard queries can be much faster when they are identical and the source objects are unchanged. A common misconception is that any logically equivalent query will reuse the result cache; in practice, query text and other session/context factors matter.
- B. Correct.
Correct. Snowflake uses a local disk cache on the virtual warehouse to cache data retrieved from remote storage. Repeated scans of the same data can be faster while the same warehouse resources remain available. If the warehouse is suspended, the local cache is typically lost because it is associated with the warehouse's compute nodes. This is a major reason the first run after resume is slower than later runs, even when the query result cache is not used for every statement.
- C. Incorrect.
Incorrect. This confuses metadata services with warehouse-local caching. Snowflake maintains metadata centrally in the cloud services layer, not only inside a running warehouse. Metadata such as micro-partition pruning information is not simply a warehouse-local cache that disappears on suspend. Also, suspending or resizing a warehouse does not inherently force full table scans; Snowflake can still prune micro-partitions based on metadata.
- D. Incorrect.
Incorrect. Automatic clustering affects how well table data remains clustered for pruning efficiency, not whether persisted query results are reused. Result cache reuse depends on factors like identical query text and unchanged underlying data. Turning off automatic clustering could actually hurt scan performance over time if clustering quality degrades, but it does not improve result cache eligibility.
- E. Incorrect.
Incorrect. Warehouse size affects compute capacity and can influence execution speed for queries that do run, but it does not guarantee result cache usage. Result cache reuse is not determined by warehouse size. If the SQL text changes, Snowflake may not treat it as the same query for persisted query result reuse, so a larger warehouse does not solve that issue.