Caching in Snowflake
Snowflake · Warehouses & Performance
Theory
Snowflake caches at three levels. The result cache in Cloud Services keeps the result of each query for 24 hours; if the same query text runs again, the underlying data has not changed and the role has access, the stored result is returned without using a warehouse. The local disk (warehouse) cache keeps recently read table data on the warehouse's SSDs, so repeat scans are faster, but it is lost when the warehouse suspends. The metadata cache holds statistics such as row counts and min/max values, so queries like SELECT COUNT(*) FROM orders can be answered from metadata alone.
For example, a dashboard refreshing the same query every hour is usually served from the result cache after the first run.
Why we use it
Caching avoids repeating work. It makes repeated dashboard queries near-instant, cuts warehouse credits, and explains why the second run of a query is often much faster than the first.
When to use it
Rely on it for repeated BI queries and stable reports. Turn it off with USE_CACHED_RESULT = FALSE when benchmarking real query speed. Consider a longer AUTO_SUSPEND for warehouses whose workloads benefit from a warm local cache.
Result Cache
Stores a query's complete result for 24 hours, so re-running an identical query returns instantly with zero compute cost — no warehouse even needs to be running.
Local Disk (Warehouse) Cache
Keeps recently scanned data on the warehouse's own nodes so a follow-up query touching the same data re-scans faster; this cache is lost when the warehouse suspends.
Metadata Cache
Remembers the min/max values stored per micro-partition, letting the optimizer skip partitions that can't match a filter without reading a single byte of their data.
How it works
- Query arrives
- Same query, unchanged data, result less than 24 hours old? Yes: Serve from the result cache, no warehouse. No: Send to the warehouse.
- Is the needed data in the warehouse's local disk cache? Yes: Read it from local SSD. No: Read it from remote storage.
- Execute and return the result
- Store the new result in the result cache
Example
-- Run this twice — the second run returns from cache instantly
SELECT COUNT(*) AS total_orders, SUM(amount) AS total_revenue, AVG(amount) AS avg_order
FROM orders;
SELECT QUERY_ID, QUERY_TEXT, BYTES_SCANNED, PERCENTAGE_SCANNED_FROM_CACHE
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
ORDER BY START_TIME DESC LIMIT 10;Key points
- Result cache serves identical queries for 24 hours, reset each time it is reused
- Results are not reused if the data changed or the query has non-deterministic functions
- Local disk cache lives on warehouse nodes and is cleared on suspend
- Metadata answers simple COUNT/MIN/MAX queries without scanning data
- Set USE_CACHED_RESULT = FALSE when timing queries