ADA-C01 Question 331
Single answerLocal disk (warehouse) cacheA data engineering team runs a nightly ELT validation process using a dedicated virtual warehouse named ETL_WH. The process executes the same set of large, repetitive SELECT queries against unchanged tables several times over a two-hour window. On some nights, the validation steps run much faster than expected after the first pass; on other nights, the same repeated queries remain slow even though no SQL text has changed. As the Snowflake administrator, you need to identify which operational change is most likely preventing the queries from benefiting from the warehouse's local disk cache. What is the best answer?
- A
The warehouse is being suspended and resumed between validation steps, causing the local disk cache to be lost
- B
The queries are using fully qualified object names, which bypasses the warehouse cache layer
- C
The tables are clustered, which disables local disk cache reuse for repeated scans
- D
The sessions are using different roles, so cached table data cannot be reused across users
Show answer and explanation
Correct answer: A
Explanation
The key concept is that Snowflake's warehouse cache (often called local disk cache) resides on the virtual warehouse's compute nodes and can accelerate repeated reads of the same table data. This benefit is most noticeable for repeated scans on the same active warehouse when the underlying table data has not changed significantly. However, if the warehouse suspends, Snowflake may deprovision the compute resources, and the local cache is effectively lost. That is why repeated queries may be fast on nights when ETL_WH remains warm, but slow on nights when auto-suspend or operational pauses cause the warehouse to suspend and resume between steps.
This is distinct from the result cache, which returns results for identical queries under stricter conditions. Here, the scenario emphasizes repeated large scans rather than guaranteed result reuse. Snowflake documentation and performance guidance distinguish warehouse cache from persisted query results and note that keeping a warehouse running can improve performance for repeated data access patterns, while suspending a warehouse clears its local cache context.
- A. Correct.
Correct. Snowflake's local disk cache is associated with the compute resources backing a virtual warehouse. If the warehouse suspends, those resources can be released, and the local disk cache is not preserved. Repeated scans can be faster when the same warehouse remains running and the underlying data has already been read into local storage. In a workload with repeated large SELECT statements over unchanged data, frequent suspend/resume cycles are a common reason cache benefits appear inconsistent.
- B. Incorrect.
Incorrect. Using fully qualified object names affects object resolution, not whether warehouse local disk cache is used. Cache behavior is tied to the warehouse's compute resources and whether the same micro-partitions are scanned again while they remain cached locally.
- C. Incorrect.
Incorrect. Clustering changes how table data is organized and can improve pruning efficiency, but it does not disable warehouse local disk caching. A clustered table can still benefit from local cache if the same warehouse scans the same data repeatedly and the cache remains available.
- D. Incorrect.
Incorrect. The warehouse local disk cache is not isolated per user role in the way this option suggests. Different users or roles can still benefit from data already cached on the warehouse, provided they run on the same active warehouse and the underlying data has not changed in a way that invalidates cache usefulness.