SnowPro Core: Choosing a Clustering Key for a Region-Filtered 4 TB Fact Table

Choosing a Clustering Key for a Region-Filtered 4 TB Fact Table

A 4 TB ORDERS table holds 900 million rows loaded continuously by Snowpipe. Roughly 5% of the rows are EMEA; the other 11 regions are interleaved throughout the load stream. ORDER_TS does not track arrival order - late-arriving events arrive scattered. About 200 report queries per day run the same shape: WHERE region = '<one value>' AND order_ts >= <date>, and profiling shows each one scans roughly 55% of micro-partitions. The table has never had a clustering key. Data engineering owns the table; finance already reviews every credit line item and will ask why a new one appeared.

1. Which clustering key, if any, would you set, and how would you defend the cost to finance?

Read the worked answer

Recommendation: ALTER TABLE orders CLUSTER BY (region, order_ts).

region is in the predicate of every query and has only ~12 values. Leading with it lets the optimizer eliminate ~11/12 of micro-partitions on the prefix of the key - that alone is where the 55%-scan problem is solved.
order_ts as the second column then prunes within a region for the range predicate. It cannot lead, because arrival order does not correlate with ORDER_TS, so partitions are not naturally time-ordered and a leading order_ts key would require full reclustering to become useful while still leaving all 12 regions inside every surviving partition.
Baselines to capture first: SYSTEM$CLUSTERING_INFORMATION (average_depth, average_overlapping_partitions) and the current partitions-scanned-to-total ratio for a representative query.

The finance argument: automatic clustering is serverless - it is billed as a separate credit line item and is not covered by warehouse resource monitors. Quantify the trade: 200 queries/day × ~2.2 TB scanned today versus a target of a few percent of 4 TB, against a reclustering cost that is proportional to the data actually reshuffled (large once, small thereafter). If the overwhelming majority of the 200 queries touch only recent months, compare against the cheaper alternative of a CLUSTER BY (order_ts, region) filtered derived table maintained by a task, and only build it if the full-table version cannot be justified.

2. Three weeks later, scans are down but automatic clustering credits keep climbing and `average_depth` is not improving. What is happening and what do you do?

Read the worked answer

Classic overlap churn from small, frequent loads. Snowpipe is writing many small files, so each load creates many small micro-partitions across the full region/order_ts key range. Automatic clustering must continuously merge those overlapping partitions, and because new overlaps arrive faster than they can be merged, depth flattens or regresses while credits climb.

Actions, in order:

Fix the write side first. Batch the files before ingest (target 100–250 MB compressed). Fewer, larger loads create fewer, better-aligned partitions and are the root-cause fix. Aligning load order with the key (region) further reduces overlap.
Re-measure with SYSTEM$CLUSTERING_INFORMATION before and after the batching change - depth, not query time, is the leading indicator.
Control the spend. After the one-time reclustering of existing data settles, ALTER TABLE ... SUSPEND RECLUSTER is available if the ongoing cost outweighs the pruning benefit; you can also leave clustering on a table that is no longer mutated and rely on the existing order.
Consider whether the same pruning could be had with a materialized view or by partitioning hot data into a date-bounded table, which may be cheaper than perpetual reclustering of a continuously loaded 4 TB table.