Broadcast joins
Broadcast of 'small' customer table
Symptoms
- Customer table is 12 GB after SCD history
- Executors die on the join
Broadcast joins interview questions
Interactive Spark interview questions on Broadcast joins. Same topic as /learn/spark/broadcast-join. Replicate a small dimension to every executor and skip shuffling the fact table.
Question 1 of 3
beginner
Broadcast joinsHow does a broadcast join work?
Answer it out loud, then reveal. Play steps through like the simulators.
Production scenario
Case 1 of 1 · symptoms
All questions on this page
Indexed as FAQ. Open any item if you prefer a list to Play.
beginnerHow does a broadcast join work?
Broadcast joins · tap to open the answer
Short: The driver (then each executor) gets a copy of the small table; the big table stays put.
Detailed: No shuffle of the fact table. Threshold is spark.sql.autoBroadcastJoinThreshold (default 10 MB, often raised). Too-large broadcast OOMs the driver or executors.
Common mistake: Broadcasting whichever table is mentioned first in SQL.
Follow-up: Which table should be broadcast?
intermediateBroadcast join OOM — driver or executor?
Broadcast joins · tap to open the answer
Short: Either: driver collects the build side; executors hold the hashed relation.
Detailed: Huge broadcast: driver collect of the dimension, then per-executor copy. Spark UI SQL shows broadcast exchange size. If the 'small' side is 8 GB, you chose wrong.
Common mistake: Raising executor memory without measuring broadcast size.
Follow-up: What's the hint syntax and when do you undo it?
seniorAQE switched your SMJ to broadcast mid-job. Is that good?
Broadcast joins · tap to open the answer
Short: Yes if the build side was small after filters. Bad if stats were wrong and it now OOMs.
Detailed: AQE join conversion uses runtime size. Watch SQL adaptive UI. If it OOMs, disable conversion for that query or fix the filter so the build side is truly small.
Senior: Don't hint a slowly growing dim forever. Partition/filter it, or SMJ when it crosses a documented threshold.
Common mistake: Disabling AQE globally because one query failed.
Follow-up: How do you cap broadcast size in a lakehouse with growing dims?