Performance Optimization, Querying, and Transformation · Topic 4 of 6

Snowflake Caching

Learn how the Snowflake query result cache, metadata cache and warehouse cache work, when each one is reused, and how auto suspend affects cache hits.

  • 5 min
  • Includes a diagram
In a nutshell
  • Result cache: cloud services, 24 hours, reset on reuse, 31 days maximum, no warehouse needed.
  • Result reuse needs identical query text, unchanged data, no run time functions and the right privileges.
  • USE_CACHED_RESULT defaults to TRUE; set it to FALSE to test performance.

What it is

Snowflake has three caches, and each one lives in a different layer of the architecture.

  • The query result cache in the cloud services layer stores the results of queries.
  • The metadata cache in the cloud services layer holds statistics about micro-partitions and tables.
  • The warehouse cache (also called the local disk or data cache) in each running virtual warehouse holds table data read from storage.

Exam questions describe a repeated or simple query and ask which cache served it, whether a warehouse was needed, and what would stop the cache from being used.

How it works

The three caches compared

CacheLayerWhat it holdsNeeds a running warehouseLifetime
Query result cacheCloud servicesFull result sets of executed queriesNo24 hours, extended on each reuse up to 31 days
Metadata cacheCloud servicesRow counts, min and max per column, distinct counts and other micro-partition statisticsNoAlways kept current
Warehouse cacheVirtual warehouse SSD and memoryRaw table data from micro-partitions read by earlier queriesYesLost when the warehouse suspends
Querysame text, data unchangedResult cachecloud servicesreturnsResultno warehouseQueryCOUNT or MIN or MAXMetadata cachecloud servicesreturnsResultno warehouseQueryneeds table dataWarehouse cachelocal SSDmiss readsRemote storagemicro partitions

Query result cache

When a query runs, Snowflake keeps its result for 24 hours. Each time the result is reused, the 24 hour period resets, up to a maximum of 31 days from the first run. After that the query must run again.

A later query reuses the result only when all of these hold:

  • The new query text matches the earlier one exactly. A change in letter case or an added alias makes it a different query.
  • The underlying table data has not changed.
  • The query does not use functions evaluated at run time, such as CURRENT_TIMESTAMP or RANDOM, or external functions.
  • The role running it has the privileges needed on the tables.
  • The USE_CACHED_RESULT parameter is TRUE, which is the default.

Result reuse is not limited to the same user. Any user whose role has the required privileges can get the cached result, and no warehouse needs to be running.

You can also query a previous result directly with RESULT_SCAN, for example SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())). This is handy for post processing the output of SHOW commands.

Exam tip

When you test performance changes, run ALTER SESSION SET USE_CACHED_RESULT = FALSE. Otherwise the second run returns from the result cache and looks instant.

Metadata cache

Snowflake records statistics for every micro-partition when data is written. Queries that can be answered from those statistics alone, such as SELECT COUNT(*) FROM t, or MIN and MAX on a column, are answered by the cloud services layer without starting a warehouse.

The same metadata powers pruning. The min and max values per column let the optimizer skip partitions before any data is read. Commands such as SHOW TABLES and DESCRIBE TABLE also run from metadata and need no warehouse.

Warehouse cache

When a warehouse reads micro-partitions from remote storage, it keeps the data on its local SSD and in memory. A later query on the same warehouse that needs the same data reads it locally, which is much faster.

  • The cache belongs to one warehouse. Another warehouse does not see it.
  • It is cleared when the warehouse suspends, because the compute resources are released.
  • Only the columns and partitions actually read are cached.
Warehouse runningqueries readLocal cache warmsauto suspendCache clearedresumeCold readsremote storage

This creates a trade-off with AUTO_SUSPEND. A very short suspend time saves credits but throws away the cache between bursts of queries. For BI warehouses that run similar queries all day, a slightly longer auto suspend can keep the cache warm.

Which cache answers which query

Which cache answers: an identical query on unchanged data comes from the query result cache, a COUNT, MIN or MAX query from the metadata cache, both in Cloud Services with no warehouse; anything else runs on the warehouse, using its local cache and reading remote storage on a miss.

ScenarioCache used
A dashboard reruns the same SQL, data unchanged, within 24 hoursQuery result cache
SELECT COUNT(*) FROM sales with the warehouse suspendedMetadata cache
A new query on the same warehouse reads columns another query just readWarehouse cache
Same SQL, but a row was inserted into the tableNone of the result cache; the query runs again

Common mistakes

Thinking the result cache is per user. It is shared across users, provided the role has access to the underlying data and the query text matches.

Expecting the warehouse cache to survive suspension. Suspending a warehouse releases its compute and its local cache. The next queries read from remote storage again.

Assuming any change to the table keeps the cached result. Any DML on the underlying data invalidates reuse, so the next run executes on a warehouse.

Watch out

A query that includes CURRENT_TIMESTAMP() never reuses the result cache, because the function returns a different value each time. Rewriting it to filter on a fixed date can make results reusable.

What to remember for the exam

  • Result cache: cloud services, 24 hours, reset on reuse, 31 days maximum, no warehouse needed.
  • Result reuse needs identical query text, unchanged data, no run time functions and the right privileges.
  • USE_CACHED_RESULT defaults to TRUE; set it to FALSE to test performance.
  • Metadata cache answers COUNT(*), MIN and MAX and drives pruning, with no warehouse.
  • Warehouse cache: local SSD per warehouse, cleared on suspend; balance it against auto suspend savings.
  • RESULT_SCAN(LAST_QUERY_ID()) reads a previous result as a table.

Check yourself

  • A user reruns yesterday's query, the table has not changed, and the warehouse is suspended. Does the warehouse resume?

    No, if it is within the 24 hour window the result comes from the query result cache in the cloud services layer.

  • Why might a BI warehouse with AUTO_SUSPEND of 60 seconds run repeated queries slowly?

    The warehouse suspends between bursts and loses its local cache, so each burst reads data from remote storage again.

  • Which cache answers SELECT MAX(order_date) FROM orders without a warehouse?

    The metadata cache, which holds min and max values for each column of every micro-partition.

Topic 4 of 6 in Performance Optimization, Querying, and Transformation

Test yourself on Performance Optimization, Querying, and Transformation

The full Snowflake SnowPro Core COF-C03 question bank has 845 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.