Snowflake Clustering Keys: How to Pick the Right One for Large Tables

When teams come to us with Snowflake performance or cost issues, one of the most common culprits we find is a missing or mismatched clustering key. It’s not glamorous, and it’s rarely the first thing people check when troubleshooting slow queries. But it’s often the biggest opportunity for improvement.

Here’s the typical pattern. Someone creates a table in Snowflake, loads some data, and everything works fine initially. Queries run fast enough, costs look reasonable, and the team moves on to the next thing. Six months later, the table has grown to multiple terabytes, queries are slower, and compute costs are climbing. Everyone assumes this is just the natural cost of growth.

Often, what’s actually happening is that the data isn’t clustered the way it’s queried, so Snowflake can’t skip much of it. And this one grows with the table: every new micro-partition that can’t be pruned is one more that every query has to scan.

What Clustering Actually Does

When you load data into Snowflake, it’s automatically organized into micro-partitions: compressed, columnar storage units that each hold between 50 and 500 MB of uncompressed data. Snowflake maintains metadata about each micro-partition, including the min/max values for each column, the number of distinct values, and other statistics.

When you run a query, Snowflake’s optimizer checks this metadata to figure out which micro-partitions it can skip entirely. This is called pruning, and it’s one of the most powerful optimization techniques available in Snowflake. If your WHERE clause filters on a date column, and Snowflake can tell that certain micro-partitions hold completely different dates, those partitions get pruned. They’re never read, so the query finishes faster and uses less warehouse time.

Pruning works best when your data is clustered the way you filter it. If the values you filter on are spread across hundreds or thousands of micro-partitions, Snowflake can’t skip many of them. You end up scanning most of the table even when you only need a small slice of it.

How Snowflake Organizes Data by Default

By default, Snowflake clusters your data based on insertion order. If you load data chronologically, like event logs or transaction records that arrive in time sequence, this works well. Your data ends up naturally clustered by timestamp, and queries with date filters prune efficiently.

Not every table stays that way. Continuous loading with Snowpipe can scatter rows when data arrives out of order. Updates and deletes can spread related rows across different micro-partitions. And some data just doesn’t arrive in an order that matches how you query it.

Snowflake doesn’t define a clustering key for you, and that’s deliberate. Automatic clustering consumes credits, so it’s opt-in per table, and the right key depends on how you query the data. Once you define a key, the automatic clustering service maintains it. Picking it is your call.

When Clustering Actually Matters

Not every table needs explicit clustering. If your table is under a few hundred gigabytes and queries run fast, natural clustering is probably fine.

Once you get into the multi-terabyte range, clustering starts to matter a lot. On one multi-terabyte table, I’ve seen adding the right key take a query from 20 minutes to 2 seconds, because it went from scanning about 300,000 micro-partitions to 4.

The typical threshold is around 1 TB, but I’ve seen clustering make a meaningful difference on tables as small as 100 to 200 GB when the query patterns are right.

How to Choose a Clustering Key

This is where most people get stuck. The answer isn’t complicated, but it does require looking at how your data is actually queried.

Start with your query patterns. Look at QUERY_HISTORY for the past 30 to 60 days and find the most expensive or most frequent queries against the table. Which columns show up in WHERE clauses again and again? Those are your candidates.

This query pulls the heaviest recent scans against a table, along with how much of the table each one read:

SELECT   query_text,   partitions_scanned,   partitions_total,   ROUND(partitions_scanned / NULLIF(partitions_total, 0) * 100, 1) AS pct_scanned,   total_elapsed_time / 1000 AS elapsed_seconds FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE query_text ILIKE '%analytics.events%'   AND start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())   AND partitions_total > 0 ORDER BY partitions_scanned DESC LIMIT 50;

Queries with a high pct_scanned and a selective filter are the ones a clustering key can help most. This is also where Workload Intelligence becomes useful: knowing which queries drive your compute, and which columns they filter on, lets you choose a key from evidence instead of guessing.

The best clustering keys share a few characteristics:

High Enough Cardinality

A column like status with only 3-4 values won’t enable much pruning. You need enough distinct values to meaningfully separate your data.

Low Enough Cardinality

On the flip side, a column with millions or billions of unique values (like a UUID) can still prune well, but in most cases it becomes expensive to maintain when data changes often. If the column you filter on has very high cardinality, cluster on an expression that reduces it. For a timestamp, that usually means the date:

ALTER TABLE analytics.events CLUSTER BY (TO_DATE(event_ts));

Used in Selective Filters

If most queries filter on the column and typically need only a small percentage of the data, clustering will help a lot.

Relatively Stable

Clustering key values that change often (through updates) cost more to maintain, because Snowflake has to recluster the affected data each time.

For time-series data, date columns are usually excellent choices. For user-facing applications, customer_id or user_id often work well. For multi-tenant systems, tenant_id is typically a strong pick.

Multi-Column Clustering Keys

You can cluster on multiple columns, but order matters a lot. Snowflake clusters hierarchically: first by the first column, then within each group by the second column, and so on.

If you cluster on (event_date, customer_id), queries filtering only on event_date will prune well. Queries filtering only on customer_id will scan most of the table. The first column in your clustering key is by far the most important.

ALTER TABLE analytics.events CLUSTER BY (TO_DATE(event_ts), customer_id);

General rule: put lower-cardinality columns first, but only if they’re actually useful for pruning. A status column with 3 values is technically lower cardinality than a date column, but if 90% of your queries filter by date, date should come first.

The Cost of Clustering

Defining a clustering key isn’t free. Snowflake’s automatic clustering service reorganizes your data in the background to maintain clustering as new data arrives or existing data changes. Understanding how Snowflake pricing works helps you decide where that’s worth it.

Snowflake now runs two versions of automatic clustering. Tables clustered since September 1, 2026 use Optima Clustering, billed on the volume of data you ingest. Tables clustered earlier stay on Clustering Classic, billed on serverless compute. You can check which version a table uses in SYSTEM$CLUSTERING_INFORMATION or the VERSION column of AUTOMATIC_CLUSTERING_HISTORY.

For tables that are queried frequently but updated infrequently, the performance gains usually dwarf the clustering costs. On Classic tables with heavy, continuous DML, reclustering can run often enough that its cost outweighs the pruning gain.

You can track clustering spend per table in AUTOMATIC_CLUSTERING_HISTORY:

SELECT   database_name,   schema_name,   table_name,   SUM(credits_used) AS credits_30d,   SUM(num_bytes_reclustered) / POWER(1024, 3) AS gb_reclustered_30d FROM SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP()) GROUP BY 1, 2, 3 ORDER BY credits_30d DESC;

Start with one or two high-value tables, measure the impact, and expand from there. This is part of a broader approach to monitoring Snowflake performance.

If a table’s reclustering cost stops paying for itself, you can pause it without dropping the key:

ALTER TABLE analytics.events SUSPEND RECLUSTER; -- and later ALTER TABLE analytics.events RESUME RECLUSTER;

How to Check Your Current Clustering

Before you define a clustering key, check how well your table is currently clustered with SYSTEM$CLUSTERING_INFORMATION:

-- Current clustering against the table's defined key SELECT SYSTEM$CLUSTERING_INFORMATION('analytics.events');  -- Test a candidate key without defining it SELECT SYSTEM$CLUSTERING_INFORMATION('analytics.events', '(TO_DATE(event_ts), customer_id)');

This returns JSON with metrics like average_depth (lower is better; the minimum is 1, and what counts as good depends on table size, so compare it against your own baseline over time) and average_overlaps (how many micro-partitions have overlapping value ranges).

The useful part of the second form is that you can test different clustering strategies without implementing them. Try different column combinations and see which ones show the best clustering metrics.

Where Clustering Ends and Warehouse Optimization Starts

Clustering reduces how much data a query reads. Warehouse sizing decides how much compute you pay for while it reads.

Keebo works on that second layer, adjusting warehouse size and suspend settings as workloads change, so the gains from good clustering show up as lower spend. Those decisions need to happen continuously, not once a quarter during an optimization review.

The Bottom Line

If you run multi-terabyte tables that are filtered the same way most of the time, an explicit clustering key is usually worth testing. The payoff shows up as fewer micro-partitions scanned per query.

Start by identifying your 3 to 5 largest, most-queried tables. Analyze their query patterns. Run SYSTEM$CLUSTERING_INFORMATION to understand the current state. Pick a clustering key that matches how the data is actually used. Then monitor the impact in QUERY_HISTORY and AUTOMATIC_CLUSTERING_HISTORY.

On large tables with a consistent filter pattern, the right key can cut the micro-partitions a query scans to a small fraction of the table. Measure it on one table before rolling it out.

Snowflake’s clustering keeps your data organized for pruning. Keebo keeps the warehouses that query it sized to the work.