Course contents

SnowPro Core COF-C03 Study GuidePart 1: ArchitectureThe Three Caches

The Three Caches

The Three Caches

Every query you run asks the same implicit question first: has some version of this work already been done, and can Snowflake hand you the answer instead of redoing it? Three separate answers exist to that question, stored in three separate places, and the gap between them is enormous: a result-cache hit returns in under a second with zero compute, while a cold read from remote storage on a freshly resumed warehouse can take minutes on a large table. Most of what feels like "Snowflake is fast" or "Snowflake is slow" on a given day is really just which of these three caches you happened to hit.

Definition

Snowflake maintains three caches at three different layers: the metadata cache (cloud services), the result cache (cloud services), and the local disk cache, also called the warehouse or data cache (on each warehouse's SSDs). They sit at different distances from the data and survive different events. The exam tests which cache serves which scenario and what invalidates each one, so learn them as a trio, ordered fastest to slowest.

CacheLives inHoldsLifetimeInvalidated / lost whenWarehouse needed?
Metadata cacheCloud servicesObject definitions, per-partition statistics (min/max, counts)Always currentNever stale; maintained on every writeNo
Result cacheCloud servicesComplete result sets of previous queries24h, extended on each reuse, up to 31 days maxUnderlying data changes; query text differs; certain functions used; disabled by parameterNo
Local disk cacheWarehouse SSDsRaw micro-partition data read from storageWhile the warehouse runsWarehouse suspended or dropped; a downsize drops the part on removed nodesYes

Which cache answers this query?

Which layer answers a query, checked in order, cheapest first. First: is the same query text on unchanged data in the result cache, from the last 24 hours? If yes, cloud services returns it with no warehouse and no credits. Second: can metadata alone answer it, such as COUNT(*), MIN or MAX on a whole table, SHOW or DESCRIBE? If yes, also no warehouse. Otherwise the warehouse runs and costs credits. Third: are the needed micro-partitions already on this warehouse's local SSD since it last resumed? If yes, the local disk cache serves them fast; if not, they are read from remote object storage, the slowest path.A query arrivesSame text, same data,run in the last 24 h?Can metadata aloneanswer it?Warehouse runscredits from herePartitions already onthis warehouse's SSD?ANSWERED BYResult cachecloud services · 0 creditsANSWERED BYMetadataCOUNT, MIN/MAX, SHOW · 0 creditsREAD FROMLocal disk cachewarehouse SSD · fastREAD FROMRemote storageS3 / Blob / GCS · slowestyesnoyesnoyesno

A query arrives

Same text, same data,run in the last 24 h?

no

yes

ANSWERED BYResult cachecloud services · 0 credits

Can metadata aloneanswer it?

no

yes

ANSWERED BYMetadataCOUNT, MIN/MAX, SHOW · 0 credits

Warehouse runscredits from here

Partitions already onthis warehouse's SSD?

yes

READ FROMLocal disk cachewarehouse SSD · fast

no

READ FROMRemote storageS3 / Blob / GCS · slowest

Figure 1.7 Which layer answered the query? Snowflake checks the cheapest option first. The two green outcomes need no warehouse and no credits; from the moment a warehouse runs, credits are spent, and the local SSD cache only decides how fast the data arrives.

The result cache in depth

When any query completes, its result is stored in cloud services for 24 hours. If any user in the account submits the same query, Snowflake returns the stored result instantly, at zero compute cost, with no warehouse running. The conditions, and every one of them is testable:

  • The new query text must match the cached query essentially exactly (a trivial difference like SELECT a, b vs SELECT b, a breaks it).
  • The underlying table data must not have changed, and neither may the relevant micro-partitions.
  • The query must not use functions that return a different result on each run, such as RANDOM(), UUID_STRING() or RANDSTR(), and must not call external functions.
  • The role running the query must hold the privileges it needs on every table the query reads; results are shared across users and across warehouses, since the cache lives above the warehouse layer.
  • Each reuse resets the 24-hour clock, up to a hard ceiling of 31 days from first execution, after which the result is purged regardless.
  • It can be disabled per session or account with ALTER SESSION SET USE_CACHED_RESULT = FALSE (a standard trick when benchmarking).

Two mechanics behind those rules. The match is a hash of the query text plus execution context, which is why SELECT a, b and SELECT b, a miss each other: nobody compares result shapes, the texts simply hash differently. And a hit isn't a security bypass. Cloud services re-checks that your role holds the privileges on every underlying object before serving the stored result, so revoking access revokes cached results too.

Exam trap · what does NOT kill the result cache

Suspending the warehouse, resizing the warehouse, running the query from a different warehouse, or a different user running it. None of these invalidate the result cache, because it doesn't live in a warehouse. What does end its availability: underlying data changes, and 24 hours passing without reuse. Learn them as a pair.

The local disk cache in depth

While a warehouse runs, every micro-partition it reads from remote storage is kept on its local SSDs. Subsequent queries touching the same data read from SSD instead of object storage, which is dramatically faster. Properties that matter:

  • It's per-warehouse. BI_WH's cache does nothing for ETL_WH.
  • It's purged on suspend. This is the hidden cost of aggressive auto-suspend: a warehouse that suspends every 60 seconds re-reads everything from remote storage on each resume. Cheap on paper, slower in practice.
  • Partial hits work: a query can read some partitions from SSD and fetch the rest remotely.

The cache evicts least-recently-used data as it fills, so a warehouse alternating between two working sets larger than its SSDs will thrash between them; that pattern runs better on two warehouses or one size up, since more nodes means more aggregate SSD. Suspending drops the cache. Making a warehouse smaller drops the cache on the nodes it removes.

Step by step: one query, three caches

  1. Query arrives. Cloud services checks the result cache: exact match, fresh data? Return instantly, done, no credits.
  2. No hit. Can metadata alone answer it (COUNT, MIN, MAX, SHOW)? Return from metadata, no warehouse, no credits.
  3. No. The optimizer builds a plan, prunes partitions via metadata, and hands the plan to the warehouse.
  4. The warehouse reads needed partitions, from its local disk cache when present, from remote storage otherwise, executes, returns the result, and the result is written into the result cache for next time.

Real production example

Production ScenarioIsmaFrost · finance reporting

A Monday-morning classic. The CFO opens a dashboard at 08:00 and it renders instantly: every tile hits the result cache, because a scheduled job ran the identical queries at 07:30 and no load has touched those tables since. At 08:05 the daily load lands in FINANCE_FACT, invalidating those cached entries the instant the underlying micro-partitions change.

The 08:10 refresh misses the result cache, runs on the warehouse, and repopulates the cache on completion. Everyone else's 08:15 refresh is instant again, served from that same cache entry regardless of who they are or which warehouse they're attached to. Understanding this rhythm is also how you explain to finance why Monday 08:10 shows a compute spike and 08:15 doesn't: it isn't a fluke, it's the architecture working exactly as designed.

Memory aid

"Results upstairs, data downstairs, metadata everywhere." The result cache lives upstairs in cloud services (survives anything that happens to warehouses). The data cache lives downstairs on the warehouse's own disks (dies with suspend). Metadata isn't really a cache at all, it's the always-current catalog. If the question mentions suspend, the answer concerns the local disk cache. If it mentions 24 hours or another user, it's the result cache.

Key takeaways
  • Result cache: cloud services, 24h sliding window, 31-day cap, account-wide, zero compute, killed by data changes.
  • Local disk cache: per-warehouse SSD, killed by suspend, partly lost on a downsize, the reason instant auto-suspend isn't always cheaper.
  • Metadata cache: always current, answers COUNT/MIN/MAX and SHOW/DESCRIBE without any warehouse.
  • USE_CACHED_RESULT = FALSE for honest benchmarking.