Comparison
Clustering keyvsPartitioning strategy
Clustering key
the table is partitioned by day and sorted within each day by customer, so filtering on a customer skips most blocks inside the partition.
The column data is physically ordered by within a partition, so per-block statistics become selective. It is the second-order layout decision after partitioning, and it is what makes filters on high-cardinality columns cheap without creating a folder per value. Ordering is not free: it is maintained by rewrite, so a clustered table needs periodic maintenance to stay clustered as data is appended.
Full entry →Partitioning strategy
you partitioned by customer id, there are two million customers, and now every query lists two million tiny folders.
The choice of what a table is physically split by, which sets how much data a filtered query can avoid reading. It is the highest-leverage physical decision in a warehouse and the hardest to change later, because changing it rewrites the table. The two failure modes are opposite and equally common: too coarse and nothing is pruned, too fine and you have the small files problem plus metadata that dwarfs the data.
Full entry →