Auto-Clustering Costs More When You Ignore How Data Arrives

Choosing a clustering key isn’t just about which columns or functions to include — it’s about how your data arrives. In most real-world systems, data lands in approximate or exact date order. That arrival pattern interacts with your clustering key in ways that significantly affect both auto-clustering cost and query performance.

This article assumes you have the basics of clustering down pat. I have absolutely written articles on the basics of clustering, and choosing a clustering key. From official Snowflake channels, by me, check out these articles, if you’re not already familiar:
Snowflake Clustering Demystified
A Data-Driven Approach to Choosing a Clustering Key in Snowflake
An Example of Choosing a Clustering Key in Snowflake
Quickstart: A Data-Driven Methodology for Choosing a Snowflake Clustering Key

With all of that content, there are still aspects of clustering keys I haven’t talked about. Clustering is a system that I have learned to reason in and to describe to customers. The demonstration in this article can be performed in a free trial account, though it did use up more than $100 of the credits included in a free trial when I ran it. I absolutely used (a free trial of) Cortex Code to design this demonstration.

Goals

The goal of this limited demonstration is to simulate the arrival of data on a time-based dimension, and then to look at two different potential orders of columns in a clustering key. The two columns are a time-based dimension and a category-based dimension. The question we’re looking to answer here is whether it is cheaper and more efficient to have the time-based dimension first, or the category-based dimension. The category-based dimension is lower in cardinality, and conventional wisdom often says to put the lower-cardinality dimension first. I would argue in a number of cases that is not the right decision.

The query workload would be a major consideration for decisions in this space in the real world. I’m actually simulating two conversations I’ve had with Snowflake customers in the past month, and a conversation that I frequently end up having. In these scenarios, the user had two columns for their clustering key, and had chosen to put the category dimension first in the clustering key. They were both concerned over how expensive clustering was for them. In both cases, every query in their workload included predicates for BOTH the category column and the time-based column. The requirements are not always that clear and well-defined.

Setup

The setup here involves taking one of the standard tables in the SNOWFLAKE_SAMPLE_DATA database and feeding it into another couple of tables with different clustering keys on a periodic basis. In my case, I ran this loading process over the course of several hours. This simulates the very common pattern of time-based arrival of data. The setup looks like this:

-- ============================================================================
-- CLUSTERING DEMO: Impact of Clustering Key Column Order on Auto-Clustering
-- ============================================================================
-- This demo shows how the order of columns in a clustering key affects:
--   1) The amount of work (credits) auto-clustering must do
--   2) The resulting clustering quality (depth, overlaps)
--
-- Scenario 1 (LINEITEM_CAT_FIRST):  CLUSTER BY (L_SHIPMODE, L_SHIPDATE)
-- Scenario 2 (LINEITEM_DATE_FIRST): CLUSTER BY (L_SHIPDATE, L_SHIPMODE)
--
-- Data arrives chronologically by month, which naturally aligns with the
-- date column. When date is FIRST in the key, new data lands in already-
-- semi-sorted micro-partitions. When category is FIRST, auto-clustering
-- must rearrange far more data to achieve good clustering.
-- ============================================================================

USE ROLE ACCOUNTADMIN;

-- 1. Create the demo database
CREATE OR REPLACE DATABASE CLUSTERING_DEMO 
  COMMENT = 'Demo: impact of clustering key column order on auto-clustering cost and quality';

USE DATABASE CLUSTERING_DEMO;
USE SCHEMA PUBLIC;

-- 2. Create the two scenario tables (empty, with different clustering keys)
CREATE OR REPLACE TABLE LINEITEM_CAT_FIRST 
  CLUSTER BY (L_SHIPMODE, L_SHIPDATE)
  COMMENT = 'Scenario 1: categorical column first in clustering key'
AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.LINEITEM WHERE 1=0;

CREATE OR REPLACE TABLE LINEITEM_DATE_FIRST 
  CLUSTER BY (L_SHIPDATE, L_SHIPMODE)
  COMMENT = 'Scenario 2: date column first in clustering key'
AS
  SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.LINEITEM WHERE 1=0;

-- 3. Create the logging table to track clustering quality over time
CREATE OR REPLACE TABLE CLUSTERING_LOG (
  LOG_TS                          TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  TABLE_NAME                      VARCHAR(100),
  PHASE                           VARCHAR(30),    -- BEFORE_INSERT, AFTER_INSERT, POST_LOAD_HOUR_N
  BATCH_NUM                       INT,
  BATCH_MONTH                     DATE,
  ROWS_IN_BATCH                   INT,
  TOTAL_ROWS_LOADED               INT,
  TOTAL_PARTITION_COUNT           INT,
  TOTAL_CONSTANT_PARTITION_COUNT  INT,
  AVERAGE_OVERLAPS                FLOAT,
  AVERAGE_DEPTH                   FLOAT,
  PARTITION_DEPTH_HISTOGRAM       VARIANT
);

-- 4. Verify setup
SHOW TABLES LIKE 'LINEITEM%' IN SCHEMA CLUSTERING_DEMO.PUBLIC;
-- Confirm: both tables show auto_clustering = ON, correct cluster_by keys, 0 rows.

Running

I used a stored procedure to actually simulate time-based arrival of data here, because I wanted to run this for many hours. The stored procedure also records performance and cost data about clustering and logs it to a table so that I can then generate charts of that over time. The stored procedure definition looks like this:

-- ============================================================================
-- STORED PROCEDURE: LOAD_AND_MONITOR
-- ============================================================================
-- Loads 84 monthly batches chronologically into both tables with 5-min waits
-- between batches. Snapshots SYSTEM$CLUSTERING_INFORMATION before and after
-- each insert. After all 84 batches, monitors hourly for 12 more hours.
--
-- USAGE:
--   CALL CLUSTERING_DEMO.PUBLIC.LOAD_AND_MONITOR(300);  -- 300 seconds = 5 min wait
--
-- Total runtime: ~7 hours (84 batches × 5 min) + 12 hours post-load = ~19 hours
-- ============================================================================

USE DATABASE CLUSTERING_DEMO;
USE SCHEMA PUBLIC;

CREATE OR REPLACE PROCEDURE LOAD_AND_MONITOR(WAIT_SECONDS FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
  v_month       DATE;
  v_batch_num   INT DEFAULT 0;
  v_rows_cat    INT DEFAULT 0;
  v_rows_date   INT DEFAULT 0;
  v_total_cat   INT DEFAULT 0;
  v_total_date  INT DEFAULT 0;
  v_cnt         INT;
  v_ci          VARCHAR;
  v_hour_num    INT DEFAULT 0;
  v_wait_secs   FLOAT;
  
  c_months CURSOR FOR
    SELECT DISTINCT DATE_TRUNC('month', L_SHIPDATE)::DATE AS SHIP_MONTH
    FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.LINEITEM
    ORDER BY SHIP_MONTH;
BEGIN
  v_wait_secs := WAIT_SECONDS;

  -- ================================================================
  -- PHASE 1: Monthly batch loading (84 batches, chronological)
  -- ================================================================
  FOR rec IN c_months DO
    v_batch_num := :v_batch_num + 1;
    v_month     := rec.SHIP_MONTH;

    -- ---- BEFORE INSERT: snapshot clustering for CAT_FIRST --------
    v_ci := (SELECT SYSTEM$CLUSTERING_INFORMATION('CLUSTERING_DEMO.PUBLIC.LINEITEM_CAT_FIRST'));
    INSERT INTO CLUSTERING_DEMO.PUBLIC.CLUSTERING_LOG 
      (TABLE_NAME, PHASE, BATCH_NUM, BATCH_MONTH, ROWS_IN_BATCH, TOTAL_ROWS_LOADED,
       TOTAL_PARTITION_COUNT, TOTAL_CONSTANT_PARTITION_COUNT, 
       AVERAGE_OVERLAPS, AVERAGE_DEPTH, PARTITION_DEPTH_HISTOGRAM)
    SELECT 
      'LINEITEM_CAT_FIRST', 'BEFORE_INSERT', :v_batch_num, :v_month, 0, :v_total_cat,
      PARSE_JSON(:v_ci):total_partition_count::INT,
      PARSE_JSON(:v_ci):total_constant_partition_count::INT,
      PARSE_JSON(:v_ci):average_overlaps::FLOAT,
      PARSE_JSON(:v_ci):average_depth::FLOAT,
      PARSE_JSON(:v_ci):partition_depth_histogram::VARIANT;

    -- ---- BEFORE INSERT: snapshot clustering for DATE_FIRST -------
    v_ci := (SELECT SYSTEM$CLUSTERING_INFORMATION('CLUSTERING_DEMO.PUBLIC.LINEITEM_DATE_FIRST'));
    INSERT INTO CLUSTERING_DEMO.PUBLIC.CLUSTERING_LOG 
      (TABLE_NAME, PHASE, BATCH_NUM, BATCH_MONTH, ROWS_IN_BATCH, TOTAL_ROWS_LOADED,
       TOTAL_PARTITION_COUNT, TOTAL_CONSTANT_PARTITION_COUNT, 
       AVERAGE_OVERLAPS, AVERAGE_DEPTH, PARTITION_DEPTH_HISTOGRAM)
    SELECT 
      'LINEITEM_DATE_FIRST', 'BEFORE_INSERT', :v_batch_num, :v_month, 0, :v_total_date,
      PARSE_JSON(:v_ci):total_partition_count::INT,
      PARSE_JSON(:v_ci):total_constant_partition_count::INT,
      PARSE_JSON(:v_ci):average_overlaps::FLOAT,
      PARSE_JSON(:v_ci):average_depth::FLOAT,
      PARSE_JSON(:v_ci):partition_depth_histogram::VARIANT;

    -- ---- INSERT month's data into CAT_FIRST ----------------------
    INSERT INTO CLUSTERING_DEMO.PUBLIC.LINEITEM_CAT_FIRST
    SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.LINEITEM
    WHERE DATE_TRUNC('month', L_SHIPDATE) = :v_month;
    
    v_cnt := SQLROWCOUNT;
    v_rows_cat := :v_cnt;
    v_total_cat := :v_total_cat + :v_cnt;

    -- ---- INSERT month's data into DATE_FIRST ---------------------
    INSERT INTO CLUSTERING_DEMO.PUBLIC.LINEITEM_DATE_FIRST
    SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.LINEITEM
    WHERE DATE_TRUNC('month', L_SHIPDATE) = :v_month;
    
    v_cnt := SQLROWCOUNT;
    v_rows_date := :v_cnt;
    v_total_date := :v_total_date + :v_cnt;

    -- ---- WAIT for auto-clustering to work ------------------------
    CALL SYSTEM$WAIT(:v_wait_secs, 'SECONDS');

    -- ---- AFTER INSERT: snapshot clustering for CAT_FIRST ---------
    v_ci := (SELECT SYSTEM$CLUSTERING_INFORMATION('CLUSTERING_DEMO.PUBLIC.LINEITEM_CAT_FIRST'));
    INSERT INTO CLUSTERING_DEMO.PUBLIC.CLUSTERING_LOG 
      (TABLE_NAME, PHASE, BATCH_NUM, BATCH_MONTH, ROWS_IN_BATCH, TOTAL_ROWS_LOADED,
       TOTAL_PARTITION_COUNT, TOTAL_CONSTANT_PARTITION_COUNT, 
       AVERAGE_OVERLAPS, AVERAGE_DEPTH, PARTITION_DEPTH_HISTOGRAM)
    SELECT 
      'LINEITEM_CAT_FIRST', 'AFTER_INSERT', :v_batch_num, :v_month, :v_rows_cat, :v_total_cat,
      PARSE_JSON(:v_ci):total_partition_count::INT,
      PARSE_JSON(:v_ci):total_constant_partition_count::INT,
      PARSE_JSON(:v_ci):average_overlaps::FLOAT,
      PARSE_JSON(:v_ci):average_depth::FLOAT,
      PARSE_JSON(:v_ci):partition_depth_histogram::VARIANT;

    -- ---- AFTER INSERT: snapshot clustering for DATE_FIRST --------
    v_ci := (SELECT SYSTEM$CLUSTERING_INFORMATION('CLUSTERING_DEMO.PUBLIC.LINEITEM_DATE_FIRST'));
    INSERT INTO CLUSTERING_DEMO.PUBLIC.CLUSTERING_LOG 
      (TABLE_NAME, PHASE, BATCH_NUM, BATCH_MONTH, ROWS_IN_BATCH, TOTAL_ROWS_LOADED,
       TOTAL_PARTITION_COUNT, TOTAL_CONSTANT_PARTITION_COUNT, 
       AVERAGE_OVERLAPS, AVERAGE_DEPTH, PARTITION_DEPTH_HISTOGRAM)
    SELECT 
      'LINEITEM_DATE_FIRST', 'AFTER_INSERT', :v_batch_num, :v_month, :v_rows_date, :v_total_date,
      PARSE_JSON(:v_ci):total_partition_count::INT,
      PARSE_JSON(:v_ci):total_constant_partition_count::INT,
      PARSE_JSON(:v_ci):average_overlaps::FLOAT,
      PARSE_JSON(:v_ci):average_depth::FLOAT,
      PARSE_JSON(:v_ci):partition_depth_histogram::VARIANT;

  END FOR;

  -- ================================================================
  -- PHASE 2: Post-load hourly monitoring for 12 hours
  -- ================================================================
  FOR v_hour_num IN 1 TO 12 DO
    CALL SYSTEM$WAIT(3600, 'SECONDS');

    -- snapshot CAT_FIRST
    v_ci := (SELECT SYSTEM$CLUSTERING_INFORMATION('CLUSTERING_DEMO.PUBLIC.LINEITEM_CAT_FIRST'));
    INSERT INTO CLUSTERING_DEMO.PUBLIC.CLUSTERING_LOG 
      (TABLE_NAME, PHASE, BATCH_NUM, BATCH_MONTH, ROWS_IN_BATCH, TOTAL_ROWS_LOADED,
       TOTAL_PARTITION_COUNT, TOTAL_CONSTANT_PARTITION_COUNT, 
       AVERAGE_OVERLAPS, AVERAGE_DEPTH, PARTITION_DEPTH_HISTOGRAM)
    SELECT 
      'LINEITEM_CAT_FIRST', 'POST_LOAD_HOUR_' || :v_hour_num, :v_batch_num, NULL, 0, :v_total_cat,
      PARSE_JSON(:v_ci):total_partition_count::INT,
      PARSE_JSON(:v_ci):total_constant_partition_count::INT,
      PARSE_JSON(:v_ci):average_overlaps::FLOAT,
      PARSE_JSON(:v_ci):average_depth::FLOAT,
      PARSE_JSON(:v_ci):partition_depth_histogram::VARIANT;

    -- snapshot DATE_FIRST
    v_ci := (SELECT SYSTEM$CLUSTERING_INFORMATION('CLUSTERING_DEMO.PUBLIC.LINEITEM_DATE_FIRST'));
    INSERT INTO CLUSTERING_DEMO.PUBLIC.CLUSTERING_LOG 
      (TABLE_NAME, PHASE, BATCH_NUM, BATCH_MONTH, ROWS_IN_BATCH, TOTAL_ROWS_LOADED,
       TOTAL_PARTITION_COUNT, TOTAL_CONSTANT_PARTITION_COUNT, 
       AVERAGE_OVERLAPS, AVERAGE_DEPTH, PARTITION_DEPTH_HISTOGRAM)
    SELECT 
      'LINEITEM_DATE_FIRST', 'POST_LOAD_HOUR_' || :v_hour_num, :v_batch_num, NULL, 0, :v_total_date,
      PARSE_JSON(:v_ci):total_partition_count::INT,
      PARSE_JSON(:v_ci):total_constant_partition_count::INT,
      PARSE_JSON(:v_ci):average_overlaps::FLOAT,
      PARSE_JSON(:v_ci):average_depth::FLOAT,
      PARSE_JSON(:v_ci):partition_depth_histogram::VARIANT;

  END FOR;

  RETURN 'Completed: ' || :v_batch_num || ' batches + 12hr post-load monitoring. Rows: CAT=' || :v_total_cat || ' DATE=' || :v_total_date;
END;
$$;

Once created, running the stored procedure looks like this:

-- ============================================================================
-- RUN THE DEMO
-- ============================================================================
-- Execute this in a Snowsight worksheet. It will run for ~19 hours total:
--   Phase 1: 84 monthly batches × 5 min wait = ~7 hours
--   Phase 2: 12 hours of hourly post-load monitoring
--
-- The warehouse running the CALL stays active for the duration. Auto-clustering
-- runs on separate serverless compute (no extra warehouse cost for clustering).
-- ============================================================================

USE ROLE ACCOUNTADMIN;
USE DATABASE CLUSTERING_DEMO;
USE SCHEMA PUBLIC;
USE WAREHOUSE COMPUTE_WH;  -- XS is fine; clustering uses serverless compute

-- Launch the overnight job (300 seconds = 5 minute wait between batches)
CALL LOAD_AND_MONITOR(600);

Results

The results are quite striking, even for this very small and synthetic data set.

This is looking at clustering depth, which is a measure of how many micro-partitions we would expect to read to find one value of our clustering key. Notice the sawtooth nature of clustering depth – it gets worse as we load data and then better when auto-clustering kicks in. This could lead to uneven query performance against this data.

The cost information is also starkly different:

While the actual numbers of credits here are tiny in our example, we can see that the cost of the category-first clustering key is nearly double that of the date-first clustering key. This can get even more drastic with data growth. The most our date-first clustering needs to change is the most recent few micro-partitions. The category-first clustering may need to rearrange the entire table, which can be expensive at scale.

A Control to the Experiment

Let’s compare the clustering obtained by creating the entire table at once.

CREATE TABLE clustering_demo.public.control_lineitem  
    CLUSTER BY (L_SHIPDATE, L_SHIPMODE)
    AS  SELECT * 
          FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.LINEITEM 
         ORDER BY L_SHIPDATE, L_SHIPMODE;

SELECT SYSTEM$CLUSTERING_INFORMATION('clustering_demo.public.control_lineitem', '');

When we do this, we see that the clustering depth is lower than either of our previous scenarios at a solid 2.0:

{
  "cluster_by_keys" : "LINEAR(L_SHIPDATE, L_SHIPMODE)",
  "total_partition_count" : 512,
  "total_constant_partition_count" : 0,
  "average_overlaps" : 1.0,
  "average_depth" : 2.0,
  "partition_depth_histogram" : {
    "00000" : 0,
    "00001" : 0,
    "00002" : 512,
    "00003" : 0,
    "00004" : 0,
    "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" : [ ]
}

That is a pretty perfect example of a good clustering state.

For this tiny control table, we didn’t see automatic clustering kick in at all, so there was zero cost for clustering.

Conclusion

There are basics to consider when choosing a clustering key, but there are also more advanced considerations. Depending on query workload, sometimes choosing a different order for the elements of a clustering key can be cheaper, more efficient, or both.