The three caches: when Snowflake skips the work
The fastest query is the one Snowflake never runs. Three caches make that happen, and the same query can be instant one minute and slow the next.
Ismail's favourite speed-up costs nothing: letting Snowflake skip work it has already done. Its real speed isn't raw horsepower. It's that, before scanning a single row, it asks "have I already done this, or something close to it?" Three caches answer that question. Picture an office where two analysts work on the same data all day. They stay fast by never redoing work, and they have three habits that map exactly onto the three caches.
The order Snowflake checks
A query falls through these in order and stops at the first one that can answer. The higher it stops, the faster and cheaper the answer, until the last resort: a full read from remote storage.
1 · Result cache: hand over the finished report
At 9:00, someone runs a sales total. Snowflake starts a warehouse, reads the partitions, and computes the sum. At 9:05 someone else runs the exact same query, character for character.
-- 9:00: warehouse resumes (a second or two, billed 60 s minimum), query ~200 ms SELECT sum(amount) FROM sales WHERE order_date = '2026-02-22'; -- run again at 9:05, same text, returns in ~1ms, no warehouse SELECT sum(amount) FROM sales WHERE order_date = '2026-02-22';
The second run doesn't start a warehouse, doesn't scan a byte, and costs zero credits. Snowflake kept the finished result in the cloud services layer and just handed it back. In the query profile you'd see QUERY RESULT REUSE and no warehouse name at all. It works for 24 hours, and only while three things stay true:
- The query text is identical, character for character. A changed alias or a different letter case is enough to make it a different query.
- The underlying data hasn't changed, not even through background reclustering, and the role running it can still read every table involved.
- No non-deterministic functions like
random()oruuid_string(), and no external functions.
The 24 hours aren't a hard wall: each reuse restarts the clock, up to 31 days after the first run, so a dashboard query that fires every morning on unchanged data keeps getting served for free. To benchmark a query honestly, switch the cache off for your session: ALTER SESSION SET USE_CACHED_RESULT = FALSE, otherwise your "optimisation" may just be measuring the cache.
2 · Metadata cache: check the master index
Every micro-partition already carries a summary of itself: row count, null count, and the min and max of every column. That's the "registry" from the office picture, not an index you build but statistics Snowflake keeps anyway, in the cloud services layer, current automatically. So some questions never need a warehouse at all, they're answered straight from the index.
Answered from the index, no scan
Has to wake the warehouse
METADATA-BASED RESULT node and no warehouse in the header. The moment you add avg() or a GROUP BY, the profile grows to several nodes and a warehouse turns on. That jump from one node to several is metadata vs real work.3 · Warehouse cache: the folders already on your desk
Now a query that genuinely needs data. The first run is cold: every byte travels from remote storage to the warehouse's local SSD before it can be processed. A second query that touches the same data is warm: the partitions are already on local disk, so it skips the slow trip.
-- cold: SSD empty, data fetched from remote storage SELECT * FROM sales WHERE country = 'FRANCE'; -- warm: a different query, but it reads the same partitions SELECT sum(amount) FROM sales WHERE country = 'FRANCE';
The second query has different text, so the result cache can't help, the warehouse still computes the sum. But the French partitions are already on the SSD, so it reads 100% from cache and skips remote storage entirely.
Same partitions, same warehouse. The only difference is that the data was already close by. This is also why suspending a warehouse has a hidden cost: it throws the SSD away, and the next query starts cold again.
The three caches, side by side
What the caches change in your day
QUERY RESULT REUSE on a dashboard you expected to be cached, compare two of its query texts in the history.AUTO_SUSPEND saves money but clears that cache, so for a busy BI warehouse a slightly longer suspend keeps reads warm. The result and metadata caches survive suspend either way.
