5.3 Micro-Partitions, Pruning, Clustering, and the Query Profile

SnowPro Core Study Guide Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: SnowPro Core Orientation and Exam Strategy

1.1 Current SnowPro Core Exam Facts 1.2 Eligibility, Registration, and Scheduling 1.3 Blueprint Domains and Weighting 1.4 Editions, Question Style, and Score Report Thinking 1.5 Study Calendar and Hands-On Practice Plan

2 Chapter 2: Snowflake AI Data Cloud Features and Architecture

2.1 Snowflake AI Data Cloud Features and Architecture Overview 2.2 Core Workflows and Decision Points 2.3 Scenario Practice for Snowflake AI Data Cloud Features and Architecture 2.4 Common Traps in Snowflake AI Data Cloud Features and Architecture 2.5 Practice Drills and Readiness Markers

3 Chapter 3: Account Management and Data Governance

3.1 Account Management and Data Governance Overview 3.2 Core Workflows and Decision Points 3.3 Scenario Practice for Account Management and Data Governance 3.4 Common Traps in Account Management and Data Governance 3.5 Practice Drills and Readiness Markers

4 Chapter 4: Data Loading, Unloading, and Connectivity

4.1 Data Loading, Unloading, and Connectivity Overview 4.2 Core Workflows and Decision Points 4.3 Snowpipe, External Tables, and Storage Integrations 4.4 Bulk Unloading and Semi-Structured Data 4.5 Connectors, Drivers, and Snowsight Loading

5 Chapter 5: Performance Optimization, Querying, and Transformation

5.1 Performance Optimization, Querying, and Transformation Overview 5.2 Core Workflows and Decision Points 5.3 Micro-Partitions, Pruning, Clustering, and the Query Profile 5.4 Search Optimization, Materialized Views, and Query Best Practices 5.5 Transformations: ELT, Streams & Tasks, and Semi-Structured Data

6 Chapter 6: Data Collaboration

6.1 Secure Data Sharing Architecture 6.2 Creating Shares, Granting Objects, and Reader Accounts 6.3 Snowflake Marketplace, Listings, and Data Exchange 6.4 Secure Views, Cross-Region Sharing, and Governance 6.5 Choosing the Right Sharing Method and Readiness Drills

7 Chapter 7: Final Review and Test Day

7.1 Timed Practice Strategy 7.2 Last-Week Review Map 7.3 Exam-Day Checklist 7.4 After the Exam and Next Steps

5.3 Micro-Partitions, Pruning, Clustering, and the Query Profile

Key Takeaways Explain This Topic AI

Snowflake stores tables as immutable micro-partitions of 50–500 MB of uncompressed data, each with per-column min/max metadata used for automatic pruning.

Define a clustering key only on large tables (multi-TB) where a common, selective filter no longer prunes well; reclustering consumes credits automatically.

SYSTEM$CLUSTERING_INFORMATION reports clustering depth and overlap — lower average depth means better-clustered data.

Query Profile flags spilling (undersized warehouse), low 'partitions scanned/total' (good pruning), and the most expensive nodes.

Last updated: October 2026

Micro-Partitions and Automatic Pruning

Snowflake physically stores every table as a set of micro-partitions — immutable, compressed columnar files of 50–500 MB of uncompressed data (stored compressed, so smaller on disk). Crucially, Snowflake records metadata for every micro-partition: the range (min/max) of each column, the number of distinct values, and null counts. This metadata is what enables query pruning .

When a query has a filter such as WHERE order_date = '2026-01-15' , the optimizer reads micro-partition metadata and skips every partition whose min/max range cannot contain that value — without scanning a byte of data. This is automatic and requires no configuration; it is Snowflake's substitute for indexes. Pruning is most effective when the filter column correlates with the order in which data was loaded, because related values then sit together in the same micro-partitions. As tables grow and receive many small DML operations, this natural ordering can degrade, and pruning becomes less selective.

Two properties make micro-partitions special: they are immutable (an update writes new partitions rather than editing in place, which is what powers Time Travel ), and they are columnar within each partition , so a query reads only the columns and partitions it needs. Pruning therefore operates on both axes — partitions (rows) and columns — which is why SELECT * and unselective filters are doubly wasteful.

Clustering Keys and Reclustering

A clustering key is one or more columns (or expressions) that Snowflake uses to co-locate related rows into the same micro-partitions, restoring good pruning. You define it with ALTER TABLE t CLUSTER BY (order_date) . Once defined, Snowflake performs automatic reclustering in the background as data changes — this consumes Snowflake-managed compute credits , billed separately from your warehouse.

Clustering is not free and is not for every table. Define a clustering key only when all of these hold:

The table is large (generally multiple terabytes / hundreds of millions of rows).

Queries frequently filter or join on the chosen column with high selectivity .

Query Profile shows the table no longer prunes well (high partitions-scanned ratio).

Choose a key with the right cardinality : high enough to be selective, low enough that values group into partitions (a unique key like a primary key is a poor choice). On small tables, the reclustering cost outweighs any benefit, and the exam expects you to reject clustering there.

Use SELECT SYSTEM$CLUSTERING_INFORMATION('t', '(order_date)') to inspect clustering quality. It returns the average clustering depth (how many overlapping micro-partitions a typical value spans) and overlap histograms. A lower average depth is better — depth near 1 means each value lives in few partitions, so pruning is excellent.

Reading the Query Profile and EXPLAIN

EXPLAIN returns the logical plan and estimated partitions/bytes without running the query. The Query Profile (in Snowsight, per executed query) is the richer diagnostic. Learn to read these signals:

Profile signal Meaning Typical fix

Partitions scanned ≈ Partitions total Poor pruning; the filter isn't skipping data Add/repair clustering key, or use search optimization

Bytes spilled to local storage Warehouse memory exceeded; using SSD Scale up the warehouse

Bytes spilled to remote storage Even SSD exceeded; spilling to cloud storage (worst case) Scale up further; reduce data processed

A single node = most of execution time That operator (Join, Aggregate, TableScan) is the bottleneck Optimize that step (join order, filter earlier)

Exploding / cartesian join Missing or wrong join condition Fix join keys

The percentage of time each operator consumes and the partitions scanned / partitions total ratio are the two numbers the exam cares about most. A low ratio (e.g., 12 of 4,000) proves pruning is working; a high ratio proves it is not. Spilling — local first, then the much slower remote spilling — is the unambiguous signal to scale up.

What Kills Pruning, and What to Do

Pruning quietly degrades for predictable reasons; recognizing them is exam gold:

Functions or casts on the filtered column. WHERE TO_DATE(ts) = '2026-01-15' can prevent the optimizer from using stored min/max ranges; filtering on the raw column (or a half-open range) preserves pruning.

Leading wildcards. LIKE '%abc' cannot prune on a range; consider the Search Optimization Service for substring search.

Heavy small-DML churn. Many small inserts/updates create poorly ordered micro-partitions, raising clustering depth over time — the case for a clustering key on a large table.

Low correlation between filter column and load order. If data arrives sorted by id but you filter by region , every partition may match — clustering by region fixes it.

Reclustering cost is the trade you must weigh. Snowflake reclusters automatically using serverless credits proportional to how much data must be re-sorted; high-churn tables can recluster expensively. The guidance: cluster a large table on a frequently filtered, selective column, then monitor with SYSTEM$CLUSTERING_INFORMATION and SYSTEM$CLUSTERING_DEPTH . If reclustering credits exceed query savings, drop the key.

Finally, distinguish the Profile from EXPLAIN . EXPLAIN is pre-execution and shows estimates (useful to compare plans cheaply); the Query Profile is post-execution and shows what actually happened — real partitions scanned, real bytes spilled, real per-operator time. When a question asks you to diagnose a query that already ran slowly , the Query Profile is the correct tool; when it asks how to check a plan before running it , EXPLAIN is the answer.