Snowflake
Clustering Keys for Large Table Optimization
Manually improve pruning efficiency on very large or poorly-ordered tables.
When a table stops pruning well, a clustering key tells Snowflake which column to keep data ordered by.
It's like periodically reshuffling a filing cabinet so folders for the same month end up in the same drawer, even after years of inserting new folders in random order.
Key Concepts
1
ALTER TABLE orders CLUSTER BY (order_date);2
It is not an index. Nothing new is stored. It changes how a background service reorganises the existing
micro-partitions so that rows with similar values end up in the same file.
3
Why a table stops pruning. New data arrives ordered by load time. If queries filter on something else —
a customer id, a region — those values are spread evenly across every file, so every file might match.
4
Updates make it worse, because a modified row is rewritten into a new partition that no longer sits with
its neighbours.
5
Reclustering is a managed service you pay for. Snowflake rewrites partitions in the background,
charging credits in proportion to the data moved. It is continuous, not a one-off.
6
That is the whole reason not to reach for it casually. An index costs disk; a clustering key costs an
ongoing bill that grows with how often the table changes.
7
Cardinality decides whether it can work.
too low (country, 20 values) -> a partition holds one country and 400 million rows,
so pruning gains little
too high (order_id, all unique) -> every new row belongs somewhere different,
so reclustering never settles and costs forever
right (order_date, or
region + month) -> enough groups to prune, few enough to stay ordered8
The reliable pattern is a date or timestamp, or a low-cardinality dimension combined with a date.
9
Order matters, like a composite index. CLUSTER BY (region, order_date) prunes well on region, and
on region + order_date, and poorly on order_date alone. Put the column your queries always filter on
first.
CLUSTER BY (region, order_date)regionregion + order_dateorder_date
10
Prove that pruning is the problem first. Snowflake's own guidance is multi-terabyte tables only, and
the sequence is:
11
1. Query profile — is "partitions scanned" close to "partitions total"?
2. `SYSTEM$CLUSTERING_INFORMATION` — how much do the partition ranges overlap?
3. Only then, and only if the answer is yes to both, add the key.SYSTEM$CLUSTERING_INFORMATION
12
Most slow Snowflake queries are not clustering problems. They are an undersized warehouse, a query that
prevents pruning by wrapping the filtered column in a function, or a join producing far more rows than
expected. A clustering key fixes none of those and bills you monthly for the attempt.
13
What the interviewer is probing.1. "When is a clustering key worth it?" Probing: the threshold. Stalls: "Always." *Moves
up:* multi-terabyte tables with a consistent filter column that natural order does not already serve
— below that the reclustering cost outweighs the gain.
14
2. "What does it cost?" Probing: the credits. Stalls: "Nothing." Moves up: automatic
clustering consumes credits continuously as data changes, which can be substantial on a high-churn
table.
15
3. "How do you know whether a table needs one?" Probing: the tooling. Stalls: "Guess."
Moves up: SYSTEM$CLUSTERING_INFORMATION shows overlap and depth, and the query profile shows
partitions scanned versus total.
16
4. "How many columns should a key have?" Probing: the guidance. Stalls: "As many as needed."
Moves up: typically one to three, ordered low to high cardinality — too many and clustering
becomes ineffective and expensive.