Data Partitioning vs Clustering Key
People think partitioning and clustering are the same trick. One cuts a table into separate parts, often one per day. The other keeps the table whole but sorts the rows in it. Split on a column with few values, like a date. Cluster on one with many, like a customer ID.
Data Partitioning
A big table cut into parts by one column. Most often that column is a date.
Partitioning splits one big table into parts by the value of a column. The column is often a date, so each day or month gets its own part. In a data lake, each part is usually its own folder. Queries that filter on that column only read the parts they need. Old parts can also be dropped in one step.
Clustering Key
Rows are kept in order by one column. Then a query can skip blocks.
A clustering key tells a warehouse to store rows with close values near each other. Unlike a partition, it does not cut the table into separate parts. It sorts the data inside the files, often on a column you filter by a lot. Then the engine skips blocks that cannot match. Snowflake and BigQuery both offer it.
In one line
- Data Partitioning
- A big table cut into parts by one column. Most often that column is a date.
- Clustering Key
- Rows are kept in order by one column. Then a query can skip blocks.