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

Small table(2 KB on disk)Driver collects it,then sends a copyExecutor 1: its slice of the big table+ a full copy of the small tableExecutor 2: its slice of the big table+ a full copy of the small tableExecutor 3: its slice of the big table+ a full copy of the small tableEach slice joins locallyshuffle written: 0 bytesthe big table never moves

No shuffle of the big table at all.

Measured. Joining the 200,000-row table to a 10-row table produced a 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.autoBroadcastJoinThreshold at 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

Big table Aslices on many executorsTable Bslices on many executorsShuffle by thejoin keysame key lands onthe same executor35.8 MB movedSort each sideby the keythen walk bothlists together(merge)Joined rowstasks: 12time: 0.73 sBoth sides cross the network. This is the expensive path, and the one used when neither table is small.

Both sides move. Nothing is small enough to copy.

Measured. Switching broadcast off for the same join forced a SortMergeJoin: 32.6 MB shuffled and 12 tasks instead of 8. Two big tables joined on event_id shuffled 35.8 MB.
Join (measured)StrategyShuffle writtenTasksTime
big × small (10 rows), defaultBroadcastHashJoin0 B80.53 s
same, broadcast switched offSortMergeJoin32.6 MB120.73 s
big × medium (50,000 rows)BroadcastHashJoin0 B80.36 s
big × big (threshold lowered)SortMergeJoin35.8 MB120.73 s
big × big, AQE onBroadcastHashJoin (AQE changed it)35.8 MB120.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

Planned before runningSortMergeJoin(sizes unknown)Both sides are shuffled35.8 MB writtennow real sizes are knownOne side is smallenough to broadcastFinal plan:BroadcastHashJoinAQE changes the plan using real sizes after the shuffle stage finishes. The shuffle itself was already paid for.So if you know a table is small, hint the broadcast up front instead of waiting for AQE.

AQE can only switch after it has measured the shuffle.

Measured. With AQE on and the planning threshold lowered, the join that would have been a sort-merge ended as a 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 doesDefault
Sort-merge to broadcastSwitches when a side is small after the shuffle30 MB (spark.databricks.adaptive.autoBroadcastJoinThreshold)
Coalesce partitionsMerges many small shuffle partitionstarget 64 MB (advisoryPartitionSizeInBytes)
Skew joinSplits oversized partitionsfactor 5, 256 MB
Empty relation propagationDrops work on empty inputson

Skew

When one key has most of the rows

Rows per shuffle partition (illustration, not measured)← one huge partition: one slow taskSkew handling splits the huge partition into piecesso several tasks share the workDatabricks defaults: a partition counts as skewed if it is5× the median and above 256 MB

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 SKEW hints 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

Small table filtered:region = 'AMER'→ US, BRSpark passes that listinto the big table's scanbefore it readsINUSUKDEFRAUAESGJPBRpartitions of the big table, one folder per country; dashed = never readWithout it: 22 files, 200,010 rowsWith it: 6 files, 40,010 rows

The filter on the small table travels into the big table's scan.

Measured. Joining the table partitioned by country to the country table filtered to 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

ArrangementHelps becauseSource
Partition by a low-cardinality join columnPartition pruning, and dynamic partition pruning from the other sideMeasured above
Cluster or Z-order by the join keyNarrow min/max per file, so pushed-down key values skip filesZ-order measured on filters; join effect from the Databricks docs
Keep the small side small and broadcast-ableNo shuffle of the big tableMeasured above
Compact small filesFewer, bigger tasks on both sidesMeasured on layout page

The Databricks dynamic file pruning page recommends liquid clustering on frequently joined columns and Photon-enabled compute.

Choosing

Which join, when

SituationExpectVerdict
One side is smallBroadcast hash join, no shufflePursue
Both sides large, same keySort-merge join, plan for the shufflePursue
You know a side is small but stats are missingAdd a broadcast hint instead of waiting for AQEPursue
Broadcasting a big tableOut-of-memory riskDrop
Joining on a column with a few huge keysSkew: check the plan and the task timesDrop

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.

← 3 · Layout5 · Inside Spark →