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 ... RECLUSTERor 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
| Action | Syntax Example | Description |
|---|---|---|
| Create table with clustering key | CREATE TABLE t (c1 INT) CLUSTER BY (c1); | Defines clustering key at table creation |
| Add clustering key to existing table | ALTER TABLE t CLUSTER BY (c1); | Sets or changes clustering key |
| Remove clustering key | ALTER TABLE t CLUSTER BY NONE; | Removes clustering key |
| Trigger manual reclustering | ALTER TABLE t RECLUSTER; | Reorganizes data based on clustering key |
| Check clustering depth | SELECT 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.
