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
In a nutshell
  • 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.

Rows loadedin arrival orderformMicro partitionswith min and max per columnfilter checksPruningskip partitions that cannot match

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);
GuidelineReason
Use on very large tables, typically multi-terabyteSmall tables prune well enough without it
Cluster on columns used in filters and joinsPruning only helps on filtered columns
Order columns from lower to higher cardinalityLow cardinality first groups rows well
Avoid very low cardinality alone, such as a flagToo few distinct values give little pruning
Avoid very high cardinality, such as a unique ID or raw timestampCostly to maintain; use an expression such as a date
Keep to a few columns, three or four at mostMore columns raise cost and give diminishing returns
Table should be queried far more often than it changesMaintenance cost must be repaid by faster reads
Exam tip

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 RECLUSTER stops it and RESUME RECLUSTER restarts it; dropping the key with DROP CLUSTERING KEY removes 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.
DML on tableinserts and updatesdegradesClusteringoverlap risestriggersAutomatic ClusteringserverlessrewritesBetter partitions

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 fieldMeaning
total_partition_countNumber of micro-partitions
average_overlapsAverage number of partitions that overlap each partition's range; lower is better
average_depthAverage number of overlapping partitions at any point; closer to 1 is better
partition_depth_histogramHow many partitions fall at each depth
total_constant_partition_countPartitions 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

SituationFeature
Range filters on a column in a huge tableClustering key
Point lookups of a few rows by ID or text searchSearch optimization service
Repeated aggregation on one tableMaterialized view
Everything fits one warehouse and the table is smallNeither

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_INFORMATION and SYSTEM$CLUSTERING_DEPTH report 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_date but 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.