Performance Optimization · Topic 6 of 9
Caching - Layers, Expiration and Cost Impact
Know the three Snowflake cache layers: metadata, result and warehouse data cache. Learn expiry, what clears each, and the cost trade-offs of each.
- 5 min
- Includes a diagram
- Three layers: metadata cache and result cache in cloud services, data cache on the warehouse local disk.
- Result cache lasts 24 hours, extended on reuse up to 31 days; needs identical text and unchanged data.
- Result cache hits cost no warehouse credits;
USE_CACHED_RESULTdefaults to TRUE.
What it is
Snowflake caches at three levels. The metadata cache and the result cache live in the cloud services layer and need no running warehouse. The warehouse data cache lives on the local disks of a running warehouse. Each saves different work, expires differently and affects cost differently.
How it works
The three layers

| Layer | Where | What it holds | Needs a warehouse |
|---|---|---|---|
| Metadata cache | Cloud services | Row counts, min and max, distinct counts, table statistics | No |
| Result cache | Cloud services | Final results of past queries | No |
| Warehouse data cache | Local SSD of the warehouse | Micro-partition data read from storage | Yes, it must be running |
Metadata cache. Queries that need only statistics, such as SELECT COUNT(*) FROM t, MIN and MAX on a column, or SHOW and DESCRIBE commands, can be answered without scanning data. They often run without using a warehouse.
Result cache. If the same query is run again and nothing relevant has changed, Snowflake returns the stored result at once, with no warehouse credits.
Warehouse data cache. When a query reads micro-partitions, the warehouse keeps them on local disk. Later queries over the same data read from there instead of remote storage. The Query Profile shows this as "percentage scanned from cache".
When the result cache is used
All of these must hold.
- The new query text matches a previous query exactly, including case and spacing, after normal parsing.
- The underlying table data has not changed since the result was produced.
- The role has the privileges needed, and any row access or masking policies still give the same result.
- The query does not use functions that return a different result on each run, such as
RANDOM()orUUID_STRING(), and does not call external functions or read hybrid tables. - The previous result is still within its retention.
The parameter USE_CACHED_RESULT (default TRUE) turns the result cache on or off for a session, which is useful when benchmarking.
To measure real warehouse performance, set USE_CACHED_RESULT = FALSE, otherwise the second run returns from the result cache.
Cache expiration
| Cache | Expires when |
|---|---|
| Result cache | 24 hours after the result was produced, unless reused. Each reuse extends it, up to 31 days from the first run |
| Result cache | Earlier if the underlying data changes |
| Warehouse data cache | The warehouse suspends, or the data is evicted by newer reads on a full local disk |
| Metadata cache | Maintained by Snowflake with the table, so it is always current |
Suspending a warehouse clears its local cache completely. A multi-cluster warehouse has a separate local cache per cluster.
Impact on cost
- A result cache hit costs no warehouse credits and gives results in milliseconds. Dashboards that repeat the same queries benefit greatly.
- A warehouse data cache hit reduces remote reads and runtime, so credits are lower, but the warehouse must stay running to keep it.
- Short auto suspend values lose the data cache. A BI warehouse that serves repeated queries can be cheaper with a longer auto suspend, because queries finish faster on warm data.
- Batch ETL that reads each file once gains little from the data cache, so suspend quickly.
- Metadata only queries are cheap or free of warehouse cost, so prefer them for counts and ranges.
- Cloud services usage has its own accounting. Cloud services charges only count when they exceed 10 percent of daily warehouse credits, which makes cache friendly metadata work almost free.
| Goal | Action |
|---|---|
| Cheapest repeat dashboards | Keep query text identical, avoid non deterministic functions |
| Faster reads on a warm warehouse | Use longer auto suspend for read heavy warehouses |
| Fair benchmark | Set USE_CACHED_RESULT = FALSE and compare cold and warm runs |
| Cheapest batch ETL | Short auto suspend; cache matters little |
Designing to use the cache
- Group similar queries on one warehouse, so the data cache serves several users.
- Avoid
SELECT *, so the cache holds only the columns that are read. - Use consistent query text from applications, such as parameterized or generated SQL with stable formatting.
- Replace
CURRENT_DATE()andCURRENT_TIMESTAMP()in dashboard queries with a parameter if freshness allows, because queries with those functions are not served from the result cache.
Common mistakes
Benchmarking with the result cache on. The rerun looks instant and proves nothing about the warehouse.
Assuming the result cache needs a running warehouse. It lives in the cloud services layer.
Expecting a warm cache after resume. Suspending clears the local data cache.
Using non deterministic functions in dashboard queries. They prevent result cache hits.
What to remember for the exam
- Three layers: metadata cache and result cache in cloud services, data cache on the warehouse local disk.
- Result cache lasts 24 hours, extended on reuse up to 31 days; needs identical text and unchanged data.
- Result cache hits cost no warehouse credits;
USE_CACHED_RESULTdefaults to TRUE. - Suspending a warehouse drops its data cache; each cluster has its own cache.
- Balance auto suspend: short for batch, longer for repeated reads.
Check yourself
A dashboard reruns the same query every minute and the data changes hourly. Where is it served from?
The result cache for most runs, at no warehouse cost, until the data changes or the 24 hour period ends without reuse.
Why can a shorter auto suspend raise cost for a BI warehouse?
Each resume starts with an empty data cache, so queries read from remote storage and run longer.
What must you change to benchmark a query fairly?
Set
USE_CACHED_RESULT = FALSE, so the result cache does not hide the real runtime.
Topic 6 of 9 in Performance Optimization
Test yourself on Performance Optimization
The full Snowflake ARA-C01 question bank has 687 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.