?? Caching in Snowflake – How It Works & Why It’s Powerful - Complete Guide to Snowflake Caching

Caching in Snowflake significantly improves query performance by reducing the need to reprocess data and re-scan storage. Snowflake automatically handles caching across different layers to reduce compute costs and speed up query execution.

?? Major Types of Caching in Snowflake

Snowflake primarily uses three types of caching:

1?? Result Cache – Stores query results for reuse (Cloud Services Layer).

2?? Local Disk Cache (Warehouse Cache) – Stores table data in SSD storage of a virtual warehouse (Compute Layer).

3?? Remote Disk Cache (Persistent Storage Cache) – Stores compressed data blocks in remote cloud storage (Storage Layer).

Each cache serves a different purpose and is used in specific scenarios.

1?? Result Cache (Cloud Services Layer)

? What It Does

• Stores fully executed query results in the Cloud Services Layer for 24 hours.
• If the same query is run again, Snowflake retrieves results instantly without recomputing.
• No warehouse compute cost if the cache is used.

?? When It’s Used

• Repeated execution of identical queries (must be exactly the same, including case and whitespace).
• Cross-user caching is allowed if users share the same role and permissions.

?? Example – Query Using Result Cache

SELECT SUM(sales) FROM orders WHERE region = 'US';        

If this query runs again within 24 hours, the result is retrieved instantly from cache.

? Benefit: Zero compute cost (no warehouse usage).

? Not Used If: Data has changed, query structure is different, or session role changes.

2?? Local Disk Cache (Warehouse Cache) – Compute Layer

? What It Does

• Stores recently accessed table data in local SSD storage of a Virtual Warehouse.
• Cached data remains available as long as the warehouse is running.
• Speeds up subsequent queries on the same data without reloading from cloud storage.

?? When It’s Used

• Repeated queries on the same dataset (within an active warehouse session).
• Different queries accessing overlapping data.
• Same query but slightly different filters (if data is still cached).

?? Example – Query Using Warehouse Cache

SELECT * FROM orders WHERE region = 'EUROPE';        

• If the warehouse is still running, this query retrieves cached data from SSD, avoiding a cloud storage scan.

? Benefit: Faster execution & lower storage access costs.

? Not Used If: The warehouse is suspended (cache is cleared on suspension).

3?? Remote Disk Cache (Persistent Storage Cache) – Storage Layer

? What It Does

• Stores compressed columnar data blocks in remote cloud storage (AWS S3, Azure Blob, GCP Cloud Storage).
• Acts as an intermediate cache between cloud storage and compute.
• Reduces the need for expensive full-table scans by keeping frequently accessed data blocks in warm storage.

?? When It’s Used

• When a query needs data that is not available in Local Disk Cache but was recently accessed.
• When warehouses are suspended and restarted, cached data is reloaded from Remote Disk Cache instead of cloud storage.

?? Example – Query Using Remote Cache

SELECT * FROM sales WHERE YEAR(sale_date) = 2023;        

• If the warehouse was suspended and restarted, Snowflake reloads cached table data from Remote Disk Cache instead of cloud storage, speeding up execution.

? Benefit: Faster query execution on reloaded warehouses.

? Not Used If: Data has never been accessed before (must fetch from cloud storage).

?? Summary – When Each Cache Is Used

?? Best Practices for Maximizing Caching in Snowflake

? Use identical queries to take advantage of Result Cache.

? Keep warehouses running if working on the same dataset to benefit from Local Disk Cache.

? Restart warehouses strategically to utilize Remote Disk Cache for quick recovery.

Final Thoughts

Caching in Snowflake dramatically improves performance by reducing compute costs and speeding up queries. Result Cache, Local Disk Cache, and Remote Disk Cache all work together to ensure efficient data retrieval without unnecessary recomputation.