Tag: snowflake

A team I worked with ran a nightly job that did something completely reasonable: it updated a single status flag on rows in a 2 TB orders table. One column. A few million rows a night. Harmless. Three months later their storage bill had nearly quadrupled, and no one had loaded any new data. The culprit wasn’t the update itself — it was a fact about Snowflake’s storage engine that almost nobody internalizes until it bites them: you cannot change a row in place. That one-column update was silently rewriting entire micro-partitions and stockpiling the old versions in Time Travel and Fail-safe, and they were paying to store every generation.

There’s a popular assumption that Snowflake just stores compressed Parquet files behind the scenes. It doesn’t. And the real design isn’t trivia — it’s the thing that explains why your queries prune well or scan everything, why a tiny update can be expensive, and why your storage bill has line items you never created directly. This is the storage layer, specifically — not the “cloud services” box everyone waves at in architecture diagrams. If you want the compute-and-query side, I’ve covered what really happens when you run a query separately; this article is about what’s actually sitting on disk, and why it dictates so much of your day.

TL;DR

• → Snowflake does not store Parquet — it stores its own proprietary columnar format (widely called FDN) in files called micro-partitions on cloud object storage.
• → Each micro-partition holds 50–500 MB of uncompressed data, stored column-by-column and compressed per column, with a metadata header of per-column min/max, distinct counts, and null counts.
• → That metadata — not the data — lives in FoundationDB and drives pruning: the optimizer skips whole micro-partitions by reading statistics, never touching the files.
• → Micro-partitions are immutable; every UPDATE, DELETE, or MERGE rewrites whole partitions rather than editing rows in place.
• → Immutability is why Time Travel, zero-copy cloning, and Fail-safe exist — and why churny small DML quietly inflates storage.
• → Clustering (natural by load order, or a defined key) controls how well pruning works; you can measure it with SYSTEM$CLUSTERING_INFORMATION.
• → You don’t tune Snowflake storage directly — you influence it by how you load, update, and cluster data.

The three layers, and why storage is the interesting one

Snowflake separates into three layers: cloud services (the brain — metadata, security, the optimizer), compute (virtual warehouses that run queries), and storage (the data itself). The famous selling point is that compute and storage are decoupled, which is why you can resize a warehouse without touching data and why the warehouse cache behaves the way it does. But the layer that quietly determines your costs and query speed is the bottom one — and it’s the one people understand the least.

What’s actually on disk: micro-partitions, not Parquet

When you load data into a table, Snowflake automatically slices it into micro-partitions. Per Snowflake’s own documentation, each one holds between 50 MB and 500 MB of uncompressed data (smaller on disk, since it’s always compressed), and the rows in it are stored in a columnar layout — each column laid out and compressed independently, with Snowflake picking the best compression scheme per column. A large table isn’t a handful of big partitions; it can be millions of these small, uniform files.

The file format is proprietary and closed — commonly referred to as FDN (“Flocon de Neige,” French for snowflake). It is emphatically not Parquet, even though both are columnar and compressed. Why build a custom format instead of reusing an open one? Because the format is co-designed with the metadata and pruning system, and that tight coupling is where Snowflake’s speed comes from. The anatomy diagram above is the mental model to hold: a header of statistics, then column chunks.

The metadata is the magic (and it lives in FoundationDB)

Here’s the part that reframes everything. For every micro-partition, Snowflake records metadata — the range of values (min and max) for each column, the number of distinct values, null counts, and more. That metadata is not stored in the file with the data; it lives in Snowflake’s metadata store, which Snowflake has publicly described as being built on FoundationDB, a distributed key-value store. The logical table you query is really a set of pointers, held in that metadata store, mapping to the physical FDN files in object storage.

This separation is what makes pruning possible. When you filter on a column, the optimizer consults the min/max metadata for each micro-partition and skips any whose range can’t possibly contain a match — without reading the file at all.

Pruning reads statistics, not data. Only the partition whose min/max range straddles July 4th is opened; the other four are eliminated before any I/O.

This is also why two SQL habits quietly kill performance. Wrapping a filter column in a function — WHERE DATE(order_ts) = '2026-07-04' — means the min/max stats on the raw order_ts column can’t be used, so pruning is defeated and every partition gets scanned. And SELECT * forces every column chunk to be read even though the columnar layout was designed to let Snowflake read only the columns you asked for. The half-open range order_ts >= '2026-07-04' AND order_ts < '2026-07-05' on the bare column, selecting only needed columns, is what lets both optimizations fire.

Immutability changes how you think about DML

Micro-partitions are immutable. Snowflake never edits a row in place — a truth that has bigger consequences than it first appears. When you run an UPDATE, DELETE, or MERGE, Snowflake reads the affected micro-partitions, produces new micro-partitions with the changes applied, and re-points the table’s metadata at the new files. The old files don’t vanish; they’re retained for Time Travel, then Fail-safe.

That’s the mechanism behind the quadrupled bill from the intro. A one-column update touching rows spread across thousands of micro-partitions rewrites all of those partitions in full — and keeps the previous versions around for the retention window. This same copy-on-write design is exactly why Time Travel isn’t really a backup feature — it’s just versioned pointers to immutable partitions you already paid to store — and why zero-copy clones are nearly free at creation: a clone is a new set of pointers to the same existing files, and it only costs storage as the two copies diverge and new partitions get written.

Seeing it for yourself

None of this has to be taken on faith. You can inspect the physical reality directly. To see how well a table is clustered — how much its micro-partitions overlap on a column, which is what determines pruning quality — use the built-in function:

SELECT SYSTEM$CLUSTERING_INFORMATION('sales', '(order_date)');

{
  "cluster_by_keys" : "LINEAR(order_date)",
  "total_partition_count" : 12483,
  "average_overlaps" : 3.11,
  "average_depth" : 2.94,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 4821,
    "00002" : 5102,
    "00004" : 2560
  }
}

A low average_depth means a filter on order_date hits few overlapping partitions — good pruning. A high depth means the values are smeared across many partitions and queries scan more than they should. To see the storage consequences of immutability, query the account usage view that breaks storage into its real components:

SELECT
    table_name,
    active_bytes      / POW(1024, 3) AS active_gb,
    time_travel_bytes / POW(1024, 3) AS time_travel_gb,
    failsafe_bytes    / POW(1024, 3) AS failsafe_gb
FROM snowflake.account_usage.table_storage_metrics
WHERE table_name = 'ORDERS'
ORDER BY active_bytes DESC;

+------------+-----------+----------------+-------------+
| TABLE_NAME | ACTIVE_GB | TIME_TRAVEL_GB | FAILSAFE_GB |
+------------+-----------+----------------+-------------+
| ORDERS     |    198.4  |         912.7  |      301.5  |
+------------+-----------+----------------+-------------+

That’s the intro’s bug, made visible: 198 GB of live data, but ~1.2 TB of retained old versions you’re billed for, generated by churny updates rewriting partitions night after night.

Clustering: natural, defined, and not free

By default, data is naturally clustered by the order it was loaded — load July’s data in date order and date-filtered queries prune beautifully; load it shuffled and they don’t. For large tables with a consistent filter pattern, you can define a clustering key and Snowflake’s Automatic Clustering service will reorganize micro-partitions in the background to keep them well-sorted. It genuinely helps pruning, and it’s central to keeping interactive dashboards fast.

But reclustering rewrites micro-partitions (immutability again), which consumes credits and generates yet more retained versions. Clustering keys earn their keep only on large tables that are frequently filtered on the key. Adding one to a small or write-heavy table often costs more than it saves — this is the same “don’t pay to reprocess what didn’t change” discipline behind dbt state-based selection.

The cost math

Put rough numbers on the immutability tax. Say you have a 200 GB table and a job that rewrites 20% of its partitions daily — a modest MERGE. With a 7-day Time Travel window, you’re retaining roughly seven days of old versions of that churned 20%: on the order of 200 GB of Time Travel bytes on top of the 200 GB active, plus a further ~7 days of Fail-safe Snowflake keeps regardless. You can easily end up billed for 3–4x your live data size. The fix isn’t to stop updating — it’s to batch changes so you rewrite partitions once instead of trickling small updates, to right-size Time Travel retention per table, and to be deliberate about which tables actually need churn. Storage is cheap per GB, but “cheap × 4 × forever” is a real line on the invoice.

FDN vs open formats

The proprietary FDN format is what gives Snowflake its pruning speed and features — but it also means your data is locked inside Snowflake’s engine. That trade-off is exactly what the industry’s shift toward open table formats is reacting to: Iceberg tables let you keep data as open Parquet on your own object storage, readable by other engines, at some cost in Snowflake-native performance and features. If you’re weighing it, I’ve written on the native-to-Iceberg migration trap and when it’s actually worth migrating. The short version: FDN for Snowflake-first workloads, Iceberg when open access matters more than raw speed.

The gotchas nobody warns you about

Small, frequent updates are a storage trap. Single-row updates to a big table rewrite whole micro-partitions and pile up retained versions. Batch DML; don’t trickle it.

Functions on filter columns silently disable pruning. DATE(ts) = ..., UPPER(name) = ..., or a cast on the filtered column all prevent Snowflake from using the raw column’s min/max stats. Filter on the bare column with ranges.

Load order is a performance decision. Because natural clustering follows insertion order, loading data shuffled destroys pruning for range queries. Sort on load, or accept the cost of a clustering key.

DELETE doesn’t free storage immediately. Deleted rows live on as old partition versions through Time Travel and Fail-safe. If you need space back fast, that retention window matters.

Overlapping ranges beat pruning even with a clustering key. A high average_depth from SYSTEM$CLUSTERING_INFORMATION means your “clustered” table still scans widely. Measure it; don’t assume the key is working.

The one principle

Snowflake doesn’t store rows — it stores immutable, self-describing micro-partitions, and every cost and speed characteristic you experience is a downstream consequence of that one fact. Pruning is the metadata header doing its job. Expensive updates are immutability doing its job. Time Travel, cloning, and your storage bill are all the same design viewed from different angles. Once you see storage as immutable statistics-wrapped column chunks, Snowflake stops being magic and starts being predictable — which is exactly when you can make it cheap and fast.

Related reading: What really happens when you run a query · Why Time Travel isn’t a backup · Zero-copy clone storage costs · FDN vs Iceberg: the migration trap · Snowflake micro-partitions docs · FoundationDB powers Snowflake metadata

The Snowflake bill for our CI account had roughly tripled over a quarter, and nobody could point to why. We hadn’t loaded meaningfully more data. Compute was flat. But storage kept climbing, month over month, in an account whose entire job was to spin up throwaway test environments and tear them down. Throwaway. Torn down. And yet the storage line kept going up and to the right.

The culprit was the feature I’d been recommending to everyone as “basically free”: zero-copy clones. Our CI pipeline cloned production on every pull request, ran migrations and tests against the clone, and dropped it at the end. Textbook. The problem is that “zero-copy” describes the moment of creation and nothing after it, and “drop” doesn’t mean what you think it means when clones are involved. We were paying for storage we believed we’d deleted weeks ago.

This is the guide to why that happens, how to find it in your own account, and how to stop it. If you run clone-based CI/CD at any scale, some version of this is almost certainly happening to you right now.

TL;DR

→ Zero-copy clones are free at creation — they share the source’s micro-partitions through metadata pointers. They are not free after anything writes. Every INSERT/UPDATE/DELETE on either side writes new micro-partitions that are billed.

→ In CI/CD the divergence is your migrations. Clone prod, run a schema migration or a backfill against the clone, and you’ve just created new micro-partitions that cost real storage — every pull request, every pipeline run.

→ Dropping the clone does not immediately free that storage. Dropped tables enter Time Travel, then Fail-safe (up to 1 day + 7 days on permanent tables) before the bytes are physically removed. Fast CI loops drop clones constantly and stack up a rolling backlog of retained bytes.

→ The nasty one: clone-group ownership transfer. Storage for shared micro-partitions is owned by the oldest table in the clone group. Drop the source and its still-shared partitions don’t vanish — ownership transfers to a surviving clone. You can delete “the original” and watch storage not move.

→ Diagnose with SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS: ACTIVE_BYTES, TIME_TRAVEL_BYTES, FAILSAFE_BYTES, and the key one, RETAINED_FOR_CLONE_BYTES — bytes kept alive only because a clone still references them.

→ Fixes: clone with transient tables/databases for CI (no Fail-safe, minimal Time Travel), set Time Travel to 0 days on CI objects, actually DROP at pipeline end even on failure, and don’t run heavy migrations against the clone if a lighter check will do.

Why “zero-copy” is a half-truth

Snowflake stores table data in immutable micro-partitions — compressed columnar files, tens to hundreds of MB each. Once written, a micro-partition is never modified. When you clone a table, Snowflake doesn’t copy those files. It writes a new metadata entry pointing at the same set of micro-partitions. That’s why a clone of a 5 TB table is instant and costs nothing extra at that instant. It’s a hard link at the partition level, not a copy.

The word “zero-copy” describes exactly that instant and no other. Because micro-partitions are immutable, the moment you change a row — in the clone or the original — Snowflake can’t edit the shared partition in place. It writes a new micro-partition containing the change, and that new partition is owned exclusively by whichever side made the change. The unchanged partitions stay shared. So your storage cost isn’t the size of the clone; it’s the size of the divergence between the clone and its source.

The correct mental model, which took me an embarrassingly large bill to internalize: a clone is not a free copy, it’s an instant branch that gets more expensive as it diverges. Read-only clone of 1 TB? Costs nothing. Clone you fully rewrite? Costs a second 1 TB. Real CI workloads land in between — and “in between,” multiplied by every pull request, is a budget line.

Where the cost actually enters in CI/CD

The clone is free at step 1. Your migration at step 2 is what creates billed storage — and step 4’s drop doesn’t reclaim it right away.

Here’s the standard CI pattern, the one in every tutorial:

CREATE DATABASE ci_test_${BUILD_ID} CLONE production_db;
-- run migrations against the clone
-- run integration tests
DROP DATABASE IF EXISTS ci_test_${BUILD_ID};

Step one is genuinely free. The cost enters at “run migrations.” A migration that adds a column, backfills a value, rebuilds a table, or runs a dbt model against the clone writes new micro-partitions for every affected partition. If your migration touches 10% of a 500 GB table, you just materialized ~50 GB of new storage — for one CI run. Run that pipeline 40 times a day across a team and the daily divergence is measured in terabytes of writes, even though each individual run “only” changed a slice.

None of that is visible while you’re looking at it, because the clone gets dropped at the end and the environment looks clean. Which brings us to the part that actually generates the surprise bill.

The two things that keep paying after you “delete”

1. Dropping a table doesn’t free its bytes immediately. When you DROP a permanent table (or database), it doesn’t evaporate — it goes into Time Travel for its retention period (default 1 day, and up to 90), and then into Fail-safe for a further 7 days, during which only Snowflake can recover it. Throughout both windows you’re billed for those bytes. A CI loop that creates and drops clones dozens of times a day is continuously feeding a rolling backlog: at any given moment you’re paying for the Time-Travel-and-Fail-safe tail of every clone dropped in roughly the last week, not just the ones alive right now.

2. Clone-group ownership transfer — the one that breaks intuition. Every table in a clone group has an independent lifecycle, but the storage for shared micro-partitions is owned by the oldest table in the group. Here’s the trap: you decide the source table is the problem and drop it. You expect storage to fall. It doesn’t. Because a clone still references those shared partitions, Snowflake can’t release them — so when they’d otherwise exit Time Travel, ownership transfers to a surviving clone instead. You deleted the original and the bytes simply changed owner. This is why teams stare at a dropped production backup table and can’t understand why the account storage didn’t budge.

Finding it in your own account

Four columns tell the whole story. RETAINED_FOR_CLONE_BYTES is the one that reveals storage kept alive purely because a clone still references it.

The view you want is SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS. It breaks every table’s footprint into the buckets that matter:

SELECT
  table_catalog,
  table_schema,
  table_name,
  active_bytes / POW(1024,3) AS active_gb,
  time_travel_bytes / POW(1024,3) AS time_travel_gb,
  failsafe_bytes / POW(1024,3) AS failsafe_gb,
  retained_for_clone_bytes / POW(1024,3) AS clone_retained_gb
FROM snowflake.account_usage.table_storage_metrics
WHERE retained_for_clone_bytes > 0
ORDER BY retained_for_clone_bytes DESC;

ACTIVE_BYTES is your live data — the part you expect to pay for. TIME_TRAVEL_BYTES and FAILSAFE_BYTES are the recovery tails. RETAINED_FOR_CLONE_BYTES is the smoking gun: bytes that are only still on disk because some clone in the group references them. If that column is large on tables you thought were long gone, you’ve found your leak.

To hunt CI clones specifically, filter by naming convention and age. Because Snowflake records clone lineage, you can surface old clones still retaining significant storage:

SELECT
  table_catalog,
  table_name,
  clone_group_id,
  retained_for_clone_bytes / POW(1024,3) AS clone_retained_gb,
  table_created
FROM snowflake.account_usage.table_storage_metrics
WHERE table_catalog ILIKE 'CI_TEST_%'
  AND retained_for_clone_bytes > 0
ORDER BY clone_retained_gb DESC;

One caveat worth knowing: ACCOUNT_USAGE views have latency (often a couple of hours), so don’t expect a drop you ran five minutes ago to show up instantly. Debug against yesterday’s picture, not this second’s.

The cost math, concretely

Say production is 2 TB and your CI migration reliably rewrites ~8% of it per run: ~160 GB of new micro-partitions per pipeline. The clone is dropped at the end, so those 160 GB immediately become Time Travel + Fail-safe bytes rather than active bytes — and they linger for the retention tail. With a 1-day Time Travel plus 7-day Fail-safe window on permanent objects, each run’s divergence sticks around for roughly 8 days before it’s physically purged.

Run the pipeline 30 times a day and, in steady state, you’re carrying roughly 30 runs/day × 8 days × 160 GB ≈ 38 TB of retained bytes that you believe you deleted. At standard on-demand storage rates that’s a four-figure monthly line for data that exists only because “drop” isn’t “delete” and permanent tables carry a Fail-safe tail. The exact number depends on your migration’s write volume and your retention settings — but the shape is always the same, and it’s always bigger than teams expect.

The fixes, in priority order

Clone into transient objects for CI. This is the single biggest lever. Transient tables and databases have no Fail-safe period and a Time Travel retention of 0 or 1 day. Clone production into a transient database for CI, and when you drop it there’s no 7-day Fail-safe tail — the bytes are reclaimable almost immediately. CREATE TRANSIENT DATABASE ci_test_${BUILD_ID} CLONE production_db; Note the source’s own storage behavior is unchanged; this only governs the CI-side lifecycle, which is exactly the part generating your backlog.

Set Time Travel to zero on CI objects. If you can’t use transient objects for some reason, at least set DATA_RETENTION_TIME_IN_DAYS = 0 on the CI database so dropped clones don’t linger in Time Travel. Combined with the above, your CI divergence becomes genuinely short-lived.

Actually drop, even on failure. The tutorial pattern drops the clone at the end — but if tests fail and the pipeline exits early, the DROP may never run. Orphaned clones from failed builds are a classic source of retained storage. Put the DROP in a finally/always block so it runs regardless of test outcome, and add a scheduled sweeper task that drops any CI_TEST_% database older than a few hours as a backstop.

Diverge less. Ask whether your CI actually needs to rewrite 8% of a 2 TB table. Often the migration under test only needs to run against a representative subset, or the test only needs schema validation, not a full data backfill. Cloning gives you production-realistic structure for free; you don’t always need to exercise it against production-scale writes.

Mind the ownership trap when cleaning up. If you’re deleting old backup clones to reclaim space, remember that dropping the oldest member of a clone group transfers ownership rather than freeing bytes. To actually reclaim storage from a clone group, you generally need to drop all members that reference the shared partitions and let the retention windows expire. Deleting one and expecting the bill to fall is how the confusion starts.

The gotchas nobody warns you about

Grants diverge at clone time. A clone inherits grants and masking policies from the source at the instant of cloning, then becomes independent. For CI this is usually fine, but if your pipeline relies on grants applied to production after the clone was taken, they won’t be there.

Small-file defragmentation writes Time Travel bytes too. Even plain INSERT/COPY/Snowpipe loads can generate Time Travel and Fail-safe bytes, because Snowflake periodically compacts small micro-partitions — deleting the small ones (which enter the recovery tail) and writing a consolidated one. So retained bytes aren’t exclusively a clone phenomenon; clones just amplify it.

External tables, stages, and pipes don’t clone. If your CI environment depends on them, cloning the database won’t bring them along — you’ll need to recreate them in the clone.

ACCOUNT_USAGE latency hides fast loops. Because the storage views lag by up to a few hours, a tight CI loop can be generating and “hiding” retained storage faster than your dashboards refresh. Trust the trend over days, not the instantaneous number.

The one principle

Zero-copy cloning is free to create and expensive to diverge — and “drop” is not “delete.” In CI/CD, the storage you pay for is the write volume of your migrations times the retention tail of your dropped clones. Clone into transient objects, keep Time Travel short, drop reliably, and diverge only as much as the test actually requires. The feature isn’t lying to you; it’s just describing creation, not the whole lifecycle. Manage the lifecycle and the “free” clone stays close to free.

Related reading: Snowflake data storage considerations (clone groups & CDP) · TABLE_STORAGE_METRICS view reference · dbt State on Snowflake: Skip Unchanged Models · Snowflake Query Execution: What Really Happens · Snowflake Iceberg v3: When to Migrate