Micro-Partition in Snowflake: What It Is and How It Works
micro-partition in Snowflake is a small, immutable unit of data storage that organizes table data into contiguous blocks. These micro-partitions enable fast querying and efficient storage by automatically clustering data without manual indexing.How It Works
Imagine a large library where books are not just randomly placed but grouped into small, neat boxes based on their topics. In Snowflake, these boxes are called micro-partitions. Each micro-partition holds a small chunk of table data, usually between 50 MB and 500 MB compressed.
Snowflake automatically divides your data into these micro-partitions as you load it. Each micro-partition stores metadata like the range of values for each column, which helps Snowflake quickly find the data it needs without scanning the entire table. This is like having a detailed index for each box in the library, so you can jump directly to the right box instead of searching every shelf.
Because micro-partitions are immutable, Snowflake never changes them after creation. Instead, it creates new micro-partitions when data is updated or inserted. This design helps with fast, consistent queries and efficient storage management.
Example
This example shows how Snowflake automatically creates micro-partitions when you load data into a table.
CREATE OR REPLACE TABLE sales ( id INT, product STRING, amount FLOAT, sale_date DATE ); INSERT INTO sales VALUES (1, 'Shoes', 59.99, '2024-01-01'), (2, 'Hat', 19.99, '2024-01-02'), (3, 'Jacket', 99.99, '2024-01-03'); -- Query to check micro-partition metadata SELECT partition_number, min(sale_date) AS min_date, max(sale_date) AS max_date FROM TABLE(INFORMATION_SCHEMA.MICRO_PARTITIONS('sales')) GROUP BY partition_number ORDER BY partition_number;
When to Use
Micro-partitions are used automatically by Snowflake and require no manual setup. They are especially useful when you have large tables and want fast query performance without managing indexes or partitions yourself.
Use cases include:
- Data warehousing where large datasets are queried frequently.
- Time-series data where queries filter by date ranges.
- Analytical workloads that benefit from automatic clustering and pruning of data.
Because micro-partitions store metadata about data ranges, Snowflake can skip irrelevant partitions during queries, making data retrieval faster and cheaper.
Key Points
- Micro-partitions are small, immutable data blocks Snowflake uses to store table data.
- They automatically organize data and store metadata for fast query pruning.
- No manual partitioning or indexing is needed from users.
- Micro-partitions improve query speed and reduce compute costs by scanning only relevant data.
- They support Snowflake’s automatic clustering and data pruning features.
