Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Use Cluster Key in Snowflake: Syntax and Examples

In Snowflake, use a CLUSTER BY clause to define a cluster key on a table, which organizes data to improve query speed on large datasets. You specify one or more columns as the cluster key when creating or altering a table with CLUSTER BY (column1, column2).
📐

Syntax

The CLUSTER BY clause defines one or more columns as the cluster key to organize data physically in the table. It can be used during table creation or by altering an existing table.

  • CREATE TABLE ... CLUSTER BY (columns): Defines cluster key at table creation.
  • ALTER TABLE ... CLUSTER BY (columns): Adds or changes cluster key on existing table.
sql
CREATE TABLE table_name (
  column1 TYPE,
  column2 TYPE,
  ...
)
CLUSTER BY (column1, column2);
💻

Example

This example creates a table with a cluster key on the customer_id column to speed up queries filtering by that column.

sql
CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  amount NUMBER
)
CLUSTER BY (customer_id);

-- Query that benefits from clustering
SELECT * FROM orders WHERE customer_id = 12345;
Output
Query runs faster on large data because data is organized by customer_id.
⚠️

Common Pitfalls

Common mistakes when using cluster keys include:

  • Choosing columns with low cardinality (few unique values), which reduces clustering effectiveness.
  • Not reclustering data after large inserts or updates, causing clustering to degrade.
  • Expecting clustering to improve performance on small tables where it adds overhead.

Always monitor clustering depth and consider using RECLUSTER or automatic clustering features.

sql
/* Wrong: clustering on low cardinality column */
CREATE TABLE sales (
  sale_id INT,
  region STRING,
  amount NUMBER
)
CLUSTER BY (region);  -- region has few unique values

/* Right: clustering on high cardinality column */
CREATE TABLE sales (
  sale_id INT,
  customer_id INT,
  amount NUMBER
)
CLUSTER BY (customer_id);
📊

Quick Reference

ActionSyntax ExampleNotes
Create table with cluster keyCREATE TABLE t (id INT) CLUSTER BY (id);Defines clustering at creation
Alter table to add cluster keyALTER TABLE t CLUSTER BY (id);Adds or changes cluster key
Remove cluster keyALTER TABLE t CLUSTER BY NONE;Removes clustering
Use automatic clusteringALTER TABLE t SET AUTOMATIC_CLUSTERING = TRUE;Snowflake manages clustering automatically
✅

Key Takeaways

Use CLUSTER BY to define columns that organize data physically for faster queries.
Choose high-cardinality columns as cluster keys for best performance.
Recluster data after large changes to maintain clustering benefits.
Avoid clustering on small tables where overhead outweighs benefits.
Snowflake supports automatic clustering to simplify maintenance.