Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 38 ยท Architecture

Caching Layers

Learn Snowflake's three caches: the 24-hour result cache, the warehouse local disk cache and the metadata cache. See when each applies and how to test it.

Snowflake caches aggressively at several levels. Knowing which cache served your query explains why the second run is fast, why a freshly resumed warehouse seems slow and why benchmarks sometimes lie. There are three caches, and each lives in a different layer.

flowchart TB
  A["Query"] --> B{"Result cache<br/>(cloud services)"}
  B -- hit --> R["Return saved result<br/>no warehouse used"]
  B -- miss --> C["Warehouse"]
  C --> D{"Local disk cache<br/>(warehouse SSD)"}
  D -- hit --> E["Read cached partitions"]
  D -- miss --> F["Read from storage layer"]

A query is checked against the result cache first, then the warehouse cache, then remote storage.

1. The result cache

When a query finishes, Snowflake saves its result in the cloud services layer for 24 hours. If you (or anyone with the right privileges) run the same query again, and the underlying data has not changed, Snowflake returns the saved result without using a warehouse. Each time the cached result is reused, the 24 hours are extended, up to a maximum of 31 days.

Conditions for a result-cache hit:

  • The SQL text is the same (same spelling, same literals; whitespace and case in keywords are normalized, but a different alias or column order is a different query).
  • The data in the tables has not changed (no inserts, updates, deletes or reclustering since).
  • The query does not use non-deterministic functions like RANDOM() or CURRENT_TIMESTAMP().
  • The role running it has the same access to the data.
  • The session parameter USE_CACHED_RESULT is TRUE (the default).

A result-cache hit is free of compute credits and returns in milliseconds. You can see it in the Query Profile as "Query Result Reuse".

SQL
-- Snowflake SQL (read-only)
ALTER SESSION SET USE_CACHED_RESULT = FALSE;   -- for honest benchmarking
SELECT region, SUM(total) FROM shop.raw.orders GROUP BY region;
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

Always turn the result cache off when you time a query, otherwise your second measurement is meaningless.

2. The warehouse (local disk) cache

Each warehouse node has local SSD storage. When a query reads micro-partitions from the remote storage layer, the warehouse keeps copies on those SSDs. The next query that needs the same partitions reads them locally, which is much faster than fetching over the network. This cache holds raw table data (the needed columns), not results, so different queries can benefit as long as they read the same data.

Important properties:

  • It belongs to one warehouse. Another warehouse starts with its own, empty cache.
  • It is lost when the warehouse suspends. A freshly resumed warehouse is "cold".
  • It is managed automatically with a least-recently-used policy; a bigger warehouse has more cache.

This is the real trade-off with AUTO_SUSPEND. A dashboard warehouse with AUTO_SUSPEND = 60 that is queried every 3 minutes starts cold every time, paying both resume time and cache misses. With AUTO_SUSPEND = 300 it stays warm and may finish queries much faster, while idle cost is small. Run the numbers, as in the stand-in below, which compares a suspended-every-time pattern with a stay-warm pattern:

SQL Stand-in: cost of staying warm versus going cold

Press Run (or Ctrl + Enter) to see the result here.

The numbers are invented, but they show the pattern: a few extra billed minutes can save users much more waiting time. Measure in your own account before deciding.

3. The metadata cache

The cloud services layer holds table statistics and partition metadata, as described in Cloud Services and Metadata. Queries like COUNT(*) or MAX(date) and SHOW commands use it. It needs no warehouse and is always on. You cannot configure it.

Which cache served my query?

In the Query Profile for a query:

  • "Query Result Reuse" means the result cache.
  • "Percentage scanned from cache" in the Profile Overview shows the warehouse local cache usage. A high number means most data came from the warehouse SSD.
  • A query that scanned 0 bytes and ran instantly probably used metadata.

In SQL you can check from the history:

SQL
-- Snowflake SQL (read-only)
SELECT query_id,
       total_elapsed_time,
       bytes_scanned,
       percentage_scanned_from_cache
FROM TABLE(information_schema.query_history(RESULT_LIMIT => 10))
ORDER BY start_time DESC;

Practical rules

  • To benchmark: set USE_CACHED_RESULT = FALSE, run the query twice and compare cold versus warm; suspend and resume the warehouse to reset the local cache.
  • Dashboards that run identical queries all day benefit from result reuse. Avoid CURRENT_TIMESTAMP() inside them if you can pass a date from the BI tool instead.
  • Do not suspend busy warehouses too eagerly if the same data gets queried again within minutes.
  • Do not run SELECT * just to "warm" a cache. It evicts useful data and wastes credits.

Here is a small model of result-cache behavior you can run: results saved by query text and invalidated when the table changes.

SQL Stand-in: a result cache keyed by query text

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How long does Snowflake normally keep a saved query result for reuse?

    The result cache lasts 24 hours and the clock resets each time the result is reused, up to 31 days from the original run.
  2. 2. What empties the warehouse local disk cache?

    The cache lives on the warehouse nodes. When they are released on suspend, the cache is lost.
  3. 3. Which query will NOT be served from the result cache?

    Non-deterministic functions such as CURRENT_TIMESTAMP() prevent reuse.
  4. 4. How should you time a query fairly?

    Disabling the result cache ensures each run actually executes.

Summary

  • Result cache (cloud services): same query, unchanged data, 24 hours, no compute.
  • Local disk cache (per warehouse): raw partitions on SSD, lost on suspend.
  • Metadata cache: counts, min/max and SHOW results, always available.
  • Turn off USE_CACHED_RESULT for benchmarks.
  • Balance AUTO_SUSPEND against the benefit of a warm cache.

Next: Time Travel, Fail-safe and Cloning