There are some really cool details about Snowflake’s clustering. Working with these details can help reduce the cost of clustering. In the last article, I dug into how aligning a clustering key with data arrival patterns can help you reduce cost. This week I’d like to talk about the costs of clustering in the first place, or changing the clustering key for a table.
Foundational Clustering Concepts
Clustering is not the thing that is required for improving performance. Performance improvements related to clustering come from reducing the range between the minimum and the maximum values recorded for key columns on each micro-partition. At query execution time, the minimum and the maximum values are used to identify micro-partitions that cannot contain any data needed for the query – a process known as pruning. Using auto-clustering is one way to arrange data such that the range between the minimum and the maximum is as narrow as possible, and therefore the most micropartitions can be eliminated. Another way to achieve that same state is to load data in order and then not modify it.
Not modifying existing data is a tall order for most systems, and that is the sweet-spot for automatic clustering. Automatic clustering will kick in as data changes and rearrange data as needed to maintain a decent clustering state.
Function, Help Me
There is a function to estimate what clustering a table or changing a clustering key on a table might cost. I ran it on the tables created in my last blog entry. The parameters I ran it with would reverse the order of the columns in the clustering key.
SELECT SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS('clustering_demo.public.lineitem_date_first', '(l_shipmode, l_shipdate)');The output is presented as json, and looks like this:
{
"reportTime": "Tue, 19 May 2026 00:13:55 GMT",
"clusteringKey": "LINEAR(l_shipmode, l_shipdate)",
"initial": {
"unit": "Credits",
"value": 0.320559358,
"comment": "Total upper bound of one time cost"
},
"maintenance": {
"comment": "Unable to produce maintenance cost estimate because table does not have enough substantial DMLs in the past 7 days."
}
}This tells us that changing the order of the columns in the clustering key would cost about a third of a credit for this little table. In this case, we don’t have enough recent data to estimate an ongoing clustering cost.
Experimentation
Let’s experiment with data here to illustrate some points. I’m going to create a couple of tables to play with, based on data in SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM. This is smaller and simpler than most real-world scenarios, but can illustrate some points.
Setup
Here is the syntax I used to create these two tables in my free trial account:
CREATE OR REPLACE TABLE LINEITEM_CLUSTERING_COST_UNORDERED
AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM;
CREATE OR REPLACE TABLE LINEITEM_CLUSTERING_COST_ORDERED
AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM ORDER BY l_shipdate, L_shipmode;One table is created using an ORDER BY in the CTAS (Create Table AS), and the other is not created with any specific ordering. The CTAS with the ORDER BY took a minute and 30 seconds, while the CTAS without it took 59 seconds. There is a cost in query duration of including the ORDER BY, and it increases with table size.
In fact, there is a size above which a single CTAS with an ORDER BY is not reasonable to run, even on the largest available warehouse. That size seems to lie somewhere between 250,000 micro-partitions and 1,000,000 micro-partitions. Where in that range depends on factors like the row width and how well data can be compressed. Often the largest warehouse sizes are used for tables near 250,000 micro-partitions. The inflection point is often remote spilling. Avoiding remote spilling makes it worthwhile to do the sort in one go.
Looking at the clustering state of the unordered table, as obtained using SELECT SYSTEM$CLUSTERING_INFORMATION('clustering_demo.public.LINEITEM_CLUSTERING_COST_UNORDERED', '(l_shipdate,l_shipmode)');, we see:
{
"cluster_by_keys" : "LINEAR(l_shipdate,l_shipmode)",
"total_partition_count" : 4893,
"total_constant_partition_count" : 0,
"average_overlaps" : 3477.7486,
"average_depth" : 2521.3626,
"partition_depth_histogram" : {
"00000" : 0,
"00001" : 0,
"00002" : 0,
"00003" : 0,
"00004" : 0,
"00005" : 0,
"00006" : 0,
"00007" : 0,
"00008" : 0,
"00009" : 0,
"00010" : 0,
"00011" : 0,
"00012" : 0,
"00013" : 0,
"00014" : 1,
"00015" : 0,
"00016" : 0,
"00032" : 1,
"00064" : 1,
"00128" : 1,
"00256" : 2,
"00512" : 13,
"01024" : 85,
"02048" : 462,
"04096" : 4327
},
"clustering_errors" : [ ]
}The numbers here tell us that the data is not well clustered over this clustering key. We see most of the micropartitions counted in the largest bucket, an average depth at nearly half of the cardinality of micro-partitions in the table, and a very high number of overlaps.
Looking at the same output for the ordered table, we see:
{
"cluster_by_keys" : "LINEAR(l_shipdate,l_shipmode)",
"total_partition_count" : 4680,
"total_constant_partition_count" : 59,
"average_overlaps" : 2.8692,
"average_depth" : 2.8942,
"partition_depth_histogram" : {
"00000" : 0,
"00001" : 56,
"00002" : 407,
"00003" : 4193,
"00004" : 24,
"00005" : 0,
"00006" : 0,
"00007" : 0,
"00008" : 0,
"00009" : 0,
"00010" : 0,
"00011" : 0,
"00012" : 0,
"00013" : 0,
"00014" : 0,
"00015" : 0,
"00016" : 0
},
"clustering_errors" : [ ]
}In this output, we see that the average depth is just 2.8942 and most of our micro-partitions are in the 00003 bucket, which is far better than the 04096 bucket from the last one.
This is not surprising. All we’ve proven so far is that using an ORDER BY on a CTAS clusters the table. Let’s dig further and estimate what it would take to cluster each of these tables using auto-clustering.
Estimating Cost
The function we use here is SELECT SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS('clustering_demo.public.LINEITEM_CLUSTERING_COST_UNORDERED', '(l_shipdate,l_shipmode)'). This function takes a bit to run. It uses a sampling methodology, so when it really matters, you should run it multiple times and average the results.
The results here look like this for the unordered table:
{
"reportTime": "Tue, 19 May 2026 01:28:15 GMT",
"clusteringKey": "LINEAR(l_shipdate,l_shipmode)",
"initial": {
"unit": "Credits",
"value": 5.781735684,
"comment": "Total upper bound of one time cost"
},
"maintenance": {
"comment": "Unable to produce maintenance cost estimate because table was created within the past 7 days and therefore does not have sufficient data retention."
}
}Our function estimates it would cost about 5.8 credits to cluster the unordered table.
The results on the ordered table are interesting in a different way:
{
"warning": "Was not able to retrieve clustering sample. Data in table may already be well clustered."
}This is common to get if the table is already well-clustered. However, even if a table is perfectly clustered, auto-clustering still has some analysis work to do and may take a pass or two through the data, so you should never assume the cost will be zero.
Adding Clustering Keys
Let’s actually add clustering keys and see what clustering costs we get from doing that.
ALTER TABLE clustering_demo.public.LINEITEM_CLUSTERING_COST_UNORDERED CLUSTER BY (l_shipdate,l_shipmode);
ALTER TABLE clustering_demo.public.LINEITEM_CLUSTERING_COST_ORDERED CLUSTER BY (l_shipdate,l_shipmode);These commands return right away because auto-clustering is a background service.
Looking at the work and credits billed for automatic clustering is easy with this query:
select *
from table(information_schema.automatic_clustering_history(
date_range_start=>dateadd(D, -7, current_date),
date_range_end=>current_date,
table_name=>'clustering_demo.public.LINEITEM_CLUSTERING_COST_UNORDERED'));Clustering is a background service, and it may take a while for it to catch up, depending on the table size, so running this right after adding or changing a clustering key doesn’t make sense. I’ve even seen tables that take days to get to an initially clustered state, depending on their size and table activity.
Running this for the ordered table shows only the smallest fraction of a credit on automatic clustering:
We see a different story on the unordered table:
The clustering cost here was actually over our estimate of 5.8 credits at about 6.6 credits
Note also that the number of rows there exceeds the count of rows in the table. The table has 5,999,989,709 rows. The count from the clustering data above sums to 15,885,544,824 – nearly three times as much.
This is because each row likely needs to be moved more than once. Automatic clustering works in batches of micro-partitions, making the clustering state incrementally better with each pass. The numbers tell us that on average each row had to be moved three times during the automatic clustering.
Final Clustering State
Here is a detail that some miss. Even when automatic clustering has been allowed to “complete” its work, the clustering state of the unordered table after clustering may not be as good as the table we explicitly ordered. This depends on a number of factors and is not always true or not always all that bad:
{
"cluster_by_keys" : "LINEAR(l_shipdate,l_shipmode)",
"total_partition_count" : 4681,
"total_constant_partition_count" : 0,
"average_overlaps" : 10.8584,
"average_depth" : 6.8513,
"partition_depth_histogram" : {
"00000" : 0,
"00001" : 0,
"00002" : 2,
"00003" : 31,
"00004" : 106,
"00005" : 968,
"00006" : 1095,
"00007" : 984,
"00008" : 744,
"00009" : 388,
"00010" : 216,
"00011" : 116,
"00012" : 31,
"00013" : 0,
"00014" : 0,
"00015" : 0,
"00016" : 0
},
"clustering_errors" : [ ]
}The result here is better than not clustering by a long shot, but not nearly as good as the ORDER BY methodology.
Re-clustering Existing Tables
If you have an existing table that is small enough to run an ORDER BY without remote spilling, then it makes sense to use INSERT … OVERWRITE methodology. That statement might look something like this:
INSERT OVERWRITE INTO LINEITEM_CLUSTERING_COST_UNORDERED_OVERWRITE
SELECT * FROM LINEITEM_CLUSTERING_COST_UNORDERED_OVERWRITE ORDER BY l_shipdate, L_shipmode;Without concurrency, this takes about the same amount of time as the CTAS with ORDER BY. It also allows reads against the table while it is in process, and obtains proper locks to work with other connections that may be concurrently updating the table. The resulting clustering state is just as good as the CTAS with ORDER BY:
{
"cluster_by_keys" : "LINEAR(l_shipdate,l_shipmode)",
"total_partition_count" : 4642,
"total_constant_partition_count" : 32,
"average_overlaps" : 2.9897,
"average_depth" : 2.9601,
"partition_depth_histogram" : {
"00000" : 0,
"00001" : 26,
"00002" : 359,
"00003" : 4031,
"00004" : 226,
"00005" : 0,
"00006" : 0,
"00007" : 0,
"00008" : 0,
"00009" : 0,
"00010" : 0,
"00011" : 0,
"00012" : 0,
"00013" : 0,
"00014" : 0,
"00015" : 0,
"00016" : 0
},
"clustering_errors" : [ ]
}Large Tables
For tables that cannot fit an ORDER BY in memory and local storage even within the largest available warehouse size, there are other possible techniques to recluster efficiently. The main one is to identify non-overlapping ranges of the table. Then use CTAS with an ORDER BY for the first range, and INSERT with an ORDER BY for the remaining ranges. Don’t get me wrong, this can be a fair amount of work, and isn’t itself free, but tends to be cheaper than simply altering the table to change the clustering key. This can be
Pitfalls
One of the banes of my existence currently is CoCo recommending adding or changing a clustering key with a simple alter table statement. Most of the time, CoCo is right that clustering is needed. Sometimes it is right about the clustering key, but I’ve never yet seen it recommend an alternate methodology for clustering.
Summary
Hopefully the information here can help you achieve well-clustered tables more efficiently and avoid some of the more common expensive mistakes when working with clustering.
