What is a micro-partition in Snowflake?

An immutable storage unit of 50-500 MB of uncompressed data, stored columnar, created automatically as data is written. Snowflake keeps min/max values and a distinct count per column per partition, and uses that metadata to skip partitions a query cannot need.

What is partition pruning?

Skipping micro-partitions whose metadata proves they contain no matching rows. A filter on a column the table is sorted by prunes almost everything; a filter on an unrelated column prunes nothing, because matching values are spread across every partition.

When is a clustering key worth adding?

When a large table — hundreds of gigabytes or more — is repeatedly filtered on a column that does not match its natural load order, and the query profile shows poor pruning. Automatic clustering runs continuously and costs credits, so a small table or a rarely run query will not repay it.

How do I know if my table is well clustered?

`SYSTEM$CLUSTERING_INFORMATION` reports average overlap and depth for a column. Low depth means a value lives in few partitions, so a filter on it prunes well. It is also the before-and-after measurement for whether adding a clustering key changed anything.

Micro-Partitions, Pruning, and Clustering

Snowflake has no indexes. What it has is metadata on every micro-partition, and a query planner that uses it to skip whole files. Performance work is almost entirely about making that skipping effective.

The structure

As rows are written, Snowflake groups them into immutable micro-partitions of 50-500 MB uncompressed, stored column by column. For each partition and each column it records:

the minimum and maximum value
the number of distinct values
the number of nulls

That metadata lives in the services layer, so evaluating it costs no warehouse time.

select
    table_name,
    row_count,
    round(bytes / power(1024, 3), 2) as gb,
    clustering_key
from information_schema.tables
where table_name = 'ORDERS_LARGE';

+--------------+------------+--------+----------------+
| TABLE_NAME   | ROW_COUNT  | GB     | CLUSTERING_KEY |
|--------------+------------+--------+----------------|
| ORDERS_LARGE | 4212000000 | 412.60 | NULL           |
+--------------+------------+--------+----------------+
1 Row(s) produced. Time Elapsed: 0.402s

Pruning, measured

The table was loaded daily, so it is naturally ordered by ordered_at. Filter on that:

select count(*), sum(amount)
from orders_large
where ordered_at between '2026-01-01' and '2026-01-07';

+-----------+---------------+
| COUNT(*)  | SUM(AMOUNT)   |
|-----------+---------------|
|  28114202 | 712884201.55  |
+-----------+---------------+
1 Row(s) produced. Time Elapsed: 3.204s

select
    partitions_scanned,
    partitions_total,
    round(100 * partitions_scanned / partitions_total, 2) as pct_scanned,
    round(bytes_scanned / power(1024, 3), 2) as gb_scanned
from table(information_schema.query_history())
order by start_time desc limit 1;

+--------------------+------------------+-------------+------------+
| PARTITIONS_SCANNED | PARTITIONS_TOTAL | PCT_SCANNED | GB_SCANNED |
|--------------------+------------------+-------------+------------|
|                 61 |             8442 |        0.72 |       2.98 |
+--------------------+------------------+-------------+------------+
1 Row(s) produced. Time Elapsed: 0.402s

61 partitions out of 8,442 — 3 GB read instead of 412 GB. Nothing was configured; the load order did the work.

Now filter on a column with no relationship to load order:

select count(*), sum(amount)
from orders_large
where customer_id = 481920;

+----------+-------------+
| COUNT(*) | SUM(AMOUNT) |
|----------+-------------|
|       42 |     1284.60 |
+----------+-------------+
1 Row(s) produced. Time Elapsed: 88.412s

+--------------------+------------------+-------------+------------+
| PARTITIONS_SCANNED | PARTITIONS_TOTAL | PCT_SCANNED | GB_SCANNED |
|--------------------+------------------+-------------+------------|
|               8442 |             8442 |      100.00 |     412.60 |
+--------------------+------------------+-------------+------------+

42 rows returned; 412 GB scanned. Because customers appear on every date, that customer’s rows are scattered across every partition, and no partition can be excluded on min/max alone.

PARTITIONS_SCANNED / PARTITIONS_TOTAL is the single most useful performance number in Snowflake. Close to 1.0 on a selective query means the table is not organised for it.

Measuring clustering directly

select system$clustering_information('orders_large', '(customer_id)');

+---------------------------------------------------------------------------+
| SYSTEM$CLUSTERING_INFORMATION('ORDERS_LARGE', '(CUSTOMER_ID)')            |
|---------------------------------------------------------------------------|
| {                                                                         |
|   "cluster_by_keys" : "LINEAR(customer_id)",                              |
|   "total_partition_count" : 8442,                                         |
|   "total_constant_partition_count" : 0,                                   |
|   "average_overlaps" : 8441.0,                                            |
|   "average_depth" : 8442.0,                                               |
|   "partition_depth_histogram" : {                                         |
|     "00000" : 0,                                                          |
|     "00001" : 0,                                                          |
|     "01024" : 8442                                                        |
|   }                                                                       |
| }                                                                         |
+---------------------------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 2.104s

average_depth: 8442 means a typical customer_id value is present in every partition — the worst possible case. Compare the same call for the load-ordered column:

select system$clustering_information('orders_large', '(ordered_at)');

| {                                                                         |
|   "cluster_by_keys" : "LINEAR(ordered_at)",                               |
|   "total_partition_count" : 8442,                                         |
|   "average_overlaps" : 1.4,                                               |
|   "average_depth" : 2.1,                                                  |
|   "partition_depth_histogram" : {                                         |
|     "00001" : 5102,                                                       |
|     "00002" : 2988,                                                       |
|     "00004" : 352                                                         |
|   }                                                                       |
| }                                                                         |

Depth 2.1 — a given date lives in about two partitions. That is what 0.72% pruning looks like from the other side.

Adding a clustering key

alter table orders_large cluster by (customer_id, ordered_at);

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.288s

Snowflake’s automatic clustering service now reorganises the table in the background — there is no blocking rebuild, and it costs credits. Watch it work:

select
    start_time,
    num_bytes_reclustered / power(1024, 3) as gb_reclustered,
    credits_used
from snowflake.account_usage.automatic_clustering_history
where table_name = 'ORDERS_LARGE'
order by start_time desc limit 3;

+-------------------------------+----------------+--------------+
| START_TIME                    | GB_RECLUSTERED | CREDITS_USED |
|-------------------------------+----------------+--------------|
| 2026-09-09 14:00:00.000 -0700 |         182.44 |        24.80 |
| 2026-09-09 13:00:00.000 -0700 |         208.11 |        28.14 |
| 2026-09-09 12:00:00.000 -0700 |          22.06 |         3.02 |
+-------------------------------+----------------+--------------+
3 Row(s) produced. Time Elapsed: 0.688s

Once it settles, the same lookup:

+--------------------+------------------+-------------+------------+
| PARTITIONS_SCANNED | PARTITIONS_TOTAL | PCT_SCANNED | GB_SCANNED |
|--------------------+------------------+-------------+------------|
|                 12 |             8688 |        0.14 |       0.58 |
+--------------------+------------------+-------------+------------+

88 seconds to under a second, and 412 GB to 0.58 GB.

Whether it is worth it

That improvement cost ~56 credits to reorganise and will cost more on every subsequent write. The arithmetic:

Query saving. 88s → 0.9s on a Large warehouse (8 credits/hr) is ~0.19 credits per query. Run 500 times a day: 95 credits/day saved.
Clustering cost. Steady-state reclustering after the initial pass, from the history above: ~10-30 credits/day.

Worth it here. It would not be if the query ran twice a day, or if the table were 5 GB, where a full scan is a couple of seconds anyway.

Rules that hold up:

Do	Don’t
Cluster tables in the hundreds of GB and up	Cluster small tables — pruning is already fine
Put the lowest-cardinality column first	Lead with a unique id — every partition gets its own range and nothing prunes
Cluster on what `WHERE` and `JOIN` filter	Cluster on what you `SELECT`
Re-measure with `system$clustering_information`	Assume it helped

Order within the key matters a great deal. cluster by (customer_id, ordered_at) groups all of a customer’s orders together and sorts by date within that. The reverse is close to the unclustered case for customer lookups.

For very high-cardinality point lookups, the search optimization service is the better tool — it builds a separate search access path rather than reordering data:

alter table orders_large add search optimization on equality(order_id);

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Things that quietly defeat pruning

A function on the filtered column stops metadata comparison working:

-- no pruning: every partition must be read to evaluate the cast
where to_char(ordered_at, 'YYYY-MM') = '2026-01'

-- prunes: min/max on the column can be compared directly
where ordered_at >= '2026-01-01' and ordered_at < '2026-02-01'

-- to_char version
| PARTITIONS_SCANNED | PARTITIONS_TOTAL | PCT_SCANNED |
|               8442 |             8442 |      100.00 |

-- range version
| PARTITIONS_SCANNED | PARTITIONS_TOTAL | PCT_SCANNED |
|                268 |             8442 |        3.17 |

Same rows, 30× less data. Wrapping the column in a function is the mistake; wrapping the literal is fine. The same applies to LIKE '%foo%' with a leading wildcard, and to joining on an expression rather than a plain column.

And SELECT * reads every column’s storage. On a wide table, naming the six columns you need is often a bigger win than any clustering key:

-- select *            : 412.60 GB scanned, 88.412s
-- select 6 columns    :  31.04 GB scanned,  7.208s

Practice

1. Compare pruning for a filter on the load-order column and on another column.

-- where ordered_at between ...
|                 61 |             8442 |        0.72 |
-- where customer_id = ...
|               8442 |             8442 |      100.00 |

Both queries are equally simple to write; one reads 3 GB and the other 412 GB. Checking partitions_scanned before optimising anything tells you whether you have a pruning problem or a compute problem.

2. Run system$clustering_information before and after adding a key.

-- before
"average_depth" : 8442.0
-- after automatic clustering settles
"average_depth" : 2.8

Depth is the number to track. It is also how you notice clustering degrading months later as writes accumulate, before anyone reports a slow dashboard.

3. Wrap a filtered column in a function and compare.

-- where year(ordered_at) = 2026
|               8442 |             8442 |      100.00 |
-- where ordered_at >= '2026-01-01' and ordered_at < '2027-01-01'
|               3104 |             8442 |       36.77 |

The optimiser cannot compare year(column) against stored min/max values, so it reads everything. Rewriting date filters as ranges is the highest-value, lowest-effort change in most Snowflake codebases.

4. Check what automatic clustering is costing you.

select table_name, sum(credits_used) as credits
from snowflake.account_usage.automatic_clustering_history
where start_time >= dateadd('day', -30, current_timestamp())
group by 1 order by credits desc;

+--------------+---------+
| TABLE_NAME   | CREDITS |
|--------------+---------|
| ORDERS_LARGE |  612.40 |
| EVENTS_RAW   |  488.02 |
+--------------+---------+

EVENTS_RAW is worth investigating — a table clustered on a key nobody filters by pays this every day for nothing. Reclustering cost is one of the most common invisible line items on a Snowflake bill.

Next: streams and tasks — change data capture and scheduling inside the warehouse.