Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Clustering keys and automatic clustering

Snowflake, BigQuery & Databricks · Snowflake

Clustering keys and automatic clustering

Mediumwarehouses-03
snowflakeclustering-keyautomatic-clusteringcost

Question

When should you define a clustering key on a Snowflake table?

Solution

Define a clustering key on a very large table when your queries filter on a column that is not aligned with how the data was loaded, and pruning is poor. For most tables, you should not define one.

What a clustering key does

It tells Snowflake to keep rows with similar values of the chosen columns together in the same micro-partitions. After that, a filter on those columns touches few partitions. An automatic clustering service reorganises data in the background as new data arrives, and that service uses credits.

When it is worth it

  • The table is large, typically many terabytes. On a 50 GB table, a full scan is cheap, and clustering costs more than it saves.
  • Queries filter or join on columns that differ from the load order, such as customer_id on a table loaded by date.
  • Query profiles show poor pruning (most partitions scanned) on these filters.
  • The table is not rewritten constantly. If most of it changes every day, the service keeps re-clustering and burns credits.

Checking before you commit

SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(customer_id)');

It returns details such as the average depth and a histogram of partition overlap. A low average depth means well clustered. Compare this with how your queries prune in the profile.

Choosing the columns

Pick columns used often in filters. Cardinality should be medium: a column with 3 values (like a status) gives little pruning, and one with a billion unique values (a raw timestamp or an id) is costly to maintain. A common trick is to cluster on an expression that lowers cardinality, such as TO_DATE(event_ts). Put the column with lower cardinality first when you use several.

Costs

Initial clustering of a big table can use a lot of credits, and ongoing maintenance adds more. Always estimate and monitor it. If a key is not helping, you can drop it with ALTER TABLE ... DROP CLUSTERING KEY and suspend the service.

PreviousNext