Snowflake
Result Caching: Query, Metadata & Warehouse Caches
Leverage Snowflake's multi-layer caching to reduce cost and latency on repeated queries.
Snowflake has three caches that work differently. Telling them apart is a standard interview question, and it decides real money.
The result cache is like getting the exact same photocopy handed back instantly because someone already made that exact copy today; the metadata cache is like being told a book's page count from its catalog entry without opening it; the warehouse cache is like a librarian who remembers where the books were on the cart from your last visit, but forgets once they go home for the day.
Key Concepts
1
1. The result cache — the whole answer, free. Run a query, and for 24 hours an identical query returns
the stored result.
2
No warehouse runs. No credits are consumed. The warehouse does not even need to be resumed.
3
The conditions are strict: the SQL must match, the session context must match, and the underlying data must
not have changed. Any DML on a source table invalidates it.
4
The 24 hours extend each time the result is reused, up to 31 days.
5
This is why a dashboard refreshed by ten people costs one query. It is also why an innocuous change —
adding CURRENT_TIMESTAMP() to a query, or a tool appending a unique comment to every statement — quietly
turns a free dashboard into a paid one.
CURRENT_TIMESTAMP()
6
2. The metadata cache — answers with no data read. The cloud services layer knows each micro-partition's
row count and per-column minimum and maximum. Some queries need nothing else:
7
SELECT COUNT(*) FROM orders; -- from metadata
SELECT MAX(order_date) FROM orders; -- from metadata
SELECT MIN(id), MAX(id) FROM orders; -- from metadata8
Instant, no warehouse, no cost, on a table of any size. SELECT COUNT(*) on a billion rows returns
immediately — which surprises people used to databases where that is a full scan.
SELECT COUNT(*)
9
3. The warehouse cache — data on local disk. A running warehouse keeps the micro-partitions it has read
on its local SSD. Later queries touching the same data read it from there instead of from object storage.
10
This one belongs to a specific warehouse and disappears when it suspends.
11
The three differ on every axis.
cache scope survives suspend costs credits
result whole account yes no
metadata whole account yes no
warehouse one warehouse no yes (the warehouse runs)12
The auto-suspend trade-off comes from the third one. Aggressive suspension saves credits and empties the
local cache, so the next query re-reads from storage.
13
For a warehouse queried every few minutes by a dashboard, a slightly longer suspend keeps the cache warm.
For one used twice a day, suspend at 60 seconds — the cache would be stale and irrelevant anyway.
14
Note that this only affects the warehouse cache. The result cache is unaffected by suspension, which is why
repeated identical dashboard queries stay free regardless.
15
What the interviewer is probing.1. "What are the three caches?" Probing: the layers. Stalls: "One cache." Moves up:
metadata answering some queries with no compute, the warehouse local disk cache of micro-partitions,
and the 24-hour result cache in cloud services.
16
2. "When does the result cache not apply?" Probing: the conditions. Stalls: "When data
changes." Moves up: if the underlying data changed, the query text differs at all, or it uses
non-deterministic functions like CURRENT_TIMESTAMP.
17
3. "What does the result cache cost?" Probing: the attraction. Stalls: "Credits." *Moves
up:* nothing — it is served without a warehouse, which is why repeated dashboard queries can be
free.
18
4. "Why does suspending a warehouse hurt?" Probing: the connection. Stalls: "It restarts
slowly." Moves up: the local cache is lost, so the next queries re-read from storage.