Clustering keys — when they help, when they cost
The problem
A large table used to prune well on event_date and now it does not. Queries that filter the last few days seem to scan far more micro-partitions than they used to. Someone suggests “just add a clustering key.” Sometimes that is the right move. Official Snowflake documentation is equally clear that clustering keys are not intended for all tables, because you pay to cluster the data initially and to keep it clustered.
This page is a reading of official guidance, not a benchmark. No credit figures, runtimes, or “speedup” numbers are claimed here.
What a clustering key is
Official docs: a clustering key is a subset of columns (or expressions) you designate so that similar rows are co-located in the same micro-partitions. A table with a clustering key defined is a clustered table. You can set the key at CREATE TABLE time or later with ALTER TABLE … CLUSTER BY. You can also drop it.
-- Snowflake syntax from official documentation (not executed)
ALTER TABLE events CLUSTER BY (event_date, account_id);
SHOW TABLES LIKE 'events'; -- cluster_by and automatic_clustering appear in the output
ALTER TABLE events DROP CLUSTERING KEY;
Automatic clustering then maintains the key. Official docs say that after a key is defined, future maintenance is performed automatically, unless you drop or change the key. Changing a key does not rewrite existing records until Snowflake reclusters the table.
Hybrid tables cannot have a clustering key; official docs say they are always ordered by primary key.
When official docs say clustering is worth considering
Indicators they list:
- Queries on the table are slower than expected, or have noticeably degraded over time.
- The clustering depth of the table is large.
Benefits they list for very large tables:
- Better scan efficiency because unchanged micro-partitions can be skipped when the filter matches the key.
- Better column compression, especially when other columns are strongly correlated with the key.
- Less manual “sort and reload the table” work.
They also say queries typically benefit when they filter or sort on the clustering key (WHERE, ORDER BY, GROUP BY, and some joins).
And they say clustering is optimal when either you need the fastest possible response regardless of cost, or the query-performance improvement offsets the credits used to cluster and maintain the table. That is a judgment you have to make with your query history and your billing. This site will not invent a threshold.
SYSTEM$CLUSTERING_INFORMATION is the official function for inspecting clustering depth and related stats. Use it, and the official clustering guide, rather than adding a key “just in case.”
The gotcha
A clustering key on a table that is not very large, or on a column that almost every query ignores, is a standing maintenance cost with little pruning benefit. Official docs lead with this: do not treat clustering as a default table option.
A key that does not match how the table is loaded will fight automatic clustering. If you cluster on event_date but each load randomly updates old dates across the whole history, reclustering has more work to do. Official docs do not require you to pre-sort every load; they do require you to consider DML patterns when you choose a key.
Expressions in a key for an already-populated table are restricted. Official docs tell you to check SHOW FUNCTIONS … and the valid_for_clustering column before you use a function in a key.
Materialized views can be clustered too; official docs say the rules are generally the same, with a few extra tips on the materialized-view page.
When not to use this
- The table is small, or it is still well clustered naturally from insert order.
- Queries do not filter or sort on a stable, reasonably selective set of columns.
- You have not looked at clustering depth or actual slow queries — only at a feeling that “large tables should be clustered.”
- The table is a hybrid table (not supported).
- You were about to cluster on a very high-cardinality column that will not prune usefully, or on a column that is updated across the whole table every run.
This page is a summary of official Snowflake clustering documentation. No clustering statement was executed, and no performance or credit measurements were made.