TopolearnPruning is the main reason Snowflake queries are fast or slow on large tables.
Every table is stored as micro-partitions: immutable files holding about 50 to 500 MB of uncompressed data each, stored column by column. For each micro-partition and column Snowflake keeps metadata such as min and max values, distinct counts and null counts.
When a query filters (WHERE event_date = '2025-06-01'), the optimizer compares the filter to that metadata and prunes micro-partitions that cannot contain matches, before reading anything. In the query profile, the TableScan node shows "Partitions scanned" versus "Partitions total"; that ratio is the first thing to check on a slow query.
Pruning works only if matching values are concentrated in few micro-partitions. Data loaded in time order is naturally well clustered by time. Data that arrives randomly, or a filter on a column unrelated to load order, spreads values across every micro-partition.
A clustering key (ALTER TABLE t CLUSTER BY (event_date, tenant_id)) tells Snowflake to reorganize data in the background (Automatic Clustering) so similar values sit together. Consider it when:
- the table is large (multi-terabyte, or many thousands of micro-partitions),
- queries filter on the same columns most of the time and prune poorly,
- the table isn't rewritten constantly, since churn makes reclustering expensive.
Automatic Clustering consumes credits. Measure with SYSTEM$CLUSTERING_INFORMATION, where a lower average_depth is better. Use low-to-medium-cardinality columns or expressions like TO_DATE(ts) rather than raw timestamps or ids.
Example
-- How well is the table clustered on the column we filter by?
SELECT SYSTEM$CLUSTERING_INFORMATION('events', '(TO_DATE(event_ts))');
-- {"cluster_by_keys": "LINEAR(TO_DATE(event_ts))",
-- "total_partition_count": 48211, "average_depth": 812.4, ...} <- poor
ALTER TABLE events CLUSTER BY (TO_DATE(event_ts), tenant_id);
-- Later: depth drops as Automatic Clustering works in the background
SELECT SYSTEM$CLUSTERING_INFORMATION('events');
-- Check pruning for a typical query in the query profile:
-- TableScan: Partitions scanned 112 / Partitions total 48211
SELECT tenant_id, COUNT(*) FROM events
WHERE event_ts >= '2025-06-01' AND event_ts < '2025-06-02'
GROUP BY tenant_id;
-- Pause reclustering (and its credit use) if needed
ALTER TABLE events SUSPEND RECLUSTER;Remember
- Micro-partition min/max metadata lets Snowflake prune before reading
- Check partitions scanned vs total in the query profile
- Clustering keys pay off on large, frequently filtered, rarely rewritten tables
Watch out for
- Clustering by a high-cardinality column such as a raw timestamp or UUID, which is costly to maintain and prunes little.