Micro-Partitions and Clustering
Overview
Micro-partitions are the fundamental building blocks of Snowflake's storage architecture. Unlike traditional databases where database administrators manually define partitions and indexes, Snowflake handles this automatically. For the COF-C03 exam, you must understand how micro-partitions work, how data is pruned during queries, and when to intervene with manual clustering.
Key Concepts
Micro-Partitions
All data in Snowflake tables is automatically divided into micro-partitions.
- Size: They are contiguous units of storage containing between 50 MB and 500 MB of uncompressed data, which typically compresses down to roughly 16 MB.
- Columnar: Data within a micro-partition is stored in a columnar format.
- Immutable: Once written, a micro-partition cannot be changed. Updates or deletes to data result in new micro-partitions being written, while the old ones are retained for Time Travel until they expire.
Query Pruning (The Cloud Services Layer Magic)
Because Snowflake does not use traditional indexes, it relies on metadata to find data fast.
- As data is ingested, the Cloud Services layer calculates and stores metadata for every micro-partition (e.g., MIN value, MAX value, NULL count, distinct values for every column).
- When a query runs (e.g.,
WHERE date = '2023-10-01'), the Cloud Services layer checks the metadata. It instantly ignores (prunes) any micro-partitions where the requested date falls outside the MIN/MAX range. - Only the relevant micro-partitions are downloaded to the Virtual Warehouse for processing.
Natural Clustering
As data is loaded into Snowflake (e.g., by date), it is naturally ordered in the micro-partitions. If you typically query your data by the same order it is loaded (like querying logs by timestamp), Snowflake's natural clustering provides excellent performance without any manual intervention.
Clustering Keys and Automatic Clustering
Over time, as data is updated, deleted, or inserted out of order, the natural clustering can degrade (micro-partitions begin to overlap in their MIN/MAX ranges), leading to less effective pruning.
- Clustering Key: If natural clustering is insufficient (usually for tables > 1 TB), you can define a Clustering Key on 1-4 columns that are frequently used in
WHEREclauses. - Automatic Clustering: Once a Clustering Key is defined, Snowflake's serverless backend automatically and continuously reorganizes the micro-partitions in the background to align with the key. You do not schedule this; Snowflake handles it.
- Cost: Automatic Clustering consumes serverless compute credits.
How It Works: Clustering Depth
Clustering Depth is a metric that measures how overlapping the micro-partitions are.
- A lower clustering depth means micro-partitions are well-sorted and don't overlap much (good for pruning).
- A higher clustering depth indicates heavy overlap (bad for pruning).
SQL Examples
-- Check the clustering depth of a table based on a specific column
SELECT SYSTEM$CLUSTERING_DEPTH('sales_data', '(sales_date)');
-- Define a clustering key on an existing table
ALTER TABLE sales_data CLUSTER BY (sales_date, region);
-- Suspend automatic clustering (stops background reorganization and billing)
ALTER TABLE sales_data SUSPEND RECLUSTER;
Exam Tips
⚠️ Crucial for COF-C03:
- Size: Memorize the 50-500 MB uncompressed size, compressing to roughly 16 MB.
- When to Cluster: Do not use clustering keys on small tables. Snowflake recommends them only for multi-terabyte tables where query performance has degraded.
- Who does the clustering? Automatic Clustering is a serverless feature. It does not use your Virtual Warehouses.
- Immutability: DML operations (UPDATE/DELETE) create new micro-partitions. They do not modify existing ones in place.
Key Takeaways
- Micro-partitions are automatic, columnar, and immutable.
- Query pruning uses metadata (MIN/MAX) to avoid scanning unnecessary data.
- Clustering Keys are for manual sorting definition on very large tables.
- Automatic Clustering runs continuously in the background using serverless compute.