Snowflake
Micro-Partitions & Automatic Data Clustering
Understand how Snowflake physically organizes data for efficient pruning.
Every table in Snowflake is automatically divided into micro-partitions — contiguous units of storage typically 50-500 MB (uncompressed) that store data in a columnar format. This is a foundational concept because it drives Snowflake's query performance without requiring manual index management.
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
1
For each micro-partition, Snowflake's metadata layer stores rich statistics: the number of rows, min/max values for every column, and (for some types) additional distribution info. When a query includes a filter (e.g., WHERE order_date = '2024-01-01'), the optimizer uses this metadata to skip micro-partitions that cannot possibly contain matching rows — a technique called partition pruning.
WHERE order_date = '2024-01-01'partition pruning
2
As data is loaded, micro-partitions are created in roughly the order data arrives. If a table is loaded incrementally by date, it naturally clusters well on date. But tables with high-cardinality, frequently-updated, or randomly-ordered data can become poorly clustered over time, degrading pruning efficiency — this is where clustering keys and reclustering come in.
3
Because micro-partitions are immutable, updates and deletes don't modify them in place; instead, Snowflake writes new micro-partitions and marks old ones for removal, which is also the mechanism underlying Time Travel.