skip to content

How do Snowflake's query result cache and a virtual warehouse's local disk cache differ?

level: middleimportance: must knowfreq 75%

answer

  1. one cache lives in compute, one above it
  2. only one of them needs a warehouse running
  3. suspending compute costs you something
  4. results survive 24 hours, and longer if reused
  5. the profile reports a percentage scanned from cache

basics

~20 s

The query result cache stores finished result sets in the cloud services layer and serves repeats without starting any warehouse, so it costs no warehouse credits. A warehouse's local disk cache holds column data on its own SSDs, still costs full warehouse time, and vanishes when that warehouse suspends.

solid answer

~50 s

Snowflake avoids work at three levels. The **metadata cache** in cloud services holds per-micro-partition statistics, so `SELECT COUNT(*)` or a `MIN`/`MAX` on a column can be answered without touching data or starting compute. The **query result cache**, also in cloud services, keeps the finished result set of a previous query for 24 hours — reused, that window is extended up to 31 days total — and serves an identical query to any user in the account who has the right privileges, with **no warehouse resumed and no warehouse credits**. The **local disk cache** is different in kind: as a warehouse scans micro-partition files from remote storage it keeps column data on the cluster's local SSDs, so later queries on the same data do less remote I/O. It belongs to one warehouse, is dropped when that warehouse suspends, and still bills every second the warehouse runs.

code

sql · 4 lines
sql
-- Force a cold run for benchmarking, then restore reuse
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT country, SUM(amount) FROM sales GROUP BY country;
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

go deeper

for a junior

Be able to say that a repeated identical query can come back instantly without running on a warehouse, and that this is the result cache rather than anything you configure per query.

for a middle

Explain the mechanics: where each cache lives, the 24-hour retention extended up to 31 days, account-wide result reuse, and the fact that the local disk cache dies when the warehouse suspends.

for a senior

Show the cost reasoning — a result-cache hit costs no warehouse credits while a local-cache hit still bills warehouse-seconds — and read the evidence out of Query Profile and QUERY_HISTORY.

for a principal

Own the trade-off between keeping compute warm for cache locality and letting it suspend to stop the meter, and the effect that splitting workloads across many warehouses has on cache fragmentation.

## Three layers, two of them free Snowflake has three distinct mechanisms that let a query do less work, and interviewers ask you to separate them because they differ in *where they live*, *how long they last*, and *what they cost*. ### 1. Metadata cache (cloud services) Snowflake keeps statistics for every micro-partition of every table — row counts, per-column min/max values, distinct-value information, null counts — in the cloud services layer. Some queries can be answered from that metadata alone: `SELECT COUNT(*) FROM big_table`, `SELECT MIN(event_ts), MAX(event_ts) FROM big_table`, `SHOW`-style catalog lookups. These return in milliseconds without resuming a warehouse. The same statistics are what drives partition pruning for ordinary queries. ### 2. Query result cache (cloud services) When a query completes, Snowflake persists its **entire result set**. A later query that matches can be served straight from that stored result. The retention is 24 hours from the last use; each reuse extends it, up to a maximum of 31 days from original execution, after which the result is purged and the query must run again. Properties that matter: - **No warehouse is needed.** A suspended warehouse is not even resumed. The query consumes no warehouse credits, only cloud-services work. - **It is account-scoped, not per-user.** A colleague running the identical query on a different warehouse gets the cached result, provided their role has the necessary privileges on all the underlying tables. - **It is invalidated by data change.** Any DML that changes a micro-partition of a referenced table drops the result, as does background maintenance that rewrites micro-partitions. - **You can see it.** A result-cache hit shows in Query Profile as a single `QUERY RESULT REUSE` node, and `QUERY_HISTORY` shows the query scanning no bytes and finishing in milliseconds. Setting `USE_CACHED_RESULT = FALSE` in a session disables reuse, which is exactly how you benchmark a cold run. - **`RESULT_SCAN`** lets you query the stored output of a previous query by id, which is a related but separate feature from automatic reuse. ### 3. Warehouse local disk cache This one lives on the compute cluster, not in cloud services. Table data lives in remote object storage; when a warehouse scans micro-partition files it caches the column data it read on the cluster's local SSDs. A subsequent query needing the same columns of the same micro-partitions reads locally instead of over the network. Query Profile reports this as **Percentage scanned from cache** in its IO statistics. Properties that matter: - **It is per-warehouse.** Warehouse A's cache does nothing for warehouse B. Splitting one workload across many warehouses fragments the cache and can make everything colder. - **It dies with the compute.** Suspending a warehouse releases the compute resources and the cache with them; the next resume starts cold. Newly added compute — a resize upward, or an extra cluster in a multi-cluster warehouse — also starts cold. - **It still costs money.** A local-cache hit shortens the query but the warehouse is running, so you pay per second either way. It reduces *latency and I/O*, not the *billing unit*. ## The cost distinction in one line A dashboard query that hits the result cache is essentially free. The same query hitting only the local disk cache is fast but still billed for the warehouse-seconds it takes. That is why "make the repeated query result-cacheable" is a far stronger cost lever than "keep the warehouse warm". ## The interaction with auto-suspend This is where teams get it wrong in both directions. Setting auto-suspend very aggressively throws away the local cache constantly, so every query pays remote I/O and the 60-second resume minimum. Setting it very long keeps the cache warm but bills idle seconds. The result cache is unaffected by suspension either way, which is the argument for shaping repeated BI traffic so it can be reused rather than for keeping compute hot. ## What it does not do None of these layers help a query that is genuinely new each time — a fresh date range, a new filter value, a randomly generated predicate. For those the levers are pruning, query shape, and materialization, not caching.

  • Two analysts on different warehouses run the same query — does the second one get a cached result?
    Yes. The query result cache is account-scoped, not session- or warehouse-scoped, so the second analyst is served from it provided the query text matches, the underlying data has not changed, and their role has the required privileges on every referenced table. The local disk cache, by contrast, is private to one warehouse.
  • Does splitting a workload across several warehouses hurt caching?
    It fragments the local disk cache: each warehouse builds its own copy of hot micro-partition data, so the same scan is paid for repeatedly and each cluster runs colder. The result cache is unaffected — it is account-wide. Isolation is usually still worth it, but expect more remote I/O than a single warm warehouse would show.
  • How would you confirm from Query Profile that a query benefited from caching rather than from a better plan?
    A result-cache hit shows as a single QUERY RESULT REUSE node with no scan at all. A local-cache benefit shows in the IO statistics as a high Percentage scanned from cache with bytes still scanned. If neither appears, the improvement came from pruning, a plan change, or less spilling.

The result cache is a photocopy of the finished answer filed at reception — anyone with the right badge can collect it without opening the office. The local disk cache is the pile of books left open on one desk: useful only at that desk, and cleared the moment the desk is vacated.

saying these in an interview costs you the question

  • Treats result cache and warehouse cache as the same thing
  • Claims caching keeps working after the warehouse suspends, for both caches
  • Says a local cache hit makes the query free
  • Believes the result cache is per-user or per-session
  • Thinks a bigger warehouse is needed to serve a cached result

context

resolving links…