Snowflake Micro-Partitions & Data Clustering Explained
Snowflake stores every table in immutable columnar files called micro-partitions, and skips the ones a query does not need. Today covers what micro-partitions are, how pruning works, and when a clustering key is worth the credits.
| Term | Plain meaning |
|---|---|
| Micro-partition | One small immutable file (50–500 MB uncompressed) holding a chunk of a table’s rows in columnar format. The unit of storage in Snowflake. |
| Immutable | Once written, never changed. UPDATE and DELETE create new micro-partitions. The old ones are kept for Time Travel and Fail-safe, then purged. |
| Columnar | Each column is stored separately inside the file. A query touching two columns reads only those two, not the whole row. |
| Pruning | Skipping micro-partitions whose value ranges can’t possibly match the WHERE clause. The single biggest performance lever in Snowflake. |
| Natural clustering | The implicit ordering data inherits from how it was loaded. A nightly ETL sorted by date produces partitions naturally clustered on date. |
| Clustering key | A column or expression you explicitly declare so Snowflake keeps data physically sorted by it, preserving pruning as the table changes. |
| Clustering depth | The average overlap of micro-partitions on the key. Lower is better. A depth of 1.0 means perfect. Almost never seen in production. |
Today’s Concept
Micro-Concept 1: What a Micro-Partition Is
Every Snowflake table is automatically split into micro-partitions on load. Five properties matter for the exam:
- Size: 50 MB to 500 MB uncompressed. On disk they are much smaller. After compression they typically run up to about 16 MB. The exam quotes the uncompressed range.
- Immutable: Never rewritten in place. UPDATE, DELETE, and MERGE all write new partitions. The original ones stay alive for Time Travel and Fail-safe before they are purged.
- Columnar: Each column is stored independently inside the partition. Snowflake picks the compression algorithm per column automatically. You don’t tune this.
- Automatic: No
PARTITION BY. No ALTER required. Snowflake creates partitions on every load, insert, and CTAS. - Per-partition metadata: Snowflake records the min and max for each column in each partition, plus distinct counts and other stats. This metadata is the entire basis for pruning.
The most common exam trap here is “Snowflake builds an index per micro-partition.” It doesn’t. Pruning is metadata-based, not index-based.
Micro-Concept 2: Pruning. How Queries Skip Partitions
Write WHERE order_date = '2025-03-15' and Snowflake reads the min/max metadata for order_date on every partition. Any partition whose range cannot include that date is skipped. It is never opened, never read from storage. The rest are scanned.
Query Profile exposes this directly in two numbers in the right-hand panel:
- Partitions scanned: partitions actually read for this query
- Partitions total: the table’s full partition count
Good pruning looks like 20 of 5,000, a tiny fraction. Bad pruning looks like 12,400 of 12,500. The filter narrowed nothing. This ratio is the first thing to check when a query runs slowly. It tells you whether the problem is data layout or compute size. The two need very different fixes.
Micro-Concept 3: Natural Clustering. Often Enough
Data lands in micro-partitions in the order it arrives. A nightly ETL that loads orders sorted by date produces partitions naturally clustered on date. No key needed. This is why many well-designed Snowflake tables perform fine without anyone touching a CLUSTER BY clause.
Natural clustering degrades when:
- The table sees many UPDATEs or DELETEs that rewrite partitions out of the original order
- Rows arrive out of order: backfills, Snowpipe streaming, late-arriving events
- The “natural” column is not the column queries actually filter on
The third one is the most common. On a recent client project, a fact table was loaded in record-creation order, but reports filtered overwhelmingly on transaction date. Natural clustering on the creation date gave nothing for the workload that actually mattered.
Micro-Concept 4: Clustering Keys. What They Are and Aren’t
What a clustering key IS: A declaration that tells Snowflake to keep the table physically organized by one or more columns or expressions. Once defined, the auto-clustering service reorganizes micro-partitions in the background using serverless compute. No warehouse needed.
What a clustering key is NOT:
- Not an index. Snowflake has no indexes at all. Pruning runs off partition metadata, not B-trees or hash indexes.
- Not free. Reclustering consumes serverless credits, billed separately from your warehouse credits, and tracked in
SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY. - Not required. Most tables don’t need an explicit key. Adding one out of habit is one of the easier ways to waste credits in Snowflake.
Micro-Concept 5: When to Add a Clustering Key
Snowflake’s docs are explicit: clustering keys are intended for tables that meet all of the following:
- Large. Multi-TB. The docs use the phrase “large number of micro-partitions”. Practically, that means tables in the thousands of partitions or more. Small tables don’t have enough partitions for pruning gains to outweigh the maintenance cost.
- Filtered heavily on a few specific columns. The workload concentrates on a small set of selective predicates that the same key can serve.
- Showing degraded pruning. Query Profile shows “Partitions scanned / Partitions total” close to 1 on the queries you care about.
Column priority for cluster keys. This is the most exam-tested clustering fact on the COF-C03:
- Columns most often in WHERE clauses (selective filters)
- Then columns often in JOIN predicates
- Rarely: GROUP BY columns. The docs call this “usually less helpful”
- Almost never: ORDER BY columns alone. The docs use the same “usually less helpful” wording
When the exam offers GROUP BY or ORDER BY as the “best” clustering candidate, it is a distractor. Pruning happens before grouping and ordering. By the time those operators run, the rows are already in memory.
Cardinality matters as much as the column choice. Avoid the two extremes:
- Very high cardinality (UUIDs, nanosecond timestamps). Too many distinct values mean Snowflake cannot group rows effectively. The reclustering bill rises with little pruning gain.
- Very low cardinality (booleans, three-value status flags). Too few distinct values mean every partition’s range covers most values. Pruning barely helps.
For a timestamp column the docs recommend wrapping it in TO_DATE() or a similar truncation when you define the key. Snowflake supports expressions in clustering keys for exactly this reason. A common practice-test trap offers raw CLUSTER BY (event_ts) as the “obvious” answer for a date-filtered workload. The right answer is CLUSTER BY (TO_DATE(event_ts)).
Micro-Concept 6: Inspecting Clustering with SYSTEM$CLUSTERING_INFORMATION
The diagnostic function. It returns JSON with average_depth, a partition depth histogram, and the total partition count. Run it before adding a key, then again after, to confirm the key actually helps.
SELECT SYSTEM$CLUSTERING_INFORMATION('my_table', '(order_date)');Reading the output: average_depth close to 1 is excellent. Above 10 is a real signal that the table would benefit from clustering on that column. The function works without a clustering key being defined. You can test a candidate key on real data before paying to apply it. This is the single most useful clustering tool, and few teams use it before reaching for ALTER TABLE in production.
Micro-Concept 7: Costs of Auto-Clustering
Once you set a clustering key, Snowflake’s auto-clustering service monitors the table and reclusters as DML degrades the layout. This runs on serverless compute, completely separate from your warehouse credits. It shows up on the bill under AUTOMATIC_CLUSTERING. The pattern is straightforward: heavy DML on a clustered table means a bigger reclustering bill. A high-churn table is the worst candidate for clustering for exactly this reason. You pay continuously to undo the disorder your own writes introduce.
Cheat Sheet
| Concept | What to remember |
|---|---|
| Size | 50–500 MB uncompressed; up to ~16 MB compressed per micro-partition |
| Immutable | Never modified. UPDATE/DELETE rewrites as new partitions. |
| Columnar | Each column stored independently. Compression chosen per column automatically. |
| Pruning | Snowflake skips partitions whose min/max excludes the predicate. Visible in Query Profile. |
| No indexes | Snowflake uses partition metadata, not indexes. Ever. |
| Natural clustering | Order of load = implicit clustering. Often good enough. |
| When to cluster | Multi-TB + selective filters + degraded pruning (depth high) |
| Cluster-key column priority | WHERE > JOIN >> GROUP BY > ORDER BY |
| Cardinality sweet spot | Avoid very-high (UUID, ns timestamp) and very-low (boolean) cardinality columns |
| Auto-clustering edition | Enterprise+ (serverless reclustering) |
| Inspect | SYSTEM$CLUSTERING_INFORMATION('tbl','(col)'). Works without a key being set. |
| Cost | Reclustering uses serverless credits, separate from warehouse. Heavy DML = bigger bill. |
Three traps the COF-C03 plants on this topic. (1) “Snowflake uses indexes.” FALSE. Pruning is metadata-driven. If an answer option mentions an index, B-tree, or hash lookup, eliminate it on sight. (2) “Cluster on GROUP BY or ORDER BY columns.” Almost always WRONG. The Snowflake docs use the exact phrase “usually less helpful” for these. The documented priority is WHERE first, JOIN second. Sorting happens after the scan, not before, so the clustering key cannot help skip what the query has to read. (3) “Clustering is always beneficial.” FALSE. Clustering a small table, a low-cardinality column, or a column nobody filters on burns credits and changes nothing. The trigger for clustering is a specific signal in Query Profile. The table is large. Partitions scanned ≈ Partitions total. The workload has selective filters Snowflake isn’t currently exploiting. If even one of those is missing, the answer is “don’t cluster.”
Hands-On Lab
lab_xs warehouse present. | Note: Creates day10_orders. Keep through Day 39.Build the lab table. A 1.5-million-row snapshot from TPCH sample data. This table is reused on Days 11, 13, 26, 31, 32, 35, and 39. Do not drop it at the end.
USE ROLE SYSADMIN;
USE WAREHOUSE lab_xs;
ALTER WAREHOUSE lab_xs RESUME IF SUSPENDED;
CREATE OR REPLACE TABLE day10_orders AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;
SELECT COUNT(*) AS rows FROM day10_orders;
-- ~1.5 million rowsPARTITION BY in the DDL. Snowflake split the data into micro-partitions automatically during the CTAS. This is exactly the point the exam keeps testing.Inspect clustering on a good candidate column, O_ORDERDATE. No clustering key is defined yet. The function works either way.
SELECT SYSTEM$CLUSTERING_INFORMATION('day10_orders', '(O_ORDERDATE)');total_partition_count, average_depth, and a partition_depth_histogram. Because the TPCH source is naturally clustered on date, average_depth will be low. Pruning on date will work well without an explicit key. This is what “natural clustering is often enough” looks like in practice.Inspect a deliberately bad candidate, O_COMMENT. A free-text column that is near-unique per row.
SELECT SYSTEM$CLUSTERING_INFORMATION('day10_orders', '(O_COMMENT)');average_depth. Comments are near-unique, so every partition’s value range effectively covers everything. Pruning is useless. This is what very-high-cardinality looks like in the diagnostic output.Run a date-filtered query and read pruning from Query Profile.
SELECT COUNT(*), SUM(O_TOTALPRICE)
FROM day10_orders
WHERE O_ORDERDATE = '1995-03-15';
-- After running:
-- Snowsight → Activity → Query History → click the query → Query Profile tab
-- In the right panel, look at:
-- Partitions scanned : N
-- Partitions total : M
-- Good pruning = N much less than MContrast with a poorly-pruning query. Filter on a column whose three values are spread evenly across every partition.
SELECT COUNT(*)
FROM day10_orders
WHERE O_ORDERSTATUS = 'F';
-- Check Query Profile: Partitions scanned will be close to Partitions total.
-- O_ORDERSTATUS has 3 values, all spread evenly → nothing to prune.Define an explicit clustering key (demo only) and inspect it again.
ALTER TABLE day10_orders CLUSTER BY (O_ORDERDATE);
SHOW TABLES LIKE 'day10_orders';
-- Check the cluster_by column
-- Confirm
SELECT SYSTEM$CLUSTERING_INFORMATION('day10_orders');
-- No need to pass the column name now — Snowflake uses the defined keyaverage_depth dramatically. The principle is identical on a multi-TB production table. The visible effect is just larger when there are more partitions to reorganize.Partial cleanup. Drop the clustering key but keep the table. day10_orders is reused on multiple later days per the lab dependency map.
ALTER TABLE day10_orders DROP CLUSTERING KEY;
ALTER WAREHOUSE lab_xs SUSPEND;
-- KEEP day10_orders — used Days 11, 13, 26, 31, 32, 35, 39SYSTEM$CLUSTERING_INFORMATION on the candidate column first, and you can burn serverless credits with no measurable query speedup. I have seen this exact mistake on three different client accounts. Always inspect before you alter.Snowflake Documentation
Official docs for today’s topics.
External References
Clustering diagnostics and cost monitoring.
Practice Questions
Options:
A. Micro-partitions are mutable — UPDATE statements modify them in place
B. Each micro-partition contains 50–500 MB of uncompressed data and is stored in columnar format
C. Micro-partitions require manual sizing via the PARTITION BY clause
D. Snowflake indexes each micro-partition for sub-second lookups
Why B: 50–500 MB uncompressed and columnar layout are the two facts Snowflake’s documentation calls out first. The actual on-disk size is smaller after compression (around 16 MB), but the uncompressed range is what the exam quotes.
Why not A: Micro-partitions are immutable. Every UPDATE, DELETE, and MERGE writes new partitions. The originals are retained for Time Travel and Fail-safe, then purged. “Mutable” is the classic Day 10 trap.
Why not C: There is no PARTITION BY in Snowflake DDL. Partitioning is automatic on every load. If you see PARTITION BY in an answer, that answer is wrong by definition.
Why not D: Snowflake has no indexes anywhere in the product. Pruning runs off per-partition min/max metadata. Any answer that mentions indexes is a distractor designed to catch candidates from a traditional RDBMS background.
Options:
A. A column that appears most frequently in ORDER BY operations
B. A column that appears most frequently in WHERE operations
C. A column that appears most frequently in GROUP BY operations
D. A column that appears most frequently in AGGREGATE operations
E. A column that appears most frequently in JOIN operations
Why B & E: Snowflake’s clustering best-practices documentation gives an explicit priority order. Cluster first on columns used in selective filters (WHERE). Then on columns used in JOIN predicates. These are the only two the docs recommend as primary candidates. Pruning runs before joins. A clustering key that aligns with the JOIN predicate lets Snowflake skip partitions on the joined side too.
Why not A: The docs explicitly describe clustering on ORDER BY columns as “usually less helpful.” Sorting happens after data is already scanned. The clustering key cannot help skip what the query has to read anyway.
Why not C: Same wording in the docs: clustering on GROUP BY columns is “usually less helpful.” Grouping is a post-scan operation. The cost is in the scan itself, which only pruning can reduce.
Why not D: AGGREGATE is not a separate operator in the priority list. Aggregations run on already-scanned rows. Clustering does not address the work they do.
Options:
A. The warehouse is too small — resize to fix
B. Inefficient pruning — the filter predicate doesn’t align with how the table is clustered
C. Cache miss — the result cache wasn’t reused
D. Too many micro-partitions — split the table
Why B: Scanning 99% of partitions on a 5 TB table is the textbook symptom of inefficient pruning. Either the filter is not selective, or the table is not physically organized on the filtered column. The fix is to change how the data is laid out (clustering key) or rewrite the query. Spending more on compute will not fix this.
Why not A: Resizing the warehouse would make each scanned partition faster to read. The query is still reading 12,400 partitions it should not have to read in the first place. Even on a 4XL, that is a lot of wasted I/O. Re-read Day 8 if missed.
Why not C: The result cache is a separate concept exposed elsewhere in Query Profile. It is not what “Partitions scanned / Partitions total” measures.
Why not D: “Split the table” is not a Snowflake operation. 12,500 partitions on a 5 TB table is normal. That works out to roughly 400 MB per partition uncompressed, right in the documented range.
Options:
A. Clustering keys are the same as indexes in traditional databases
B. Defining a clustering key on a small table is generally not recommended
C. Automatic reclustering consumes serverless credits separate from your warehouse credits
D. Once defined, a clustering key permanently freezes the table layout
E. Clustering keys cannot be dropped after creation
Why B: Small tables don’t have enough partitions for clustering to matter. Snowflake’s docs explicitly target multi-TB tables with large partition counts. On a small table the maintenance cost outweighs any pruning gain.
Why C: Auto-clustering runs on serverless compute managed by Snowflake. Your warehouse plays no part. The credits are tracked separately in AUTOMATIC_CLUSTERING_HISTORY. That is the right place to look when reclustering bills surprise you.
Why not A: Clustering keys reorganize the physical layout of micro-partitions. They are not B-tree indexes and they do not enforce uniqueness or lookup paths. Snowflake has no indexes. This shows up on the exam repeatedly.
Why not D: Auto-clustering continuously reorganizes the table as DML occurs. The layout evolves with the data. Nothing is “frozen.”
Why not E: ALTER TABLE ... DROP CLUSTERING KEY is a supported statement and is shown in the lab above. Clustering keys are fully reversible.
Options:
A. CLUSTER BY (event_ts) — full timestamp at original precision
B. CLUSTER BY (TO_DATE(event_ts)) — date truncation reduces cardinality
C. CLUSTER BY (event_id) — UUIDs cluster best
D. CLUSTER BY (event_type) — there are only 3 event types
Why B: Snowflake’s docs explicitly recommend wrapping high-cardinality timestamp columns in TO_DATE() or a similar truncation when used as a clustering key. Date is coarse enough to let Snowflake group rows into well-bounded partitions. It is still selective enough that a 1-hour BETWEEN filter prunes effectively. Snowflake only needs to read the one or two partitions covering the relevant date. This is exactly the scenario the exam writes around expression-based keys.
Why not A: A nanosecond-precision timestamp has cardinality close to the row count. Snowflake cannot group similar values into partitions, reclustering thrashes, and the credit bill rises with little pruning gain. The docs call this out directly.
Why not C: UUIDs are maximally cardinal and unrelated to the filter column. Clustering on a column the workload does not filter on is a textbook waste of credits, regardless of cardinality.
Why not D: Three distinct values mean every partition’s range covers all three. Any filter on event_type matches most partitions. Nothing to prune. Low-cardinality columns are explicitly called out in the docs as poor clustering candidates.
Today you learned: Snowflake stores every table in immutable 50–500 MB columnar micro-partitions, created automatically on every load. The min/max metadata on each column in each partition is what powers pruning. Snowflake skips partitions whose value ranges cannot satisfy the WHERE clause. Natural clustering, the implicit ordering from how data was loaded, is often enough on its own. Explicit clustering keys help on multi-TB tables with selective filters where pruning has degraded. The documented column priority is WHERE first, JOIN second. GROUP BY and ORDER BY are “usually less helpful.” Cardinality matters too: avoid both extremes. Auto-clustering uses serverless credits separate from your warehouse bill, tracked in AUTOMATIC_CLUSTERING_HISTORY.
Key takeaway: Pruning is the single most important performance lever in Snowflake. Before reaching for a bigger warehouse or a clustering key, always check Query Profile’s “Partitions scanned / Partitions total” first. That one ratio tells you whether the problem is data layout or compute size.
Tomorrow (Day 11): Table Types. Permanent vs Transient vs Temporary vs External. What each one gets (and doesn’t get) for Time Travel and Fail-safe, and the storage-cost tradeoff transient tables exist to solve. Practice Q37 from the practice doc (transient UNDROP behaviour) lives here.
