Why partition a lakehouse table by a hash bucket of user_id rather than by user_id itself?

## Cardinality is the constraint A partition column's cardinality decides how many partitions you get, and partition count decides how much metadata the planner must process and how large each partition's files can be. This gives a simple rule: the number of distinct values must be small relative to the volume of data, so that every partition holds enough rows to justify at least one properly sized data file. Date satisfies this naturally — a few thousand days for years of history. An identifier does not. Partitioning a 5 TB event table by `user_id` with 50 million distinct users yields 50 million partitions averaging 100 KB. Every query, even one that prunes perfectly, first pays to plan over 50 million partition entries, and every read of a single user still opens a tiny file whose overhead exceeds its payload. ## What a transform is A partition **transform** is a function the table records: the partition value is defined as `f(column)` rather than as a column an ingest job maintains by hand. The family in common use across formats is small: - **time transforms** — year, month, day, hour of a timestamp. Order-preserving, so range predicates translate. - **truncate(width)** — for numbers, round down to a multiple; for strings, keep a prefix. Order-preserving, so ranges and prefix matches translate. - **bucket(N)** — hash the value and take it modulo N. Order-destroying; only equality translates. - **identity** — the value itself, which is what plain Hive-style partitioning does. ## What bucketing buys `bucket(64, user_id)` gives exactly 64 partitions no matter how many users exist. Two properties follow: 1. **Bounded partition count.** The count is set by you, not by the data's cardinality, so it cannot drift as the user base grows. 2. **Equality pruning survives.** For `WHERE user_id = 91234`, the engine applies the *same* recorded hash to the literal, gets a bucket number, and reads only files in that bucket — roughly 1/64th of the table. That is a large win for a needle-in-a-haystack lookup, and it costs nothing at write time beyond the hash. A secondary benefit is join locality: two tables bucketed the same way on the same join key can, in engines that support it, be joined bucket-to-bucket without a full shuffle. ## What bucketing costs - **No range pruning.** `user_id BETWEEN 1000 AND 2000` touches every bucket, by design. If your access pattern is ranges over the key, bucketing is the wrong transform. - **No human-readable layout.** `user_bucket=17` tells an operator nothing about its contents. - **N is effectively permanent in practice.** Changing the bucket count re-maps every value, so existing files no longer satisfy the new rule; formats that support layout evolution will apply the new count only to new data, leaving history under the old bucketing. - **Skew is not solved, only spread.** A single pathological key still lands entirely in one bucket. Hashing fixes *cardinality*, not *hot keys*. ## Choosing N Work backwards from file size. Aim for each bucket, within whatever time partition sits above it, to hold at least one target-sized data file. If a day holds 64 GB and you want files in the hundreds of megabytes, a couple of hundred buckets is defensible; 4096 is not. Powers of two are conventional and make the arithmetic obvious, not because the hash requires it. ## The usual combination Production tables rarely bucket alone. The common shape is a time transform for the range access pattern and a bucket for the point-lookup access pattern: ``` partition by day(event_ts), bucket(64, user_id) ``` A dashboard filtering a date range prunes on the first field; a support tool looking up one user's history in a date range prunes on both. The partition count stays at *days x 64*, which is bounded and predictable. ## Truncate, the middle option Where the key is a string with meaningful prefixes — a country-prefixed code, a sortable identifier — `truncate(width)` keeps order while collapsing cardinality, so both prefix equality and ranges still prune. Reach for it when the key has structure worth preserving; reach for bucketing when it does not.