How Caching Works in Snowflake

1. How Caching Works in Snowflake

In this video you'll learn the three places Snowflake caches data, when each cache fires, and the SQL patterns that quietly stop them from working.

2. Three Caching Layers

Snowflake caches in three different places, and each one fires under different conditions. The Result Cache stores complete query results in Cloud Services and returns them without running a warehouse. The Warehouse Cache stores recently scanned data on the warehouse's SSD and memory. The Metadata Cache stores table statistics and row counts in Cloud Services, so simple queries can be answered from metadata alone.

3. When the Result Cache is used (and when it isn't)

The Result Cache fires when these conditions all hold - break any one and you get a cache miss. The query text has to match exactly; even a different case or alias breaks it. The query can't use functions that change between runs like UUID STRING, RANDOM, or CURRENT TIMESTAMP. The underlying tables can't have changed. The cached result has to still exist - a 24-hour TTL that resets on reuse, up to 31 days. And the role needs the right privileges. SELECTs need access to the underlying tables, not the same role as before. SHOW queries do need the same role.

4. Warehouse cache and Metadata cache

The Warehouse Cache stores recently scanned micro-partitions on the warehouse's SSD and memory. If you run a similar query soon after, it reads from the warehouse cache instead of cloud storage. When the warehouse suspends, the cache is dropped — so for hot workloads, keep auto-suspend at at least ten minutes. The Metadata Cache is always on and lives in Cloud Services. It stores table statistics — simple queries like SELECT COUNT star can be answered from metadata without running a warehouse.

5. SQL patterns that Prevent Partition Pruning

Partition pruning is how Snowflake skips micro-partitions that can't contain matching rows. But when you wrap the filter column in a function — MONTH, YEAR, CAST, DATE TRUNC - Snowflake can't evaluate the filter against the micro-partition metadata. It has to scan everything and apply the function row by row. The fix is explicit range boundaries. A concrete start and end date lets Snowflake prune at the partition level. That one change can eliminate a full table scan.

6. Two more SQL patterns to avoid

Two more patterns that quietly cost compute. SELECT star reads every column in every micro-partition. If you only need two columns from a wide table, that's bytes scanned you didn't have to pay for. Naming columns explicitly is the simplest reduction. And implicit cross-joins — listing tables in the FROM clause without a JOIN condition — produce a Cartesian product. Half a million rows joined to a thousand becomes five hundred million. Always use explicit JOIN with an ON condition.

7. Let's practice!

Now to spot which patterns hurt performance. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.