In an Iceberg table partitioned by bucket(16, user_id), why does a range filter on user_id scan every bucket?

## What the bucket transform does The Iceberg format defines `bucket[N]` as a hash of the source value — a 32-bit Murmur3 hash of its canonical serialized form — reduced to a non-negative integer modulo N. The result is a bucket number stored as that file's partition value. The point of the transform is uniform spread: a high-cardinality key such as a user or order id is scattered evenly across N partitions regardless of how skewed the raw values are, which keeps partition sizes even and gives writers a predictable number of output files. Uniform spread and order preservation are opposites. A good hash destroys locality on purpose, so consecutive ids land in unrelated buckets. ## Why that kills range pruning Iceberg prunes by projecting the user's predicate through the partition transform. The projection has to be sound: it may keep files that turn out to hold no matching rows, but it must never drop a file that holds one. For an order-preserving transform, a range on the source column implies a range on the partition value — if `event_ts >= 2026-05-01` then `day(event_ts) >= 2026-05-01`, so earlier days can be dropped. For a hash, no such implication exists. `user_id BETWEEN 100 AND 200` says nothing about the bucket numbers involved; the 101 values could occupy all 16 buckets. The only sound projection is "every bucket", so nothing is excluded. Equality is different. If `user_id = 4711`, then the file containing it must have bucket value `bucket(4711)`, a value the planner can compute directly. So equality prunes to exactly one bucket out of N, and an `IN` list prunes to at most one bucket per listed value. That is the access pattern bucketing is designed for. ```sql -- prunes to a single bucket SELECT * FROM prod.db.events WHERE user_id = 4711; -- prunes to at most three buckets SELECT * FROM prod.db.events WHERE user_id IN (4711, 4712, 9001); -- touches all 16 buckets: no sound projection through a hash SELECT * FROM prod.db.events WHERE user_id BETWEEN 100 AND 200; ``` ## Diagnosing it in the wild The symptom is a query with a selective-looking predicate whose scan plans every partition. The give-away in the plan is that the partition filter is absent or trivially true while the row-level filter is present. Check the table's partition spec first: if the column in the predicate appears under `bucket`, and the predicate is a range, this is the explanation and no amount of statistics tuning changes it. The same reasoning explains a related surprise: a `truncate` transform *does* preserve order, so ranges and prefix predicates project through it. Candidates who lump the two transforms together as "the non-time ones" usually get this wrong. ## What to do about it There are three honest options, and choosing between them is the senior part of the answer. 1. **Accept it and let file bounds work.** Declare a sort order on the bucketed column so that within each bucket, files hold contiguous id ranges. The range predicate still visits all N buckets' manifests, but per-file lower and upper bounds on `user_id` drop nearly every file, so the bytes actually read collapse even though partition pruning did nothing. This is usually the cheapest fix. 2. **Change the partition field.** If ranges on that column are the dominant access pattern, bucketing is optimising for the wrong query. Partition on a transform that preserves order — a time transform, or `truncate` on the id — and, because spec evolution is metadata-only, you can make the change without rewriting the table, accepting that only new data gets the new layout. 3. **Keep bucketing for its other benefits.** Bucketing on a key gives even file sizes on skewed data, bounds the file count per commit, and lets engines that understand the layout co-locate work on that key for joins and merges. If those matter more than range scans, keep the spec and fix the query patterns instead. ## The interview framing State the mechanism first (hash, modulo N, not order preserving), then the rule (only equality and IN project through it), then the contrast with an order-preserving transform, and finish with the practical remedy. The weak answer says "bucketing is bad for ranges" without explaining projection; the strong one explains why the projection must be sound, which is also why Iceberg never returns wrong results — it just plans more files than you hoped.