Broadcast joins

Apache Spark · Aggregations & Joins

Theory

In a broadcast join Spark sends a full copy of the small table to every executor, builds an in-memory hash table from it, and then joins each partition of the big table locally. The big table is never shuffled, so the join is usually much faster than a shuffle-based join. Spark picks it automatically when its size estimate for one side is below spark.sql.autoBroadcastJoinThreshold (10 MB by default), and you can force it with F.broadcast(df) or the SQL hint /*+ BROADCAST(c) */.

In explain() you will see BroadcastExchange and BroadcastHashJoin instead of Exchange hashpartitioning and SortMergeJoin. For example, joining a 500 GB orders table to a 2 MB customers dimension: with a broadcast, each of the 2,000 order tasks reads its partition and looks up customers in the local hash table, with no shuffle at all. The small table must fit in driver and executor memory, and for left joins it is the right side that can be broadcast.

Why we use it

Shuffling a huge fact table just to join a small dimension wastes network, disk and time. Broadcasting the small side removes that shuffle and often turns a slow join into a fast map-side lookup. It is the first join optimisation interviewers expect.

When to use it

Broadcast when one side is small (dimension tables, lookup lists, filtered subsets) and fits comfortably in memory. Do not broadcast large tables: it can cause driver or executor out-of-memory errors. Adaptive query execution can also switch to a broadcast at runtime when a side turns out small.

How it works

  1. Join a big table with a small table
  2. Is the small side under the threshold or hinted? Yes: Plan a broadcast hash join. No: Plan a shuffle join (sort-merge).
  3. Driver collects the small table
  4. Copy is sent to every executor and hashed
  5. Each big-table task looks up matches locally
  6. Joined rows with no shuffle of the big table

Syntax

big.join(F.broadcast(small), "key")
spark.sql("SELECT /*+ BROADCAST(c) */ ... FROM orders o JOIN customers c ON ...")
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", "50MB")   # -1 disables

Example

from pyspark.sql import SparkSession, functions as F

spark = (SparkSession.builder.master("local[1]")
         .config("spark.ui.enabled", "false")
         .getOrCreate())

orders = spark.createDataFrame([
    (101, 1, "North", "Laptop", 1, 55000.0, "2026-01-05"),
    (102, 2, "West",  "Phone",  2, 30000.0, "2026-01-06"),
    (103, 1, "North", "Mouse",  3,  1500.0, "2026-01-06"),
    (104, 3, "South", "Laptop", 1, 52000.0, "2026-01-07"),
    (105, 4, "West",  "Tablet", 1, 25000.0, "2026-01-08"),
    (106, 2, "East",  "Phone",  1, 15000.0, "2026-01-08"),
    (107, 5, "North", "Tablet", 2, 48000.0, "2026-01-09"),
    (108, 3, "South", "Mouse",  4,  2000.0, "2026-01-10"),
], "order_id INT, customer_id INT, region STRING, product STRING, "
   "qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))

customers = spark.createDataFrame([
    (1, "Asha",   "Jaipur",    "Retail"),
    (2, "Ravi",   "Pune",      "Corporate"),
    (3, "Meera",  "Delhi",     "Retail"),
    (4, "Karan",  "Jaipur",    "SMB"),
    (5, "Priya",  "Mumbai",    "Corporate"),
    (6, "Vikram", "Bengaluru", "SMB"),
], "customer_id INT, name STRING, city STRING, segment STRING")

joined = orders.join(F.broadcast(customers), "customer_id")
joined.explain()

(joined.groupBy("city").agg(F.sum("amount").alias("sales"))
       .orderBy(F.desc("sales")).show())

print("auto-broadcast threshold:", spark.conf.get("spark.sql.autoBroadcastJoinThreshold"))

# Output:
# == Physical Plan ==
# AdaptiveSparkPlan isFinalPlan=false
# +- Project [customer_id#1, order_id#0, region#2, product#3, qty#4, amount#5, order_date#7, name#9, city#10, segment#11]
#    +- BroadcastHashJoin [customer_id#1], [customer_id#8], Inner, BuildRight, false
#       :- Project [order_id#0, customer_id#1, region#2, product#3, qty#4, amount#5, cast(order_date#6 as date) AS order_date#7]
#       :  +- Filter isnotnull(customer_id#1)
#       :     +- Scan ExistingRDD[order_id#0,customer_id#1,region#2,product#3,qty#4,amount#5,order_date#6]
#       +- BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, false] as bigint)),false), [plan_id=28]
#          +- Filter isnotnull(customer_id#8)
#             +- Scan ExistingRDD[customer_id#8,name#9,city#10,segment#11]
#
#
# +------+-------+
# |  city|  sales|
# +------+-------+
# |Jaipur|81500.0|
# | Delhi|54000.0|
# |Mumbai|48000.0|
# |  Pune|45000.0|
# +------+-------+
#
# auto-broadcast threshold: 10485760b

Key points

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic