Snowflake Micro-partitions, Pruning and Clustering Keys

1.What a micro-partition is

Traditional data warehouses use static partitioning. A partition is a unit you define and manage yourself with special DDL, and it comes with familiar problems such as maintenance overhead and data skew, where some partitions end up much larger than others. Snowflake takes a different approach called micro-partitioning. You don't declare partitions and you don't maintain them. Every table, whatever its type, is split automatically as data arrives, and the split follows the order in which rows are inserted or loaded.

Each micro-partition holds 50 MB to 500 MB of uncompressed data. The stored size is smaller, because Snowflake always compresses data. Within a micro-partition, groups of rows are stored by column. Each column is compressed on its own, and Snowflake picks the best compression algorithm for each column in each micro-partition. Because columns are stored separately, a query reads only the columns it references.

For each micro-partition, Snowflake records metadata that includes the range of values in each column and the number of distinct values. Micro-partitions can overlap in their value ranges. Together with their small, uniform size, this overlap helps prevent skew.

DML statements such as DELETE, UPDATE and MERGE also use this metadata. Some operations never touch the data files. Deleting every row in a table, for example, is a metadata-only operation. Dropping a column works in a similar way: Snowflake doesn't rewrite the affected micro-partitions when the statement runs.

Sources1

2.Pruning: skipping micro-partitions you don't need

Because Snowflake knows the value range of every column in every micro-partition, it can skip any micro-partition that can't contain matching rows. This is called pruning, and it happens in two steps. First, Snowflake prunes the micro-partitions the query doesn't need. Then it prunes by column within the micro-partitions that remain. Pruning works on semi-structured columns too.

In the ideal case, a filter that selects 10% of a column's value range scans only about 10% of the micro-partitions. The documentation gives an example: a table holds one year of data with date and hour columns, and the data is spread evenly. A query for one particular hour would ideally scan 1/8760th of the micro-partitions, and only the hour column within them. Pruning is more efficient the closer the scanned fraction gets to the fraction of data actually selected. For time-series data, this can mean sub-second responses for slices of an hour or less.

Not every predicate can be used for pruning. A predicate that contains a subquery does not prune, even when the subquery returns a constant.

Sources1

3.Data clustering and clustering depth

Pruning only works well if similar values sit together. Table data is usually sorted along natural dimensions such as date or region, which is known as clustering. When data is loaded, Snowflake records clustering metadata for each new micro-partition. That metadata covers the total number of micro-partitions, how many of them overlap in value range for a given set of columns, and how deep those overlaps go.

Clustering depth is the average depth of overlapping micro-partitions for the columns you specify. It is 1 or greater for a populated table, and an empty table has a depth of 0. A smaller depth means better clustering. If no micro-partitions overlap at all, the table is in a constant state, meaning clustering can't improve it further. Real tables rarely reach that state, and they don't need to in order to perform well.

You can use clustering depth to track a large table's clustering health over time as DML runs against it, and to decide whether the table needs an explicit clustering key. It isn't a precise measure, though. Query performance is the real test: if queries slow down over time, the table has probably lost its clustering.

Sources1

4.Clustering keys: when to define one and what to put in it

Over time, and especially as DML hits very large tables, data can stop clustering well on the dimensions you care about. You could fix this yourself by sorting rows and reinserting them, but that is cumbersome and expensive. Instead, you can define a clustering key: one or more columns or expressions that Snowflake uses to keep similar rows together in the same micro-partitions. A table with a clustering key is called clustered. You can set the key in CREATE TABLE or add it later with ALTER TABLE, and you can change or drop it at any time. Materialized views can be clustered too. Hybrid tables can't, because their data is always ordered by primary key.

After you define a key, Snowflake handles reclustering automatically. Rows aren't necessarily reorganized straight away, because Snowflake only does maintenance when the table will benefit. That maintenance uses compute and consumes credits, so a clustering key isn't something every table should have. A table is a good candidate when it has a large number of micro-partitions (typically multiple terabytes), when queries are selective or sort the data, and when most queries filter or sort on the same few columns. To keep costs down, look for tables that are queried often and changed rarely. Before you add a key, run a representative set of queries to establish a performance baseline.

Snowflake recommends no more than 3 or 4 columns or expressions per key, because adding more usually raises cost faster than it improves performance. A good key column has enough distinct values for pruning to work, but few enough that Snowflake can group rows into the same micro-partitions.

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Every large table should get a clustering key, because clustering always makes queries faster.Why is that wrong?

   Clustering consumes credits to set up and maintain. It only makes sense for multi-terabyte tables whose queries benefit enough to cover that cost.

   Covered in Clustering keys: when to define one and what to put in it

2. 2.A higher clustering depth means a table is better clustered.Why is that wrong?

   Depth measures how much micro-partitions overlap, so a smaller depth means better clustering.

   Covered in Data clustering and clustering depth