Course contents

SnowPro Core COF-C03 Study GuidePart 1: ArchitectureMicro-partitions

Micro-partitions

Micro-partitions

Definition

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.

50–500MBuncompressed size
~16MBtypical compressed
0manual partitions ever created
∞effective capacity

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

  1. You run SELECT SUM(amount) FROM sales WHERE order_date = '2026-06-15'.
  2. Cloud services checks the min/max of order_date in every micro-partition's metadata. A partition whose range is 2026-01-02 → 2026-01-09 cannot contain the target date, so it's eliminated without being read. This is partition pruning.
  3. Within the surviving partitions, only the order_date and amount columns are read. Every other column is skipped. This is column pruning.
  4. The warehouse scans what remains, which might be 0.1% of the table.
Partition pruning for WHERE order_date = 2026-06-15. Six micro-partitions are drawn as bars covering the min to max order_date stored in their metadata. The filter date crosses only MP 2 (June 12 to 18) and MP 3 (June 14 to 20), so only those two are scanned. The other four can't contain the date and are skipped without reading a byte.MIN/MAX OF ORDER_DATE IN EACH PARTITION'S METADATARESULTMP 1Jan 02 to 09✗skippedMP 2Jun 12 to 18✓scannedMP 3Jun 14 to 20✓scannedMP 4Sep 01 to 07✗skippedMP 5Mar 20 to 27✗skippedMP 6Nov 04 to 11✗skippedJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECWHERE order_date = '2026-06-15' Partition pruning for WHERE order_date = 2026-06-15. Six micro-partitions are drawn as bars covering the min to max order_date stored in their metadata. The filter date crosses only MP 2 (June 12 to 18) and MP 3 (June 14 to 20), so only those two are scanned. The other four can't contain the date and are skipped without reading a byte.MIN/MAX OF ORDER_DATE IN EACHPARTITION'S METADATARESULTMP 1✗skippedJan 02 to 09MP 2✓scannedJun 12 to 18MP 3✓scannedJun 14 to 20MP 4✗skippedSep 01 to 07MP 5✗skippedMar 20 to 27MP 6✗skippedNov 04 to 11JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECWHERE order_date = '2026-06-15'
Figure 1.4 Partition pruning. Each bar is a micro-partition's min to max order_date, read from metadata. Only bars crossed by the filter line can hold the date, so 2 of 6 partitions are scanned, and inside them only the order_date and amount columns are read.

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.

The same date filter on the same table at two moments. Fresh table, loaded in date order: each partition covers one month, so June 15 falls in one range and 1 of 4 partitions is scanned. After two years of scattered updates, rewritten partitions mix dates from the whole year, every range covers June 15, and all 4 are scanned.FRESH TABLE · LOADED IN DATE ORDERSAME TABLE · TWO YEARS OF SCATTERED UPDATES LATERMP 1MP 2MP 3MP 41 of 4scannedMP 1MP 2MP 3MP 44 of 4scannedJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC The same date filter on the same table at two moments. Fresh table, loaded in date order: each partition covers one month, so June 15 falls in one range and 1 of 4 partitions is scanned. After two years of scattered updates, rewritten partitions mix dates from the whole year, every range covers June 15, and all 4 are scanned.FRESH TABLE ·LOADED IN DATE ORDER1 of 4scannedMP 1MP 2MP 3MP 4SAME TABLE · TWO YEARS OFSCATTERED UPDATES LATER4 of 4scannedMP 1MP 2MP 3MP 4JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
Figure 1.5 Why pruning degrades. Loaded in date order, partitions cover narrow ranges and one date maps to one partition. After years of scattered updates the ranges widen until they all cover June 15, and every partition must be scanned. That widening is overlap, measured by clustering depth (Part 2).

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.
INSERT 500k rows
append
New partitions MP 8201–8203 are written; every existing file stays untouched, so the cost doesn't depend on table size.
DELETE WHERE id = 42
copy-on-write
MP 3005 v2 is written without the row and becomes current; v1 is retired into Time Travel.
UPDATE one row in MP 3005
copy-on-write
Same mechanism as DELETE, shown in Figure 1.6: one changed row still produces a whole new partition.
Copy-on-write for one UPDATE. Before: the SALES table's metadata points at partitions 3004, 3005 version 1 and 3006. After the UPDATE commits, Snowflake has written a new file, 3005 version 2, and the metadata points at it. 3004 and 3006 are reused unchanged. 3005 version 1 is no longer current but is kept for Time Travel, then Fail-safe.BEFORE · UPDATE … WHERE id = 42AFTER · STATEMENT COMMITTEDTABLE METADATASALEScurrent versionTABLE METADATASALEScurrent versionMP 3004currentMP 3005 v1currentMP 3006currentrow id = 42 lives in MP 3005MP 3004reusedMP 3005 v2new fileMP 3006reusedMP 3005 v1retirednot current any more:kept for Time Travel, then Fail-safe

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

Figure 1.6 Copy-on-write for one UPDATE. Files are never edited: the table's metadata stops pointing at MP 3005 v1 and points at a new v2, while untouched partitions are reused. The retired v1 is what Time Travel, clones and Fail-safe rely on.

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."
UPDATE one row
copy-on-write
The new partition becomes current; the old one stays reachable through Time Travel for the retention period.
CLONE the table
metadata only
A new table points at the same partitions: 100 TB cloned in seconds, no new storage until either side changes (see Zero-Copy Cloning in Part 5).
Time Travel expires
+7 days
Fail-safe: partitions still exist for permanent tables, reachable only through Snowflake Support, then purged (see Fail-safe in Part 5).

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

Production ScenarioIsmaFrost · sales analytics

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).
Common mistakes
  • 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.
Exam traps
  • 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.
Key takeaways
  • 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.