Skip to the page
Tecyfy

PySpark interview question · medium

What are the performance benefits and practical use cases of broadcast joins in Spark?

The short answerA broadcast join sends a full copy of the small table to every executor, so each executor joins its part of the large table locally.

The full answer

A broadcast join sends a full copy of the small table to every executor, so each executor joins its part of the large table locally. The large table is never shuffled, which removes the most expensive part of a join and also avoids skew problems on the join key.

How it works

  1. The driver collects the small table and broadcasts it to all executors.
  2. Each executor builds an in-memory hash table from it.
  3. Each task scans its partition of the large table and looks up matches. There is one stage for the join instead of two shuffle stages plus a sort, as in a sort-merge join.

Performance benefits

  • No shuffle of the large table: no network transfer, no shuffle files, no sort.
  • Fewer stages: lower latency and less scheduling overhead.
  • Immune to key skew: hot keys in the large table don't concentrate on one task.
  • Works with dynamic partition pruning: the broadcast result can also be used to skip partitions of the large table.

When Spark uses it

  • Automatically when the estimated size of one side is under spark.sql.autoBroadcastJoinThreshold (10 MB by default).
  • At runtime with adaptive query execution: if a side turns out to be small after filtering, AQE switches a planned sort-merge join to a broadcast join.
  • When you ask for it:
from pyspark.sql.functions import broadcast

result = sales.join(broadcast(stores), "store_id")
SELECT /*+ BROADCAST(s) */ *
FROM sales f JOIN stores s ON f.store_id = s.store_id;

Limits and risks

  • The small side must fit in the driver's and every executor's memory; forcing a broadcast of a large table causes out-of-memory errors or timeouts (spark.sql.broadcastTimeout, 300 seconds by default).
  • The smaller side can't be the side whose unmatched rows are kept: in a left outer join, only the right side can be broadcast.
  • Size estimates for complex subqueries can be wrong; check the plan for BroadcastHashJoin.
  • Raising the threshold a lot shifts load to the driver.

Practical use cases

  • Fact tables joined to dimension tables: products, stores, currencies, calendars.
  • Lookup and mapping tables: country codes, status codes.
  • Filtering a large table by a small list of keys (often a left semi join).
  • Enriching streaming data with a small static table in Structured Streaming.

Check the result with explain(): BroadcastHashJoin with BroadcastExchange on the small side confirms it.

Last updated 16 October 2026

Sources

Where each fact comes from

  1. 1Performance tuning
  2. 2Spark configuration
  3. 3Hints