Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 38 ยท Warehouses and Performance

Clustering Keys and Automatic Clustering

Learn when to define a Snowflake clustering key, how clustering depth and overlap are measured, how automatic clustering costs credits, and how to choose columns.

When a Snowflake table is loaded, its rows land in micro-partitions in the order they arrive. If you load by date, the table is naturally ordered by date and filters on date prune well. But what if analysts filter by customer_id, or the table receives late data from many sources? Then each partition's customer_id range spans nearly everything, and pruning fails. A clustering key tells Snowflake to organize rows so that similar values sit in the same micro-partitions.

Defining a clustering key

SQL
CREATE TABLE web_events (
  event_time  TIMESTAMP_NTZ,
  customer_id NUMBER,
  event_type  STRING,
  payload     VARIANT
)
CLUSTER BY (TO_DATE(event_time), customer_id);

-- or on an existing table
ALTER TABLE web_events CLUSTER BY (TO_DATE(event_time), customer_id);

-- remove it
ALTER TABLE web_events DROP CLUSTERING KEY;

Once a table has a key, Automatic Clustering runs in the background as serverless compute, reorganizing micro-partitions as data changes. You are charged serverless credits for this reclustering, and storage can temporarily grow because old partitions are kept for Time Travel and fail-safe. You can pause it:

SQL
ALTER TABLE web_events SUSPEND RECLUSTER;
ALTER TABLE web_events RESUME RECLUSTER;

Measuring clustering quality

Two ideas describe how well-clustered a table is:

  • Overlap: how many partitions' ranges include the same value.
  • Depth: the average number of partitions you have to read to find all rows for one value. A perfectly clustered table has depth 1.

Snowflake reports them with a system function:

SQL
SELECT SYSTEM$CLUSTERING_INFORMATION('web_events', '(customer_id)');

The JSON result includes average_overlaps, average_depth and a partition depth histogram. Low depth and few overlaps mean good clustering. (This function only reads metadata, so it is cheap.)

Seeing it with a simulation

The code below creates partitions from randomly arranged keys, measures the depth for one value, then sorts the data (what reclustering does) and measures again. It is a stand-in that models the concept.

Python Depth before and after clustering (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Before clustering, nearly every partition's range covers any customer (depth near the partition count); after sorting, a customer lives in one or two partitions.

SQL Which column order clusters best? (SQL stand-in) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

A column with very few values (like status) gives too little separation; one with a unique value per row is too fine to group. Dates, regions or a moderate-cardinality ID are the sweet spot.

Choosing a key

Snowflake's guidance, in practice:

  1. Cluster only large tables. Think multi-terabyte, or at least many thousands of micro-partitions. Small tables prune fine already and reclustering would cost more than it saves.
  2. Cluster tables that are queried much more often than they change. Constantly rewritten tables keep triggering reclustering.
  3. Start with the columns used most in WHERE and JOIN predicates. Range filters (dates) and equality filters (region) both benefit.
  4. Order the columns from lower to higher cardinality. (TO_DATE(event_time), customer_id) is better than (customer_id, TO_DATE(event_time)), which would break up each customer across all days.
  5. Use expressions to lower cardinality. A timestamp has nearly one value per row, so cluster on TO_DATE(ts) or DATE_TRUNC('hour', ts) instead. For a long string, cluster on a prefix such as SUBSTR(url, 1, 20).
  6. Use at most 3 or 4 columns. More adds cost without benefit.
  7. Measure first. Check the Query Profile for poor pruning and SYSTEM$CLUSTERING_INFORMATION for depth before and after.

An alternative to a key is to load in sorted order yourself: for example, create a table from SELECT ... ORDER BY customer_id. It costs no background credits, but you must redo it when data drifts.

Clustering costs and trade-offs

Benefit Cost
Faster, cheaper queries through better pruning Serverless credits for reclustering
Better compression of similar values Extra storage during rewrites (Time Travel, fail-safe)
Helps range and equality filters, joins and some aggregates Not useful if queries never filter on the key

Watch the cost in the account usage view:

SQL
SELECT table_name, SUM(credits_used) AS credits, SUM(num_rows_reclustered) AS rows
FROM snowflake.account_usage.automatic_clustering_history
WHERE start_time > DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY table_name
ORDER BY credits DESC;

If a table's reclustering bill is large and its queries are not noticeably faster, drop the key.

Clustering is for scans over ranges of data. Two sibling features exist for other shapes of query, covered in the next lesson: the search optimization service for needle-in-haystack lookups, and materialized views for repeated aggregations.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which table is the best candidate for a clustering key?

    Clustering pays off for very large tables that are queried far more often than they change.
  2. 2. In a multi-column clustering key, how should columns usually be ordered?

    Putting the coarse column first keeps related rows together; a unique-ish column first would scatter everything else.
  3. 3. What is the ideal average clustering depth?

    Depth is the number of partitions overlapping a value; 1 means each value sits in exactly one partition.
  4. 4. Why cluster on TO_DATE(event_time) rather than event_time?

    Reducing cardinality with an expression gives partitions that actually hold related rows.
  5. 5. How is Automatic Clustering billed?

    Reclustering runs as serverless compute and consumes credits, shown in AUTOMATIC_CLUSTERING_HISTORY.

Summary

  • A clustering key co-locates similar values so pruning works on large tables.
  • Measure with SYSTEM$CLUSTERING_INFORMATION: aim for low depth and overlap.
  • Choose 1 to 4 columns, low cardinality first, using expressions to reduce cardinality.
  • Automatic Clustering costs serverless credits and extra storage; use it only where queries benefit.

Next: Search optimization and materialized views