Performance Optimization · Topic 4 of 9
Clustering - Natural Clustering, Clustering Keys and Auto-Clustering
Decide when clustering pays off: natural clustering, choosing keys, Automatic Clustering cost, and the clustering depth and overlap functions.
- 5 min
- Natural clustering follows load order; a clustering key helps when filters do not follow that order.
- Keys suit very large, read heavy tables; use low to medium cardinality, expressions for timestamps, few columns.
- Automatic Clustering is serverless, billed in credits, can be suspended with
SUSPEND RECLUSTER, and adds storage.
What it is
Snowflake stores a table in micro-partitions, each holding 50 to 500 MB of uncompressed data with metadata such as the minimum and maximum of every column. A query skips (prunes) micro-partitions whose range cannot match the filter. Clustering is how well the rows with similar values in a column are grouped into the same micro-partitions, and so how well pruning works.
How it works
Natural clustering
Data is clustered naturally by the order in which it is loaded. A table loaded daily is naturally clustered by date, so date filters prune well without any extra work.
Natural clustering degrades with frequent updates and deletes, with data loaded out of order, and with filters on columns that are unrelated to load order. Do not add a clustering key when natural clustering is good enough.
Clustering keys
A clustering key is one or more columns or expressions that Snowflake uses to co-locate rows.
ALTER TABLE events CLUSTER BY (event_date, region);
-- or an expression to lower cardinality
ALTER TABLE events CLUSTER BY (TO_DATE(event_ts), region);
| Guideline | Reason |
|---|---|
| Use on very large tables, typically multi-terabyte | Small tables prune well enough without it |
| Cluster on columns used in filters and joins | Pruning only helps on filtered columns |
| Order columns from lower to higher cardinality | Low cardinality first groups rows well |
| Avoid very low cardinality alone, such as a flag | Too few distinct values give little pruning |
| Avoid very high cardinality, such as a unique ID or raw timestamp | Costly to maintain; use an expression such as a date |
| Keep to a few columns, three or four at most | More columns raise cost and give diminishing returns |
| Table should be queried far more often than it changes | Maintenance cost must be repaid by faster reads |
A raw timestamp with seconds is too high in cardinality. Cluster on TO_DATE(ts) or DATE_TRUNC('hour', ts) instead.
Automatic Clustering
Once a key is set, Automatic Clustering reclusters the table in the background, with no warehouse needed.
- It is serverless and billed per credit consumed, in addition to the extra storage that reclustering creates (old micro-partitions stay in Time Travel and Fail safe).
- It runs when DML changes the table enough to degrade clustering, not on a fixed schedule.
ALTER TABLE ... SUSPEND RECLUSTERstops it andRESUME RECLUSTERrestarts it; dropping the key withDROP CLUSTERING KEYremoves clustering.- Cost depends on table size and how often data changes, so busy tables cost more to keep clustered.
- Credit use is shown in
AUTOMATIC_CLUSTERING_HISTORY.
Loading data pre sorted with ORDER BY on the key, or INSERT OVERWRITE ... ORDER BY, is a cheaper way to cluster a table that changes rarely, because no background service is needed.
System clustering information
Two functions describe clustering quality for a table and a candidate key.
SELECT SYSTEM$CLUSTERING_INFORMATION('events', '(event_date, region)');
SELECT SYSTEM$CLUSTERING_DEPTH('events', '(event_date)');
| Output field | Meaning |
|---|---|
total_partition_count | Number of micro-partitions |
average_overlaps | Average number of partitions that overlap each partition's range; lower is better |
average_depth | Average number of overlapping partitions at any point; closer to 1 is better |
partition_depth_histogram | How many partitions fall at each depth |
total_constant_partition_count | Partitions where the key has a single value, fully clustered |
A depth near 1 means little overlap and good pruning. A high or rising depth means pruning has degraded, which is a signal to add or fix the key. You can pass a column list that is not the current key to test a candidate before creating it. Compare these figures with the profile's partitions scanned versus total.
Choosing a pruning feature
| Situation | Feature |
|---|---|
| Range filters on a column in a huge table | Clustering key |
| Point lookups of a few rows by ID or text search | Search optimization service |
| Repeated aggregation on one table | Materialized view |
| Everything fits one warehouse and the table is small | Neither |
Common mistakes
Clustering a small table. Pruning already works and the maintenance cost is wasted.
Clustering on a unique ID or raw timestamp. Use a lower cardinality expression.
Clustering a table that is updated constantly. Reclustering credits can exceed the savings.
Putting the high cardinality column first. Order from low to high cardinality.
What to remember for the exam
- Natural clustering follows load order; a clustering key helps when filters do not follow that order.
- Keys suit very large, read heavy tables; use low to medium cardinality, expressions for timestamps, few columns.
- Automatic Clustering is serverless, billed in credits, can be suspended with
SUSPEND RECLUSTER, and adds storage. SYSTEM$CLUSTERING_INFORMATIONandSYSTEM$CLUSTERING_DEPTHreport depth and overlap; lower depth is better.- Clustering improves pruning, so it is judged by partitions scanned versus total.
Check yourself
A 5 TB table is filtered by
event_datebut loaded randomly, and queries scan most partitions. What do you do?Add a clustering key on the date, check it with
SYSTEM$CLUSTERING_INFORMATION, and let Automatic Clustering maintain it.A key on a raw timestamp costs a lot to maintain. What is the fix?
Cluster on an expression that lowers cardinality, such as
TO_DATE(ts).What does a high average depth tell you?
Many micro-partitions overlap in key range, so pruning is poor and reclustering may help.
Topic 4 of 9 in Performance Optimization
Test yourself on Performance Optimization
The full Snowflake ARA-C01 question bank has 687 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.