The Result Cache
Snowflake maintains a global result cache that stores the results of every query executed on the platform for up to 24 hours (extendable to 31 days as long as the query keeps being reused). If an identical query, matched exactly at the SQL text level, is resubmitted and the underlying data hasn't changed, Snowflake returns the cached result directly with essentially zero compute cost, no warehouse even needs to be running, and the query completes almost instantly. This cache is shared across the entire account, not per warehouse or per user, so if one analyst runs a report and a colleague runs the exact same query minutes later, the colleague gets the cached result too.
Cricket analogy: It's like a scoreboard operator who, when asked the exact same query, "What was the score after 10 overs?", simply reads back the number already written on the board rather than recalculating it from ball-by-ball data each time.
Warehouse Local Disk Cache
Each running virtual warehouse also maintains a local SSD cache of the raw micro-partition data it has recently read from cloud storage. When a warehouse re-scans the same table it queried recently, even with a different SQL statement (so it can't use the result cache), it can pull the relevant micro-partitions from this local disk cache instead of re-fetching them from remote storage, which is significantly faster. This cache is warmed by use and is lost when a warehouse suspends, which is one reason a warehouse that suspends and resumes frequently for a repetitive workload can feel slower on the first few queries after each resume, the cache has to warm back up.
Cricket analogy: It's like a fielding coach keeping the last few overs' field-placement charts on a clipboard rather than the archive room: as long as the same session continues, pulling up recent data is fast, but if the coach leaves and returns later, the clipboard has been cleared.
Metadata Cache and Pruning
Snowflake also stores rich metadata about every micro-partition, including min/max values per column, row counts, and distinct value estimates, in a separate metadata cache maintained by the cloud services layer. This lets certain queries, such as SELECT COUNT(*), or queries with a WHERE clause filtering on a well-clustered column, be answered or dramatically narrowed using only metadata, without scanning any actual data and without even needing a running warehouse in some cases. This same metadata drives partition pruning: when you filter on a column like order_date, Snowflake consults the min/max metadata for each micro-partition to skip ones that couldn't possibly contain matching rows, often eliminating the vast majority of a table's partitions from a scan.
Cricket analogy: It's like a scorer's summary sheet listing each innings' highest and lowest partnership without needing the full ball-by-ball log: if you ask which innings had a partnership over 150, the scorer checks the summary sheet instantly instead of replaying every ball.
-- These two queries are identical text, so the second hits the result cache
SELECT region, SUM(revenue) FROM sales WHERE order_date >= '2026-01-01' GROUP BY region;
SELECT region, SUM(revenue) FROM sales WHERE order_date >= '2026-01-01' GROUP BY region;
-- Force bypassing the result cache to test true query performance
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
-- Check partition pruning effectiveness for a filtered query
SELECT *
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE QUERY_TEXT ILIKE '%order_date%'
ORDER BY START_TIME DESC
LIMIT 5;You can confirm result cache usage by checking the Query Profile in Snowsight: a cached query shows a "Query Result Reuse" step and reports zero credits consumed, which is a quick way to verify whether a fast repeated query actually hit the cache versus just running on a warm local disk cache.
The result cache only applies when the SQL text matches exactly (including whitespace-insensitive but case-sensitive literal differences in some contexts) and the underlying table data hasn't changed since the cached result was produced. Even a trivial change, like reordering SELECT columns or changing a literal value, produces a cache miss and triggers a fresh execution.
- The result cache stores query results account-wide for 24 hours (extendable to 31 days) with zero compute cost on a hit.
- Result cache hits require an exact SQL text match and unchanged underlying data.
- Each warehouse maintains a local SSD cache of recently read micro-partitions, lost on suspension.
- The metadata cache stores per-partition min/max values, enabling partition pruning to skip irrelevant data.
- Some queries, like COUNT(*) or well-clustered filters, can be answered from metadata alone.
- Query Profile in Snowsight shows whether a query hit the result cache via a Query Result Reuse step.
- Frequent warehouse suspend/resume cycles can slow down repetitive workloads by cooling the local disk cache.
Practice what you learned
1. What is required for a query to hit Snowflake's result cache?
2. What happens to a warehouse's local disk cache when it suspends?
3. What does the metadata cache primarily enable?
4. By default, how long does a result cache entry remain valid if unused?
5. In Snowsight's Query Profile, what indicates a query used the result cache?
Was this page helpful?
You May Also Like
Clustering Keys
Understand how clustering keys influence micro-partition organization in Snowflake to improve pruning and query performance on large tables.
Virtual Warehouses
Learn how Snowflake's virtual warehouses provide independently scalable compute clusters that are fully decoupled from storage.
Multi-Cluster Warehouses
Understand how multi-cluster warehouses handle high concurrency by automatically adding and removing compute clusters.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics