What you will be able to do
- Explain what a running warehouse caches and what makes that cache disappear
- Choose an AUTO_SUSPEND value that balances cache retention against idle credit cost
- Measure how much data a warehouse reads from its cache
- Recognize queries answered from metadata alone, without a virtual warehouse
1.The warehouse cache: table data kept on a running warehouse
Snowflake has a second cache that works at a lower level than finished results. A running warehouse keeps a cache of table data. Later queries on the *same* warehouse can read from that cache instead of fetching the data from the tables, and that can make them faster. Unlike the result cache, this cache doesn't skip execution. The query still runs and still uses compute. It just reads its input from a closer, faster place. Because of this, the warehouse cache can help queries that are similar but not identical, which the result cache can't serve.
The cache lives with the warehouse's compute, not with the table and not in the cloud services layer. Snowflake's architecture has each node in a warehouse cluster store a portion of the data set locally, and the documentation calls this the *local data cache* (query history reports it as the *local disk cache*). You'll also see it called the warehouse cache, the data cache or the table data cache. These are all the same tier. It's a common distractor to place it in remote storage or to treat it as a copy shared by all warehouses. Each warehouse is an independent compute cluster, and the cache belongs to the warehouse that read the data.
The cache belongs to a running warehouse, so it disappears when the warehouse stops: the cache is dropped when the warehouse is suspended. This is the most important invalidation rule for this tier. Snowflake's interactive-warehouse guidance describes the same behaviour: suspending and resuming resets the cache, and the warehouse then has to spend time warming it up again. Until the cache is warm, queries read their data from remote storage, so the first queries after a resume are the slow ones. That is why a warehouse that was suspended between bursts of work can show a higher remote read percentage in Query Profile at the start of the next burst.
Checkpoint 1 of 6· Check yourself
A BI warehouse has served the same dashboard queries all morning. It auto-suspends over lunch and resumes at 13:00. What state is its table-data cache in when it resumes?
The warehouse cache only exists while the warehouse is running, and suspension drops it. The 24-hour lifetime belongs to persisted query results, not to the warehouse cache.
“the cache is dropped when the warehouse is suspended.”Source: docs.snowflake.com
2.Tuning AUTO_SUSPEND to keep the cache, without paying for idle time
Since suspension drops the cache, the warehouse's auto-suspend setting directly affects query performance. If a warehouse runs frequent, similar queries, suspending it between them may drop the cache just before the next query could have used it. Snowflake gives guidelines by workload type:
| Workload | Recommended auto-suspend | Reasoning |
|---|---|---|
| Tasks | Immediate suspension | Cache retention is not a priority |
| DevOps, DataOps, Data Science | About 5 minutes | Ad-hoc and unique queries benefit less from the cache |
| Query warehouses (BI and SELECT use cases) | At least 10 minutes | Keeps the cache available for users |
There's a cost on the other side. A running warehouse consumes credits even when it isn't processing queries, so a longer auto-suspend isn't automatically better. The setting has to fit the gap between queries. The documentation's example: if a warehouse runs a query every 30 minutes, a 10-minute auto-suspend makes no sense. The warehouse pays for 10 idle minutes, then drops the cache before the next query arrives, so you pay the cost and get none of the benefit.
You can change the setting in Snowsight (Compute » Warehouses, then edit Suspend After (min)) or with ALTER WAREHOUSE. Watch the units: Snowsight asks for minutes, but in SQL the value is in seconds.
Checkpoint 2 of 6· Fill the gap
You want a BI warehouse to auto-suspend after 10 minutes. Which value completes the documented SQL?
ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = ? ;AUTO_SUSPEND in ALTER WAREHOUSE is given in seconds, so 10 minutes is 600. A value of 10 would suspend the warehouse after 10 seconds.
Source: docs.snowflake.comSources1
3.Measuring cache use, and isolating it from the result cache
Before you tune anything, measure. Snowflake provides a diagnostic query over ACCOUNT_USAGE.QUERY_HISTORY. For each warehouse, it computes the share of scanned bytes that came from the cache over the past month. It needs access to the shared SNOWFLAKE database, which by default only ACCOUNTADMIN has.
SELECT warehouse_name
,COUNT(*) AS query_count
,SUM(bytes_scanned) AS bytes_scanned
,SUM(bytes_scanned*percentage_scanned_from_cache) AS bytes_scanned_from_cache
,SUM(bytes_scanned*percentage_scanned_from_cache) / SUM(bytes_scanned) AS percent_scanned_from_cache
FROM snowflake.account_usage.query_history
WHERE start_time >= dateadd(month,-1,current_timestamp())
AND bytes_scanned > 0
GROUP BY 1
ORDER BY 5;A low percentage only matters when the workload could benefit from the cache, meaning frequent and similar queries. In that case, optimizing the cache can improve performance. For a single query, Snowflake's interactive-analytics guidance points to Query Profile: a warehouse too small to hold a useful working set in its local data cache reads heavily from remote storage, and this shows up as a high remote read percentage.
When you test warehouse performance, the result cache can distort what you're measuring. Repeated runs of identical queries would come back from persisted results and look artificially fast. The guidance is to turn off the query result cache with USE_CACHED_RESULT, so the queries use only the warehouse's table data cache. These are two separate controls. Turning off result reuse doesn't affect the warehouse cache, and the warehouse cache is controlled by whether the warehouse keeps running.
Checkpoint 3 of 6· Check yourself
A team sets USE_CACHED_RESULT = FALSE for a test session and keeps the warehouse running between queries. Which cache can repeated queries still benefit from?
USE_CACHED_RESULT only controls reuse of persisted results. With it off, queries still execute against table data that the running warehouse has cached.
“That way, the queries only use the table data cache from the interactive warehouse.”Source: docs.snowflake.com
4.Metadata-based results: answers that need no warehouse
The third tier skips table data altogether. Snowflake keeps metadata about every table, and metadata management runs in the cloud services layer, alongside query parsing and optimization. That layer is separate from the virtual warehouses that process your queries. Some queries can be answered from that metadata alone. Query Profile labels these as a Metadata-based Result: a query whose result comes purely from metadata, without accessing any data. The documentation's examples are SELECT COUNT(*) on a table and SELECT CURRENT_DATABASE(). Most importantly, these queries are not processed by a virtual warehouse, so no running warehouse is involved. Query Profile has a separate label, Query Result Reuse, for a query that reuses an earlier query's result. The two are easy to confuse because both return almost instantly, but they come from different mechanisms.
What metadata does Snowflake hold? For every micro-partition it stores the range of values for each column, the number of distinct values, and other properties used for optimization and efficient query processing. Snowflake also maintains statistics on tables and views, which is why a simple COUNT is faster on a table or view without a row access policy. With a row access policy, Snowflake must scan each row and check whether the user may see it, so metadata alone can't answer the query.
The same metadata also supports other work. Micro-partition metadata lets Snowflake prune micro-partitions precisely at query run time. Some DML is metadata-only too: deleting all rows from a table, for example, doesn't need to scan the data.
Checkpoint 4 of 6· Match them up
Match each scenario to the mechanism that serves it
Tap a term, then the definition that fits it.
Result reuse needs the same query on unchanged data and skips execution. A metadata-based result uses metadata and needs no warehouse. The warehouse cache speeds up execution, but only while the warehouse is running.
“A query whose result is computed based purely on metadata, without accessing any data.”Source: docs.snowflake.com
Checkpoint 5 of 6· Check yourself
Which of these is shown in Query Profile as a Metadata-based Result, and is therefore not processed by a virtual warehouse?
CURRENT_DATABASE() is one of the documented examples of a result computed purely from metadata. A repeated identical query is Query Result Reuse instead, and the aggregation over table data and the aliased query both need a warehouse to execute.
“A query whose result is computed based purely on metadata, without accessing any data.”Source: docs.snowflake.com
Checkpoint 6 of 6· Exam question
During an incident investigation, an engineer needs every query in the current session to recompute against live data instead of returning persisted results, so recent fixes can be verified. Which setting achieves this?
Correct answer: C — Setting the session parameter `USE_CACHED_RESULT` to `false`, which forces every query to bypass the persisted result cache and recompute against current data.
- A. Clustering keys affect micro-partition organization and pruning, not whether the result cache is consulted, so removing one does not force recomputation.
- B. Keeping a warehouse resumed has no bearing on result cache lookups, which occur in the cloud services layer regardless of warehouse suspend state.
- C. The `USE_CACHED_RESULT` session parameter directly controls whether Snowflake checks the persisted result cache, so setting it false guarantees fresh recomputation for every query in the session.
- D. A statement timeout only limits how long a query may run; it does not change whether the result cache is checked before execution begins.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.ALTER WAREHOUSE ... SET AUTO_SUSPEND = 10 keeps the warehouse and its cache alive for 10 minutes.Why is that wrong?
In SQL, AUTO_SUSPEND is given in seconds, so 10 minutes is 600. Snowsight's field is the one that uses minutes.
Covered in Tuning AUTO_SUSPEND to keep the cache, without paying for idle time
2.A longer auto-suspend always helps, because it keeps the cache warm.Why is that wrong?
An idle warehouse still uses credits. If queries arrive less often than the auto-suspend interval, you pay for idle time and the cache is dropped anyway.
Covered in Tuning AUTO_SUSPEND to keep the cache, without paying for idle time
3.A SELECT COUNT(*) answered from metadata still needs a running virtual warehouse.Why is that wrong?
Metadata-based results are computed without accessing table data and aren't processed by a virtual warehouse.
Covered in Metadata-based results: answers that need no warehouse
Practise it for real
Measure each warehouse's cache usage and set auto-suspend to fit the workload
1.Using the ACCOUNTADMIN role, run the percent_scanned_from_cache diagnostic query against snowflake.account_usage.query_history.
Why: By default, only ACCOUNTADMIN can query the shared SNOWFLAKE database.
You should see: One row per warehouse, ordered from the lowest share of bytes scanned from cache to the highest.
2.For a BI warehouse with a low percentage and frequent, similar queries, run ALTER WAREHOUSE my_wh SET AUTO_SUSPEND = 600;
Why: Snowflake recommends at least 10 minutes for query warehouses so the cache stays available to users. The value is in seconds.
You should see: The warehouse now suspends after 10 idle minutes instead of dropping its cache sooner.
3.In a test session, run alter session set use_cached_result = false; and then rerun a representative query several times.
Why: Turning off result reuse makes every run actually execute, so the timings reflect the warehouse cache and not persisted results.
You should see: Each run executes on the warehouse instead of returning a reused result.
Stuck? Get a nudge
If the warehouse only gets a query every 30 minutes, a 10-minute auto-suspend adds idle cost and gives no cache benefit.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“A running warehouse maintains a cache of table data that can be accessed by queries running on the same warehouse.”
↩︎ The warehouse cache: table data kept on a running warehouse“Snowflake recommends setting auto-suspend to at least 10 minutes to maintain the cache for users.”
↩︎ Tuning AUTO_SUSPEND to keep the cache, without paying for idle time“Keep in mind that a running warehouse consumes credits even if it is not processing queries.”
↩︎ Tuning AUTO_SUSPEND to keep the cache, without paying for idle time“By default, only the ACCOUNTADMIN role has the privileges needed to execute the queries.”
↩︎ Measuring cache use, and isolating it from the result cache“which is specified in seconds, not minutes”
↩︎ Exam trap 1“if a warehouse executes a query every 30 minutes, it does not make sense to set the auto-suspend setting to 10 minutes”
↩︎ Exam trap 2“the cache is dropped when the warehouse is suspended.”
↩︎ Checkpoint - 2.https://docs.snowflake.com/en/user-guide/interactiveOfficial docs
“Suspending and resuming an interactive warehouse (whether manually or through auto-suspend) resets the cache and incurs cache warm-up time”
↩︎ The warehouse cache: table data kept on a running warehouse - 3.
“The warehouse is too small to hold a useful working set in the local data cache, so the query reads heavily from remote storage.”
↩︎ The warehouse cache: table data kept on a running warehouse“You can see this in Query Profile as a high remote read percentage.”
↩︎ Measuring cache use, and isolating it from the result cache“Turn off the query result cache to make the benchmark results consistent between multiple benchmark runs.”
↩︎ Measuring cache use, and isolating it from the result cache“That way, the queries only use the table data cache from the interactive warehouse.”
↩︎ Checkpoint - 4.
“each node in the cluster stores a portion of the entire data set locally”
↩︎ The warehouse cache: table data kept on a running warehouse“Metadata management, including the SNOWFLAKE database and the Snowflake Information Schema”
↩︎ Metadata-based results: answers that need no warehouse - 5.
“Percentage of data scanned from the local disk cache.”
↩︎ The warehouse cache: table data kept on a running warehouse - 6.
“A query that reuses the result of a previous query.”
↩︎ Metadata-based results: answers that need no warehouse“A query whose result is computed based purely on metadata, without accessing any data.”
↩︎ Metadata-based results: answers that need no warehouse“These queries are not processed by a virtual warehouse.”
↩︎ Exam trap 3 - 7.
“The range of values for each of the columns in the micro-partition.”
↩︎ Metadata-based results: answers that need no warehouse“some operations, such as deleting all rows from a table, are metadata-only operations.”
↩︎ Metadata-based results: answers that need no warehouse - 8.
“Snowflake maintains statistics on tables and views, and this optimization allows simple queries to run faster.”
↩︎ Metadata-based results: answers that need no warehouse