Tag: snowflake

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.

Cache	Layer	What it stores	Survives suspend?	Compute cost
Result cache	Cloud services	Final query result sets	Yes (24h, resets on reuse, 31-day max)	None
Metadata cache	Cloud services	Row counts, min/max, distinct counts	Yes	None
Local disk (warehouse) cache	Compute	Micro-partitions read from storage	No — purged on suspend	Warehouse already running

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:

Scenario	What actually runs	~Cost / day	~Cost / month
No cache, bad pruning (30s/run, scans ~95% of partitions)	100 full compute runs	~$10	~$300
Result cache hits 90% of the time	10 compute runs, 90 free cached returns	~$1	~$30
Cache + good pruning (3s/run, scans ~5%)	10 runs at a tenth of the scan	~$0.10	~$3

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

Cache Layer	Where It Lives	What It Stores	Cleared When	Compute Cost
Result Cache	Cloud Services layer	Full query results	24 hours of inactivity, or DDL on underlying tables	Zero — no warehouse needed
Warehouse Cache	SSD on each compute node	Raw compressed micro-partitions	Warehouse suspends, resizes, or node is replaced	Warehouse must be running
Metadata Cache	Cloud Services layer	Min/max values, row counts, partition stats	Rarely — persists with the table	Zero — used for pruning before scan

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

Every time I demo Snowflake to someone new, zero-copy cloning gets the biggest reaction. You type one line. You get an instant copy of a table — or an entire database — with no data duplication, no storage cost at the moment of creation. It feels like magic.

And it is genuinely impressive engineering. I’m not here to tell you it’s a bad feature. It’s one of my favourite things about Snowflake and I use it constantly.

But I’ve watched teams get badly surprised by it. A dev environment clone that started silently inflating the storage bill. A cloned database used for UAT that bypassed data masking policies on PII columns. A Time Travel query on a clone that returned nothing because the source table’s retention window had already expired.

None of these are edge cases. They’re predictable consequences of how zero-copy cloning actually works — consequences that the marketing language around “instant, free copies” tends to obscure. Let me get into it.

TL;DR

• Zero-copy cloning is one of Snowflake’s best features — and one of the most misunderstood ones in production
• Clones share micropartitions with the source — any modification to either side starts writing new storage, and that cost adds up fast in ways that aren’t visible upfront
• Clones don’t inherit resource monitors, row-level security policies, or dynamic data masking by default — this is a compliance and governance trap waiting to happen
• Time Travel on clones behaves differently from what most people expect, especially when the source table has already moved past its retention window
• Clone sprawl is real — it’s invisible in the UI, expensive to audit, and teams rarely have a cleanup strategy until the bill arrives
• This article covers what zero-copy cloning actually does under the hood, where it silently fails you, and how to use it without it becoming a liability

---

HOW ZERO-COPY CLONING ACTUALLY WORKS

When you clone a table in Snowflake, you’re not copying data. You’re creating a new metadata pointer that references the same underlying micropartitions as the source object.

-- Instant. No data movement. No storage cost at this moment.
CREATE TABLE orders_clone CLONE orders;

-- Works for schemas too
CREATE SCHEMA dev_schema CLONE prod_schema;

-- And entire databases
CREATE DATABASE dev_db CLONE prod_db;

At the moment of creation, the clone costs you nothing in storage. Both the original and the clone point to the same micropartitions on disk. The moment either side changes, Snowflake uses copy-on-write. The modified micropartition gets written fresh for whichever side made the change.

Think of it like a fork in a Git repo. At fork time, both repos share the same commit history. The moment either side commits, they diverge. The more divergence, the more independent storage you accumulate. Zero-copy cloning works exactly like this — except the “commits” are DML operations and the cost is real money.

---

PROBLEM 1 — STORAGE COSTS THAT CREEP UP INVISIBLY

A team clones production to create a development environment. The dev team runs experiments, updates records, backfills some columns. Six weeks later, storage is up 40%. Every modified micropartition in the dev database is now independent storage. Production kept its micropartitions too. You’re paying for both.

The cost also compounds with Time Travel. If production has 90-day retention and you clone it for dev, that clone also starts with 90-day retention. DML operations in dev accumulate 90 days of write history.

-- Clone with reduced Time Travel for non-production environments
CREATE DATABASE dev_db CLONE prod_db;

-- Immediately reduce Time Travel on the clone
ALTER DATABASE dev_db SET DATA_RETENTION_TIME_IN_DAYS = 1;

-- Or set it at schema level for finer control
ALTER SCHEMA dev_db.analytics SET DATA_RETENTION_TIME_IN_DAYS = 0;

Audit clone storage footprint regularly:

-- Find clones and their storage footprint
SELECT
    table_catalog,
    table_schema,
    table_name,
    clone_group_id,
    bytes / (1024 * 1024 * 1024)        AS size_gb,
    row_count,
    created                              AS clone_created_at
FROM snowflake.account_usage.tables
WHERE clone_group_id IS NOT NULL
  AND deleted IS NULL
ORDER BY bytes DESC;

This same class of invisible cost creep comes up a lot with Snowflake features that look free until you read the bill — similar to what I covered in “Why I Stopped Using Snowflake Tasks for Orchestration“

---

PROBLEM 2 — GOVERNANCE POLICIES DON’T FOLLOW THE CLONE

Dynamic data masking policies are not automatically inherited by clones. The clone is a new object with no masking policies applied.

-- On prod table — email is masked for non-PII roles
SELECT email FROM prod_orders LIMIT 5;
-- Result: ***@***.com (masked)

-- On the clone without explicit policy assignment
SELECT email FROM orders_clone LIMIT 5;
-- Result: [email protected] (unmasked raw PII)

Same problem with row access policies. A user restricted to one region in production can see all regions on the clone.

The fix — make policy application part of your clone process:

-- Step 1: Clone the table
CREATE TABLE dev_db.analytics.orders CLONE prod_db.analytics.orders;

-- Step 2: Re-apply masking policies immediately
ALTER TABLE dev_db.analytics.orders
    MODIFY COLUMN email
    SET MASKING POLICY prod_db.security.email_mask;

ALTER TABLE dev_db.analytics.orders
    MODIFY COLUMN phone_number
    SET MASKING POLICY prod_db.security.phone_mask;

-- Step 3: Re-apply row access policy
ALTER TABLE dev_db.analytics.orders
    ADD ROW ACCESS POLICY prod_db.security.region_access_policy
    ON (region_code);

Better: wrap it in a stored procedure that enforces policy application as part of the clone operation:

CREATE OR REPLACE PROCEDURE create_governed_clone(
    source_table      VARCHAR,
    target_table      VARCHAR,
    masking_policies  ARRAY
)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
    var clone_stmt = snowflake.execute({
        sqlText: `CREATE TABLE ${TARGET_TABLE} CLONE ${SOURCE_TABLE}`
    });

    for (var i = 0; i < MASKING_POLICIES.length; i++) {
        var policy = MASKING_POLICIES[i];
        snowflake.execute({
            sqlText: `ALTER TABLE ${TARGET_TABLE}
                      MODIFY COLUMN ${policy.column}
                      SET MASKING POLICY ${policy.policy_name}`
        });
    }

    return 'Clone created with governance policies applied: ' + TARGET_TABLE;
$$;

A clone that exists without its governance policies re-applied is a compliance gap, not a convenience feature. If you’re running dbt on top of Snowflake, the same mindset applies — see “The Problem with dbt Tests Nobody Talks About“:

---

PROBLEM 3 — TIME TRAVEL ON CLONES ISN’T WHAT YOU THINK

A clone’s Time Travel history starts from its creation date. You cannot go back to a point before the clone was created on the clone object.

-- Source table: exists since 2024-01-01, 90-day retention
-- Clone created: 2024-03-01

-- This works — within the clone's own history
SELECT * FROM orders_clone
AT (TIMESTAMP => '2024-03-15 10:00:00'::TIMESTAMP_TZ);

-- This FAILS — before the clone existed
SELECT * FROM orders_clone
AT (TIMESTAMP => '2024-02-01 10:00:00'::TIMESTAMP_TZ);
-- Error: Statement time travel is not available for this object

-- For pre-clone history, query the SOURCE table
SELECT * FROM orders
AT (TIMESTAMP => '2024-02-01 10:00:00'::TIMESTAMP_TZ);

Also watch: if you clone a table that’s near the end of its retention window, any history that expires on the source is gone. The clone can’t access expired source history.

---

PROBLEM 4 — CLONE SPRAWL AND THE INVISIBLE COST PROBLEM

Zero-copy cloning is so easy that people create clones for everything — UAT, load testing, feature branches, one-off investigations that were supposed to be deleted on Friday. Three months later, nobody knows what exists or how diverged it’s become.

Full clone audit query:

SELECT
    t.table_catalog                                   AS database_name,
    t.table_schema                                    AS schema_name,
    t.table_name,
    t.clone_group_id,
    t.row_count,
    ROUND(t.bytes / POW(1024, 3), 3)                  AS size_gb,
    t.created                                         AS created_at,
    t.last_altered                                    AS last_modified_at,
    DATEDIFF('day', t.created, CURRENT_TIMESTAMP())   AS age_days,
    CASE
        WHEN DATEDIFF('day', t.last_altered, CURRENT_TIMESTAMP()) > 30
        THEN 'STALE — review for deletion'
        ELSE 'Active'
    END AS staleness_flag
FROM snowflake.account_usage.tables t
WHERE t.clone_group_id IS NOT NULL
  AND t.deleted IS NULL
ORDER BY t.bytes DESC;

Tag every clone at creation with expiry metadata:

CREATE DATABASE uat_db CLONE prod_db
    COMMENT = '{"purpose": "UAT for v2.4 release", "owner": "[email protected]", "expires": "2024-04-30", "ticket": "JIRA-1234"}';

-- Query clones past their expiry date
SELECT
    table_catalog,
    table_schema,
    table_name,
    TRY_PARSE_JSON(comment):expires::DATE AS expiry_date,
    TRY_PARSE_JSON(comment):owner::STRING AS owner
FROM snowflake.account_usage.tables
WHERE clone_group_id IS NOT NULL
  AND deleted IS NULL
  AND TRY_PARSE_JSON(comment):expires::DATE < CURRENT_DATE();

---

PROBLEM 5 — CLONING STREAMS AND TASKS DOESN’T WORK HOW YOU EXPECT

Streams are not cloned when you clone a table or schema. The clone contains the data but has no streams attached.

-- Prod table has a stream attached
SHOW STREAMS ON TABLE prod_db.analytics.orders;
-- Returns: orders_cdc_stream

-- Clone the table
CREATE TABLE dev_db.analytics.orders CLONE prod_db.analytics.orders;

-- Check streams on clone
SHOW STREAMS ON TABLE dev_db.analytics.orders;
-- Returns: (empty)

If you need CDC streams on cloned tables, create them explicitly after cloning:

CREATE OR REPLACE STREAM dev_db.analytics.orders_cdc_stream
    ON TABLE dev_db.analytics.orders
    APPEND_ONLY = FALSE
    SHOW_INITIAL_ROWS = FALSE;

CREATE OR REPLACE TASK dev_db.analytics.process_orders_changes
    WAREHOUSE = dev_wh
    SCHEDULE = '5 minute'
    WHEN SYSTEM$STREAM_HAS_DATA('dev_db.analytics.orders_cdc_stream')
AS
    CALL dev_db.analytics.process_orders_sp();

Tasks are cloned but start in a SUSPENDED state — they don’t auto-resume, which is correct behaviour (you don’t want dev tasks firing against prod targets), but it surprises teams expecting a live pipeline copy. If your pipeline relies on dbt incremental models consuming from those streams, the failure compounds further — see “The Problem with Incremental Models in dbt Nobody Talks About“

---

WHEN ZERO-COPY CLONING IS THE RIGHT TOOL

Before risky migrations — clone first, get an instant rollback point:

-- Before a risky migration
CREATE TABLE orders_pre_migration CLONE orders;

-- Run your migration
ALTER TABLE orders ADD COLUMN new_column VARCHAR;
UPDATE orders SET new_column = derive_value(existing_column);

-- If something went wrong:
-- DROP TABLE orders;
-- ALTER TABLE orders_pre_migration RENAME TO orders;

Instant dev environments, UAT cycles, zero-downtime data fixes — all excellent use cases. The feature is great. Using it without understanding the lifecycle is where teams get into trouble. If you want to go further on cost reduction for dev workloads, pairing clone strategy with DuckDB is worth exploring — “How to Query Snowflake in DuckDB and Cut Your Bill While Doing It“

---

FREQUENTLY ASKED QUESTIONS

Q: Does zero-copy cloning in Snowflake really cost nothing?
A: At creation: yes. The cost begins the moment either side is modified via copy-on-write. In active dev environments that are modified frequently, storage costs can grow significantly over weeks. Time Travel retention on the clone compounds this further.

Q: Do data masking policies transfer when you clone a table?
A: No. Masking policies are not inherited by clones. Sensitive columns are exposed in plaintext on the clone unless you explicitly re-apply policies after creation. Treat clone creation and policy application as a single atomic operation.

Q: Can I use Time Travel on a clone to go back before it was created?
A: No. A clone’s Time Travel history starts at its creation date. For history before the clone was created, query the source table directly.

Q: Are Snowflake Streams copied when you clone a table?
A: No. Streams are not part of the clone operation. Create them explicitly on the clone if your pipeline depends on CDC. Tasks are cloned but start suspended.

Q: How do I audit all clones in my Snowflake account?
A: Query snowflake.account_usage.tables filtering on clone_group_id IS NOT NULL. Tag clones at creation with JSON metadata in the COMMENT field — owner, expiry, purpose — to make audits actionable.

Q: What’s the best practice for cloning production for dev?
A: Clone, then immediately: reduce Time Travel retention to 0 or 1 day, re-apply all masking and row access policies, set a resource monitor on dev warehouses, and tag the clone with an expiry date in the COMMENT field.

---

Related blogs

→ Snowflake official docs — cloning objects
→ Snowflake dynamic data masking docs
→ Snowflake Time Travel docs
→ Snowflake resource monitors docs
→ Snowflake Streams