Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Use Clustering Key in Snowflake for Better Query Performance

In Snowflake, use a CLUSTER BY clause when creating or altering a table to define a clustering key, which organizes data to speed up queries. Clustering keys help Snowflake prune data efficiently by grouping related rows together based on specified columns.
📐

Syntax

The CLUSTER BY clause specifies one or more columns as the clustering key when creating or altering a table. It looks like this:

  • CREATE TABLE table_name (columns) CLUSTER BY (column1, column2, ...);
  • ALTER TABLE table_name CLUSTER BY (column1, column2, ...);

This tells Snowflake to organize data internally based on these columns to improve query pruning.

sql
CREATE TABLE my_table (
  id INT,
  category STRING,
  created_date DATE
) CLUSTER BY (category, created_date);
💻

Example

This example creates a table with a clustering key on category and sale_date. It demonstrates how to define clustering keys to optimize queries filtering on these columns.

sql
CREATE OR REPLACE TABLE sales_data (
  sale_id INT,
  category STRING,
  sale_date DATE,
  amount NUMBER
) CLUSTER BY (category, sale_date);

-- Insert sample data
INSERT INTO sales_data VALUES
  (1, 'Electronics', '2024-01-01', 100),
  (2, 'Clothing', '2024-01-02', 50),
  (3, 'Electronics', '2024-01-03', 200);

-- Query filtering on clustering keys
SELECT * FROM sales_data WHERE category = 'Electronics' AND sale_date >= '2024-01-01';
Output
SALE_ID | CATEGORY | SALE_DATE | AMOUNT --------|-------------|------------|-------- 1 | Electronics | 2024-01-01 | 100 3 | Electronics | 2024-01-03 | 200
⚠️

Common Pitfalls

Common mistakes when using clustering keys include:

  • Choosing columns with low cardinality (few unique values), which reduces pruning benefits.
  • Not running ALTER TABLE ... RECLUSTER or relying on automatic reclustering when data changes, causing clustering to degrade.
  • Using clustering keys on columns rarely used in filters, which wastes resources.

Always pick columns frequently used in WHERE clauses and monitor clustering depth.

sql
/* Wrong: clustering on low-cardinality column */
CREATE TABLE wrong_cluster (
  id INT,
  status STRING
) CLUSTER BY (status);  -- 'status' has few unique values

/* Right: clustering on higher-cardinality column */
CREATE TABLE right_cluster (
  id INT,
  user_id INT
) CLUSTER BY (user_id);
📊

Quick Reference

ActionSyntax ExampleDescription
Create table with clustering keyCREATE TABLE t (c1 INT) CLUSTER BY (c1);Defines clustering key at table creation
Add clustering key to existing tableALTER TABLE t CLUSTER BY (c1);Sets or changes clustering key
Remove clustering keyALTER TABLE t CLUSTER BY NONE;Removes clustering key
Trigger manual reclusteringALTER TABLE t RECLUSTER;Reorganizes data based on clustering key
Check clustering depthSELECT SYSTEM$CLUSTERING_DEPTH('t');Measures clustering effectiveness
✅

Key Takeaways

Use CLUSTER BY to define columns that group related data for faster queries.
Choose clustering keys on columns often used in filters with many unique values.
Monitor and maintain clustering with RECLUSTER to keep performance benefits.
Avoid clustering on low-cardinality or rarely filtered columns.
You can add, change, or remove clustering keys using ALTER TABLE commands.