Micro-partitions, Pruning and Clustering Keys

Micro-partitions

Snowflake stores each table as many immutable micro-partitions (roughly 50–500 MB uncompressed). For every column in every micro-partition it keeps metadata: min, max, distinct count and null count.

Pruning

SELECT SUM(net_sales) FROM fact_sales WHERE business_date = '2026-01-05';

Snowflake reads only micro-partitions whose business_date range includes 2026-01-05. Check the query profile: partitions scanned versus partitions total.

Data loaded in date order is naturally well clustered by date. Data loaded in random order is not.

Clustering keys

ALTER TABLE fact_sales CLUSTER BY (business_date, store_id);
SELECT SYSTEM$CLUSTERING_INFORMATION('fact_sales', '(business_date, store_id)');

Automatic clustering then reorganises data in the background, and that consumes credits.

Choosing keys

• Lead with the column used most in filters, usually a date.
• Use low-to-medium cardinality. Clustering on a unique id rarely helps.
• Wrap very high-cardinality columns, e.g. CLUSTER BY (TO_DATE(event_ts)).