Performance Monitoring and Tuning · Topic 5 of 16

Caching - Result, Local Disk (Warehouse) and Metadata Caches

Compare Snowflake caches: result cache, local disk warehouse cache and metadata cache, and what happens to the warehouse cache on suspend and resume.

  • 5 min
  • Includes a diagram
In a nutshell
  • Result cache: cloud services, 24 hours renewed on reuse up to 31 days, needs no warehouse, controlled by USE_CACHED_RESULT.
  • Warehouse cache: local SSD and memory, per warehouse, dropped on suspend, cold on resume.
  • Metadata cache: statistics in cloud services; answers COUNT(*), MIN, MAX without a warehouse.

What it is

A query first checks the result cache in cloud services if the same query ran on unchanged data, otherwise it uses the warehouse local disk cache, and if that is cold it reads remote storage micro-partitions. Snowflake keeps three kinds of cache, one per layer of the architecture. They save time and credits in different ways, and the exam tests which cache answers a given query and what clears it.

CacheLayerHoldsNeeds a running warehouse
Result cacheCloud servicesFinal results of past queriesNo
Local disk (warehouse) cacheVirtual warehouseTable data read from storage, on local SSD and memoryYes
Metadata cacheCloud servicesTable statistics such as row counts and min and max valuesNo
Query arrivesSQLsame query, data unchanged?Result cachecloud servicesno, need dataWarehouse cachelocal diskno, coldRemote storagemicro partitions

How it works

Result cache

When a query finishes, Snowflake keeps its result. If the same query is submitted again, the stored result is returned without using a warehouse, so it costs no compute credits.

A cached result is reused when all of these hold:

  • The new query text matches the earlier one exactly, apart from limited cases.
  • The underlying table data has not changed since.
  • The query does not use functions that change per run, such as RANDOM or UUID_STRING.
  • The role running it has the privileges needed on the objects.
  • The USE_CACHED_RESULT parameter is TRUE, which is its default.

A persisted result lasts 24 hours after it was last used. Each reuse resets that timer, up to a maximum of 31 days after the first run, after which the result is purged.

ALTER SESSION SET USE_CACHED_RESULT = FALSE;   -- for fair benchmarking
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));   -- reuse a prior result

RESULT_SCAN queries the result of an earlier statement, within the same retention window.

Exam tip

When testing tuning changes, turn off USE_CACHED_RESULT. Otherwise a repeat run is instant and proves nothing.

Local disk (warehouse) cache

Each running warehouse holds data it has read from remote storage on local SSD and in memory. A later query that needs the same micro partitions reads them locally instead of from storage. The profile shows this as Percentage scanned from cache in the IO statistics.

Characteristics:

  • It belongs to one warehouse. A second warehouse starts with its own empty cache.
  • It caches table data, not results.
  • When it is full, older data is evicted to make room.
  • It is warmed by the queries that run, so a workload that repeatedly reads the same tables gains the most.

Impact of suspend and resume on the local disk cache

The warehouse cache lives on the compute nodes. When a warehouse is suspended its nodes are released and the cache is dropped. When the warehouse resumes, it starts with a cold cache and rebuilds it from remote storage as queries run.

EventWarehouse cache
Warehouse suspendedLost
Warehouse resumedEmpty, then warmed by queries
Result cacheUnaffected by suspend
Metadata cacheUnaffected by suspend

This creates a real trade off. A short AUTO_SUSPEND saves credits when the warehouse is idle but throws away the cache, so the next queries run slower. For a workload that rereads the same data, such as dashboards, a longer auto suspend can be cheaper overall because queries finish sooner. Note that the result cache keeps working while the warehouse is suspended.

Warehouse runningcache warmAUTO_SUSPEND firesSuspendedcache droppedquery arrives, resumeRunning againcache coldqueries warm itRunningcache warm

Metadata cache

The cloud services layer stores metadata about every table and micro partition: row counts, byte sizes, and min and max values per column, plus object definitions. Because of this, some queries are answered from metadata with no warehouse at all.

  • SELECT COUNT(*) FROM t with no filter.
  • MIN and MAX on a column, in common cases.
  • SHOW and DESCRIBE commands.

The Query Profile shows a metadata based result node for these. The metadata cache also feeds compile time pruning, which is how the optimizer decides which partitions to skip.

Watch out

The metadata cache is not the same as the result cache. Metadata answers depend on current statistics and need no matching earlier query.

Common mistakes

Believing a suspended warehouse keeps its cache. The warehouse cache is dropped on suspend.

Expecting the result cache after data changes. If the underlying data changed, the stored result is not reused.

Using non deterministic functions and wondering why the result cache misses. RANDOM or UUID_STRING in the query prevents reuse.

Thinking caches are shared by all warehouses. The result and metadata caches are account wide services, but the local disk cache is per warehouse.

What to remember for the exam

  • Result cache: cloud services, 24 hours renewed on reuse up to 31 days, needs no warehouse, controlled by USE_CACHED_RESULT.
  • Warehouse cache: local SSD and memory, per warehouse, dropped on suspend, cold on resume.
  • Metadata cache: statistics in cloud services; answers COUNT(*), MIN, MAX without a warehouse.
  • Short AUTO_SUSPEND saves credits but loses the warehouse cache.
  • Disable the result cache when benchmarking.

Check yourself

  • A warehouse is suspended and resumed. Which cache is lost?

    The local disk warehouse cache, which restarts cold; result and metadata caches stay.

  • Which cache lets a repeated query run with no warehouse?

    The result cache, if the query and data are unchanged.

  • Why can a long AUTO_SUSPEND be cheaper for a dashboard workload?

    It keeps the warehouse cache warm so queries read less from remote storage and finish sooner.

Topic 5 of 16 in Performance Monitoring and Tuning

Test yourself on Performance Monitoring and Tuning

The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.