# Micro-partitions, Pruning and Clustering Keys

> How Snowflake skips data without indexes, how to tell whether a query prunes well, and when a clustering key is worth paying for.

- Source: https://akshaythakare.com/notes/snowflake/clustering-and-pruning
- Topic: Snowflake
- Level: advanced
- Reading time: 2 min
- Tags: snowflake, performance, clustering, pruning
- Updated: 2026-10-11

## 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

```sql
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

```sql
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**.

| Worth it | Not worth it |
| --- | --- |
| Multi-TB tables | Small tables (a full scan is cheap) |
| Queries filter on the same columns repeatedly | Filters vary widely |
| Pruning is poor today | Data already arrives in filter order |

## 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))`.

> **Tip: Interview framing**
>
> "Snowflake has no indexes; it prunes micro-partitions by min/max metadata. I check partitions scanned in the profile before adding a clustering key, because reclustering costs credits."