A Snowflake table is clustered by event_date, yet queries still scan every micro-partition. Why?

## Establish the fact first Before theorising, open **Query Profile** and read the TableScan operator's *Partitions scanned* against *Partitions total*, or pull `partitions_scanned` and `partitions_total` from `SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY` for that `query_id`. "Feels slow" is not a pruning diagnosis. If scanned equals total, pruning genuinely failed and the checklist below applies. If scanned is already tiny, the time is going somewhere else — a spilling join, an under-sized warehouse, an exploded intermediate result — and clustering is the wrong lever entirely. ## Cause 1: the predicate is not on the clustering key The most common answer and the most embarrassing. A table clustered by `event_date` gives you nothing for `WHERE customer_id = 918273`, because `customer_id` values are spread across every micro-partition and their recorded ranges all overlap the predicate. Clustering helps exactly the columns it names (and columns strongly correlated with them). Check what the important queries actually filter on before blaming the service. ## Cause 2: the column is wrapped ```sql -- typically prunes poorly: compares a computed value select count(*) from events where to_date(event_ts) = '2026-08-01'; -- prunes: compares directly against recorded min/max select count(*) from events where event_ts >= '2026-08-01' and event_ts < '2026-08-02'; ``` The micro-partition metadata holds min and max of the **stored** column values. A function or cast applied to the column produces something those ranges do not describe, so the optimizer generally cannot exclude files. The fix is either to rewrite the predicate as a range on the bare column — a *sargable* predicate, in the classic terminology — or, if every query genuinely filters by day, to make the clustering key *be* that expression (`CLUSTER BY (to_date(event_ts))`) so the recorded organization matches how the data is asked for. The same family of problems: `WHERE cast(order_id as varchar) = '123'`, `WHERE substr(code, 1, 3) = 'ABC'`, `WHERE col LIKE '%mid%'` (a leading wildcard has no usable range), and long `OR` chains across unrelated columns, where the union of ranges covers everything. ## Cause 3: only a trailing key column is constrained With `CLUSTER BY (region, event_date)`, Snowflake separates micro-partitions primarily by `region`, and by `event_date` only within a region. A query filtering only on `event_date` must consider files from every region, so it prunes far less than the same filter would on a table clustered by date first. Order the key columns by how your queries filter — the column present in the most selective, most common predicate goes first. ## Cause 4: the key is too granular A clustering key on a raw microsecond timestamp, a UUID, or a concatenation with near-unique values gives Snowflake an ordering it can never economically maintain: nearly every value is distinct, so almost every micro-partition ends up overlapping its neighbours anyway. Lower the cardinality with an expression — `to_date(ts)` instead of `ts`, a truncated prefix instead of a full identifier — and keep the key to a few columns. ## Cause 5: the organization has decayed or was never maintained Now the layout questions. Run: ```sql select system$clustering_information('analytics.public.events'); ``` If `average_depth` is large relative to `total_partition_count`, the files genuinely overlap. Ask: - **Is reclustering suspended?** Someone ran `ALTER TABLE t SUSPEND RECLUSTER` around a backfill months ago and never resumed. This is a real and frequent finding. - **Was the key added recently?** Convergence is incremental; a key declared yesterday on a 40 TB table has not finished. - **Does the table churn faster than the service converges?** Check `SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY`. Heavy credits *plus* persistently poor depth means DML is outrunning clustering, and the answer is to change the write pattern (batch it, append instead of update) or to drop the key. - **Do many small trickle loads keep arriving?** They produce files that straddle the key ranges until reclustering catches up. ## Cause 6: the table is too small to matter A table of forty micro-partitions cannot prune impressively, and it does not need to. Clustering is a large-table tool; on a small table the whole scan is cheap and a key just burns credits. ## The order to work in Measure partitions scanned → check the predicate names the key → check the column is bare → check the leading column is constrained → check key cardinality → only then examine depth, suspension and reclustering spend. Most incidents end at step two or three, and every one of those is fixed by editing the query rather than by paying Snowflake more.