Study guide
12.3 Three-Tier Caching Architecture & Result Reuse
SnowPro Advanced: Architect ARA-C01
Track your progress as you read
Finished sections get a check mark.
1 Introduction: The ARA-C01 Exam
About the SnowPro Advanced: Architect (ARA-C01) Exam
2 Chapter 1: Account Strategy & Organization Architecture
1.1 Multi-Account Strategy & Snowflake Organizations 1.2 Snowflake Parameter Hierarchy & Configuration 1.3 Database and Schema Design Architecture
3 Chapter 2: Identity, Access Control & Storage Integrations
2.1 Role-Based Access Control Architecture & Hierarchy 2.2 Secondary Roles, Object Ownership & Delegated Administration 2.3 Cloud Storage Integrations & IAM Trust Policies 2.4 Identity Federation, SSO, SCIM & Authentication Policies
4 Chapter 3: Governance, Data Privacy, Network Security & Compliance
3.1 Column-Level Masking & Row Access Policies 3.2 Object Tagging, Tag-Based Masking & Audit Governance 3.3 Encryption, Key Management & Tri-Secret Secure 3.4 Aggregation Policies, Projection Policies & External Tokenization 3.5 Network Policies, Network Rules, External Access & Private Connectivity 3.6 Snowflake Editions & Compliance Architecture (PCI DSS, PII & PHI)
5 Chapter 4: Data Modeling in Snowflake
4.1 Dimensional Modeling: Star vs. Snowflake Schemas 4.2 Data Vault 2.0 Architecture in Snowflake 4.3 Relational Constraints & Query Optimizer Integration
6 Chapter 5: Data Sharing & Collaboration Solutions
5.1 Direct Data Sharing Architecture & Security 5.2 Managed Reader Accounts for External Consumers 5.3 Snowflake Marketplace, Data Exchange & Clean Rooms
7 Chapter 6: Multi-Cloud, Multi-Region & Business Continuity
6.1 Database & Account Replication Mechanics 6.2 Disaster Recovery Architecture & Client Redirect 6.3 Cross-Cloud Auto-Fulfillment & Listing Replication 6.4 Data Protection: Time Travel, Fail-safe & Storage Costs
8 Chapter 7: Environments, Lakehouse, DevOps & AI/ML Applications
7.1 Multi-Tier Environment Strategy & Zero-Copy Cloning 7.2 Data Lakehouse Architecture & Apache Iceberg 7.3 DataOps, CI/CD & Snowflake Git Integration 7.4 AI/ML Pipelines & Applications: Cortex AI, ML Functions, Snowpark Container Services, Streamlit & Native Apps
9 Chapter 8: Data Ingestion Architecture
8.1 Bulk Ingestion Architecture: COPY INTO Optimization 8.2 Snowpipe Continuous Automated Ingestion 8.3 Snowpipe Streaming & Real-Time Ingestion 8.4 Data Unloading Architecture & Cloud Export Patterns 8.5 Schema Detection, Schema Evolution & Source-Change Architecture
10 Chapter 9: Continuous Pipelines & Stream Transformation
9.1 Change Data Capture (CDC) with Snowflake Streams 9.2 Task Orchestration, DAGs & Serverless Scheduling 9.3 Declarative Data Pipelines with Dynamic Tables 9.4 Views, Materialized Views & Transformation Table Design
11 Chapter 10: Semi-Structured Data & Ecosystem Tooling
10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing 10.2 Snowflake Ecosystem Connectors, Drivers & SQL API 10.3 Snowpark, User-Defined Functions & Stored Procedures
12 Chapter 11: Virtual Warehouse Architecture & Concurrency
11.1 Virtual Warehouse Architecture, Sizing & Credit Economics 11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies 11.3 Warehouse Types & Query Acceleration Service
13 Chapter 12: Query Profiling & Performance Diagnostics
12.1 Query Profile Deep-Dive & Bottleneck Analysis 12.2 Memory Spilling, Warehouse Queuing & Timeout Governance 12.3 Three-Tier Caching Architecture & Result Reuse
14 Chapter 13: Storage Optimization, Cost Governance & Observability
13.1 Micro-Partitioning & Automatic Clustering 13.2 Search Optimization Service (SOS) & Point Lookup Acceleration 13.3 Resource Monitors, Financial Governance & Usage Observability 13.4 Warehouse Monitoring, Alerts, Event Tables & Performance Explorer
12.3 Three-Tier Caching Architecture & Result Reuse
Key Takeaways Explain This Topic AI
Snowflake utilizes a three-tier caching hierarchy: Query Result Cache (Cloud Services), Metadata Cache (Cloud Services catalog), and Local Disk Cache (Warehouse SSD).
The Query Result Cache retains output for 24 hours from last execution (extensible up to 31 days on reuse) and serves identical queries with zero virtual warehouse credits.
Result reuse requires an exactly matching query (even case or alias differences prevent reuse), unchanged table data and micro-partitions, the required privileges, unchanged result-affecting settings, no non-reusable functions such as RANDOM or UUID_STRING, no external functions, and no hybrid tables.
The Metadata Cache answers catalog queries (COUNT(*), MIN/MAX on clustering keys) directly from the Cloud Services layer without resuming or provisioning virtual warehouses.
The warehouse (local disk) cache holds data read from table files on the warehouse's compute resources; it is lost when the warehouse suspends, and resizing changes the resources, so part of the cache is cold afterward.
Last updated: September 2026
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:
Sub-second Latency : Delivering instantaneous responses for repeated analytical and operational queries.
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
- Architectural Attribute Tier 1: Query Result Cache Tier 2: Metadata Cache Tier 3: Local Disk Cache
- Physical Location Cloud Services Global Layer Cloud Services Catalog Virtual Warehouse Worker Node SSDs
- Data Granularity Formatted Query Output Result Set Table & Partition Statistics Raw Data Micro-Partitions
Warehouse Required? NO (Warehouse can be suspended) NO (Warehouse can be suspended) YES (Warehouse must be running)
Credit Consumption 0 Compute Credits 0 Compute Credits Standard Running Warehouse Credits
Cross-User Sharing YES (across all users with role access) YES (across entire account) YES (across queries on same warehouse)
Retention Window 24 hours (sliding window up to 31 days) Lifetime of table object Purged immediately upon Suspend/Resize
Primary Invalidation DML commits to underlying table DML / DDL operations Node de-provisioning / auto-suspend
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:
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.
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.
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.
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.
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:
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 .
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.
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.