Storage and Data Protection · Topic 2 of 4
Micro-partitions and Clustering
Read clustering depth and information, pick effective cluster keys, and manage Automatic Clustering cost with suspend and resume in Snowflake.
- 5 min
SYSTEM$CLUSTERING_INFORMATIONgives JSON,SYSTEM$CLUSTERING_DEPTHgives a number; smaller depth is better.- Cluster keys suit very large tables with filters on the key; use 3 or 4 columns at most and low cardinality first.
- Automatic Clustering is serverless, on by default after
CLUSTER BY, paused withSUSPEND RECLUSTER.
What it is
Snowflake stores table data in micro-partitions, each with metadata such as the minimum and maximum value of every column. A query skips (prunes) every micro-partition whose range cannot match the filter. Pruning works best when similar values sit together, which is what clustering means.
Data loads in arrival order, so on a big table the natural order may not match how people filter. A cluster key tells Snowflake which columns to keep co-located. Automatic Clustering is the serverless service that maintains it.
How it works
Analyzing clustering with system functions
| Function | Returns |
|---|---|
SYSTEM$CLUSTERING_INFORMATION | A JSON document with the full picture |
SYSTEM$CLUSTERING_DEPTH | One number, the average depth |
SELECT SYSTEM$CLUSTERING_INFORMATION('sales', '(order_date, region)');
SELECT SYSTEM$CLUSTERING_DEPTH('sales', '(order_date)');
The second argument is optional if the table already has a cluster key. You can test columns that are not the current key, which lets you compare candidates before you commit.
The JSON includes total_partition_count, total_constant_partition_count, average_overlaps, average_depth and a partition_depth_histogram.
Clustering depth
Depth is the average number of micro-partitions that overlap for a given value of the key. The smaller the number, the better the table is clustered.
- A depth of 1 means no overlap, which is perfect.
- An empty table has a depth of 0.
- A high depth and a high
average_overlapsmean a filter on that column must read many partitions. - A constant micro-partition has reached a state where reclustering would not improve it significantly, so a high constant count is good.
Do not chase a depth of exactly 1 on every table. The real measure is query pruning, so look at partitions scanned versus total in the query profile.
Cluster keys
ALTER TABLE sales CLUSTER BY (TO_DATE(order_ts), region);
ALTER TABLE sales DROP CLUSTERING KEY;
Good candidates and rules:
- Columns used in filters (WHERE) and join predicates, and sometimes ORDER BY or GROUP BY, on large scans.
- The table should be large, in the multi terabyte range, and queried much more often than it changes. Small tables prune well already.
- Snowflake recommends at most 3 or 4 columns per key. More columns raise cost without much benefit.
- Put the lower cardinality column first, then the higher one.
- The key needs a balance of cardinality. A boolean has too few values to prune, and a raw timestamp or ID has so many that maintenance is expensive.
- Use an expression to reduce cardinality, for example
TO_DATE(order_ts)orDATE_TRUNC('month', order_ts).
| Column | Cardinality | Verdict |
|---|---|---|
is_active (boolean) | 2 | Too low |
order_ts (timestamp) | Millions | Too high as is, truncate it |
TO_DATE(order_ts) | Hundreds to thousands | Good |
Automatic Clustering
Once a cluster key is set on a table, Automatic Clustering is on by default. It reclusters in the background with Snowflake managed (serverless) compute, so no warehouse is involved, and it does not block DML on the table.
| Control | Command or view |
|---|---|
| Pause reclustering | ALTER TABLE sales SUSPEND RECLUSTER; |
| Resume | ALTER TABLE sales RESUME RECLUSTER; |
| Is it on? | SHOW TABLES shows the cluster key and automatic_clustering |
| Cost | AUTOMATIC_CLUSTERING_HISTORY (ACCOUNT_USAGE view and INFORMATION_SCHEMA table function) |
Cost and optimization points:
- You pay serverless credits for the initial clustering and for every later reclustering as DML changes data.
- Reclustering rewrites micro-partitions, so the old ones go into Time Travel and Fail-safe, which adds storage cost.
- Tables with heavy, constant DML cost the most to keep clustered.
- A table that is loaded once and queried often is the ideal case.
- Suspend reclustering during a large bulk load, then resume, to avoid paying to recluster data that is still arriving.
- Sorting data with
ORDER BYon the key during load can give a good start, but only Automatic Clustering keeps it sorted.
Clustering is not free. If a table is small, or its queries do not filter on the key, you pay credits and extra storage for no speed gain.
Common mistakes
Clustering a small table. There is nothing to prune, so the cost has no benefit.
Picking a timestamp as the key. Truncate it to a date to cut cardinality.
Thinking a warehouse does the reclustering. It is serverless and billed separately.
Reading a low depth as the goal. Compare pruning on real queries, not only the number.
What to remember for the exam
SYSTEM$CLUSTERING_INFORMATIONgives JSON,SYSTEM$CLUSTERING_DEPTHgives a number; smaller depth is better.- Cluster keys suit very large tables with filters on the key; use 3 or 4 columns at most and low cardinality first.
- Automatic Clustering is serverless, on by default after
CLUSTER BY, paused withSUSPEND RECLUSTER. - Reclustering costs credits and adds Time Travel and Fail-safe storage.
Check yourself
What does an average clustering depth close to 1 mean?
Micro-partitions barely overlap on the key, so the table is well clustered for that key.
How do you stop reclustering costs without removing the cluster key?
Run ALTER TABLE ... SUSPEND RECLUSTER, and resume it later.
Why truncate a timestamp in a cluster key?
To lower the cardinality, because a raw timestamp has so many distinct values that clustering becomes costly and ineffective.
Topic 2 of 4 in Storage and Data Protection
Test yourself on Storage and Data Protection
The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.