Course contents

The Analytics Engineer’s Field GuideSnowflake: the engineStep 6: The three caches

The three caches

Part 4 · Snowflake · step 6

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.

Hand over the finished report
result cache
"Someone already ran this exact query this morning. Here's their answer."
Check the master index
metadata cache
"I don't need to open the boxes. The front-desk registry already knows the count and the min and max."
Keep folders on the desk
warehouse cache
"I pulled these files up an hour ago, they're still on my desk. No trip to the basement."

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.

queryarrives
① result cachesame query, 24h?↓ hit: answer, 0 credits
miss
② metadatamin/max answers it?↓ hit: no warehouse needed
miss
③ warehouse cachedata already local?↓ miss: read remote storage
each step is cheaper than the next · the expensive read is the last resort, not the default
Result cache
cloud · 24h · no warehouse
→
Metadata cache
cloud · counts, min/max
→
Warehouse SSD
local data cache
→
Remote storage
full scan
↑ stops at the first that can answer, higher up is faster and cheaper

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() or uuid_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
SELECT count(*) FROM salesRow count is in the metadata.
SELECT min(order_date) ...Min/max per column is in the metadata.
max(), and combinations of themOn the full table, with no WHERE.
Has to wake the warehouse
avg(amount)An average isn't stored, it must read rows.
GROUP BY countryGrouping needs the actual rows.
Any WHERE that filters rowsThe index summarises partitions, not row-level filters.
The tell in the profile
A metadata answer shows a single 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 data, two runscold (remote)warm (SSD)
Where the data came fromremote storage→local SSD
% scanned from cache0.00%→100.00%
Total time~289 ms→~17 ms

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

cache
lives in
answers
survives suspend?
result
cloud services
whole query results (24h, renewed on reuse, 31 days max)
yes
metadata
cloud services
count, min / max (no WHERE)
yes
warehouse
the warehouse SSD
recently-read partitions
no

What the caches change in your day

The result cache is free money
A dashboard reloading the same query all morning is served from the result cache, zero compute, until the data behind it changes. You don't enable it, it's automatic. The catch for BI tools: many of them inject the current date or a random id into the SQL, and a single changed character is a different query. If the profile never shows QUERY RESULT REUSE on a dashboard you expected to be cached, compare two of its query texts in the history.
Warm the cache by sharing warehouses sensibly
The warehouse cache only helps queries that land on the same warehouse. Put a team's BI dashboards on one warehouse and they warm each other's data. Aggressive 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.

Lesson quiz

  1. A colleague runs exactly the same query text five minutes after you, on unchanged data. What serves it?

    1. The result cache
    2. The metadata cache
    3. The warehouse's local SSD cache
    4. The cloud provider's storage cache
  2. Why is the first query after a warehouse resumes often slower?

    1. A resumed warehouse runs at reduced speed for its first billed minute
    2. The metadata cache has to be rebuilt from scratch after every single resume
    3. Suspending dropped the local SSD cache, so data comes from remote storage
    4. The result cache expires whenever the warehouse that built it suspends
  3. A dashboard query ran on BI_WH at 08:00. At 08:30 a loader inserted rows into its table, and at 09:00 the same query reruns on BI_WH, which never suspended. What answers it?

    1. BI_WH runs it again, partly from its local SSD
    2. Remote storage only, since inserts empty the SSD
    3. The result cache, since the query text is identical
    4. The metadata cache, since no rows were deleted