Broadcast of 'small' customer table
Symptoms
- Customer table is 12 GB after SCD history
- Executors die on the join
Interactive Spark interview questions on Broadcast joins. Practice with the matching lesson. Replicate a small dimension to every executor and skip shuffling the fact table.
Question 1 of 8
How does a broadcast join work?
Answer it out loud, then reveal. Play steps through like the simulators.
Case 1 of 3 · symptoms
Symptoms
Indexed as FAQ. Open any item if you prefer a list to Play.
Showing 8 of 8 questions.
How does a broadcast join work?
Broadcast joins · tap to open the answer
Say this first: The driver (then each executor) gets a copy of the small table; the big table stays put.
Why: 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 they will ask: Which table should be broadcast?
Broadcast join OOM — driver or executor?
Broadcast joins · tap to open the answer
Say this first: Either: driver collects the build side; executors hold the hashed relation.
Why: 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 they will ask: What's the hint syntax and when do you undo it?
AQE switched your SMJ to broadcast mid-job. Is that good?
Broadcast joins · tap to open the answer
Say this first: Yes if the build side was small after filters. Bad if stats were wrong and it now OOMs.
Why: 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 depth: 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 they will ask: How do you cap broadcast size in a lakehouse with growing dims?
You join a 2 TB fact to a 5 MB lookup and the plan still shows SortMergeJoin. Name the likely reasons.
Broadcast joins · tap to open the answer
Say this first: The planner's size estimate is not 5 MB, automatic broadcast is disabled, or the join type cannot broadcast that side.
Remember these
Why: Spark broadcasts automatically when its estimate for one side is under spark.sql.autoBroadcastJoinThreshold, and that estimate comes from table statistics or file sizes, not from the number in your head — a table with no statistics can be estimated as effectively unbounded. Setting the threshold to -1 disables automatic broadcast entirely, which some platforms do deliberately. The join type also constrains which side can be the build side: a left outer join can only broadcast its right side, and a full outer join cannot use a broadcast hash join at all. Finally the join must be an equi-join; a non-equality condition plans as a broadcast nested loop or a cartesian product instead. Check the threshold, run ANALYZE TABLE, and confirm the join type before reaching for a hint.
spark.conf.get("spark.sql.autoBroadcastJoinThreshold")
spark.sql("ANALYZE TABLE dim.lookup COMPUTE STATISTICS")Common mistake: Assuming a table that is small on disk is automatically small to the planner.
Follow-up they will ask: Which side gets broadcast in a right outer join?
Why can a left outer join only broadcast its right side?
Broadcast joins · tap to open the answer
Say this first: The build side is the one looked up, and every left row must be preserved, which only works if the left side is streamed.
Remember these
Why: A broadcast hash join builds a hash table from one side and streams the other through it, emitting matches per streamed row. A left outer join must emit every left row, matched or not, so the left side has to be the streamed side and the right has to be the build side. Reverse it and a left row with no match could sit on an executor that never sees it, so the semantics break. The logic inverts for a right outer join, where only the left side can be broadcast, and a full outer join needs both sides preserved, so it cannot use a broadcast hash join at all and plans as a sort-merge join. This is why a broadcast hint on the wrong side is silently ignored: Spark is not disobeying you, the strategy is invalid for that join type.
fact.join(broadcast(dim), "dim_id", "left") # valid
dim.join(broadcast(fact), "dim_id", "left") # hint ignoredCommon mistake: Reporting that 'the broadcast hint did nothing' without checking the join type.
Follow-up they will ask: What does Spark do with a left anti join on a small right side?
The threshold is 100 MB, the table is 40 MB of Parquet, and Spark still will not broadcast. Why?
Broadcast joins · tap to open the answer
Say this first: The planner compares against its estimate of the decoded size, and Parquet on disk is compressed and encoded.
Remember these
Why: Parquet stores columns with dictionary encoding, run-length encoding, and block compression, so 40 MB on disk can be several hundred megabytes once decoded into rows. Spark's sizeInBytes for a file-based relation starts from file size, and plan-level estimates propagate through filters and projections with rules that are often pessimistic — a filter the optimizer cannot reason about leaves the estimate untouched. Read explain("cost") to see the sizeInBytes and rowCount the planner is actually using on that side; if it is wildly wrong, ANALYZE TABLE ... COMPUTE STATISTICS FOR COLUMNS gives it real numbers. The honest alternative is to let AQE decide, because spark.sql.adaptive.autoBroadcastJoinThreshold is evaluated against the measured size of a completed shuffle rather than an estimate. Measure before hinting: the whole failure mode of a hint is asserting a size instead of checking one.
df.explain("cost") # read sizeInBytes and rowCount on the build sideSenior depth: Run the query and read the BroadcastExchange node in the SQL / DataFrame tab — it reports the data size of the relation it built and the time spent building it. That is the number to put in a design doc. Pair it with the dimension's growth rate so the decision carries an expiry date, rather than a hint that quietly becomes a broadcast of eight gigabytes.
Common mistake: Comparing a table's on-disk footprint directly against autoBroadcastJoinThreshold.
Follow-up they will ask: How do you measure the real broadcast size after the fact?
A query fails with 'Could not execute broadcast in 300 secs'. Walk the mechanism, then the fix.
Broadcast joins · tap to open the answer
Say this first: The build side's own computation did not finish inside spark.sql.broadcastTimeout — the timeout is the symptom, not the cause.
Remember these
Why: A BroadcastExchange runs as its own job: the build side is computed, collected to the driver, assembled into a hash relation, and shipped to executors, while the join waits on that future. spark.sql.broadcastTimeout bounds that wait, at 300 seconds by default. Hitting it almost never means the network was slow — it means producing the build side was slow, usually because the 'small' side is a heavy subquery, a wide scan with no pruning, or a JDBC read, or because the driver is struggling to assemble a relation much larger than expected. Open the broadcast job in the SQL tab: its duration and reported data size tell you which. Fixing the build side's plan is the real work, because raising the timeout only lets the driver try longer to collect something it should not be collecting.
spark.conf.set("spark.sql.broadcastTimeout", "600") # buys time, fixes nothingSenior depth: When the measured relation size is a meaningful fraction of driver heap or of executor storage memory, or when the build side needs its own expensive plan to produce. At that point a sort-merge join on a well-partitioned key is cheaper and far more stable than a broadcast that is one growth step from an outage. Write the threshold down as a policy with a number, not as a per-query judgement call.
Common mistake: Raising broadcastTimeout as the fix and then being surprised by a driver OOM instead.
Follow-up they will ask: At what point do you stop broadcasting altogether?
Write the broadcast policy for a lakehouse where dimensions keep growing. What goes in it?
Broadcast joins · tap to open the answer
Say this first: A measured size budget per join, a named owner for every hint, and an alert when a dimension approaches the threshold.
Remember these
Why: Start from a number: the maximum broadcast relation size you accept, derived from driver heap and from executor storage memory, since every executor holds a copy. Require that any explicit broadcast hint cites the measured size from the BroadcastExchange node plus the table's growth rate, and carries a review date — a hint is an assertion with an expiry. Keep automatic conversion on and let AQE make the runtime call where it can, because it compares against measured shuffle size rather than a stale estimate. Then monitor: track each dimension's size and alert before it crosses the budget, so the failure arrives as a ticket rather than a nightly outage. Finally make dimensions broadcastable by design — current-row views instead of full SCD history, narrow projections, a sane layout — rather than raising the threshold as data grows.
Senior depth: AQE decides per run from the measured size, so the plan can flip between runs and the cost profile becomes non-deterministic — and a dimension creeping just under the threshold gets broadcast to every executor on a large cluster. Cap spark.sql.adaptive.autoBroadcastJoinThreshold explicitly rather than inheriting it, and treat plan flips as an observable event you record per run.
Common mistake: Codifying a higher autoBroadcastJoinThreshold instead of codifying a size budget and an owner.
Follow-up they will ask: What is the failure mode of letting AQE decide everything?