Explain Micro-Partitions & Automatic Data Clustering in Databases

44 / 66

Snowflake

Micro-Partitions & Automatic Data Clustering

Understand how Snowflake physically organizes data for efficient pruning.

Every Snowflake table is stored as many small files called micro-partitions — 50 to 500 MB uncompressed, columnar, and immutable once written.

It's like a library that automatically tags each shelf with the range of book titles it contains, so you never open a shelf that can't possibly hold the book you're looking for.

Key Concepts

You never create them, name them, or manage them. But how your queries perform is almost entirely about how many of them get read.

Metadata is what makes queries fast, not indexes. For every micro-partition, Snowflake records the minimum and maximum of every column, the row count, and the distinct-value counts.

So a query with a filter can eliminate files without opening them:

    query: WHERE order_date = '2026-03-15'

    partition 1   order_date  2026-01-01 .. 2026-01-31   skip
    partition 2   order_date  2026-02-01 .. 2026-02-28   skip
    partition 3   order_date  2026-03-01 .. 2026-03-31   read
    partition 4   order_date  2026-04-01 .. 2026-04-30   skip

That is pruning, and it is the single most important thing to understand about Snowflake performance. Reading 1 partition out of 4,000 is what makes a query on a huge table fast.

Pruning depends on how the data was loaded. Micro-partitions are created in the order rows arrive. Load daily and each file naturally holds one day, so the ranges barely overlap and a date filter prunes almost everything.

Now load the same data in random order:

    partition 1   order_date  2024-01-03 .. 2026-09-30
    partition 2   order_date  2024-01-01 .. 2026-09-29
    partition 3   order_date  2024-02-11 .. 2026-09-30

Every file spans the whole range, so every file might contain 2026-03-15 and none can be skipped. The metadata is still there and is useless — the query reads the entire table.

This is why the same query can be fast on one table and slow on another of identical size, and why the first question about a slow Snowflake query is how well the table is clustered.

Check it rather than guessing. The query profile reports partitions scanned against partitions total. Scanning most of the table when your filter is selective means pruning has failed.

Updates rewrite whole files. Because micro-partitions are immutable, changing one row writes a new micro-partition containing that row plus everything else in the old one, and marks the old one dead.

Two consequences follow. Row-by-row updates are expensive, so batch your DML. And the old partitions are retained rather than deleted — which is exactly what Time Travel and zero-copy cloning are built on.

What the interviewer is probing.1. "What is a micro-partition?" Probing: the unit. Stalls: "A file." Moves up: an immutable 50-500MB uncompressed block of columnar data with per-column metadata, created automatically on load.

2. "How does Snowflake avoid reading most of the data?" Probing: the mechanism. Stalls: "Indexes." Moves up: pruning — min/max metadata per column per partition lets the planner skip partitions that cannot match.

3. "Why does load order matter?" Probing: the consequence. Stalls: "It does not." *Moves up:* partitions inherit the natural clustering of how data arrived, so date-ordered loads prune well on date and poorly on everything else.

4. "Are there indexes to create?" Probing: expectations. Stalls: "Yes." Moves up: none — you influence pruning through clustering and load order, not index definitions.