Joins, shuffles & broadcast
Databricks · Apache Spark on Databricks
Theory
df.join(other, on, how) combines two DataFrames; how can be inner, left, right, full, left_semi, left_anti or cross. To join big tables, Spark must bring rows with the same key to the same executor, so it shuffles both sides across the network (a sort-merge join or shuffle hash join). Shuffles write and read data between stages and are often the slowest part of a job.
If one side is small, Spark can broadcast it: send a full copy of the small table to every executor and join locally, with no shuffle of the big table. Spark does this automatically below a size threshold (spark.sql.autoBroadcastJoinThreshold), Adaptive Query Execution can switch to it at runtime, and you can force it with F.broadcast(customers). For example, joining millions of orders to a few thousand customers on customer_id should be a broadcast hash join; df.explain() shows BroadcastHashJoin when it is.
Why we use it
Joins are everywhere in ETL, and a bad join strategy can turn a two-minute job into an hour. Knowing when Spark shuffles and how broadcast avoids it is the most useful Spark tuning skill.
When to use it
Broadcast when one side is small enough to fit comfortably in executor memory (a dimension table). Do not broadcast two large tables; let Spark shuffle, and reduce data first with filters and column selection.
How it works
- Join orders with customers
- Spark estimates the size of each side
- Is one side small (under the threshold or hinted)? Yes: Broadcast hash join: copy the small side. No: Shuffle both sides and sort-merge join.
- Matching rows are combined per partition
- Joined DataFrame is passed to the next step
Syntax
big.join(small, "customer_id", "inner")
big.join(F.broadcast(small), on="customer_id", how="left")
SQL hint: SELECT /*+ BROADCAST(c) */ ...
spark.sql.autoBroadcastJoinThreshold
df.explain() -> BroadcastHashJoin | SortMergeJoinExample
from pyspark.sql import functions as F
orders = spark.read.table("main.sales.orders")
customers = spark.read.table("main.sales.customers")
joined = (orders.join(F.broadcast(customers), on="customer_id", how="inner")
.select("order_id", "name", "city", "amount")
.orderBy("order_id"))
joined.explain() # look for BroadcastHashJoin
display(joined)
-- SQL version with a hint
SELECT /*+ BROADCAST(c) */ o.order_id, c.name, c.city, o.amount
FROM main.sales.orders o JOIN main.sales.customers c USING (customer_id);Key points
- Shuffles move data across the network and create new stages
- Broadcast joins copy the small table to every executor and avoid shuffling the big one
- Spark auto-broadcasts small tables; AQE can switch to broadcast at runtime
- Filter and select columns before joining to shrink shuffles
- Never broadcast a large table; it can run executors out of memory