skip to content

What is a Snowflake micro-partition, and why does its immutability matter?

level: middleimportance: must knowfreq 78%

answer

  1. files, not editable row storage
  2. written once, never modified in place
  3. one row changed, whole file rewritten
  4. old versions kept for Time Travel
  5. rewrites scramble the value ranges

basics

~20 s

A micro-partition is an immutable, compressed, columnar file holding a contiguous group of a Snowflake table's rows, created automatically with per-column min/max metadata. Because it is immutable, any DML rewrites whole micro-partitions rather than editing rows in place.

solid answer

~50 s

Snowflake stores each table as many **micro-partitions**: immutable files of roughly 50–500 MB of uncompressed data, written columnar and compressed per column, created automatically as rows arrive — there is no DDL to declare or size them. For each one Snowflake records per-column metadata (min/max values, distinct counts, null counts) used for pruning. Immutability is the load-bearing property. An `UPDATE` or `DELETE` cannot edit a row inside a file; Snowflake writes new micro-partitions containing the surviving and changed rows and stops referencing the old ones, which are retained for Time Travel and Fail-safe. Three consequences follow: touching a handful of rows can rewrite hundreds of megabytes, storage grows for the retention window even when row counts do not, and each rewrite drops rows into new files whose value ranges may overlap the old ones — which is how a well-organized table gradually loses its pruning quality.

code

sql · 4 lines
sql
-- 40 rows change; every micro-partition holding one of them is rewritten in full
update events
set status = 'VOID'
where event_id in (select event_id from voided_events);

go deeper

for a junior

Know the definition: automatically created, immutable, compressed, columnar files of roughly 50–500 MB uncompressed, each with per-column min/max metadata. Nothing about them is declared by you.

for a middle

Explain the rewrite model — DML produces new micro-partitions and supersedes old ones — and derive the three consequences: write amplification, storage retained for Time Travel, and drifting value ranges.

for a senior

Turn it into operational advice: batch DML into set-based MERGEs, prefer appends over scattered updates, and expect a churn-heavy table to need reclustering while an append-only one may not. Diagnose slow small updates by this mechanism.

for a principal

Own the workload-fit argument: an immutable-file store makes Snowflake excellent for append-and-scan analytics and a bad home for row-level mutation, which shapes where transactional state lives in the platform.

## Anatomy A Snowflake table is not one file and it is not a set of user-declared partitions. It is a collection of **micro-partitions**. Each micro-partition is: - **A contiguous group of rows** — the rows that happened to be written together, in arrival order. - **Columnar inside** — the group's values are reorganised column by column, so a query touching three of eighty columns reads only three columns' worth of bytes. - **Compressed** — Snowflake chooses the compression per column, based on the actual data. - **Sized automatically** — documented as roughly 50 MB to 500 MB of uncompressed data; the object stored is smaller after compression. You cannot set this, and there is no `PARTITION BY` clause in Snowflake's `CREATE TABLE`. - **Immutable** — once written, the file never changes. - **Described by metadata** — per column, the min and max values, distinct-value count, null count and other properties, held in the cloud services layer, which is what makes pruning possible without reading storage. Because creation is automatic and follows arrival order, a table loaded daily is *naturally* organised by load time. That accidental ordering is often the only reason date filters prune well. ## Why immutability is the interesting part Snowflake's storage sits on cloud object storage, where objects are written once and replaced rather than edited. Rather than fight that, Snowflake's whole DML model is built on it. When you run `UPDATE events SET status = 'VOID' WHERE event_id = 918273`, Snowflake locates the micro-partition holding that row, and writes a **new** micro-partition containing that file's rows with the change applied. The table's metadata now points at the new file; the old file is no longer part of the current table version but is retained so Time Travel can reconstruct earlier versions, and afterwards Fail-safe holds it briefly for disaster recovery. `DELETE` works the same way — the surviving rows are rewritten without the deleted ones. ### Consequence 1: write amplification A one-row update can rewrite an entire micro-partition. A thousand single-row updates scattered across a large table can rewrite a thousand micro-partitions — potentially hundreds of gigabytes of I/O and warehouse time for a few kilobytes of logical change. This is why Snowflake work is written as **set-based batch DML** — one `MERGE` over a staged batch — and not as row-at-a-time updates driven from an application loop. It is also why Snowflake is a poor fit for OLTP-style point updates. ### Consequence 2: storage is not row count Rewritten micro-partitions do not vanish. They are kept for the table's `DATA_RETENTION_TIME_IN_DAYS` (Time Travel) and then for Fail-safe. A table whose rows are heavily churned can occupy several times the storage its live rows suggest, and the bill reflects that. When someone asks why storage grew after a big backfill that changed no row counts, this is the answer. ### Consequence 3: organization decays This is the consequence that matters most for query performance. Suppose a table's micro-partitions each cover a narrow range of `event_date` — perfect for pruning. Now a nightly job updates a status flag on rows scattered across two years. Each rewritten file contains rows from wherever those updates landed, so the new micro-partitions cover wide `event_date` ranges. Their ranges now overlap each other and overlap the untouched files. A date filter that used to exclude 99% of the table now overlaps far more files, and the query gets slower without anyone changing the query. The same happens with loads. Micro-partitions written by a backfill that arrives out of order, or by many small trickle loads, do not line up with the table's existing organization. That decay is exactly what a declared clustering key plus Snowflake's Automatic Clustering service exists to counteract: the service rewrites overlapping micro-partitions in the background so the table's files go back to covering narrow, non-overlapping key ranges. ## What to take into design Batch your DML rather than trickling it. Prefer inserting a new day's data over updating old days. Expect a heavily-updated table to need reclustering (and to cost reclustering credits) while an append-only one may need none. And when you are asked why a small `UPDATE` took four minutes on a Large warehouse, reach for immutability first: the engine did not change 40 rows, it rewrote every micro-partition those 40 rows lived in.

  • Why does a Snowflake table's storage bill grow after an UPDATE that changes no row count?
    Because the rewritten micro-partitions are additive. The new files hold the current version while the superseded ones are retained for the table's Time Travel retention and then briefly for Fail-safe. Until those windows expire you pay for both copies, so heavy churn can inflate storage well beyond what the live row count implies.
  • How does a single MERGE compare with a thousand individual UPDATE statements on the same rows?
    Dramatically better. Each statement rewrites every micro-partition it touches, so a thousand statements can rewrite the same files a thousand times over. One set-based `MERGE` against a staged batch computes all changes and rewrites each affected micro-partition once, cutting both warehouse time and the volume of superseded files retained for Time Travel.
  • Does the columnar layout inside a micro-partition mean an UPDATE rewrites only the changed column?
    No. The unit of rewrite is the whole micro-partition, not one column's blocks within it. Columnar layout is a read-side benefit — a query fetches only the columns it names — but on the write side the file is replaced in full, which is where write amplification comes from.

Micro-partitions behave like printed pages in a bound ledger: to change one line you reprint the whole page and file the old one in the archive. Cheap for appending pages, expensive for editing scattered lines.

saying these in an interview costs you the question

  • Says rows are modified in place inside the micro-partition
  • Thinks you choose micro-partition size or count in DDL
  • Claims only the changed column's blocks are rewritten
  • Assumes deleted rows free storage immediately
  • Ignores that DML degrades a table's pruning quality

context

resolving links…