Clustering Keys and Automatic Clustering in Snowflake

1.When a clustering key is worth its cost

Snowflake generally produces well-clustered data. Over time, though, and especially as DML runs on very large tables, rows may stop clustering well on the dimensions you care about. You could fix this by sorting rows and re-inserting them yourself, but that is cumbersome and expensive. A clustering key does the work for you. It is one or more columns or expressions explicitly designated to co-locate the table's data in the same micro-partitions. A table with a clustering key is called clustered. Materialized views can be clustered as well. Hybrid tables cannot, because their data is always ordered by primary key.

Two signs suggest a table may need a key: queries run slower than expected or have degraded over time, and the table's clustering depth is large. A key can be defined at CREATE TABLE or added later with ALTER TABLE, and it can be changed or dropped at any time. The benefits are better scan efficiency, because Snowflake skips data that doesn't match filter predicates, and better column compression.

Clustering costs credits, both to cluster the data initially and to keep it clustered, so it isn't meant for every table. Snowflake recommends it for tables that meet all of these criteria: a large number of micro-partitions, typically multiple terabytes; queries that are selective or that sort the data; and a high share of queries that benefit from the same key. If your main goal is lower cost, the table should also be queried much more often than it is changed by DML. If DML is unavoidable, group it into large, infrequent batches. Before you cluster, run a representative set of queries to establish a performance baseline.

Sources1

2.Choosing the columns for a clustering key

A key can contain one or more columns or expressions. Snowflake recommends no more than 3 or 4 for most tables, because beyond that costs tend to grow faster than benefits. Choose candidates in this order. First, columns used in selective filters, such as a date column on a fact table queried by date range. Second, if there is room, columns used in join predicates. Columns used in GROUP BY or ORDER BY can help, but less so. If the filter and join columns differ from the sort columns, favor the filter and join columns.

Cardinality decides whether a column works as a key. It needs enough distinct values to allow effective pruning, but few enough that Snowflake can group rows into the same micro-partitions. A Boolean column like IS_NEW_CUSTOMER gives only minimal pruning. A nanosecond timestamp has too many distinct values and makes a poor key. For a high-cardinality column, define the key as an expression that reduces the number of distinct values while keeping the column's original ordering, so each partition's minimum and maximum values still support pruning.

CREATE OR REPLACE TABLE t2 (c1 timestamp, c2 STRING, c3 NUMBER) CLUSTER BY (TO_DATE(C1), substring(c2, 0, 10));

A key can be built from base columns, expressions on base columns, or expressions on paths in VARIANT columns. A key column itself cannot be of type GEOGRAPHY, VARIANT, OBJECT, or ARRAY. Key length also matters. Clustering Classic uses only the first 5 bytes of each key column, while Optima Clustering can use up to 1 KB across all key columns combined. If the leading characters are identical in every row, cluster on a substring that starts after them.

create or replace table t3 (vc varchar) cluster by (SUBSTRING(vc, 5, 5));

Sources1

3.How Automatic Clustering maintains a clustered table

DML statements such as INSERT, UPDATE, DELETE, MERGE, and COPY gradually wear down a table's clustering. Reclustering restores it. Reclustering deletes the affected records and re-inserts them grouped by the clustering key, which creates new micro-partitions. The original micro-partitions are marked as deleted and kept for Time Travel and Fail-safe, so reclustering can add storage costs as well as credit costs.

Automatic Clustering is the Snowflake service that runs all of this for you. You don't monitor clustering state and you don't assign a warehouse. Snowflake evaluates tables as DML happens and reclusters them in the background using resources it manages itself. It reclusters only when the table will benefit, so defining a key doesn't necessarily start reclustering right away. Reclustering doesn't block DML on the table.

In most cases, defining a key is all it takes to enable Automatic Clustering. Clones are the exception. A table created with CREATE TABLE … CLONE starts with Automatic Clustering suspended, even if it is active on the source table. The automatic_clustering column of SHOW TABLES shows whether clustering is ON or OFF, and the TABLES views expose the same status as AUTO_CLUSTERING_ON. To add clustering to a table, you also need USAGE or OWNERSHIP on its schema and database. You can pause and restart clustering with ALTER TABLE. While it is suspended, the table is never reclustered and incurs no clustering credits. Two related actions are easy to confuse. Changing the clustering key resumes clustering, and re-issuing CLUSTER BY with the word LINEAR counts as a change even if the columns are the same. Dropping the key stops all future reclustering.

ALTER TABLE t1  ?  RECLUSTER;

Sources21

4.Optima Clustering versus Clustering Classic

Automatic Clustering comes in two versions. Since September 1, 2026, newly clustered tables use Optima Clustering. Tables that were already clustered stay on Clustering Classic indefinitely. Changing the key with ALTER TABLE doesn't move a table to Optima, and there is no migrate command. Optima clusters new data faster, prioritizes the data that matters most for query performance, and allows longer keys.

The biggest difference is billing, although both versions bill under the same Auto Clustering service type. Optima charges by data ingested: uncompressed GB ingested × per-GB rate × an overlap factor between 0 and 1. Append-only loads clustered by an ingestion timestamp usually have an overlap factor near 0. Updates that change key values usually have a factor of 1. Changing the key triggers a one-time cost based on the table's size at that moment. Suspending Optima stops billing for new data, but resuming it adds a catch-up charge for the data ingested in the meantime. If you want to stop paying for good, drop the key instead.

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Defining a clustering key makes Snowflake recluster the table immediately.Why is that wrong?

   Automatic Clustering reclusters only when the table will benefit, so rows are not necessarily reorganized right after the key is defined.

   Covered in How Automatic Clustering maintains a clustered table

2. 2.A clone of a clustered table inherits active Automatic Clustering from its source.Why is that wrong?

   Cloned tables start with Automatic Clustering suspended, whatever the source table's state.

   Covered in How Automatic Clustering maintains a clustered table

3. 3.Changing the clustering key on a Clustering Classic table moves it to Optima Clustering.Why is that wrong?

   Tables that were already clustered stay on Clustering Classic. An ALTER TABLE key change does not migrate them, and no migrate command exists.

   Covered in Optima Clustering versus Clustering Classic

Practise it for real

Create a clustered table, check its clustering status and clustering information, then suspend and resume Automatic Clustering.

1. 1.Run: CREATE OR REPLACE TABLE t1 (c1 DATE, c2 STRING, c3 NUMBER) CLUSTER BY (c1, c2);

   Why: Defining a clustering key at creation time is all it takes to make the table clustered.

   You should see: The table is created with a clustering key on c1 and c2.

2. 2.Run: SHOW TABLES LIKE 't1';

   Why: SHOW TABLES displays both the key and the Automatic Clustering status.

   You should see: cluster_by shows LINEAR(C1, C2) and automatic_clustering shows ON.

3. 3.Run: SELECT SYSTEM$CLUSTERING_INFORMATION('t1');

   Why: Because t1 has a clustering key, the table name alone is enough.

   You should see: A JSON string with fields such as cluster_by_keys, version, and total_partition_count. The table is still empty, so it has no micro-partitions yet.

4. 4.Run: ALTER TABLE t1 SUSPEND RECLUSTER; then SHOW TABLES LIKE 't1';

   Why: Suspending stops all automatic reclustering on the table.

   You should see: automatic_clustering now shows OFF.

5. 5.Run: ALTER TABLE t1 RESUME RECLUSTER; then SHOW TABLES LIKE 't1';

   Why: Resuming lets Snowflake recluster again when the table would benefit.

   You should see: automatic_clustering shows ON again.