Micro-partitions
A micro-partition is Snowflake's unit of storage: an immutable, compressed, columnar file holding between roughly 50 MB and 500 MB of uncompressed data (stored compressed; about 16 MB is a commonly quoted figure, not a documented one). Every table is automatically divided into micro-partitions in the order data arrives. You never create, name, or manage them, but understanding them explains pruning, clustering, cloning, Time Travel, and half the performance domain.
Why it exists
Traditional warehouses forced admins to choose static partitioning schemes (by month, by region) up front. Choose wrong and you get skew: one giant partition doing all the work while others sit empty, plus expensive repartitioning later. Snowflake's answer removes the decision entirely: partitions are created automatically, they're small enough that skew is negligible, and metadata about every one of them lets the optimizer skip the irrelevant ones at query time.
The size range is itself a design decision worth understanding. Smaller partitions mean finer pruning (skip more, scan less) but more metadata to track and more files to open. Larger partitions amortize overhead but drag more irrelevant rows into every scan. Fifty to five hundred megabytes uncompressed is Snowflake's equilibrium: small enough that a selective filter skips almost everything, large enough that a multi-terabyte table stays at hundreds of thousands of partitions rather than hundreds of millions, a count cloud services can hold statistics on and prune through in milliseconds at compile time.
Internal architecture
Inside each micro-partition, data is stored by column: all values of ORDER_DATE together, all values of REGION together, each column compressed with the algorithm best suited to its type and content. On top of this, the cloud services layer keeps metadata per micro-partition:
- The range (min and max) of values for every column
- The number of distinct values per column
- NULL counts and other properties used by the optimizer
- These are statistics about the data in each partition. Table-level settings, such as a clustering key (Part 2), aren't stored per partition.
Step by step: how pruning works
- You run
SELECT SUM(amount) FROM sales WHERE order_date = '2026-06-15'. - Cloud services checks the min/max of
order_datein every micro-partition's metadata. A partition whose range is2026-01-02 → 2026-01-09cannot contain the target date, so it's eliminated without being read. This is partition pruning. - Within the surviving partitions, only the
order_dateandamountcolumns are read. Every other column is skipped. This is column pruning. - The warehouse scans what remains, which might be 0.1% of the table.
Overlap: how pruning degrades
Pruning quality is really a question about overlap. If data arrives in date order, each partition covers a tight date range and the ranges barely intersect: a filter on one date touches one or two partitions, exactly as in the schema above. Now imagine two years of UPDATEs scattering rows, or loads arriving from twelve source systems in no particular order. Partition date ranges widen and start to overlap, until any given date might plausibly live in forty partitions and the optimizer must scan all forty. Nothing broke; the min/max metadata just stopped excluding things. Part 2 gives this a number (clustering depth) and a fix (clustering keys), but the mechanism is worth having now: pruning works exactly as well as your data's physical order agrees with your queries' filters.
Immutability, and why it powers everything else
Micro-partitions are never edited in place. An UPDATE to one row rewrites the entire micro-partition containing it: the old file stays (marked as a previous version), a new file is written with the change. This copy-on-write behavior is the single mechanism behind three flagship features: Time Travel, zero-copy cloning and Fail-safe.
Walk through what each DML statement actually does at the file level, because "immutable" is meaningless until you see it applied.
- INSERT is the easy case: new rows land in brand-new micro-partitions appended to the table. Nothing existing is touched, so INSERT is cheap regardless of table size, and it's why chronological load order is what makes natural clustering happen for free.
- DELETE never erases a row inside a file. Snowflake finds every micro-partition containing at least one row to delete, writes new micro-partitions with those rows excluded, and marks the old micro-partitions as no-longer-current for regular queries. The old files stay on disk, invisible to normal SELECTs, but still addressable by Time Travel. Delete one row out of 200 million in a partition, and the entire partition is rewritten to produce that new version.
- UPDATE is a DELETE plus an INSERT under the hood: the micro-partitions holding the old row versions are replaced by new ones with the changed values, exactly like DELETE's rewrite, then the new state is written. There's no in-place byte edit anywhere in Snowflake, ever.
BEFORE · UPDATE … WHERE id = 42
TABLE METADATASALEScurrent version
MP 3004current
MP 3005 v1current
MP 3006current
row id = 42 lives in MP 3005
AFTER · STATEMENT COMMITTED
TABLE METADATASALEScurrent version
MP 3004reused
MP 3005 v2new file
MP 3006reused
MP 3005 v1retired
not current any more: kept for Time Travel, then Fail-safe
The three pictures share one rule: files are created and retired, never edited. Retired versions are what Time Travel queries, what a clone keeps pointing at, and what Fail-safe holds for its seven days. One asymmetry to memorize now: the Time Travel window is adjustable (0 to 90 days depending on edition), while Fail-safe is fixed at 7 days for permanent tables and cannot be changed by anyone.
Each of the three features is that same rule, seen from a different angle:
- Time Travel is just "keep pointing at the old partitions for N days."
- Zero-copy cloning is just "a new table whose metadata points at the same partitions." A clone of a 100 TB table is instant and costs nothing until either side changes, because changes create new partitions while unchanged ones stay shared.
- Fail-safe is just "Snowflake keeps the old partitions 7 more days after Time Travel ends."
The consequence for production tables: an UPDATE or DELETE that touches rows scattered across every micro-partition rewrites the entire table, even if the logical change was tiny. A single-row UPDATE always rewrites one file, but on a table where the filter column has no relation to the physical layout, finding that row means scanning every partition. And an UPDATE whose filter matches rows spread across every partition rewrites all of them, even when the logical change is small. This is why high-churn OLTP-style row-by-row updates are the one workload Snowflake actively discourages: batch your DML.
Real production example
The SALES_FACT table is 2.5 TB and receives daily loads ordered by load timestamp. Because data arrives chronologically, micro-partitions are naturally well clustered on ORDER_DATE: each partition covers a narrow date range. Dashboards filtering on the last 7 days prune 99% of partitions and return in seconds on a Small warehouse.
Two years later, an analyst starts filtering by REGION_CODE instead, a column scattered across every partition since load order has nothing to do with region. The same warehouse now scans the full table for a query that used to prune to almost nothing. That's the moment to consider a clustering key on (REGION_CODE, ORDER_DATE), covered in the performance chapter, not a bigger warehouse: the problem is which bytes get read, not how fast they get processed once read.
Performance, cost, security
- Performance: pruning quality determines scan time. Filters on columns correlated with load order prune well; filters on scattered columns don't.
- Cost: heavy UPDATE/DELETE churn rewrites partitions constantly, inflating Time Travel storage (old versions are retained) and any reclustering costs.
- Security: each micro-partition is individually encrypted, and Snowflake rotates its keys without rewriting data (Editions page).
- Trying to "create" or size partitions manually. There is no syntax for it. Clustering keys influence organization, not partition boundaries.
- Assuming an UPDATE of one row is cheap. It rewrites at least one full micro-partition.
- Confusing micro-partitions (storage units) with the partitions of other engines (logical, user-defined). Exam wording exploits this.
- Size: 50–500 MB uncompressed, stored compressed. The wrong options usually say "16 MB fixed" (that's the old VARIANT limit and a commonly quoted compressed size, two separate figures blended to confuse you) or "1 GB".
- Micro-partitions are immutable. Any option describing in-place modification is wrong.
- Metadata stores min/max per column per partition, which is what enables pruning. If a question asks "how does Snowflake avoid scanning all data," the answer is micro-partition metadata pruning, not an index.
- Automatic, immutable, columnar, 50–500 MB uncompressed, invisible to you.
- Per-partition min/max metadata powers pruning; pruning powers performance.
- Immutability + metadata pointers = Time Travel, cloning, Fail-safe.

