What does CLUSTER BY do to a Snowflake table, and who maintains that ordering?

## What the clause actually declares ```sql create table events ( event_ts timestamp_ntz, tenant_id number, payload variant ) cluster by (to_date(event_ts), tenant_id); ``` This does not build an index, and it does not create partitions in the way a range-partitioned relational table has partitions. It records an *intent* about physical layout: Snowflake should try to arrange the table's micro-partitions so that each one covers a narrow range of `to_date(event_ts)` and, within that, of `tenant_id`. The payoff is pruning — a query filtering on those expressions can exclude most micro-partitions using their recorded min/max metadata. A clustering key can be columns or expressions on columns, and the practical guidance is to keep it short (a few columns), to put the column your queries filter on most selectively first, and to prefer lower-cardinality leading columns — often via an expression like `to_date(ts)` rather than a raw timestamp with microsecond precision. A key so granular that nearly every row is unique gives Snowflake an ordering it can never economically maintain. You add and remove it on a live table: ```sql alter table events cluster by (to_date(event_ts), tenant_id); alter table events drop clustering key; ``` `SHOW TABLES` reports the current clustering key per table, which is the quickest audit of what is declared where. ## Who does the work Once a clustering key exists, **Automatic Clustering** — a Snowflake-managed serverless service — takes responsibility for it. It monitors the table and, when micro-partitions have drifted into overlapping key ranges, rewrites the worst offenders so they again cover narrow, disjoint ranges. Important properties: - It runs in the **background**, on Snowflake-managed compute, not on your virtual warehouse. Your queries do not block on it and you do not schedule it. - It is **incremental and best-effort**. It never promises a fully sorted table; it works toward reducing overlap, and it prioritizes the micro-partitions where rewriting buys the most. - Because micro-partitions are immutable, "reclustering" literally means reading overlapping files and writing new ones — so it produces exactly the write amplification you would expect, and superseded files remain under Time Travel and Fail-safe retention. - Manual reclustering — running a statement yourself to re-sort the table — is no longer the model; Automatic Clustering is how a declared key is maintained. ## What it costs Automatic Clustering consumes **serverless credits**, billed separately from the credits your virtual warehouses burn. The consumption tracks the volume of data it has to rewrite. That leads to the single most important operational fact about the feature: *the cost is driven by how much the table churns, and the benefit is driven by how much the table is queried on the key*. An append-mostly fact table loaded in roughly key order, read by hundreds of dashboards a day, is the ideal case — little to rewrite, large savings. A staging table fully reloaded every hour is the worst case — everything is rewritten every hour, and the reads may not even filter on the key. Monitor it rather than assume: ```sql select start_time, table_name, credits_used from snowflake.account_usage.automatic_clustering_history where table_name = 'EVENTS' order by start_time desc; ``` The same information is available as an `INFORMATION_SCHEMA` table function for recent activity. Compare those credits against the warehouse credits the improved pruning saves; if the first exceeds the second, the key is a net loss. ## Controlling it ```sql alter table events suspend recluster; -- large backfill runs here alter table events resume recluster; ``` Suspending is the standard move around a bulk rewrite: there is no point paying to reorganize data that is about to be replaced. Resume afterwards and let the service converge once, rather than chasing every intermediate state. Dropping the clustering key stops the service entirely and leaves the data as it currently lies — the existing organization is not undone, it simply stops being maintained. ## The mental model to carry into an interview A clustering key is a **standing instruction plus a paid background service**, not a structure. You are buying maintained physical order with credits, in exchange for scanning fewer micro-partitions. That framing immediately produces the right follow-up questions: how much does the table churn, how many queries actually filter on the key, and what did partitions-scanned look like before and after.