Snowflake Clustering Keys: When They Pay for Themselves and When They Burn Credits

A clustering key is not an index. It is an instruction to Snowflake to keep the table’s micro-partitions physically sorted by your chosen columns, and to keep re-sorting them as new data lands. The first half of that sentence is why queries get faster. The second half is why your credit consumption grows a line item that never goes away.

I have added clustering keys that cut a dashboard’s scan from 40 minutes of warehouse time a day to four. I have also removed clustering keys that cost more in nightly reclustering than the queries they helped ever cost in scans. The difference was never the feature. It was whether the table’s churn pattern and query pattern justified paying rent on physical order.

This post assumes you know how micro-partitions and pruning work — clustering exists only to make pruning better. If your Query Profile shows good pruning already, close this tab.

What a clustering key actually does

Every table is stored as immutable micro-partitions, roughly 16 MB compressed. Snowflake keeps min/max metadata per column per partition and skips partitions a predicate cannot match. Data loaded in time order is naturally well-clustered by time. Data loaded in time order but queried by customer_id is scattered: every partition contains the full range of customer IDs, min/max overlaps everywhere, and nothing prunes.

Declaring a clustering key tells the automatic clustering service to rewrite partitions in the background so rows with similar key values end up in the same partitions. Overlap shrinks. Pruning starts working for that key.

flowchart LR
  load["Data lands in load order"] --> scatter["Key values scattered across partitions"]
  scatter --> svc["Automatic clustering service rewrites partitions"]
  svc --> sorted["Key ranges compact per partition"]
  sorted --> prune["Predicates prune most partitions"]

Clustering is a background rewrite that buys pruning. The rewrite recurs as long as data churns.

The service is serverless: no warehouse to manage, billed per credit as it works. It never finishes, because every new load and every UPDATE disturbs the order. That is the rent.

Measuring: clustering depth before anything else

Do not add a key on a hunch. Measure how badly clustered the table is for the columns you would cluster by:

select system$clustering_information(
  'analytics.silver.orders',
  '(customer_id)'
);

The JSON that comes back has two numbers I care about. average_depth is how many partitions, on average, overlap a given key value — depth 1 is perfect, depth 800 means a point query on that key touches 800 partitions. The partition_depth_histogram shows the distribution; a long tail of deep partitions on a table you filter constantly is the signature of a table that would benefit.

Then confirm with real queries. Pull the actual scan ratios from the Query Profile or from QUERY_HISTORY for the workload that hurts. If the expensive queries scan 90 percent of partitions and their predicates match a candidate key, you have a case. If they scan 5 percent, clustering will not help no matter what the depth number says.

Choosing the key

The rules that have held up for me:

Order columns by how often they appear in selective predicates, not by intuition about the data model. The key serves WHERE clauses and join filters, nothing else.

Prefer low-to-moderate cardinality. A few thousand to a few million distinct values clusters well. Clustering directly on a UUID is pathological: every partition gets a unique range, churn touches everything, and the reclustering bill explodes. Coarsen high-cardinality columns with an expression:

alter table analytics.silver.orders
  cluster by (to_date(ordered_at), region);

-- High-cardinality key: cluster on a prefix, not the full value
alter table analytics.silver.events
  cluster by (event_date, substring(session_id, 1, 4));

Two or three columns maximum, most selective pattern first. Every column you add multiplies the ways churn can disturb the order.

And check the write path: if the loader can sort data before it lands — an ORDER BY in the dbt incremental model that builds the table, or sorted files into the copy — you may get 80 percent of the benefit with zero reclustering rent. A one-time manual sort is also a legitimate move for a table that no longer churns:

insert overwrite into analytics.silver.orders
select * from analytics.silver.orders
order by to_date(ordered_at), region;

What reclustering costs, in numbers you can read

The automatic clustering service bills serverless credits, and the meter is queryable. Read it before and after you add any key:

select
  table_name,
  to_date(start_time) as day,
  sum(credits_used) as credits,
  sum(num_bytes_reclustered) / power(1024, 4) as tb_reclustered,
  sum(num_rows_reclustered) as rows_reclustered
from snowflake.account_usage.automatic_clustering_history
where start_time > dateadd('day', -30, current_timestamp())
group by 1, 2
order by credits desc;

The pattern to expect: a large one-time cost as the table is initially reorganized, then a steady state proportional to churn. A table that appends in rough key order (clustering by load date, for instance) costs almost nothing to maintain. A table with scattered updates across the whole key range costs the most — UPDATE-heavy tables clustered on a business key are the classic credit furnace.

The comparison that decides everything: credits spent reclustering per month versus warehouse credits saved by better pruning per month. Both are measurable. If you cannot show the second number exceeding the first, suspend it — ALTER TABLE ... SUSPEND RECLUSTER keeps the key definition but stops the spend, which is also the right setting during a large backfill. This is the same discipline as everything in the cost optimization guide: a feature is a trade, and both sides of the trade have a meter.

Clustering vs Search Optimization vs nothing

The decision framework I actually use:

flowchart TD
  start["Slow queries with selective predicates"] --> size{"Table over ~1 TB?"}
  size -->|no| nothing["Do nothing. Fix SQL or accept the scan."]
  size -->|yes| pattern{"Predicate pattern?"}
  pattern -->|"Ranges and moderate-cardinality equality"| ck["Clustering key"]
  pattern -->|"Point lookups on high-cardinality columns"| sos["Search Optimization Service"]
  pattern -->|"Both patterns on the same table"| both["Cluster for ranges plus SOS for lookups"]
  ck --> verify["Measure reclustering credits vs scan savings monthly"]

Size gates the whole decision. Under a terabyte, full scans are usually cheaper than either service.

Clustering wins for range scans and repeated filters on moderate-cardinality columns — dates, regions, tenant IDs. It reorganizes the table itself, so it also speeds up joins that filter on the key.

Search Optimization wins for needle-in-haystack equality lookups on high-cardinality columns — an order ID, an email, a UUID. It builds a separate access path and leaves the table’s physical order alone, so the two coexist on one table without fighting.

Nothing wins more often than either. A well-pruned table in natural load order, queried mostly by recent time ranges, needs no help. Snowflake’s own guidance points clustering at multi-terabyte tables, and my experience agrees: below that, the maintenance rarely pays back.

Pitfalls

Clustering a table under a terabyte. The scans you are saving are already cheap. The service credits are real. Run the math; it usually says no.

Clustering on a UUID or raw timestamp. Maximum cardinality means maximum churn sensitivity. Coarsen with TO_DATE, a substring, or cluster on something else entirely.

Adding the key during a backfill. The service will recluster data you are about to rewrite. Suspend reclustering, finish the backfill, resume.

Keys nobody re-validates. Query patterns drift. The key that matched 2024’s dashboards may serve nothing today while still billing every night. Re-run the depth check and the history query quarterly.

Expecting instant results. Reclustering is gradual and backlogged tables take days to converge. Judge the key after the depth stabilizes, not the first morning.

Treating depth as the goal. Depth is a proxy. The goal is partitions scanned on real queries. A table can hold depth 3 and still not help if your predicates do not use the key.

FAQ

Does a clustering key speed up writes? No. Loads are unaffected at write time; the reorganization happens asynchronously afterward. Heavy write churn makes the key more expensive to maintain, not the writes slower.

Can I cluster a table by more than one access pattern? One key definition per table. If you genuinely have two patterns, cluster for the range pattern and add Search Optimization for the lookup pattern, or materialize a second copy sorted differently and let a dynamic table keep it fresh.

How do I know the key is working? Partitions scanned drops on the target workload. Compare QUERY_HISTORY scan ratios for the same queries before and after convergence, not runtime alone — warehouse contention can mask either direction.

Should I cluster the table my dbt model rebuilds every night? Usually no — add an ORDER BY to the model instead and get the layout for free at build time. Cluster keys earn rent on tables that append and churn, not tables that are rewritten wholesale.

What does suspending reclustering do to existing order? Nothing immediately. The table keeps its current layout and decays as new data lands unsorted. Pruning degrades gradually, and you can resume the service whenever the math flips back.

What this means for data engineers

Clustering keys are a standing purchase of physical order, paid for in serverless credits proportional to churn. Buy that order when a measured workload scans too much on predicates a key would serve, the table is large enough for the scans to matter, and the monthly reclustering bill stays below the monthly scan savings.

Both sides of that inequality are queryable — AUTOMATIC_CLUSTERING_HISTORY on one side, QUERY_HISTORY scan columns on the other. Put them in a scheduled report and the decision renews itself instead of fossilizing.

And keep the null option honest: most tables are fine in load order. The best clustering key is frequently the one you did not add.