What are micro-partitions in Snowflake and how do they enable automatic data clustering?

Micro-partitions are the fundamental storage units in Snowflake. When data is loaded, Snowflake automatically divides it into contiguous blocks of 50–500 MB of uncompressed data (typically ~16 MB compressed on disk). Each micro-partition is stored as a columnar compressed file in cloud object storage. The key optimization property comes from the rich metadata Snowflake stores for each partition in the Cloud Services layer — not the data itself.

Per micro-partition, Snowflake records: minimum and maximum column values for every column, null count, distinct value count, and row count. When the query optimizer evaluates a predicate like WHERE order_date = '2024-03-15', it reads this metadata and skips any micro-partition whose max(order_date) is earlier or whose min(order_date) is later. This is partition pruning — no data bytes are read from skipped partitions.

Automatic clustering refers to the fact that data loaded chronologically (e.g., daily appended events) naturally groups similar values into the same micro-partitions. For tables where load order does not match query patterns, explicit clustering keys can trigger reorganization. The better the clustering, the fewer micro-partitions must be scanned per predicate, directly reducing query cost.

-- Check clustering quality of the sales table
SELECT SYSTEM$CLUSTERING_INFORMATION('sales', '(order_date)');

-- Micro-partition statistics via Information Schema
SELECT table_name, clustering_key, row_count, bytes
FROM information_schema.tables
WHERE table_schema = 'PUBLIC';