Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 38 ยท Warehouses and Performance

The Snowflake Caching Layers

Learn the three Snowflake caches: metadata, the 24-hour result cache and the warehouse SSD cache. See when each is used, what invalidates it and how to test it.

The fastest query is the one that does no work. Snowflake keeps three kinds of cache, and knowing which one answered your query explains why the same statement can take 40 seconds once and 40 milliseconds the next time. It also explains odd benchmarks: if you time a query twice, the second run measures the cache, not your design.

The three layers

flowchart TB
  Q["Your query"] --> R{"Result cache<br/>(cloud services)"}
  R -->|"hit"| A["Return stored result"]
  R -->|"miss"| W["Warehouse runs it"]
  W --> L{"Local disk cache<br/>(SSD on warehouse)"}
  L -->|"hit"| P["Read cached micro-partitions"]
  L -->|"miss"| S["Read from cloud storage"]

Metadata answers some queries before any of this, without a warehouse.

1. Metadata cache

Snowflake stores statistics about every micro-partition: the row count and the minimum and maximum of each column. Queries that need only these numbers can be answered from metadata, with no warehouse:

SQL
SELECT COUNT(*) FROM orders;               -- row count is in metadata
SELECT MIN(order_date), MAX(order_date)
FROM orders;                                -- min and max are in metadata

In the Query Profile such queries show a metadata-based result and consume no warehouse credits. The same statistics power partition pruning (see pruning and the Query Profile).

2. Result cache

When a query finishes, its result is stored by the cloud services layer for 24 hours. If the same query is run again and the cache is still valid, Snowflake returns the stored result without starting a warehouse and without using credits. Each reuse resets the 24 hours, up to a maximum of 31 days since the first run.

A cached result is reused only when all of these hold:

  • the query text is identical (aside from details like whitespace-insensitive matching rules; a different alias or a changed literal means a different query),
  • the underlying data has not changed (no insert, update, delete or other change to the tables),
  • the query contains no non-deterministic functions such as RANDOM(), UUID_STRING() or CURRENT_TIMESTAMP(),
  • the role running it has the privileges needed, and
  • the parameter USE_CACHED_RESULT is TRUE (the default).

You can switch it off, which is what you do when benchmarking:

SQL
ALTER SESSION SET USE_CACHED_RESULT = FALSE;

Results are shared between users who run the same query with sufficient privileges, so one analyst's dashboard query can warm it for the whole team.

3. Warehouse (local disk) cache

When a warehouse reads micro-partitions from cloud storage, it keeps copies on its local SSD and in memory. Another query that needs the same partitions reads them locally, much faster. This cache belongs to the warehouse:

  • It is lost when the warehouse suspends and is smaller on smaller warehouses.
  • It benefits queries that touch the same tables, even with different SQL.
  • It is why a very short auto-suspend can make a BI warehouse feel slow: every resume starts cold.

A cache simulator

The next example is a stand-in written in Python. It models a result cache keyed by query text that is invalidated when the data version changes, and a partition cache that is cleared when the warehouse is suspended. It is not Snowflake code; it shows the rules.

Python Result cache and warehouse cache (stand-in)

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Step 3 shows that a different query reuses the warehouse cache (no partitions read from storage) even though it cannot use the result cache. Steps 4 and 5 show invalidation and the cost of a cold start.

A real fingerprint of the result cache

The result cache idea, "same question, same data, same answer", also works in a plain database. This SQLite example stores results of a query in a cache table and invalidates it whenever the data changes, using a trigger.

SQL Invalidating a cached result with a trigger ยท database: shop

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

The first listing shows valid = 1, the second valid = 0. Snowflake does the equivalent bookkeeping for you with table versions.

Practical rules

  • Benchmark honestly: turn off USE_CACHED_RESULT, and compare warm and cold warehouse runs separately.
  • Dashboards: many users running the identical query benefit most from the result cache. Avoid CURRENT_TIMESTAMP() in them, or move it to a parameter rounded to the hour.
  • Do not suspend too eagerly on interactive warehouses.
  • Look in the Query Profile: "Query Result Reuse" means the result cache served you; "Percentage scanned from cache" shows the warehouse cache effect.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How long is a query result normally kept in the result cache?

    Results are kept for 24 hours, and each reuse extends it, up to 31 days from the first run.
  2. 2. Which event invalidates a cached result?

    Cached results are valid only while the underlying data is unchanged.
  3. 3. What is lost when a warehouse suspends?

    The SSD cache belongs to the running warehouse. Results and metadata live in the cloud services layer.
  4. 4. Why might a query using CURRENT_TIMESTAMP() never hit the result cache?

    A result that depends on the current time cannot be safely reused.
  5. 5. How do you benchmark a query fairly in a session?

    Disabling the result cache makes each run actually execute on the warehouse.

Summary

  • Metadata answers counts and min/max without a warehouse.
  • The result cache returns identical queries on unchanged data for free, for 24 hours.
  • The warehouse cache holds partitions on SSD and disappears at suspend.
  • Disable the result cache when benchmarking; do not over-eagerly suspend interactive warehouses.

Next: Pruning and the Query Profile