When is a Snowflake clustering key worth its reclustering credits, and what are the alternatives?

## Frame it as a trade, not a checkbox A clustering key on Snowflake buys maintained physical order and pays for it in serverless credits. It is only a win when the credits saved on the read side exceed the credits spent on the write side. That reframing produces the whole analysis. **The benefit side** is a function of the read workload: - How many queries filter on the candidate key, how often do they run, and are they on a warehouse that is otherwise idle? - What fraction of the table do they scan today? Pull `partitions_scanned` and `partitions_total` from `SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY` for the top consumers. - What would that fraction become? Selectivity caps the benefit: a filter that legitimately touches 30% of the table cannot be improved much no matter how well organised the files are. **The cost side** is a function of the write workload: - Volume rewritten. Reclustering means reading overlapping micro-partitions and writing new ones, so its credits track bytes moved. - Churn shape. Appends in roughly key order cost little. Scattered updates across the entire history, or a full truncate-and-reload, mean the service reorganises the same data again and again. - Convergence. A table churned faster than it can be reorganised never reaches good depth; you pay continuously for a benefit you never fully receive. ## The profile that pays - Large — enough micro-partitions that skipping most of them is a real saving. Clustering is a multi-terabyte tool; a few-gigabyte table is not worth it. - Read-heavy on one axis — many recurring dashboards or pipelines filtering or joining on the same column. - Append-mostly, and loaded in an order that does *not* already match the key. This is the crucial subtlety: if data arrives in key order, natural clustering already gives you the layout and a declared key adds cost for little gain. - Selective predicates — the queries want a small slice. ## The profiles that do not - Staging or intermediate tables fully replaced each run: everything is rewritten anyway. - Tables under heavy scattered `UPDATE`: the service loses the race. - Small tables, or huge tables queried rarely: the read saving never covers the maintenance. - Tables where queries filter on many different columns: one key cannot serve them all, and adding columns to the key dilutes the leading column that does the real separation. ## The method 1. **Baseline the reads.** From `QUERY_HISTORY`, list the top queries on the table by warehouse time and record their partitions-scanned ratios. 2. **Simulate the key.** `SELECT SYSTEM$CLUSTERING_INFORMATION('db.schema.t', '(to_date(event_ts), tenant_id)')` measures the layout the table would have under a candidate key without declaring it. Compare candidates this way; it costs nothing. 3. **Try it in isolation.** `CREATE TABLE t_test CLONE t` gives a zero-copy clone; declare the key there, let it converge, and run representative queries against it. 4. **Measure both sides.** After a week on the real table, compare `AUTOMATIC_CLUSTERING_HISTORY` credits with the warehouse-credit reduction on the affected queries. 5. **Be willing to reverse.** `ALTER TABLE t DROP CLUSTERING KEY` stops the spend; the current physical layout is untouched, so you keep whatever ordering exists and simply stop maintaining it. ## The alternatives, in the order to consider them **Sort on load.** The cheapest ordering is the one you never pay a service to fix. Writing each batch with an `ORDER BY` on the future key column — `INSERT INTO t SELECT ... ORDER BY event_ts`, or `CREATE TABLE AS SELECT ... ORDER BY ...` for a rebuild — produces micro-partitions with narrow ranges at load time. For an append-only pipeline this can deliver most of the clustering benefit with no reclustering bill. It costs warehouse time on the load, which is usually far less. **Lean on natural clustering.** Data loaded chronologically is already organised by time. Before declaring anything, measure whether the layout you want is already there. **Change the shape of the data.** Splitting a hot recent table from a cold history table, or rebuilding the table periodically with a sorted `CREATE TABLE AS SELECT`, can beat continuous reclustering for a table with a strong recency access pattern. **Reduce the key's cardinality.** If a key is only expensive because it is too granular, an expression such as `to_date(ts)` may make the same idea affordable. **Accept the scan.** On a table queried a few times a day, a full scan on an appropriately sized warehouse can be cheaper than continuous maintenance. Not tuning is a legitimate, defensible outcome. **Consider whether the problem is point lookups at all.** Highly selective needle-in-haystack lookups are served by a different Snowflake feature, the search optimization service, with its own cost profile — a separate decision from clustering, and worth naming as such rather than forcing the clustering key to do that job. ## What a strong answer sounds like Not "cluster large tables", but: here is the read workload, here is the churn, here is the measured layout under a candidate key, here is the credit comparison after a week, and here is the condition under which I would drop it again.