Home › Learn › How reads and writes work › 4 · Joins
Delta Lake and Spark · from zero to advanced
Joins: big with small, big with big, and how layout helps
The join strategies Spark picks and why: broadcast hash, sort-merge, what AQE changes at run time, dynamic partition pruning that cut files read from 22 to 6, skew, and how organising files by the join key speeds joins up. Measured shuffle bytes for each.
The problem
Why joins are the expensive part
A join has to bring matching rows together. If they already sit on the same machine that is cheap. If not, rows must shuffle across the network, and that is where most join time goes. Spark chooses between a few strategies to avoid or minimise the movement.
Strategy 1: broadcast
Big table with a small table: send the small one everywhere
No shuffle of the big table at all.
BroadcastHashJoin with 0 bytes shuffled. A 50,000-row table (204 KB on disk) was broadcast too.- The limit: a table is broadcast if it is below a size threshold. The Databricks AQE page lists
spark.databricks.adaptive.autoBroadcastJoinThresholdat 30 MB for the run-time switch. The planning-time threshold default (10 MB in open-source Spark) was not re-checked because the Spark docs page was not reachable. - A broadcast table has to fit in the driver and in every executor's memory. Broadcasting something too big is a classic cause of out-of-memory errors.
Strategy 2: sort-merge
Big table with big table: shuffle both by the key
Both sides move. Nothing is small enough to copy.
SortMergeJoin: 32.6 MB shuffled and 12 tasks instead of 8. Two big tables joined on event_id shuffled 35.8 MB.| Join (measured) | Strategy | Shuffle written | Tasks | Time |
|---|---|---|---|---|
| big × small (10 rows), default | BroadcastHashJoin | 0 B | 8 | 0.53 s |
| same, broadcast switched off | SortMergeJoin | 32.6 MB | 12 | 0.73 s |
| big × medium (50,000 rows) | BroadcastHashJoin | 0 B | 8 | 0.36 s |
| big × big (threshold lowered) | SortMergeJoin | 35.8 MB | 12 | 0.73 s |
| big × big, AQE on | BroadcastHashJoin (AQE changed it) | 35.8 MB | 12 | 0.80 s |
Times are small because the data is small. The shuffle bytes and task counts are what grow with the data. The Databricks docs say Photon replaces sort-merge joins with hash joins (see the Photon page).
Adaptive query execution
AQE: fixing the plan with real sizes
AQE can only switch after it has measured the shuffle.
BroadcastHashJoin, yet it still wrote 35.8 MB of shuffle. This matches the Databricks docs, which say AQE only switches after both sides are shuffled and that a broadcast hint is still worth using when you know the sizes.| AQE feature (Databricks docs) | What it does | Default |
|---|---|---|
| Sort-merge to broadcast | Switches when a side is small after the shuffle | 30 MB (spark.databricks.adaptive.autoBroadcastJoinThreshold) |
| Coalesce partitions | Merges many small shuffle partitions | target 64 MB (advisoryPartitionSizeInBytes) |
| Skew join | Splits oversized partitions | factor 5, 256 MB |
| Empty relation propagation | Drops work on empty inputs | on |
Skew
When one key has most of the rows
One slow task holds up the whole stage.
- AQE splits skewed partitions for most join types, but never in a broadcast join, and not on the outer side of some outer joins (the docs list left-side handling for INNER, LEFT OUTER, LEFT SEMI, LEFT ANTI and right-side for INNER, RIGHT OUTER).
- The old
SKEWhints are marked retired in the docs: AQE handles it by default. - Skew was not measured here.
Pruning through the join
Dynamic partition pruning: let the small side shrink the big side
The filter on the small table travels into the big table's scan.
region = 'AMER': without dynamic partition pruning 22 files and 200,010 rows were scanned; with it 6 files and 40,010 rows, and the plan contained dynamicpruning. It needs a broadcast of the filtered side to work, so turning broadcast off in the first attempt switched it off too.- Databricks adds dynamic file pruning (not measured): for joins on Delta tables it pushes the small side's values into the big Delta table's scan to skip files, not only partitions. Per the docs it is on by default (
spark.databricks.optimizer.dynamicFilePruning), applies when the big table is above 10 GB and 10 files, works best on non-partitioned tables, and needs Photon for MERGE, UPDATE and DELETE.
Layout helps joins
How organising files speeds up joins
| Arrangement | Helps because | Source |
|---|---|---|
| Partition by a low-cardinality join column | Partition pruning, and dynamic partition pruning from the other side | Measured above |
| Cluster or Z-order by the join key | Narrow min/max per file, so pushed-down key values skip files | Z-order measured on filters; join effect from the Databricks docs |
| Keep the small side small and broadcast-able | No shuffle of the big table | Measured above |
| Compact small files | Fewer, bigger tasks on both sides | Measured on layout page |
The Databricks dynamic file pruning page recommends liquid clustering on frequently joined columns and Photon-enabled compute.
Choosing
Which join, when
| Situation | Expect | Verdict |
|---|---|---|
| One side is small | Broadcast hash join, no shuffle | Pursue |
| Both sides large, same key | Sort-merge join, plan for the shuffle | Pursue |
| You know a side is small but stats are missing | Add a broadcast hint instead of waiting for AQE | Pursue |
| Broadcasting a big table | Out-of-memory risk | Drop |
| Joining on a column with a few huge keys | Skew: check the plan and the task times | Drop |
FAQ
Questions people ask
What is the difference between a broadcast join and a sort-merge join?
A broadcast join copies the small table to every executor, so the big table is never moved. A sort-merge join shuffles both tables by the join key, sorts them and merges them. In the local test the broadcast join wrote 0 bytes of shuffle and the sort-merge wrote 32.6 MB for the same data.
Does AQE avoid the shuffle?
No. It uses the real sizes after the shuffle stage to change the plan, for example to a broadcast join, so the shuffle that produced those sizes has already happened.
What is dynamic partition pruning?
When a join's other side is filtered to a few values, Spark passes those values to the big table's scan so only matching partitions are read. Here it cut 22 files and 200,010 rows to 6 files and 40,010 rows.