How Snowflake Caching Works: Result Cache, Metadata Cache, Warehouse Cache

Snowflake automatically manages 3 caches to optimize query performance: the Result Cache (Cloud Services layer), the Metadata Cache (Cloud Services layer), and the Warehouse Cache (SSD on the compute layer). Understanding exactly which layer each cache runs in and what invalidates it matters both for performance tuning and for the SnowPro exams.

The 3 Caches Side by Side

Result Cache (Query Result Cache)

The Result Cache stores the result set of any identical SQL query executed within the past 24 hours, and instantly returns it on a re-run without going through a warehouse.

Query execution flow (with Result Cache):

[SQL: SELECT region, SUM(amount) FROM sales GROUP BY region]
       |
       v
[Cloud Services layer]
       |
       +- Hit in Result Cache?
       |     |
       |     +- YES -> Return from cache instantly (no warehouse, no credits)
       |     |
       |     +- NO  -> Execute on the warehouse
       |                    |
       |                    +- Return result + store in Result Cache (24 hours)
       v
[Query result]

Conditions for a Result Cache hit

• The SQL text matches exactly (including case, whitespace, and comments)
• The target table's data has not changed
• The last execution was within 24 hours
• Run by the same role (different roles do not hit the cache)
• Session parameter USE_CACHED_RESULT = TRUE (the default)

What invalidates the Result Cache

-- Disable the Result Cache and run the query
ALTER SESSION SET USE_CACHED_RESULT = FALSE;

SELECT region, SUM(amount) FROM sales GROUP BY region;
-- -> Always re-executed on the warehouse

-- Re-enable the Result Cache
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

Metadata Cache

The Metadata Cache stores table metadata (row count, byte size, MIN/MAX values per micro-partition, etc.) in the Cloud Services layer. Queries that can be answered from metadata alone return results without using a warehouse.

-- Queries answered instantly from the Metadata Cache
SELECT COUNT(*) FROM large_table;      -- row count is in metadata
SELECT MIN(id) FROM large_table;       -- MIN/MAX are in partition metadata
SELECT MAX(created_at) FROM large_table;

-- These queries run even when the warehouse is SUSPENDED
-- Query Profile shows "METADATA-BASED RESULT"

What the Metadata Cache stores

• Table row count (ROW_COUNT)
• Table byte size
• MIN/MAX per micro-partition (used for partition pruning)
• NULL counts and approximate DISTINCT values per micro-partition
• Table schema definitions

Warehouse Cache (Local Disk Cache)

The Warehouse Cache caches raw micro-partition data on the SSDs of the warehouse's compute nodes. This reduces I/O to remote storage (S3 / Azure Blob / GCS) and shortens query execution time.

Query execution flow (with Warehouse Cache):

[SQL: SELECT * FROM sales WHERE region = 'APAC']
       |
       v
[Warehouse compute node]
       |
       +- Are the required micro-partitions already on SSD?
       |     |
       |     +- YES -> Read locally from SSD (fast)
       |     |
       |     +- NO  -> Read from remote storage (S3, etc.)
       |                    |
       |                    +- Cache the loaded data on SSD
       v
[Query result]

Warehouse Cache behavior

Cache evaluation order at query time

Cache evaluation priority:

[Incoming SQL query]
       |
       v
[1] Check the Metadata Cache
       |  Can metadata alone answer this? (COUNT(*), MIN, MAX, etc.)
       |     +- YES -> Return instantly (no warehouse)
       |     +- NO  -> Continue
       v
[2] Check the Result Cache
       |  Same SQL + same role + within 24 hours + data unchanged?
       |     +- YES -> Return cached result (no warehouse)
       |     +- NO  -> Continue
       v
[3] Execute on the warehouse (leveraging the Warehouse Cache)
       |  If the needed micro-partitions are on SSD, read them locally (fast)
       |  Otherwise read from remote storage and cache to SSD
       v
[Return result + store in Result Cache]

AUTO_SUSPEND and the cache

A warehouse's AUTO_SUSPEND setting has a major impact on how effective the Warehouse Cache is.

• Short AUTO_SUSPEND (e.g. 60 seconds): the warehouse suspends often and the Warehouse Cache is cleared frequently
• Long AUTO_SUSPEND (e.g. 600 seconds): the cache lasts longer but idle credit consumption goes up
• For batch workloads, prioritize cost savings by letting the warehouse suspend
• For interactive queries, set AUTO_SUSPEND a bit longer to preserve the cache

Inspecting caches in Query Profile

-- Cache-related signals in Query Profile

Result Cache hit:
  -> Query Profile shows "QUERY RESULT REUSE"
  -> No warehouse processing nodes are shown

Metadata Cache hit:
  -> Query Profile shows "METADATA-BASED RESULT"

Warehouse Cache in use:
  -> Check the Statistics tab for:
     - Percentage Scanned from Cache: share of reads from SSD cache
     - Bytes Scanned from Cache: bytes read from cache

-- Comparing Warehouse Cache effectiveness
-- 1st run: read from remote storage (Percentage Scanned from Cache = 0%)
-- 2nd run: read from SSD cache (Percentage Scanned from Cache ~ 100%)

Exam takeaways

• Result Cache is retained for 24 hours and serves results without a warehouse
• Result Cache requires the same role; a different role will not hit the cache
• Metadata Cache applies to queries answerable from metadata alone, such as COUNT(*)/MIN/MAX
• Warehouse Cache holds raw data on SSD and is cleared on SUSPEND
• Queries with non-deterministic functions (CURRENT_TIMESTAMP, etc.) are excluded from the Result Cache
• Any DML change to the table invalidates the Result Cache
• AUTO_SUSPEND settings directly affect how effective the Warehouse Cache is

Sample question

Frequently Asked Questions