Snowflake Micro-Partitions & Clustering: How Data Organization Impacts Performance

Most Snowflake users understand warehouses, credits, and queries. But key details sit under the hood. Tables use micro-partitions, which are data blocks that enable pruning. This reduces extra data scans and improves query performance.

If you understand how these micro-partitions (and, by extension, clustering) work, you can improve query efficiency. However, this approach has limits, and relying on it alone could lead to missed savings.

In this article, we’ll break down how Snowflake structures data into micro-partitions, how clustering affects query pruning, and how to use metadata functions like SYSTEM$CLUSTERING_INFORMATION to diagnose issues and improve performance.

What Are Micro-Partitions in Snowflake?

Micro-partitions are Snowflake’s core storage units. Each holds between 50 and 500 MB of uncompressed data. Snowflake always stores data compressed, so the actual footprint is smaller. Snowflake creates and manages micro-partitions automatically as data is loaded or changed.

For each micro-partition, Snowflake tracks column-level statistics like min/max values, number of distinct values, null counts, additional internal statistics, and more. This rich array of metadata enables Snowflake’s query optimizer to decide which partitions to skip during query execution (also known as pruning), turning what would be a full table scan into a targeted, efficient read.

What’s more, Snowflake micro-partitions are immutable, meaning that Snowflake doesn’t update them when data changes. Instead, the platform simply creates new blocks. There are many benefits to this approach, including improved consistency, simplified concurrency, and powerful Time Travel and cloning features.

Understanding Clustering in Snowflake

To understand micro-partitions, understand clustering. It organizes data by key columns to group related rows, improving pruning and query performance on large tables.

There are two main types of clustering behavior:

• Natural clustering happens when there’s no defined clustering key; in this case, Snowflake organizes data in order of insertion
• Defined clustering happens when you set explicit clustering keys; in this case, Snowflake orders data around specific columns

Here’s an example of how to define a clustering key: 

CREATE OR REPLACE TABLE orders ( order_id INT, customer_id INT, order_date DATE, total_amount NUMBER(10,2) ) CLUSTER BY (order_date, customer_id);

Defined clustering takes some effort to set up and uses compute resources. However, it helps avoid split or overlapping partitions and better match common query patterns.

Use clustering with care. A setup that helps one workload may hurt another. Choosing keys requires understanding your data, queries, and performance impact.

How Automatic Clustering Works

As tables change with inserts, deletes, and updates, clustering can drift from the key you defined. Defining a clustering key enables Automatic Clustering, which reclusters the table in the background. You don’t assign a warehouse to it, and it doesn’t block DML. Snowflake decides when it’s worth reclustering a table and runs those jobs on its own.

Snowflake now runs two versions of Automatic Clustering:

Manual reclustering is deprecated and removed from most accounts. Defining a clustering key enables automatic clustering. To control cost, you can suspend automatic reclustering.

ALTER TABLE orders SUSPEND RECLUSTER;

Replace SUSPEND with RESUME to enable automatic clustering again as needed.

How Do I Query Clustering Information and Health?

Snowflake offers two system functions to surface key information for assessing your clustering health. First, the SYSTEM$CLUSTERING_INFORMATION provides a summary of how well data is organized relative to the clustering keys:

SELECT SYSTEM$CLUSTERING_INFORMATION('MY_TABLE');

If you run that query, Snowflake will return:

• Clustering key expression (i.e. which columns define the clustering)
• Average overlap depth
• Number of partitions

As well as several other useful stats that indicate how the current clustering of your table is performing.

Additionally, the SYSTEM$CLUSTERING_DEPTH function focuses specifically on depth metrics for defined keys. It’s useful for detecting when a table has drifted and might need reclustering. You can execute that query by replacing the function in the example listed above. 

You can also pass candidate columns to these functions to evaluate a clustering key before you define it, either on an unclustered table or when you’re considering a new key:

SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(order_date)');

How Does Query Pruning Work in Snowflake? 

Snowflake query pruning is the process of skipping data that does not match a query. This helps improve performance and reduce costs. But how does it work? Here’s a quick look:

1. Each micro-partition stores metadata, including minimum and maximum values for each column, along with distinct counts and other stats.
2. When a query runs with filters (e.g., WHERE clauses), Snowflake compares those filters to the min and max metadata for each micro-partition.
3. If the metadata shows no overlap with the filter, Snowflake skips that micro-partition. This avoids scanning extra data.

Because micro-partitions are small (50 to 500 MB of uncompressed data), pruning works at a very detailed level. This is why queries on large tables can run fast, often scanning only a small portion of the data.

Limitations of Snowflake Clustering

Clustering is a tool for specific tables and query patterns. A few things to keep in mind:

• Determining a good clustering key requires a careful analysis of the entire workload.
• Choosing a poor clustering key can degrade performance rather than improve it, and may even be worse than natural clustering.
• Reclustering, especially on large tables with heavy churn, consumes credits. Check how often a table changes before you cluster it.
• Complex filters and query patterns may not benefit from pruning sufficiently and may still call for a larger warehouse or Snowflake’s search optimization service.
• Clustering can differ by object type, such as external tables. This affects how it works and how you inspect it.

Final Thoughts on Snowflake Micro-Partitions and Clustering

Snowflake micro-partitions are the base of its performance, and clustering helps organize the data inside them.

These features can improve performance and help control costs. However, they do not work well in every case. Even with automatic clustering, some workloads are hard to manage. For example, real-time changes or sudden spikes can be difficult, since clustering has a delay. Workloads whose filter patterns change often are harder to cluster well, because the right key depends on how the table is queried. Revisit the key when query patterns shift.

Clustering reduces how much data a query reads. Warehouse sizing decides how much compute you pay for to read it. Keebo works on that second layer, adjusting warehouse size continuously as your workloads change, so the gains you get from good clustering show up as lower spend.

Snowflake’s clustering keeps your data organized for pruning. Keebo keeps the warehouses that query it sized to the work.

Frequently Asked Questions

How do micro-partitions improve query performance?

Snowflake stores metadata for each partition. The optimizer uses it to skip partitions that don’t match filters. This reduces I/O and speeds up queries.

Does clustering always improve performance?

No. Clustering is most beneficial for large tables with stable data and frequent, selective queries. For small tables or write-heavy workloads, clustering overhead may outweigh benefits.

What are the risks or downsides of clustering?

The main cost is credits for reclustering, which rise with table size and churn. On tables with heavy, continuous DML, reclustering can run often enough that the cost outweighs the pruning gain. Poor key choice can waste those credits.

When should I avoid clustering and rely on default micro-partitioning?

For small-to-medium tables, frequently updated tables, or datasets with high insert churn where the clustering key would quickly become stale, default micro-partitioning may be sufficient and cheaper.