12.3 Three-Tier Caching Architecture & Result Reuse

12.3 Three-Tier Caching Architecture & Result Reuse

Snowflake's decoupled storage-and-compute architecture is supported by a sophisticated, automated three-tier caching model. Caching in Snowflake serves two vital architectural goals:

1. Sub-second Latency: Delivering instantaneous responses for repeated analytical and operational queries.
2. Financial Cost Reduction: Eliminating unnecessary virtual warehouse compute credits by serving results and partition statistics directly from cloud services or local node SSDs.

For the SnowPro Advanced: Architect exam, you must master the operational characteristics of each cache layer, understand exact invalidation triggers, identify non-deterministic functions that prevent result reuse, and architect warehouse lifecycle policies that maximize cache hit efficiency.

The Three-Tier Caching Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│                     Snowflake Three-Tier Caching Hierarchy                  │
├─────────────────────────────────────────────────────────────────────────────┤
│  TIER 1: QUERY RESULT CACHE                                                 │
│  • Location: Cloud Services Layer                                           │
│  • Contents: Final compiled query result sets                               │
│  • Retention: 24 hours (resets on reuse, up to 31 days)                     │
│  • Compute Cost: ZERO virtual warehouse credits                             │
├─────────────────────────────────────────────────────────────────────────────┤
│  TIER 2: METADATA CACHE                                                     │
│  • Location: Cloud Services Layer                                           │
│  • Contents: Micro-partition statistics (min/max, null counts, row counts)  │
│  • Retention: Permanent (updated synchronously during DML)                  │
│  • Compute Cost: ZERO virtual warehouse credits                             │
├─────────────────────────────────────────────────────────────────────────────┤
│  TIER 3: LOCAL DISK (WAREHOUSE SSD) CACHE                                   │
│  • Location: Virtual Warehouse Compute Layer                                │
│  • Contents: Raw, uncompressed/compressed data micro-partitions             │
│  • Retention: Duration of warehouse active lifecycle                        │
│  • Compute Cost: Standard virtual warehouse credit consumption              │
└─────────────────────────────────────────────────────────────────────────────┘

Comprehensive Caching Tier Comparison

Tier 1: Query Result Cache Mechanics & Invalidation

The Query Result Cache resides entirely within Snowflake's Cloud Services layer. When a query executes, Snowflake persists the final output result set in the cloud services cache.

Operational Rules & Sliding Retention Lifecycle

• 24-Hour Base TTL: Any query result is retained in the cache for 24 hours from the exact completion timestamp.
• Sliding Window Extension: Each time an identical query is submitted within the 24-hour window and reuses the cached result, the 24-hour retention counter is reset. This sliding window can extend cache retention up to a maximum lifetime of 31 days from original execution.
• Zero Compute Credits: Queries served from the Result Cache do not require a virtual warehouse. If the user's assigned warehouse is suspended, Snowflake does not resume the warehouse, consuming 0 compute credits. (Standard Cloud Services credit policies apply, but Cloud Services is free up to 10% of daily warehouse spend).
• Cross-User Reuse: If User A runs a query and User B later runs the same query with a role that has the required privileges on all referenced tables, User B can receive the cached result.

The 5 Mandatory Requirements for Result Cache Reuse

For a query to successfully reuse the Query Result Cache, all five of the following conditions must be met:

1. Exact Match: The new query must match the previous one exactly. Snowflake's documentation notes that differences such as lowercase versus uppercase keywords or adding a table alias prevent full reuse, so BI tools that generate slightly different SQL each time get little benefit.
2. Underlying Data Immutability: No micro-partitions of any table referenced in the query have changed. Any committed DML (INSERT, UPDATE, DELETE, MERGE, TRUNCATE) or DDL modifying the table immediately invalidates the result cache for that table.
3. No Non-Reusable Functions: The query must not include functions that return different results on successive runs — Snowflake cites UUID_STRING, RANDOM, and RANDSTR, and time functions such as CURRENT_TIMESTAMP() behave the same way because their value changes every run. Queries that call external functions do not reuse results either.
4. Privileges and Settings: The role using the cached result must have the required privileges on every table in the query (for SHOW commands, the role must match the one that produced the result), and configuration options that affect the result must not have changed.
5. Unchanged Storage: Beyond DML, changes to the table's micro-partitions — for example reclustering or consolidation — also prevent reuse. Results are not reused for queries on hybrid tables, and a disabled USE_CACHED_RESULT parameter turns reuse off for the session.

Bypassing the Result Cache for Benchmarking

When conducting performance tuning, query profiling, or sizing benchmarks, architects must bypass the Result Cache. Failing to disable result caching produces false positive test results where complex queries return in 15 milliseconds simply because they hit the Cloud Services cache rather than exercising the virtual warehouse.

-- Disable Query Result Cache for the active session
ALTER SESSION SET USE_CACHED_RESULT = FALSE;

-- Benchmark query executing directly against compute and storage
SELECT 
    c.c_mktsegment,
    COUNT(o.o_orderkey) AS total_orders,
    SUM(o.o_totalprice) AS total_revenue
FROM snowflake_sample_data.tpch_sf100.customer c
JOIN snowflake_sample_data.tpch_sf100.orders o ON c.c_custkey = o.o_custkey
GROUP BY c.c_mktsegment;

-- Re-enable Query Result Cache after benchmarking
ALTER SESSION SET USE_CACHED_RESULT = TRUE;

Tier 2: Metadata Cache (Cloud Services Catalog)

Snowflake's columnar storage engine automatically records extensive statistical metadata for every micro-partition written to table storage. This metadata is maintained in the Cloud Services catalog and constitutes the Metadata Cache.

Stored Statistical Metadata

For every single micro-partition, Snowflake automatically maintains:

• Minimum and Maximum value for each column (MIN, MAX).
• Total number of rows in the micro-partition.
• Number of NULL values in each column.
• Number of distinct values (cardinality approximations).
• Byte size of uncompressed and compressed column data.

┌─────────────────────────────────────────────────────────────────────────────┐
│                     Micro-Partition Metadata Catalog Entry                  │
├─────────────────────────────────────────────────────────────────────────────┤
│ Partition ID: 3a7b-0012   │ Column: ORDER_DATE    │ Column: ORDER_TOTAL     │
│ • Total Rows: 2,450,000   │ • MIN: 2026-03-01     │ • MIN: $12.50           │
│ • Null Count: 0           │ • MAX: 2026-03-07     │ • MAX: $98,400.00       │
│ • Size: 18.2 MB           │ • Distinct: 7         │ • Distinct: 412,000     │
└─────────────────────────────────────────────────────────────────────────────┘

Queries Answered Exclusively by Metadata Cache

When a query can be satisfied entirely by aggregating partition statistics, Snowflake answers the query in the Cloud Services layer with zero virtual warehouse compute:

-- 1. Exact table row count (read directly from table catalog)
SELECT COUNT(*) FROM sales_fact;
SELECT COUNT(1) FROM sales_fact;

-- 2. Min and Max on un-filtered or clustering columns
SELECT MIN(order_date), MAX(order_date) FROM sales_fact;

-- 3. Structural and catalog introspection
SHOW TABLES IN SCHEMA prod_dw.public;
SHOW COLUMNS IN sales_fact;

Verifying Metadata Cache Usage in Query Profile

• In the Query Profile execution plan, a query served purely from metadata displays a single operator labeled MetadataBasedResult (or Query executed entirely from metadata).
• No TableScan operator exists in the operator tree.
• If the warehouse assigned to the session was suspended, it remains suspended. Zero compute credits are consumed.

Tier 3: Local Disk Cache (Warehouse SSD Cache)

The Local Disk Cache (often called the Warehouse SSD Cache or Data Cache) operates within the virtual warehouse compute layer. It stores raw micro-partitions on the high-speed local NVMe/SSD storage attached to active worker nodes.

               Query 1 Execution (Cold Warehouse)               
  Cloud Object Storage (S3/Blob/GCS) ──► Read Micro-Partitions (Remote I/O: 100%)
                                               │
                                               ▼
                                     Populate Local SSD Cache
                                               │
                                               ▼
               Query 2 Execution (Warm Warehouse - Different SQL) 
  Local SSD Cache ───────────────────► Read Micro-Partitions (Local I/O: 100%)
  (Zero Remote Storage Fetches)

Architectural Distinction: Result Cache vs. Local Disk Cache

A critical distinction tested on the SnowPro Advanced: Architect exam is how the Local Disk Cache differs from the Result Cache:

• Result Cache: Holds the final compiled query result set for an exact query string. If the query text or aggregations change, the Result Cache is completely useless.
• Local Disk Cache: Holds the raw underlying micro-partitions accessed during execution. If a subsequent query executes with different SELECT columns, different WHERE filters, or different GROUP BY aggregations, but references the same table micro-partitions, the query reads data directly from the local node SSDs without making remote calls to cloud object storage.

Cache Warmth & Warehouse Lifecycle Governance

The Local Disk Cache lives and dies with the virtual warehouse compute nodes:

1. Warehouse Suspension (AUTO_SUSPEND):
   • When a virtual warehouse suspends, the underlying cloud virtual machines are immediately de-provisioned and terminated.
   • The entire Local Disk Cache is completely lost and erased.
   • When the warehouse subsequently resumes, it is in a cold state. The first queries executed must fetch micro-partitions from remote cloud object storage, incurring high Remote Disk I/O.
2. Warehouse Resizing:
   • Scaling up adds compute resources whose caches start empty; scaling down removes resources and the cache they held. Either way, expect some cold reads after a resize.
3. Optimizing AUTO_SUSPEND for Cache Retention:
   • Setting AUTO_SUSPEND too aggressively (e.g., 60 seconds) in active business hours repeatedly destroys the Local Disk Cache, subjecting dashboard users to cold-query remote read latencies.
   • Enterprise architects set AUTO_SUSPEND = 300 (5 minutes) or 600 (10 minutes) during business hours to maintain cache warmth, ensuring continuous sub-second query performance across interactive reporting workloads.

Telemetry: Percentage of Data Scanned from Local Cache

• In the Query Profile TableScan operator details, the metric "Percentage scanned from cache" indicates the proportion of micro-partitions read from local SSDs versus remote cloud storage:
  • 100%: Optimal cache warmth. All micro-partitions read from local SSD.
  • 0%: Completely cold scan. 100% of micro-partitions fetched from remote S3/Blob/GCS.