Bucketing vs Partitioning, and the Full-Scan Trap
Bucket (or cluster) a high-cardinality join column into a fixed number of files instead of partitioning it, and keep the partition key bare in a filter so the engine can actually prune.
Bucketing: partitioning's high-cardinality cousin
Some columns you want to organize by are exactly the ones you must not partition on: user_id, session_id, a hash. They are high cardinality, so partitioning explodes into tiny files. Bucketing (Spark and Hive) or clustering (Snowflake, BigQuery) solves this. Instead of one folder per value, you hash the column into a fixed number of buckets, say 8 or 256, and every row lands in one bucket by its hash. You get organization for joins and aggregations without the file explosion, because the number of files is capped no matter how many distinct values there are.
| technique | key type | how many files | use for |
|---|---|---|---|
| partitioning | low-cardinality (dt) | one folder per value | the column you filter on |
| bucketing / clustering | high-cardinality (user_id) | a fixed number of buckets | the column you join or group on |
The teaching rule: partition by the low-cardinality column you filter on; bucket (or cluster) by the high-cardinality column you join or group on. A table can do both: partition by dt, bucket by user_id.
Bucketing only helps when the hash spreads evenly. A single mega-key (one user with a huge share of the rows) hashes entirely into one bucket and makes it far larger than the others, which reintroduces the skew you were trying to avoid. The exercises measure exactly this by comparing the largest bucket to the average.
The full-scan trap
Pruning only happens when the engine can match your filter to the partition key, and it is easy to accidentally defeat it. Two classic mistakes:
- Filtering on a non-partition column. If the table is partitioned by
dtbut you filter only onuser_id, nothing prunes and the engine scans every partition. - Wrapping the partition key in a function. Comparing a bare partition column to a constant (
WHERE dt = '2026-01-05') prunes. Transforming the column first stops the engine from pruning, so it reads the whole table. For exampleWHERE substr(dt, 1, 7) = '2026-01'orWHERE date(dt) = date('2026-01-05')wrapsdtin a function and forces a full scan, where a bareWHERE dt >= '2026-01-01' AND dt < '2026-02-01'would have pruned. The portable rule is to keep the partition column bare on one side and a constant on the other.
The cost of the trap is total: a query that should read one partition ends up reading all of them. The practice exercise reads a query log and flags the queries that defeated pruning and scanned every partition.
One more bucketing payoff to bank: two tables bucketed the same way (same key, same bucket count) can be joined on the bucket key with no shuffle, because matching keys already sit in matching buckets. That is the join-side twin of the broadcast trick you will meet in the next module.
Common mistake: wrapping the partition key in a function. Comparing the bare column to a constant prunes, but transforming it first (a substring or a cast) forces a full scan, so keep the partition column bare on one side and a constant on the other.
Interview nuance: "you partitioned by date but the query still scans everything, why" almost always means the filter did not land on the bare partition key: it filtered a different column, or wrapped dt in a function. Keep the partition column bare and compared to a constant.
On a real platform this differs. Modern table formats reduce these traps: Apache Iceberg supports hidden partitioning (you query
event_timeand Iceberg derives theday(event_time)partition for you) and partition evolution (change the scheme as a metadata-only operation, with no data rewrite). The bucketing and pruning arithmetic you compute here is the same reasoning those systems automate.
CREATE TABLE user_buckets (
bucket_id INTEGER, -- hash(user_id) % 8
user_count INTEGER,
size_bytes INTEGER
);
INSERT INTO user_buckets (bucket_id, user_count, size_bytes) VALUES
(0, 125000, 310000000),
(1, 130000, 320000000),
(2, 120000, 305000000),
(3, 128000, 315000000),
(4, 122000, 300000000),
(5, 131000, 322000000),
(6, 119000, 298000000),
(7, 900000, 1400000000); -- a mega-user hashes here and skews the bucket-- The bucket layout, largest first. A balanced hash keeps buckets close in size.
SELECT bucket_id, user_count,
ROUND(size_bytes / 1000000.0, 0) AS size_mb
FROM user_buckets
ORDER BY size_bytes DESC;Apply
Your turn
The task this lesson builds to.
Write a query that returns how balanced a bucketed layout is, as (bucket_count, avg_mb, max_mb, skew_ratio), over user_buckets(bucket_id, user_count, size_bytes).
skew_ratio is the largest bucket size divided by the average bucket size (a balanced hash gives a ratio near 1; a mega-key pushes it up). Treat 1 MB as 1,000,000 bytes and round avg_mb, max_mb, and skew_ratio to 2 decimals.
3 hints and 1 automated check are waiting in the workspace.
Practice
Make it stick
A second problem on the same idea, plus 4 bonus drills.
Write a query that returns the queries that fell into the full-scan trap (they scanned every one of the table's 7 partitions), as (query_id, predicate, bytes_scanned), most bytes first, over query_log(query_id, predicate, partitions_scanned, bytes_scanned).
A query pruned correctly when it scanned fewer than all 7 partitions; it hit the trap when partitions_scanned equals 7 (it read the whole table). Order by bytes_scanned descending, then query_id.
2 hints and 1 automated check are waiting in the workspace.