Caching in Snowflake

Snowflake · Warehouses & Performance

Theory

Snowflake caches at three levels. The result cache in Cloud Services keeps the result of each query for 24 hours; if the same query text runs again, the underlying data has not changed and the role has access, the stored result is returned without using a warehouse. The local disk (warehouse) cache keeps recently read table data on the warehouse's SSDs, so repeat scans are faster, but it is lost when the warehouse suspends. The metadata cache holds statistics such as row counts and min/max values, so queries like SELECT COUNT(*) FROM orders can be answered from metadata alone.

For example, a dashboard refreshing the same query every hour is usually served from the result cache after the first run.

Why we use it

Caching avoids repeating work. It makes repeated dashboard queries near-instant, cuts warehouse credits, and explains why the second run of a query is often much faster than the first.

When to use it

Rely on it for repeated BI queries and stable reports. Turn it off with USE_CACHED_RESULT = FALSE when benchmarking real query speed. Consider a longer AUTO_SUSPEND for warehouses whose workloads benefit from a warm local cache.

Result Cache

Stores a query's complete result for 24 hours, so re-running an identical query returns instantly with zero compute cost — no warehouse even needs to be running.

Local Disk (Warehouse) Cache

Keeps recently scanned data on the warehouse's own nodes so a follow-up query touching the same data re-scans faster; this cache is lost when the warehouse suspends.

Metadata Cache

Remembers the min/max values stored per micro-partition, letting the optimizer skip partitions that can't match a filter without reading a single byte of their data.

How it works

  1. Query arrives
  2. Same query, unchanged data, result less than 24 hours old? Yes: Serve from the result cache, no warehouse. No: Send to the warehouse.
  3. Is the needed data in the warehouse's local disk cache? Yes: Read it from local SSD. No: Read it from remote storage.
  4. Execute and return the result
  5. Store the new result in the result cache

Example

-- Run this twice — the second run returns from cache instantly
SELECT COUNT(*) AS total_orders, SUM(amount) AS total_revenue, AVG(amount) AS avg_order
FROM orders;

SELECT QUERY_ID, QUERY_TEXT, BYTES_SCANNED, PERCENTAGE_SCANNED_FROM_CACHE
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
ORDER BY START_TIME DESC LIMIT 10;

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic