Performance Optimization, Querying, and Transformation · Topic 4 of 6
Snowflake Caching
Learn how the Snowflake query result cache, metadata cache and warehouse cache work, when each one is reused, and how auto suspend affects cache hits.
- 5 min
- Includes a diagram
- Result cache: cloud services, 24 hours, reset on reuse, 31 days maximum, no warehouse needed.
- Result reuse needs identical query text, unchanged data, no run time functions and the right privileges.
USE_CACHED_RESULTdefaults toTRUE; set it toFALSEto test performance.
What it is
Snowflake has three caches, and each one lives in a different layer of the architecture.
- The query result cache in the cloud services layer stores the results of queries.
- The metadata cache in the cloud services layer holds statistics about micro-partitions and tables.
- The warehouse cache (also called the local disk or data cache) in each running virtual warehouse holds table data read from storage.
Exam questions describe a repeated or simple query and ask which cache served it, whether a warehouse was needed, and what would stop the cache from being used.
How it works
The three caches compared
| Cache | Layer | What it holds | Needs a running warehouse | Lifetime |
|---|---|---|---|---|
| Query result cache | Cloud services | Full result sets of executed queries | No | 24 hours, extended on each reuse up to 31 days |
| Metadata cache | Cloud services | Row counts, min and max per column, distinct counts and other micro-partition statistics | No | Always kept current |
| Warehouse cache | Virtual warehouse SSD and memory | Raw table data from micro-partitions read by earlier queries | Yes | Lost when the warehouse suspends |
Query result cache
When a query runs, Snowflake keeps its result for 24 hours. Each time the result is reused, the 24 hour period resets, up to a maximum of 31 days from the first run. After that the query must run again.
A later query reuses the result only when all of these hold:
- The new query text matches the earlier one exactly. A change in letter case or an added alias makes it a different query.
- The underlying table data has not changed.
- The query does not use functions evaluated at run time, such as
CURRENT_TIMESTAMPorRANDOM, or external functions. - The role running it has the privileges needed on the tables.
- The
USE_CACHED_RESULTparameter isTRUE, which is the default.
Result reuse is not limited to the same user. Any user whose role has the required privileges can get the cached result, and no warehouse needs to be running.
You can also query a previous result directly with RESULT_SCAN, for example SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())). This is handy for post processing the output of SHOW commands.
When you test performance changes, run ALTER SESSION SET USE_CACHED_RESULT = FALSE. Otherwise the second run returns from the result cache and looks instant.
Metadata cache
Snowflake records statistics for every micro-partition when data is written. Queries that can be answered from those statistics alone, such as SELECT COUNT(*) FROM t, or MIN and MAX on a column, are answered by the cloud services layer without starting a warehouse.
The same metadata powers pruning. The min and max values per column let the optimizer skip partitions before any data is read. Commands such as SHOW TABLES and DESCRIBE TABLE also run from metadata and need no warehouse.
Warehouse cache
When a warehouse reads micro-partitions from remote storage, it keeps the data on its local SSD and in memory. A later query on the same warehouse that needs the same data reads it locally, which is much faster.
- The cache belongs to one warehouse. Another warehouse does not see it.
- It is cleared when the warehouse suspends, because the compute resources are released.
- Only the columns and partitions actually read are cached.
This creates a trade-off with AUTO_SUSPEND. A very short suspend time saves credits but throws away the cache between bursts of queries. For BI warehouses that run similar queries all day, a slightly longer auto suspend can keep the cache warm.
Which cache answers which query

| Scenario | Cache used |
|---|---|
| A dashboard reruns the same SQL, data unchanged, within 24 hours | Query result cache |
SELECT COUNT(*) FROM sales with the warehouse suspended | Metadata cache |
| A new query on the same warehouse reads columns another query just read | Warehouse cache |
| Same SQL, but a row was inserted into the table | None of the result cache; the query runs again |
Common mistakes
Thinking the result cache is per user. It is shared across users, provided the role has access to the underlying data and the query text matches.
Expecting the warehouse cache to survive suspension. Suspending a warehouse releases its compute and its local cache. The next queries read from remote storage again.
Assuming any change to the table keeps the cached result. Any DML on the underlying data invalidates reuse, so the next run executes on a warehouse.
A query that includes CURRENT_TIMESTAMP() never reuses the result cache, because the function returns a different value each time. Rewriting it to filter on a fixed date can make results reusable.
What to remember for the exam
- Result cache: cloud services, 24 hours, reset on reuse, 31 days maximum, no warehouse needed.
- Result reuse needs identical query text, unchanged data, no run time functions and the right privileges.
USE_CACHED_RESULTdefaults toTRUE; set it toFALSEto test performance.- Metadata cache answers
COUNT(*),MINandMAXand drives pruning, with no warehouse. - Warehouse cache: local SSD per warehouse, cleared on suspend; balance it against auto suspend savings.
RESULT_SCAN(LAST_QUERY_ID())reads a previous result as a table.
Check yourself
A user reruns yesterday's query, the table has not changed, and the warehouse is suspended. Does the warehouse resume?
No, if it is within the 24 hour window the result comes from the query result cache in the cloud services layer.
Why might a BI warehouse with AUTO_SUSPEND of 60 seconds run repeated queries slowly?
The warehouse suspends between bursts and loses its local cache, so each burst reads data from remote storage again.
Which cache answers SELECT MAX(order_date) FROM orders without a warehouse?
The metadata cache, which holds min and max values for each column of every micro-partition.
Topic 4 of 6 in Performance Optimization, Querying, and Transformation
Test yourself on Performance Optimization, Querying, and Transformation
The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.