Tag: snowflake

The first time a product manager asked me why our “real-time” Snowflake dashboard took four seconds to load on a Monday morning, I didn’t have a good answer. The data was fresh. The query was simple — a filtered aggregation over a few million rows. But at 9 a.m., when three hundred people opened the same dashboard at once, our XSMALL warehouse queued them up like a single cashier at a stadium. Each query was fast in isolation. Together, they were a traffic jam.

I did what everyone does: I threw a bigger warehouse at it, then a multi-cluster warehouse, then watched the credits burn. It helped the concurrency but the per-query latency floor never really dropped below a second or two, and the bill made my manager wince. Snowflake is an OLAP engine. It’s built to scan enormous columnar data for analytics, not to answer the same small lookup a thousand times a second. I was using a freight train to run a pizza delivery service.

Then Snowflake shipped Interactive Tables and Interactive Warehouses. I’ve now run them in production for a few months, and this is the honest guide I wish I’d had: what they are, when they’re worth it, when they’ll quietly cost you money, and the gotchas that only show up after you’ve committed.

The whole feature at a glance: who it serves (green), how it works (blue), and how you set it up plus its limits (yellow).

TL;DR

→ Interactive Tables + Interactive Warehouses are a serving layer inside Snowflake, built for low-latency, high-concurrency reads — dashboards, data-powered APIs, AI agents querying structured data in real time.

→ They work as a pair. The interactive table is optimized for fast retrieval; the interactive warehouse caches its data files as hot cache and serves sub-second reads. Standard warehouses can query interactive tables, but you only get the big speedup through an interactive warehouse.

→ Real numbers from independent testing: an Interactive XS delivered roughly 3.9x lower latency than a standard Gen1 XS, at about 40% lower credit cost per hour — combining to around a 75% reduction in cost per query on a serving workload.

→ The big constraint: no UPDATE or DELETE. The only DML is INSERT OVERWRITE. You keep data fresh with auto-refresh (set TARGET_LAG) against a source table, not by mutating the interactive table.

→ The clustering key is fixed at creation and cannot be changed. Choose it to match the WHERE clauses of your most latency-critical queries. Get this wrong and your only fix is recreating the table.

→ Interactive warehouses historically did not auto-suspend — they stay warm to keep the cache ready, so you effectively pay 24/7. As of the spring 2026 updates, auto-suspend, auto-resume, and auto-scaling reached GA, which changes the cost math meaningfully.

→ Use them when latency and concurrency are the product. Skip them for batch ETL, ad-hoc exploration, or anything write-heavy. This is a serving layer, not a transformation layer.

What they actually are (in plain terms)

Think of your Snowflake setup as having two jobs that have always been jammed into the same tool. Job one is transformation: big batch queries that scan and reshape data. Standard warehouses are great at this. Job two is serving: answering a flood of small, repetitive reads instantly — the dashboard that refreshes for every user, the API endpoint hit thousands of times a minute. Standard warehouses are mediocre at this, not because they’re slow, but because they’re built for throughput on big scans, not latency on small lookups under heavy concurrency.

Interactive Tables and Warehouses are Snowflake’s serving layer. An interactive table stores data in a form optimized for fast, filtered retrieval. An interactive warehouse contains a query engine tuned for short, highly concurrent reads, and it caches the interactive table’s data files locally as hot cache. When a query comes in, it’s answered from that warm cache with a latency profile closer to a key-value store than a data warehouse. The two are a matched pair — the table is fast on its own, but the warehouse is where the sub-second magic happens.

One mental model that helped me: a standard warehouse is a library where a librarian walks the stacks for every request. An interactive warehouse is the same library, but the most-requested books are already stacked on the front desk, warm and waiting. That’s the cache. It’s why the warehouse has to stay on — the moment it suspends, the front desk clears and the next reader waits for the walk to the stacks again.

When to use them (and when not to)

I’ve become fairly opinionated about this after watching a few teams reach for interactive tables because they were new and shiny, then get surprised by the bill. Here’s the honest split.

Use interactive tables when: you’re serving a customer-facing or internal dashboard with real concurrency (dozens to thousands of simultaneous users), you’re powering a data API where each call is a small filtered read and latency is a product requirement, you’re feeding an AI agent that queries structured data in real time, or you have a workload where the same shapes of query hit the same tables constantly and predictably. In all these, latency and concurrency are the product, and a continuously-running warehouse is justified because the traffic is continuous.

Don’t use them when: your workload is batch ETL or transformation (that’s what standard warehouses are for), your queries are ad-hoc and exploratory (the cache never warms usefully if every query is different), your data is write-heavy with lots of updates and deletes (the no-DML constraint will fight you), or your traffic is spiky and infrequent (a warehouse you pay to keep warm for occasional bursts is money on fire — though auto-suspend now softens this).

The question I ask before every interactive-table decision: does this workload justify a warehouse that runs continuously? If yes, the performance is genuinely excellent. If no, you’re probably better off with a well-tuned standard warehouse and result caching.

How to actually set it up

The mental model is familiar if you’ve used Snowflake, which is one of the nicer things about this feature. You create the table with a standard warehouse, then serve it through an interactive one.

First, create the interactive table. The CREATE TABLE syntax is extended with the INTERACTIVE keyword, and a CLUSTER BY clause is required — this isn’t optional like it is on standard tables:

CREATE INTERACTIVE TABLE dashboard_events
CLUSTER BY (tenant_id, event_date)
AS SELECT tenant_id, event_date, event_type, metric_value
FROM raw.events;

Notice the clustering key. Choose it to match the WHERE clauses of your most time-critical queries — here, assuming most dashboard reads filter by tenant and date. This decision is permanent, so think about it harder than you normally would.

To keep the table fresh without DML, make it a dynamic interactive table by setting a target lag. It will auto-refresh from the source to stay within that window (the minimum is 60 seconds):

CREATE INTERACTIVE TABLE dashboard_events
TARGET_LAG = '60 seconds'
CLUSTER BY (tenant_id, event_date)
AS SELECT tenant_id, event_date, event_type, metric_value
FROM raw.events;

Then create an interactive warehouse and attach the table so its data files get cached. Keep the warehouse small — an XSMALL is often plenty for serving:

CREATE INTERACTIVE WAREHOUSE serving_wh
WAREHOUSE_SIZE = 'XSMALL';

ALTER WAREHOUSE serving_wh ADD TABLES dashboard_events;

Point your dashboard or API at serving_wh, and reads now hit the warm cache. The first queries after attaching a table run while the cache warms, so they’ll be slower — don’t benchmark in that window and panic.

The cost math, honestly

This is where teams get surprised, so let’s be concrete. Interactive warehouses cost roughly 40% less per credit than standard warehouses — an XSMALL standard warehouse runs at 1 credit/hour, while an interactive XSMALL is about 0.6 credits/hour. Combined with the lower latency (fewer warehouse-seconds per query), independent testing found the cost per query dropped by around 75% on a serving workload.

That sounds like a pure win, and for the right workload it is. But the catch has always been the always-on nature. Historically, interactive warehouses did not auto-suspend — they have to stay warm to keep the cache ready, so you were effectively paying for 0.6 credits/hour × 24 hours × 30 days whether or not traffic justified it. That’s roughly 432 credits/month for a single XSMALL that never sleeps. If your traffic is genuinely continuous, the per-query savings dwarf this. If your traffic is bursty, this idle cost can erase the savings entirely.

The spring 2026 updates changed this materially: auto-suspend and auto-resume reached GA, so you can now let an interactive warehouse suspend during quiet periods and pay the cache-rewarming cost on resume instead of paying to stay warm around the clock. There’s also a fallback warehouse option — when a query on the interactive warehouse exceeds the timeout, Snowflake can transparently retry it on a designated standard warehouse instead of erroring out, which makes mixed workloads far less fragile. And auto-scaling went GA, so the warehouse scales with concurrency instead of you hand-tuning cluster counts.

My rule: model the idle cost first. Take your warehouse’s credits/hour, multiply by the hours you’ll actually keep it warm, and compare that baseline to your current serving spend before you get excited about per-query savings. The per-query numbers are real, but they only pay off above a traffic threshold.

The gotchas nobody warns you about

The clustering key is a one-way door. On a standard table, you can iterate on clustering freely. On an interactive table, the clustering key is fixed at creation and cannot be changed. If your query patterns shift, or you guessed wrong about which columns your hot queries filter on, your only remedy is recreating the table. Treat this decision like a schema migration, not a tuning knob. This is, in my experience, the single most common source of regret with the feature.

No UPDATE, no DELETE — plan your pipeline around it. The only DML is INSERT OVERWRITE. If your instinct is to patch a few rows, you can’t. The intended pattern is: mutate the source table with normal DML, and let auto-refresh (TARGET_LAG) propagate changes to the interactive table. This is actually more efficient than row-level DML on the serving layer, but it forces you into a refresh-based mental model. Append-only and full-refresh patterns fit naturally; frequent surgical updates do not.

The warehouse only queries what you attach. An interactive warehouse can only query interactive tables that have been explicitly added to it, and it only supports SELECT. Forget to ADD TABLES and your queries won’t find the data. This trips people up because it’s different from the standard warehouse model where any warehouse can query any table it has grants on.

Cache-warming latency is real and invisible in benchmarks. When you first attach a table, or right after a refresh, the cache is cold and those initial queries are slower. If you benchmark immediately after setup and conclude the feature is underwhelming, you measured the cold path. Let it warm, then measure.

No Fail-safe. The Fail-safe data recovery mechanism isn’t available for interactive tables. Time Travel still works, so you’re not flying blind, but the extra safety net you may be used to isn’t there. For a serving layer fed from a source table you control, this is usually fine — you can always rebuild from source — but know it going in.

Mistakes that drain the budget

Keeping a warehouse warm for traffic that isn’t there. The classic. You stand up an interactive warehouse for a dashboard that gets heavy use twice a day and sits idle the rest of the time, and you pay to keep the cache warm through all the dead hours. With auto-suspend now GA, configure it — don’t run 24/7 out of habit.

Routing every query to the interactive warehouse. Interactive warehouses shine on small, concurrent reads. Fire a heavy, complex analytical query at one and you’re using the wrong tool — that’s what the fallback warehouse and your standard clusters are for. Route small serving queries to interactive, keep heavy jobs on standard. Getting this routing right is where most of the real-world value (and most of the operational effort) lives.

Migrating tables that don’t need it. Every table you make interactive adds a data-management surface — a refresh to monitor, a cache to keep warm, a clustering decision you can’t undo. Only promote the tables that actually sit in a latency-critical serving path. A table that backs a weekly report has no business being interactive.

The one principle

Interactive tables are a serving layer, not a transformation layer. Transform data on standard warehouses; serve it on interactive ones — and only when the traffic is continuous enough to justify a warehouse that stays warm. The feature is genuinely excellent at the job it was built for. Nearly every problem I’ve seen with it came from asking it to do a different job.

Related reading: Snowflake interactive tables and warehouses (official docs) · CREATE INTERACTIVE TABLE reference · Snowflake Query Execution: What Really Happens · Snowflake Iceberg v3: When to Migrate · dbt State on Snowflake: Skip Unchanged Models

Ask ten data engineers what happens when you run a query in Snowflake and most of them will tell you the same thing: the warehouse runs it. SQL goes in, the warehouse chews on it, results come out. Clean, simple, and just wrong enough to cost you money. The truth is that Snowflake query execution is a trip through three separate layers, and the one you actually pay for is the last to get involved — if it gets involved at all.

The path every query takes — and the shortcut a cache hit gets to skip compute entirely.

TL;DR

→ Running a query isn’t one step. It’s a trip through three layers that scale independently, and the compute you pay for is the last one to wake up.

→ The cloud services layer does the thinking first — parses, plans, and prunes — using per-partition metadata to throw out data that can’t match your filter before a single byte is read.

→ If the exact same query ran in the last 24 hours and the data hasn’t moved, you get a cached result for free. No warehouse. No credits. Milliseconds.

→ Only on a cache miss does a virtual warehouse actually spin up and crunch the query across its nodes in parallel.

→ Warehouses keep recently-read micro-partitions on local SSD, but that cache vanishes the second the warehouse suspends.

→ “Identical” is brutally literal. A stray table alias, lowercase keywords, or a RANDOM() call quietly knocks you off the cache and back onto billed compute.

→ When a query is slow, it’s usually scanning too much, not computing too much. A bigger warehouse fixes the second problem and never the first.

The mental model that’s quietly costing you money

Here’s the reframe that changes how you think about every query: the virtual warehouse is the last thing to get involved, and plenty of queries never touch it at all. Almost everything that decides whether your query is fast or slow, cheap or expensive, happens in a layer you probably weren’t even picturing.

Get this wrong and you end up doing what I’ve watched teams do a dozen times: a dashboard feels sluggish, someone bumps the warehouse from Medium to Large, it gets a little faster, everyone moves on. The bill goes up. The query was never compute-bound in the first place. They paid more to run the same broken query faster.

So let’s actually follow a query, from the moment you hit run to the moment rows come back.

The three layers a query passes through

Snowflake splits into three layers, and the thing to internalize is that they scale completely independently. That’s not a trivia fact — it’s the reason the whole platform behaves the way it does. Every query touches all three. Some only touch the first.

The cloud services layer is the brain. The warehouse is just the muscle.

At the top sits the cloud services layer — the brain. Logins, sessions, parsing, query planning, transaction coordination, all the metadata: it lives here. Underneath that is the compute layer, made up of virtual warehouses. These are the MPP clusters that do the actual SQL grunt work. And at the bottom is storage: your data sitting in cloud object storage (S3, Azure Blob, or GCS) as immutable, compressed, columnar micro-partitions.

The magic is that none of these share resources with each other. You can resize compute without moving a single byte of storage. You can point ten warehouses at the same table and none of them slow the others down. Keep that in your back pocket, because it’s exactly why the “just make the warehouse bigger” instinct fails so often.

Step 1: The cloud services layer does the thinking

Your query lands, and before any table data gets read, the cloud services layer goes to work.

It parses the SQL, and the cost-based optimizer builds a plan. Then it does the single most important thing for performance, and it does it for free: partition pruning. Every micro-partition carries metadata, including the min and max value for each column. So when you write WHERE order_date = '2026-06-01', the optimizer reads that metadata first and skips every partition whose range can’t possibly hold a matching row. Ten thousand partitions in the table, three that actually get scanned. No index. No tuning. You didn’t do anything.

This is also why a query can come back in milliseconds with no warehouse at all. Run a SELECT COUNT(*) or a MAX() and Snowflake often answers straight from the metadata cache — you’ll see a lone METADATA-BASED RESULT step in the query profile and zero compute on the bill.

How good the pruning is comes down to how well your data is naturally ordered, which is the whole point of clustering and Snowflake’s automatic optimization features.

Step 2: The result cache check, before any compute

Now, still before any warehouse gets involved, cloud services checks the result cache. Identical query in the last 24 hours, data hasn’t changed since? Snowflake just hands back the stored result from the cloud services layer. Nothing resumes. Nothing gets billed. The answer is basically instant.

This is the cheapest query you’ll ever run, and it’s the reason refreshing a dashboard a second time costs nothing. Here’s the part that surprises people: every time that cached result gets reused, the 24-hour clock resets. Keep hitting it and a result can stay alive for up to 31 days. A popular dashboard query can effectively sit in cache for a month and never cost a credit.

Which brings us to the catch. “Identical” is doing a lot of work in that sentence, and it’s the thing nobody reads about until they’re staring at a bill wondering where the money went.

Step 3: The virtual warehouse finally runs it

Cache miss. Now — and only now — the query goes to a virtual warehouse. If it was asleep, it resumes (a second or two, usually). The warehouse is a little cluster of compute nodes, and this is where massively parallel processing earns its name: the surviving micro-partitions get split across the nodes and cores, and the filters, joins, and aggregations all happen in parallel.

This is the layer with a meter running. You’re billed by the second, with a 60-second minimum every time a warehouse starts or resumes. That minimum trips people up constantly — a warehouse that keeps flickering on and off for tiny one-off queries can quietly cost more than one you just leave warm, because every single resume restarts that 60-second clock. It’s also the only layer where resizing actually helps, and that matters less often than you’d think.

Step 4: Storage, and the cache that disappears

The warehouse goes and fetches the micro-partitions that survived pruning. Each micro-partition holds 50–500 MB of uncompressed data in a columnar format, so only the columns you actually asked for get read. Ask for two columns out of fifty and you pay to read two.

And there’s a second cache down here. As the warehouse pulls partitions from remote storage, it stashes them on local SSD. Next query on that same warehouse that needs the same data? It reads from SSD instead of making the round trip to object storage, which is a lot faster. This is what people mean when they say a warehouse “warms up.”

The catch — and there’s always a catch — is that this cache gets wiped the instant the warehouse suspends. That’s the real trade-off hiding inside your AUTO_SUSPEND setting. Suspend fast and you stop paying for idle time but you toss the warm cache. Leave it running and you keep the cache but pay for the idle. A lot of teams land on 60 seconds, but honestly the right answer depends entirely on how often your queries actually fire.

Three caches, one comparison to bookmark

There are three caches, they live in different layers, and mixing them up is behind half the “wait, why was that slow?” conversations I’ve ever had.

The two cloud-services caches are shared across the whole account — every warehouse, every user benefits. The local disk cache belongs to one warehouse and dies with it. If you want the gory details with real query-profile screenshots, Snowflake’s own community piece on caching is the place to go.

The cache rules nobody warns you about

The result cache only kicks in when Snowflake decides the new query is identical to the old one, and “identical” is far more literal than anyone expects. Snowflake’s own docs spell it out: add a table alias, or just retype your keywords in lowercase, and you miss the cache. The query goes to billed compute and you never get a warning.

It also bails on the cache if the query contains a non-deterministic function — UUID_STRING, RANDOM, RANDSTR are the usual suspects — or an external function, or if it reads from a hybrid table. And the role running it needs privileges on every table involved.

⚠️ What this means in practice: if you want your dashboards and scheduled jobs riding the cache, lock down the exact text your BI tool emits. Random aliasing, dynamic comments, an injected session variable, a stray current_timestamp() — any of it silently drops you off the cache, and you start paying to recompute answers you already had. The Query Cost Estimator is a quick way to put a number on what those misses are costing you.

Why a bigger warehouse usually isn’t the answer

This is the most expensive misunderstanding in all of Snowflake, so it’s worth being blunt about it. A query is slow for one of two reasons: it’s scanning too much data, or it’s doing too much actual computation. Resizing the warehouse only helps the second one.

When the real problem is bad pruning — Snowflake had to scan most of the table because the data wasn’t laid out in a way it could skip — a bigger warehouse just reads the same mountain of data faster and charges you more per second for the privilege. The fix isn’t more nodes. It’s better pruning: clustering, a tighter filter, a smarter query shape. The query profile tells you which world you’re in. Look at partitions scanned versus partitions total. If you’re chewing through 95% of them, no warehouse size on earth is going to save you.

And sometimes the honest answer to a giant, I/O-heavy scan isn’t a bigger Snowflake warehouse at all — it’s moving that work somewhere cheaper, which is the whole idea behind running the query in DuckDB instead. If you do decide a resize is warranted, the warehouse sizing guide will at least keep you from overshooting.

What I actually check when a query misbehaves

When something’s slow or weirdly expensive, here’s where I start. All of it comes out of the query profile and INFORMATION_SCHEMA — no guessing, no vibes.

-- Did your last query hit the result cache?
-- A cache hit shows bytes_scanned = 0 and a near-zero execution time.
SELECT query_id,
       query_text,
       bytes_scanned,
       percentage_scanned_from_cache,
       execution_time / 1000 AS exec_seconds
FROM TABLE(information_schema.query_history())
ORDER BY start_time DESC
LIMIT 10;

-- Turn the result cache off for honest benchmarking (this session only)
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
-- ...run your test queries...
ALTER SESSION UNSET USE_CACHED_RESULT;

-- Hunt down your worst pruning offenders
SELECT query_id,
       partitions_scanned,
       partitions_total,
       ROUND(100 * partitions_scanned / NULLIF(partitions_total, 0), 1) AS pct_scanned,
       bytes_scanned / 1e9 AS gb_scanned
FROM snowflake.account_usage.query_history
WHERE partitions_total > 0
ORDER BY bytes_scanned DESC
LIMIT 20;

-- Balance warm cache against idle cost
ALTER WAREHOUSE analytics_wh SET AUTO_SUSPEND = 60;  -- seconds

The number to stare at is pct_scanned in that third query. High on your slow queries? You’ve got a pruning problem, and that’s where your time should go — not the warehouse dropdown.

What this actually costs you

Architecture is interesting, but the bill is what makes it matter. So let’s put real numbers on it. Credit prices swing with your edition, cloud, and region, so treat these as illustrative — drop your own rate into the cost calculator for the exact figure.

Picture one ordinary dashboard query. It runs on a Medium warehouse (4 credits an hour, call it roughly $3 a credit), and it fires 100 times a day because that’s how often people open the dashboard. Here’s how the same query plays out depending on whether the layers are doing their job:

Same query. Same data. Roughly $300 a month versus $3, and the only difference is whether you let the result cache and pruning do what they’re built to do. Now multiply that across a dashboard with thirty queries on it, and you can see how a Snowflake bill quietly triples without anyone writing a single new query.

The gotchas nobody warns you about

The 60-second minimum punishes spiky workloads. A query that finishes in two seconds still bills a full minute if the warehouse had to resume to run it. Set AUTO_SUSPEND too aggressively on a workload that fires a query every couple of minutes and you’ll pay more in cold-start minimums than you ever saved on idle time. Aggressive suspend is not automatically cheaper.

One write nukes the whole result cache. The cache is invalidated by any change to the underlying table, not just changes to the rows your query touched. A single late-arriving record at 2 a.m. quietly wipes the cached result for every dashboard query built on that table, and tomorrow morning they all run on compute again. If your “cached” dashboard mysteriously costs money some mornings, this is usually why.

Cloud services is free, right up until it isn’t. The parsing, planning, and pruning in the cloud services layer is free — but only while it stays under 10% of your daily warehouse compute. Hammer it with thousands of tiny metadata queries or relentless INFORMATION_SCHEMA polling and you cross that line, and Snowflake starts billing the overage. Most teams never hit it; the ones running huge volumes of trivial queries do, and they never see it coming.

The local cache doesn’t follow you between warehouses. That warm SSD cache belongs to one specific warehouse. Run your ETL on one warehouse and your reporting on another — sensible workload isolation — and the reporting warehouse gets exactly zero benefit from the partitions ETL just pulled. Each warehouse warms its own cache from cold.

SELECT * throws away the columnar advantage. Snowflake only reads the columns you ask for. Ask for all of them and you pay to read all of them, even when the dashboard displays three. In a wide table that’s the difference between scanning a few columns and dragging the entire row off storage.

The mistakes that quietly drain the budget

Almost every overspending Snowflake account I’ve looked at is making some combination of these five:

1. Leaving AUTO_SUSPEND at the 600-second default. Ten full minutes of paid idle after every burst of activity. On a warehouse that’s used in short bursts, that idle time can dwarf the actual query time. Most analytics warehouses are fine at 60 seconds.

2. One giant warehouse for everything. Pile ETL, ad-hoc analysis, and dashboards onto a single warehouse and they fight over the cache and the compute. Separate warehouses per workload keep each cache warm and each workload predictable.

3. Scaling up when the real fix is pruning. The reflex we opened with. If pct_scanned is high, a bigger warehouse just burns more credits reading the same data. Fix the layout, not the size.

4. Letting the BI tool emit sloppy SQL. Inconsistent aliases, injected timestamps, and per-user comments mean “the same” dashboard query is never byte-for-byte identical, so it never reuses the result cache. Standardize what the tool sends.

5. Over-clustering a hot table. Automatic clustering isn’t free — it spends credits reorganizing data in the background. On a table that’s written constantly, that background churn can cost more than the queries it speeds up. Cluster the tables you read far more than you write.

The one principle to take away

If you remember nothing else, remember this: scan less, don’t compute faster. Snowflake decides almost everything about a query’s speed and cost before the warehouse ever wakes up — in the pruning, in the caches, in how your data is laid out. The warehouse size is the last lever you should reach for, not the first. Get the layers above it working and most “we need a bigger warehouse” conversations simply stop happening.

Related reading: Snowflake Time Travel: The Real Architecture · Snowflake Optima: 15x Faster Queries at Zero Cost · Query Snowflake in DuckDB and Cut Costs · Snowflake Streams & Tasks: SCD2 Pipeline Guide

A dashboard that ran in four seconds on Monday took nineteen seconds on Tuesday. Same query, same data, same warehouse size. I spent the better part of an hour convinced Snowflake was having a bad day, checking the status page, refreshing query history, muttering about “platform issues” in our team Slack — before I noticed our DevOps script had quietly added an aggressive auto-suspend policy the night before. The warehouse cache was getting wiped every single morning, and I’d built our entire “fast dashboard” reputation on a cache that reset itself before anyone showed up to work.

TL;DR

• Snowflake’s warehouse cache (local disk cache) stores raw compressed micro-partition data on each node’s SSD, not query results.
• It’s separate from the result cache and metadata cache — three different caches, three different jobs.
• Auto-suspending a warehouse wipes this cache completely. Resuming starts it cold every time.
• You can verify it’s working with the ‘percentage scanned from cache’ field in query profile or ACCOUNT_USAGE.QUERY_HISTORY.
• Multi-cluster warehouses don’t share this cache — a query routed to a new cluster starts cold even if a sibling cluster is warm.
• You can’t manually size or pin it. The only real lever you control is the auto-suspend timer.

That mistake is what got me actually reading how this thing works instead of just trusting that Snowflake would “handle it.” Turns out the warehouse cache isn’t magic, isn’t tunable, and isn’t the same thing as the result cache most people learn about first. Here’s what it actually is, where it lives, and how to tell when it’s helping you versus quietly costing you money.

Three caches, one name people use for all of them

Snowflake has three distinct caching layers, and the confusion starts because people say “Snowflake caches my query” without specifying which one did the work. The result cache sits in the cloud services layer and stores entire finished query results — if the same exact SQL text runs again within 24 hours and the underlying data hasn’t changed, you get the answer back with bytes_scanned = 0 and zero compute cost. The metadata cache, also in cloud services, holds statistics about every micro-partition — min and max values per column, row counts — so Snowflake can decide which partitions to skip before it ever touches the data.

The warehouse cache is the third one, and it’s the one this article is actually about. It lives on the local SSD of every node in a running virtual warehouse, and it stores raw, compressed micro-partition data — not query results, not aggregated answers, the actual columnar bytes that got pulled from remote storage to answer a scan.

Why this distinction actually matters

If you only know the result cache exists, you’ll misdiagnose a lot of performance issues. Change one character in a WHERE clause, add a comment, swap the role running the query — any of those bypass the result cache entirely, because it requires an exact text match. The warehouse cache doesn’t care about query text at all. It cares about which micro-partitions a query needs and whether those bytes are already sitting on a node’s SSD from a previous scan.

What a micro-partition actually is

You can’t understand the warehouse cache without understanding the unit it stores. When data lands in a Snowflake table, it gets automatically carved into micro-partitions — contiguous, immutable blocks holding somewhere between 50MB and 500MB of uncompressed data each, stored in a columnar format. There’s no manual partitioning scheme to design, no index to build. Snowflake just does this on every load.

Each micro-partition carries its own metadata: minimum and maximum values for every column, which is exactly what the metadata cache is built from. When you filter a query on order_date > '2026-01-01', Snowflake checks that metadata first and skips any micro-partition whose max date falls before that threshold. That’s partition pruning, and it happens before a single byte gets pulled into the warehouse cache. Pruning decides what to read; the warehouse cache decides how fast a repeat read of the same partitions will be.

The actual lifecycle of the warehouse cache

Here’s the sequence that matters in practice. A warehouse resumes from suspended state with completely empty SSD — there’s nothing cached because the compute nodes assigned to it are freshly provisioned. The first query that touches a table has to pull every relevant micro-partition from remote cloud storage, which is the slowest tier in the whole architecture. As those partitions get read, they’re written to the local SSD cache as a side effect — not because you asked for caching, just because that’s what happens when a node reads remote data.

The second query — if it touches the same micro-partitions and the warehouse is still running — can read those bytes off local SSD instead of going back to remote storage. This is meaningfully faster, and it’s why a sequence of similar queries against the same table speeds up the longer a warehouse stays warm. There’s no explicit “build the cache” step. Cache population is a byproduct of usage, which is exactly why a single cold query tells you almost nothing about real-world performance.

Then the warehouse suspends, and it’s gone

This is the part that bit me. When a warehouse auto-suspends, the compute nodes it was using get released back into Snowflake’s shared pool. The SSD on those specific nodes goes with them. When the warehouse resumes — even if it’s seconds later, even if it’s the exact same warehouse name — there’s no guarantee you get the same physical nodes back, and the cache starts from zero regardless. There’s no persistence, no “save state before suspending.” It’s just gone.

Resizing a warehouse up or down does the same thing. A different size means a different set of nodes, which means different SSDs, which means the next queries run cold no matter how warm things were five minutes earlier.

How to actually see this working

Stop assuming and go look at it. Open the query profile for any query in Snowsight and check the IO statistics panel — there’s a field literally called percentage_scanned_from_cache. Run the same query twice in a row on a warm warehouse and watch that number jump from near 0% to something much higher on the second run. Suspend the warehouse, resume it, run the same query again, and watch it drop back to 0%. That’s the entire mechanism, visible in about ninety seconds of testing.

For a wider view across your account, query history gives you the same field at scale. This is the query I run when someone asks “is our caching even helping”:

Check your real cache hit rate (last 30 days, by warehouse)

SELECT
    warehouse_name,
    COUNT(*) AS query_count,
    SUM(bytes_scanned) AS bytes_scanned,
    SUM(bytes_scanned * percentage_scanned_from_cache) AS bytes_from_cache,
    SUM(bytes_scanned * percentage_scanned_from_cache)
        / SUM(bytes_scanned) AS pct_scanned_from_cache
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(month, -1, CURRENT_TIMESTAMP())
  AND bytes_scanned > 0
GROUP BY 1
ORDER BY 5;

A low percentage here on a warehouse running frequent, similar queries is a signal — either your auto-suspend timer is too aggressive for the workload, or the queries aren’t actually similar enough at the data level to benefit from a warm cache, even if the SQL looks similar to a human reading it.

Result cache, warehouse cache, metadata cache — side by side

The auto-suspend tradeoff nobody explains clearly

Snowflake’s own guidance generally points toward short auto-suspend windows to control credit spend, and for spiky, unpredictable workloads that’s the right call. But if a warehouse runs frequent, similar queries back-to-back — a BI tool polling dashboards, an analyst iterating on the same fact table — an aggressive suspend timer means you’re paying the “cold scan” tax on nearly every query, because the cache never gets the chance to stay warm between them.

The fix isn’t complicated once you see the tradeoff: separate warehouses by access pattern. A reporting warehouse that gets hit constantly during business hours can run a longer suspend window, or stay up during known peak hours, while a warehouse running sporadic ad-hoc analyst queries can suspend aggressively without losing much, since the cache wasn’t going to be useful between unrelated queries anyway.

The multi-cluster gotcha

If you’re running a multi-cluster warehouse for concurrency, know that clusters don’t share this cache with each other. Cluster A being fully warm doesn’t help a query that gets routed to Cluster B when Snowflake spins up a new cluster to handle a concurrency spike. That new cluster starts cold, scans from remote storage, and only builds its own local cache from that point forward. Teams chasing consistent query latency under high concurrency often get surprised by this — the warehouse “should” be warm, and on average it is, but any individual query can still land on a cold cluster.

What the warehouse cache does not do

It’s worth being precise about the boundaries here, because I’ve seen this cache get credited for things it isn’t responsible for. It doesn’t store query results — that’s the result cache’s job, and it’s a different layer entirely with a different lifetime. It doesn’t help with intermediate computation that spills to local disk during a large sort or hash join — that’s a separate spillage mechanism tracked under bytes spilled to local storage in query profile, not the same SSD allocation conceptually even though it physically lives in a similar place. And it provides no benefit on the write path for a fresh INSERT into new micro-partitions, since there’s nothing previously cached to reuse.

What’s actually worth doing about this

You don’t get a dial to resize this cache or pin specific tables into it, so the practical levers are all about behavior, not configuration. Match auto-suspend timers to actual access patterns instead of using one default across every warehouse. If a workload is genuinely cache-sensitive — recurring dashboards, iterative analyst sessions — consider a short warm-up query immediately after resume rather than letting the first real user query eat the cold-start cost. And when you’re debugging a “why did this get slower” ticket, percentage_scanned_from_cache should be one of the first three things you check, right alongside partition pruning stats, before you start blaming the query itself.

For the deeper mechanics of how partition pruning interacts with clustering keys, the official Snowflake documentation on warehouse cache optimization is worth reading directly — it’s one of the rare vendor docs pages that actually shows the diagnostic query instead of just describing the concept. The micro-partitions and clustering documentation is the right follow-up if pruning efficiency turns out to be your actual bottleneck instead of cache temperature.

Does the Snowflake warehouse cache get cleared when the warehouse suspends?

Yes, completely. The warehouse cache lives on the SSD of the compute nodes assigned to that warehouse, and those nodes get released back to the pool on suspend. When the warehouse resumes — even seconds later — it’s starting from zero cached data.

Why does percentage_scanned_from_cache show 0% on a brand new warehouse?

Because there’s nothing to scan yet. The first query against any table after a cold start has to pull every micro-partition it needs from remote storage. Cache population happens as a side effect of running queries, not in advance.

Does the warehouse cache help with INSERT, UPDATE, or DELETE performance?

📷 the SSD cache layer, mid-rebuild after a warehouse resume — not glamorous, but it’s where the speed comes from