Micro-Partitions
Snowflake · Storage & Optimization
Theory
Snowflake stores every table as micro-partitions: contiguous, immutable units of storage, each holding roughly 50 to 500 MB of uncompressed data, stored in a compressed columnar format. Rows are grouped into partitions in the order they are loaded. For every partition, Snowflake records metadata such as the range (min and max) of values in each column and the number of distinct values.
The optimizer uses this metadata to prune: for WHERE order_date = '2024-03-15', any partition whose order_date range does not include that date is skipped without being read. Because partitions are immutable, an UPDATE writes new partitions and the old ones are kept for Time Travel and Fail-safe.
Why we use it
Pruning means a query on a 10 TB table may read only a small fraction of it. Snowflake does this automatically, so you do not have to design and maintain partitions by hand as in traditional databases.
When to use it
Think about micro-partitions when queries filter on large tables: filter on columns that correlate with load order (like dates), avoid wrapping filter columns in functions where possible, and check pruning in Query Profile.
How Data Gets Divided
Every table is automatically and invisibly divided into micro-partitions — contiguous, immutable, compressed chunks of roughly 50–500MB uncompressed — with no manual partitioning required.
The Metadata That Powers Pruning
For each micro-partition, Snowflake stores metadata like the min/max value per column, letting the optimizer skip (prune) partitions that can't match a query's filter without scanning them.
Why This Replaces Manual Partitioning
Traditional databases require you to design and maintain partitions by hand; Snowflake's automatic micro-partitioning removes that maintenance burden while still delivering comparable pruning performance.
How it works
- Query with WHERE order_date = '2024-03-15'
- Optimizer reads min/max metadata for every partition
- Does the partition's date range include the value? Yes: Keep it for scanning. No: Prune it, never read.
- Warehouse scans only the kept partitions and needed columns
- Return result; Query Profile shows partitions scanned vs total
Example
-- Managed automatically — no manual partitioning requiredKey points
- Contiguous units of storage, immutable once written
- Roughly 50-500 MB uncompressed each, stored compressed and columnar
- Min/max metadata enables pruning without scanning
- DML writes new partitions; old ones support Time Travel
- Eliminates the need for manual partitioning strategies