Performance Optimization · Topic 6 of 9

Caching - Layers, Expiration and Cost Impact

Know the three Snowflake cache layers: metadata, result and warehouse data cache. Learn expiry, what clears each, and the cost trade-offs of each.

  • 5 min
  • Includes a diagram
In a nutshell
  • Three layers: metadata cache and result cache in cloud services, data cache on the warehouse local disk.
  • Result cache lasts 24 hours, extended on reuse up to 31 days; needs identical text and unchanged data.
  • Result cache hits cost no warehouse credits; USE_CACHED_RESULT defaults to TRUE.

What it is

Snowflake caches at three levels. The metadata cache and the result cache live in the cloud services layer and need no running warehouse. The warehouse data cache lives on the local disks of a running warehouse. Each saves different work, expires differently and affects cost differently.

Querysubmittedsame text and data?Result cachecloud services, freemissWarehouse cachelocal disk of running warehousemissRemote storagemicro partitions

How it works

The three layers

Three cache layers: a repeated query on unchanged data is served from the result cache without a warehouse; otherwise the warehouse reads from its local cache and fetches missing data from remote storage.

LayerWhereWhat it holdsNeeds a warehouse
Metadata cacheCloud servicesRow counts, min and max, distinct counts, table statisticsNo
Result cacheCloud servicesFinal results of past queriesNo
Warehouse data cacheLocal SSD of the warehouseMicro-partition data read from storageYes, it must be running

Metadata cache. Queries that need only statistics, such as SELECT COUNT(*) FROM t, MIN and MAX on a column, or SHOW and DESCRIBE commands, can be answered without scanning data. They often run without using a warehouse.

Result cache. If the same query is run again and nothing relevant has changed, Snowflake returns the stored result at once, with no warehouse credits.

Warehouse data cache. When a query reads micro-partitions, the warehouse keeps them on local disk. Later queries over the same data read from there instead of remote storage. The Query Profile shows this as "percentage scanned from cache".

When the result cache is used

All of these must hold.

  • The new query text matches a previous query exactly, including case and spacing, after normal parsing.
  • The underlying table data has not changed since the result was produced.
  • The role has the privileges needed, and any row access or masking policies still give the same result.
  • The query does not use functions that return a different result on each run, such as RANDOM() or UUID_STRING(), and does not call external functions or read hybrid tables.
  • The previous result is still within its retention.

The parameter USE_CACHED_RESULT (default TRUE) turns the result cache on or off for a session, which is useful when benchmarking.

Exam tip

To measure real warehouse performance, set USE_CACHED_RESULT = FALSE, otherwise the second run returns from the result cache.

Cache expiration

CacheExpires when
Result cache24 hours after the result was produced, unless reused. Each reuse extends it, up to 31 days from the first run
Result cacheEarlier if the underlying data changes
Warehouse data cacheThe warehouse suspends, or the data is evicted by newer reads on a full local disk
Metadata cacheMaintained by Snowflake with the table, so it is always current

Suspending a warehouse clears its local cache completely. A multi-cluster warehouse has a separate local cache per cluster.

Run queryresult storedwithin 24 hours, data unchangedRerunserved from result cacheeach reuseExtendup to 31 days totalRun querydata changesCache invalidquery recomputes

Impact on cost

  • A result cache hit costs no warehouse credits and gives results in milliseconds. Dashboards that repeat the same queries benefit greatly.
  • A warehouse data cache hit reduces remote reads and runtime, so credits are lower, but the warehouse must stay running to keep it.
  • Short auto suspend values lose the data cache. A BI warehouse that serves repeated queries can be cheaper with a longer auto suspend, because queries finish faster on warm data.
  • Batch ETL that reads each file once gains little from the data cache, so suspend quickly.
  • Metadata only queries are cheap or free of warehouse cost, so prefer them for counts and ranges.
  • Cloud services usage has its own accounting. Cloud services charges only count when they exceed 10 percent of daily warehouse credits, which makes cache friendly metadata work almost free.
GoalAction
Cheapest repeat dashboardsKeep query text identical, avoid non deterministic functions
Faster reads on a warm warehouseUse longer auto suspend for read heavy warehouses
Fair benchmarkSet USE_CACHED_RESULT = FALSE and compare cold and warm runs
Cheapest batch ETLShort auto suspend; cache matters little

Designing to use the cache

  • Group similar queries on one warehouse, so the data cache serves several users.
  • Avoid SELECT *, so the cache holds only the columns that are read.
  • Use consistent query text from applications, such as parameterized or generated SQL with stable formatting.
  • Replace CURRENT_DATE() and CURRENT_TIMESTAMP() in dashboard queries with a parameter if freshness allows, because queries with those functions are not served from the result cache.

Common mistakes

Benchmarking with the result cache on. The rerun looks instant and proves nothing about the warehouse.

Assuming the result cache needs a running warehouse. It lives in the cloud services layer.

Expecting a warm cache after resume. Suspending clears the local data cache.

Using non deterministic functions in dashboard queries. They prevent result cache hits.

What to remember for the exam

  • Three layers: metadata cache and result cache in cloud services, data cache on the warehouse local disk.
  • Result cache lasts 24 hours, extended on reuse up to 31 days; needs identical text and unchanged data.
  • Result cache hits cost no warehouse credits; USE_CACHED_RESULT defaults to TRUE.
  • Suspending a warehouse drops its data cache; each cluster has its own cache.
  • Balance auto suspend: short for batch, longer for repeated reads.

Check yourself

  • A dashboard reruns the same query every minute and the data changes hourly. Where is it served from?

    The result cache for most runs, at no warehouse cost, until the data changes or the 24 hour period ends without reuse.

  • Why can a shorter auto suspend raise cost for a BI warehouse?

    Each resume starts with an empty data cache, so queries read from remote storage and run longer.

  • What must you change to benchmark a query fairly?

    Set USE_CACHED_RESULT = FALSE, so the result cache does not hide the real runtime.

Topic 6 of 9 in Performance Optimization

Test yourself on Performance Optimization

The full Snowflake ARA-C01 question bank has 687 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.