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
In a nutshell
  • SYSTEM$CLUSTERING_INFORMATION gives JSON, SYSTEM$CLUSTERING_DEPTH gives 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 with SUSPEND 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.

QueryWHERE order_date = xreads metadataPartition rangesmin and max per columnprunesFew partitions scannedfast and cheap

How it works

Analyzing clustering with system functions

FunctionReturns
SYSTEM$CLUSTERING_INFORMATIONA JSON document with the full picture
SYSTEM$CLUSTERING_DEPTHOne 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_overlaps mean 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.
Many overlapping partitionshigh depthreclusterSorted by keylow depthresultBetter pruningfewer partitions read
Exam tip

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) or DATE_TRUNC('month', order_ts).
ColumnCardinalityVerdict
is_active (boolean)2Too low
order_ts (timestamp)MillionsToo high as is, truncate it
TO_DATE(order_ts)Hundreds to thousandsGood

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.

ControlCommand or view
Pause reclusteringALTER TABLE sales SUSPEND RECLUSTER;
ResumeALTER TABLE sales RESUME RECLUSTER;
Is it on?SHOW TABLES shows the cluster key and automatic_clustering
CostAUTOMATIC_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 BY on the key during load can give a good start, but only Automatic Clustering keeps it sorted.
INSERT or UPDATEnew unsorted dataskewsDepth risesoverlap growsdetectedAutomatic Clusteringserverless creditsrewritesPartitions sorteddepth falls
Watch out

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_INFORMATION gives JSON, SYSTEM$CLUSTERING_DEPTH gives 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 with SUSPEND 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.