Micro-partitions are the foundation
Snowflake automatically divides table data into small, immutable micro-partitions stored in a columnar format. Each micro-partition carries metadata such as value ranges and distinct-value information for its columns. Users do not manually define or manage these storage units.
Because the metadata is available without opening every micro-partition, the optimizer can decide that some partitions cannot satisfy a query predicate. Skipping them is pruning.
Rows are organized into micro-partitions.
Snowflake records column metadata.
The optimizer evaluates query predicates.
Impossible partitions are not scanned.
Micro-partitions are automatic. A clustering key influences organization over time; it does not create traditional user-managed partitions.
Good pruning depends on useful predicates and metadata
Suppose an orders table has 50,000 micro-partitions and a query filters one recent order date. If date ranges are well localized, Snowflake may scan only a small fraction. If the same date values are scattered across most partitions, many more must be scanned.
SELECT customer_id, SUM(order_total)
FROM analytics.orders
WHERE order_date >= '2026-08-01'
AND order_date < '2026-09-01'
GROUP BY customer_id;Write predicates that can be evaluated efficiently against stored values. Applying transformations to the filtered column can make pruning less effective than comparing the column directly to compatible bounds. Validate the real result in Query Profile rather than assuming a rewritten predicate helped.
event_ts >= start AND event_ts < endDirect range conditions can align with stored metadata.
DATE(event_ts) = target_dateA function on the filtered column may limit metadata use; test the actual plan.
Clustering keys are a workload decision, not a default
A clustering key defines one or more expressions Snowflake can use to colocate related rows in micro-partitions. It can improve pruning for large tables whose common selective predicates otherwise scan too broadly. Automatic Clustering can maintain clustering after the key is defined.
Clustering also consumes resources as data changes. Small tables, infrequently queried tables or queries that scan most rows may not gain enough benefit. Choose candidate keys from recurring filters and joins, consider cardinality and expression order, then measure savings against maintenance cost.
When a clustering key is worth investigating
- The table is large enough for scan reduction to matter.
- Important queries repeatedly filter on the same selective columns or expressions.
- Query Profile shows a high scanned-to-total partition ratio.
- Natural loading order does not already provide useful clustering.
- Performance or credit savings can justify ongoing maintenance.
Use system functions such as SYSTEM$CLUSTERING_INFORMATION to inspect clustering characteristics. Treat clustering depth as diagnostic evidence, not a universal target independent of workload.
Read Query Profile from the expensive operator outward
Query Profile visualizes execution operators and statistics. Start with nodes responsible for the most time, rows or bytes. For a table scan, compare partitions scanned with partitions total. A scan of nearly all partitions for a highly selective result is a strong signal to investigate predicate design and clustering.
How many micro-partitions the table scan read.
The available partitions considered for the scan.
The volume read before later operators reduce rows.
Helps identify filters, joins or row multiplication.
Do not stop at the scan. Large join explosions, remote spill, local spill, skewed work or expensive sorts can dominate runtime even when pruning is reasonable. The best fix should target the measured bottleneck.
Worked scenario: a selective dashboard query scans 92% of the table
Situation: A dashboard filters a multi-terabyte event table to one customer and seven days. Runtime has increased as daily loads grew.
Open Query Profile
The TableScan node reads 46,000 of 50,000 partitions even though the final result contains only a small customer slice.
Check predicate shape
Use direct customer and timestamp range predicates, confirm compatible types and retest the partition count.
Assess clustering
If recurring queries still scan broadly, evaluate a clustering key aligned to customer and time, then compare query savings with maintenance credits.
More compute may process a large scan faster, but the query still reads unnecessary data. Diagnose and reduce scan volume before deciding whether the remaining workload needs a larger warehouse.
Common SnowPro Core mistakes
- Assuming Snowflake requires manually created partitions.
- Adding a clustering key to every table without workload evidence.
- Confusing clustering with a guarantee of sorted output.
- Scaling the warehouse before checking partitions scanned.
- Focusing only on TableScan while ignoring join expansion or spill.
- Using a high-cardinality key without considering query patterns and maintenance cost.
- Assuming a query rewrite improved pruning without comparing profiles.
Frequently asked questions
What is micro-partition pruning in Snowflake?
Snowflake stores metadata about values in micro-partitions. When a query predicate can use that metadata, Snowflake can avoid scanning micro-partitions that cannot contain matching rows. This reduces unnecessary data scanning.
Does every Snowflake table need a clustering key?
No. Snowflake automatically creates micro-partitions, and many tables perform well without an explicit clustering key. Consider a key for large tables with selective, recurring filters when pruning is poor and the performance benefit justifies maintenance cost.
What should I inspect first in Query Profile?
Start with the operators consuming the most time or data. For scans, compare partitions scanned with total partitions and inspect bytes scanned. Then review joins, spills, exploding row counts, remote I/O and other expensive nodes.
Will increasing warehouse size fix poor partition pruning?
A larger warehouse can provide more compute, but it does not correct a predicate or data organization problem that causes excessive scanning. Diagnose pruning and query shape before scaling compute.
What is clustering depth?
Clustering depth describes how much micro-partition overlap exists for values in the clustering key dimensions. Lower depth generally indicates better clustering, but interpret it in the context of table size, workload and clustering cost.
Official Snowflake references
Review Snowflake’s current documentation for micro-partitions and data clustering, clustering keys, and the SnowPro Core COF-C03 certification. ITCertPath content is independent and does not reproduce confidential exam questions.