Caching Mechanisms

Snowflake’s caching architecture is one of the most frequently tested topics on the COF-C02 exam. Unlike traditional databases that rely on a single buffer pool, Snowflake operates three independent caching layers, each serving a different purpose, persisting for a different duration, and carrying different cost implications. Mastering all three — and understanding exactly when each fires and when it does not — is essential for both the exam and real-world performance optimisation.

Why Caching Matters on the Exam

The COF-C02 exam regularly presents scenarios where a candidate must identify which cache layer is responsible for a performance outcome, or explain why a cache did not fire. Common question patterns include:

• A query returns instantly the second time — which cache is responsible?
• A warehouse is suspended overnight — what happens to cached data?
• Two different warehouses run the same query — do they share a cache?
• A query uses CURRENT_TIMESTAMP() — will the result cache help?

Understanding the answers to these questions requires a thorough grasp of all three layers.

The Three-Layer Caching Architecture

Snowflake Three-Layer Caching Architecture

A layered architectural diagram showing the three independent caching mechanisms in Snowflake. At the top sits the Cloud Services Layer, which houses two of the three caches: the Result Cache (Query Result Cache) shown as a large repository storing complete result sets from every executed query, retained for up to 24 hours, and the Metadata Cache shown as a database of table statistics including row counts, min/max values, null counts, and distinct value estimates. Below the Cloud Services layer sits the Compute Layer containing multiple independent Virtual Warehouses (depicted as separate server clusters). Each virtual warehouse has its own Local Disk Cache (SSD) shown as a fast storage layer attached to the warehouse. Arrows show the flow: a client query first checks the Result Cache in Cloud Services. On a miss, the Cloud Services layer routes the query to a Virtual Warehouse. The Virtual Warehouse checks its Local Disk Cache (SSD). On a miss at the SSD layer, the warehouse fetches micro-partition files from the Remote Cloud Storage (shown at the bottom as an object store — S3, Azure Blob, or GCS). The fetched data is written into the Local Disk Cache for future reuse. The Metadata Cache feeds into the query optimiser in Cloud Services to enable partition pruning before any data is read. A key annotation indicates that the Result Cache and Metadata Cache are centralised and shared across the account, while Local Disk Cache is strictly per-warehouse and is lost on suspension.

The three layers operate independently and serve different stages of query execution:

Layer 1 — Result Cache (Query Result Cache)

The result cache is the first cache layer checked for any incoming query. When Snowflake receives a SQL statement, the Cloud Services layer computes a fingerprint of the query text and checks whether an identical result set already exists in the result cache.

How the Result Cache Works

1. The user submits a SQL query.
2. Cloud Services parses the query and computes a cache key from the exact SQL text (including whitespace and case).
3. If a matching entry exists in the result cache and is still valid (not invalidated by DML/DDL on the underlying tables), the result set is returned immediately.
4. No virtual warehouse is activated. Zero compute credits are consumed.
5. If no cache hit, the query proceeds to compute execution and the result is stored in the result cache upon completion.

Result Cache Hit vs Miss Decision Flow

A detailed decision-tree flowchart illustrating the result cache lookup process for every incoming Snowflake query. The flow starts with a client submitting a SQL query to Snowflake Cloud Services. The first decision diamond asks: 'Is the SQL text byte-for-byte identical to a previously cached query, including case and whitespace?' If No, the flow branches to 'Cache Miss — Route to Virtual Warehouse for execution.' If Yes, a second decision diamond asks: 'Has the result cache entry been invalidated by DML or DDL on the underlying tables since the result was cached?' If Yes (i.e., data has changed), the flow branches to Cache Miss. If No, a third decision diamond asks: 'Is the cached result less than 24 hours old?' If No, Cache Miss. If Yes, a fourth decision diamond asks: 'Does the query contain non-deterministic functions such as CURRENT_TIMESTAMP, RANDOM, UUID_STRING, or SEQ?' If Yes, Cache Miss. A fifth decision diamond asks: 'Is the user executing the query operating under the same role context as when the result was originally cached?' If No, Cache Miss. If all checks pass, the flow reaches 'Cache Hit — Return result set immediately, zero compute credits consumed.' The Cache Miss path shows the query going to a virtual warehouse, executing against micro-partitions, and storing the new result in the result cache for future reuse, with a timestamp and table change-detection token saved alongside it.

Result Cache Rules — Exam Critical

What the Result Cache Stores and Where

The result cache lives in the Cloud Services layer, meaning it is:

• Centralised — shared across all virtual warehouses in the account
• Account-scoped — multiple warehouses or users can benefit from a result cached by any warehouse
• Transparent — no user configuration is required; it is always active

The cache entry stores the complete result set. For very large result sets, this can consume significant Cloud Services storage, but this is managed entirely by Snowflake and has no user-visible cost impact.

Non-Deterministic Functions — Why They Block Caching

A non-deterministic function is one whose output can differ between calls even when given the same inputs. Because Snowflake cannot guarantee that re-serving a cached result would be correct (the function might produce a different value today), any query containing such a function is ineligible for result caching.

1-- These queries CANNOT be served from the result cache
2-- because they contain non-deterministic functions.
3
4-- CURRENT_TIMESTAMP returns a different value every second
5SELECT order_id, CURRENT_TIMESTAMP() AS run_time
6FROM orders
7WHERE order_date = '2024-01-15';
8
9-- RANDOM() produces a new random value on every execution
10SELECT TOP 100 customer_id, RANDOM() AS sample_weight
11FROM customers;
12
13-- UUID_STRING generates a unique identifier each call
14SELECT UUID_STRING() AS batch_id, COUNT(*) AS row_count
15FROM transactions;
16
17-- SEQ (sequence objects) advance the sequence each call
18SELECT MY_SEQ.NEXTVAL, product_name
19FROM products;
20
21-- -------------------------------------------------------
22-- These queries CAN be served from the result cache
23-- (deterministic, no DML on underlying tables since last run)
24
25SELECT COUNT(*) FROM orders WHERE order_date = '2024-01-15';
26
27SELECT region, SUM(revenue) AS total_revenue
28FROM sales
29GROUP BY region
30ORDER BY total_revenue DESC;
31
32SELECT customer_id, first_name, last_name
33FROM customers
34WHERE country = 'GB';

SHOW Commands and the Result Cache

SHOW commands (e.g., SHOW TABLES, SHOW SCHEMAS, SHOW WAREHOUSES) retrieve metadata from the Cloud Services layer. These commands can benefit from the result cache in a similar fashion to standard queries, and they do not require a running virtual warehouse because they operate entirely within the Cloud Services layer.

Layer 2 — Local Disk Cache (Data Cache / Warehouse Cache)

The local disk cache is fundamentally different from the result cache. Rather than storing final query results, it stores raw columnar micro-partition data — the actual files fetched from cloud object storage (S3, Azure Blob, or GCS). This cache lives on the SSD storage attached to each virtual warehouse’s compute nodes.

How the Local Disk Cache Works

When a virtual warehouse executes a query, it must fetch the relevant micro-partition files from cloud object storage. These fetches are slower than reading from local SSD. Snowflake automatically caches the fetched micro-partition data on the warehouse’s SSD. On the next query that requires the same micro-partitions, Snowflake reads from SSD rather than cloud storage — significantly faster.

Local Disk Cache — Warm vs Cold Warehouse Behaviour

A side-by-side comparison diagram illustrating the difference between a cold warehouse (no local disk cache) and a warm warehouse (populated local disk cache). On the left side, labelled 'Cold Warehouse — First Query After Resume', a timeline shows a client query arriving at a virtual warehouse. The warehouse has an empty SSD cache (shown as a grey empty box). The warehouse sends fetch requests to Remote Cloud Storage (S3/Azure/GCS). Multiple micro-partition files (shown as coloured blocks P1, P2, P3, P4, P5) are transferred over the network from cloud storage to the warehouse's SSD. The query then reads from the freshly populated SSD. An annotation shows 'High latency — cloud storage network fetch required for every micro-partition'. On the right side, labelled 'Warm Warehouse — Subsequent Query on Same Data', the same client query arrives. The warehouse SSD is now shown as populated with the same micro-partition blocks P1-P5 (coloured green to indicate they are cached). The query reads directly from SSD without touching cloud storage. An annotation shows 'Low latency — data served from local SSD, no cloud storage fetch'. At the bottom of both sides, a critical note states: 'Local Disk Cache is LOST when the warehouse is suspended or terminated. The next resume starts with a cold cache.' A separate annotation in the centre highlights: 'Two different warehouses NEVER share local disk cache — each warehouse has its own independent SSD layer.'

Key Characteristics of the Local Disk Cache

Cache persistence:

• Persists as long as the warehouse remains in a Running state
• Lost immediately on Suspend or Terminate
• Multi-cluster warehouses: each compute cluster within a multi-cluster warehouse maintains its own SSD cache

Cache size:

• Determined by the warehouse size (larger warehouses have more nodes with more aggregate SSD capacity)
• Not user-configurable directly; scales with warehouse size selection

Cache eviction:

• Uses an LRU (Least Recently Used) eviction policy when SSD capacity is reached
• Older, less-frequently-accessed micro-partition data is evicted first

Cache Warming Strategies

Because the local disk cache is lost on suspension, workload patterns and auto-suspend settings have a direct impact on cache effectiveness:

SELECT
  query_text,
  COUNT(*) AS execution_count,
  AVG(bytes_scanned) AS avg_bytes_scanned,
  AVG(percentage_scanned_from_cache) AS avg_cache_pct
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY query_text
ORDER BY execution_count DESC
LIMIT 50;

-- Keep warehouse alive for 10 minutes of idle time
-- to preserve the warm cache between query bursts
ALTER WAREHOUSE my_analytics_wh
SET AUTO_SUSPEND = 600; -- 600 seconds = 10 minutes

-- Alternatively, query QUERY_HISTORY for cache statistics
SELECT
  query_id,
  query_text,
  bytes_scanned,
  bytes_written,
  ROUND(
    (bytes_scanned - bytes_written_to_result) /
    NULLIF(bytes_scanned, 0) * 100, 2
  ) AS estimated_cache_pct
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('hour', -24, CURRENT_TIMESTAMP())
ORDER BY bytes_scanned DESC
LIMIT 20;

Layer 3 — Metadata Cache (Cloud Services Layer)

The metadata cache is the most fundamental and persistent of the three layers. It is maintained entirely by Snowflake in the Cloud Services layer and stores statistical metadata about every table and its micro-partitions.

What the Metadata Cache Stores

For every table in Snowflake, the metadata cache maintains:

• Row count — total number of rows in the table
• Min / Max values — minimum and maximum values per column per micro-partition (used for pruning)
• Null count — number of null values per column per micro-partition
• Distinct value estimates — approximate cardinality per column
• Micro-partition file references — locations of all micro-partition files in cloud storage

How Queries Use the Metadata Cache

Metadata Cache and Query Pruning

The metadata cache is what enables micro-partition pruning — Snowflake’s most impactful performance optimisation. Before a virtual warehouse reads a single byte of data, the Cloud Services optimiser consults the metadata cache to determine which micro-partitions cannot possibly contain rows matching the WHERE clause.

For example, if a query filters on order_date = '2024-03-15' and a particular micro-partition’s metadata shows min_date = '2024-05-01' and max_date = '2024-07-31', that micro-partition is pruned (skipped) without being read. This happens at the Cloud Services layer before the warehouse executes.

Metadata cache persistence: The metadata cache is persistent and managed by Snowflake. It is updated automatically whenever DML (INSERT, UPDATE, DELETE, MERGE) or DDL operations occur on a table. Unlike the result cache and local disk cache, users have no mechanism to invalidate or manage the metadata cache — Snowflake handles this entirely.

Cache Comparison — Side by Side

Cache Invalidation Rules

Result Cache Invalidation

The result cache is invalidated for a specific query when:

1. Any DML (INSERT, UPDATE, DELETE, MERGE, COPY INTO) is executed on any table referenced by the query
2. DDL changes to the table (ALTER TABLE, DROP COLUMN, etc.)
3. 24 hours have elapsed since the result was cached (even without any data changes)

Local Disk Cache Invalidation

The local disk cache is lost when:

1. The virtual warehouse is suspended (even briefly)
2. The virtual warehouse is terminated
3. For multi-cluster warehouses: when an individual cluster scales down

Metadata Cache Invalidation

The metadata cache is not invalidated by users — Snowflake updates it automatically and continuously. When DML occurs, the metadata for affected micro-partitions is updated immediately. The metadata cache is never “stale” from a correctness standpoint.

1-- ============================================================
2-- RESULT CACHE TESTING
3-- ============================================================
4
5-- Run this query first — it executes against the warehouse
6SELECT region, SUM(sales_amount) AS total_sales
7FROM fact_sales
8WHERE sale_date BETWEEN '2024-01-01' AND '2024-03-31'
9GROUP BY region
10ORDER BY total_sales DESC;
11
12-- Run the EXACT same query again — should hit result cache
13-- Check Query Profile: "Query Result Reuse" indicator
14SELECT region, SUM(sales_amount) AS total_sales
15FROM fact_sales
16WHERE sale_date BETWEEN '2024-01-01' AND '2024-03-31'
17GROUP BY region
18ORDER BY total_sales DESC;
19
20-- Inserting data invalidates the result cache for fact_sales
21INSERT INTO fact_sales VALUES (DEFAULT, 'NORTH', 99999, '2024-01-01');
22
23-- Now the same query will NOT hit result cache — cache invalidated
24SELECT region, SUM(sales_amount) AS total_sales
25FROM fact_sales
26WHERE sale_date BETWEEN '2024-01-01' AND '2024-03-31'
27GROUP BY region
28ORDER BY total_sales DESC;
29
30-- ============================================================
31-- METADATA CACHE EXAMPLES — NO WAREHOUSE NEEDED
32-- ============================================================
33
34-- COUNT(*) on large table — served from metadata cache
35-- No warehouse credit consumption
36SELECT COUNT(*) FROM fact_sales;
37
38-- MIN/MAX on well-clustered column — from metadata
39SELECT MIN(sale_date), MAX(sale_date) FROM fact_sales;
40
41-- ============================================================
42-- CHECKING CACHE USAGE IN QUERY HISTORY
43-- ============================================================
44
45SELECT
46query_id,
47query_text,
48execution_status,
49-- This column indicates result cache reuse
50-- 'true' means the result was served from the result cache
51query_tag,
52start_time,
53end_time,
54total_elapsed_time / 1000 AS elapsed_seconds
55FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
56WHERE start_time >= DATEADD('hour', -1, CURRENT_TIMESTAMP())
57ORDER BY start_time DESC
58LIMIT 20;

Practical Cache Strategies

When to Rely on Each Cache

Cheat Sheet — Caching for the Exam

Knowledge Check — Quizzes

Flashcards — Caching

Summary

Snowflake’s three caching layers form a tiered defence against unnecessary computation and cloud storage reads:

• Result Cache — the most powerful, serving entire result sets at zero cost, but requiring strict conditions (identical SQL, same role, no DML, no non-deterministic functions, within 24 hours)
• Local Disk Cache — reduces cloud storage I/O by keeping frequently accessed micro-partitions on warehouse SSD, but is per-warehouse and ephemeral (lost on suspend)
• Metadata Cache — always-on, Snowflake-managed statistics that power pruning and answer simple aggregate queries without a warehouse

For the exam, the most critical distinctions are: the result cache is centralised and zero cost; the local disk cache is per-warehouse and ephemeral; and the metadata cache is persistent and automatic. Understanding exactly what invalidates each cache — and when each will NOT fire — is the key to answering exam scenario questions correctly.