Snowflake Clustering Keys: Micro-partition Optimization Guide

Clustering Keys are a performance-tuning feature in Snowflake that maximize the accuracy of partition pruning on large tables. By optimizing the physical data layout (the sort order inside micro-partitions) based on the columns you specify, they dramatically reduce the amount of data scanned per query. The feature is available on Enterprise Edition and above.

Micro-partitions and Partition Pruning

Snowflake automatically splits every table into compressed micro-partitions of 50-500 MB. Each micro-partition is stored in a columnar format, and metadata such as the min, max, and NULL count of every column is tracked in the Cloud Services Layer.

When a query has a WHERE clause, Snowflake consults this metadata and excludes any micro-partition that cannot possibly match. That mechanism is partition pruning. The more physically sorted the data is, the more precise the pruning becomes and the less data is scanned.

Natural Clustering and Clustering Depth

If data is naturally sorted in the order it was inserted — for example, when rows are appended chronologically by a timestamp column — the table is already naturally clustered. In that case, you do not need to define Clustering Keys.

Clustering Depth is a metric that indicates how well-organized the physical data layout of a table is. The smaller the value, the better organized the data and the more efficient the pruning.

-- Check the clustering depth for specific columns
SELECT SYSTEM$CLUSTERING_DEPTH(
  'analytics.orders',
  '(order_date, region)'
);

-- Retrieve detailed clustering information
SELECT SYSTEM$CLUSTERING_INFORMATION(
  'analytics.orders',
  '(order_date, region)'
);

system$clustering_information() Output Fields

Defining and Modifying Clustering Keys

-- Specify Clustering Keys when creating a table
CREATE TABLE analytics.orders (
  order_id    INT,
  order_date  DATE,
  region      VARCHAR(50),
  customer_id INT,
  amount      NUMBER(12,2)
)
CLUSTER BY (order_date, region);

-- Add Clustering Keys to an existing table
ALTER TABLE analytics.orders
  CLUSTER BY (order_date, region);

-- Change (overwrite) the Clustering Keys
ALTER TABLE analytics.orders
  CLUSTER BY (order_date, customer_id);

-- Drop the Clustering Keys (also stops Automatic Clustering)
ALTER TABLE analytics.orders DROP CLUSTERING KEY;

Guidelines for Choosing Columns

Automatic Clustering

Once Clustering Keys are defined, Snowflake's Automatic Clustering service reclusters the data automatically on the Cloud Services Layer. It does not consume your Warehouses and is billed as Serverless Credits instead.

• Automatically monitors and maintains the clustering state after DML operations
• No manual reclustering required
• Automatic Clustering can be suspended and resumed per table

-- Suspend Automatic Clustering
ALTER TABLE analytics.orders SUSPEND RECLUSTER;

-- Resume Automatic Clustering
ALTER TABLE analytics.orders RESUME RECLUSTER;

Monitoring and Cost Management

-- Automatic Clustering credit consumption (last 7 days)
SELECT *
FROM TABLE(INFORMATION_SCHEMA.AUTOMATIC_CLUSTERING_HISTORY(
  DATE_RANGE_START => DATEADD(DAY, -7, CURRENT_DATE()),
  DATE_RANGE_END => CURRENT_DATE(),
  TABLE_NAME => 'ANALYTICS.ORDERS'
));

-- Long-term cost trend via the Account Usage view
SELECT
  table_name,
  SUM(credits_used) AS total_credits,
  SUM(num_bytes_reclustered) AS total_bytes_reclustered
FROM SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY
WHERE start_time >= DATEADD(MONTH, -1, CURRENT_TIMESTAMP())
GROUP BY table_name
ORDER BY total_credits DESC;

Decision Flowchart for Adoption

Is the table billions of rows or larger?
  ├─ NO  → Clustering Keys are unnecessary (natural pruning is enough)
  └─ YES
       ↓
  Check partition pruning efficiency in the Query Profile
       ↓
  Is the number of scanned partitions large relative to the total?
  ├─ NO  → Natural clustering already works → Clustering Keys unnecessary
  └─ YES
       ↓
  Pick 2-4 columns that are filtered frequently in WHERE clauses
       ↓
  Define them with ALTER TABLE ... CLUSTER BY
       ↓
  Monitor cost via AUTOMATIC_CLUSTERING_HISTORY

Best Practices

• Start by diagnosing with the Query Profile: review the partition pruning status and only consider Clustering Keys when there is real room for improvement
• Lean on natural clustering for time-series data: when rows are inserted in chronological order, clustering by the date column is maintained naturally and no extra configuration is needed
• Monitor cost regularly: track credit consumption via AUTOMATIC_CLUSTERING_HISTORY and verify the cost/benefit ratio
• Expression-based Clustering Keys: expressions like TO_DATE(timestamp_col) or MONTH(order_date) are a useful trick to lower the cardinality of a high-cardinality column before clustering on it

Check Your Understanding

Frequently Asked Questions