Broadcast Join and Skew Join in PySpark
Spark tutorial · PySpark.in
Quick reference: PySpark join guide
A broadcast join sends a small DataFrame to every executor, so the large one is joined without a shuffle. Skewed joins are fixed with AQE or salting. This tutorial shows both in PySpark, with tested code, real output and explain() plans.
New to joins? Start with PySpark joins: inner, left, right, full, semi and anti or the PySpark Joins tutorial; this page is about making joins fast.
1. Broadcast join
A normal join between two DataFrames is a shuffle join: Spark repartitions both sides by the join key over the network so matching keys meet in the same partition, then usually sorts them (a sort-merge join). With large tables the shuffle is the expensive part.
In a broadcast join, Spark collects the small DataFrame on the driver and sends a full copy to every executor. Each partition of the large DataFrame is then joined with that local copy, so the large side is never shuffled. That pays off when one side is small, typically a dimension or lookup table joined to a large fact table.
Example: broadcast() and explain()
Mark the small side with broadcast() from pyspark.sql.functions. Broadcasting changes how Spark runs the join, never its result:
```
python
from pyspark.sql import SparkSession
from pyspark.sql.functions import broadcast
spark = SparkSession.builder.appName("BroadcastJoin").getOrCreate()
employees = spark.createDataFrame(
[(1, "Alice", 101), (2, "Bob", 102), (3, "Charlie", None), (4, "David", 104), (5, "Eve", 101)],
["emp_id", "name", "dept_id"])
departments = spark.createDataFrame(
[(101, "HR"), (102, "IT"), (103, "Marketing")],
["dept_id", "dept_name"])
joined = employees.join(broadcast(departments), on="dept_id", how="inner")
joined.orderBy("emp_id").show()
joined.explain()
```
Output:
```
hide
+-------+------+-----+---------+
|dept_id|emp_id| name|dept_name|
+-------+------+-----+---------+
| 101| 1|Alice| HR|
| 102| 2| Bob| IT|
| 101| 5| Eve| HR|
+-------+------+-----+---------+
== Physical Plan ==
AdaptiveSparkPlan isFinalPlan=false
+- Project [dept_id#2L, emp_id#0L, name#1, dept_name#7]
+- BroadcastHashJoin [dept_id#2L], [dept_id#6L], Inner, BuildRight, false
:- Filter isnotnull(dept_id#2L)
: +- Scan ExistingRDD[emp_id#0L,name#1,dept_id#2L]
+- BroadcastExchange HashedRelationBroadcastMode(List(input[0, bigint, false]),false), [plan_id=87]
+- Filter isnotnull(dept_id#6L)
+- Scan ExistingRDD[dept_id#6L,dept_name#7]
```
Charlie (no department) and David (department 104, which does not exist) are dropped because this is an inner join. The plan proves the broadcast: BroadcastExchange ships the departments side and BroadcastHashJoin ... BuildRight builds the hash table from it. The numbers after # and plan_id change from run to run.
Automatic broadcasting: spark.sql.autoBroadcastJoinThreshold
Without a hint, Spark broadcasts a side on its own when its estimated size is below spark.sql.autoBroadcastJoinThreshold, 10 MB by default. To turn automatic broadcasting off, set it to -1. Do not raise it to a huge value to stop broadcasts: that has the opposite effect and makes Spark broadcast larger tables. An explicit broadcast() hint is honoured either way:
```
python
from pyspark.sql import SparkSession
from pyspark.sql.functions import broadcast
spark = SparkSession.builder.appName("BroadcastThreshold").getOrCreate()
employees = spark.createDataFrame([(1, "Alice", 101), (2, "Bob", 102)], ["emp_id", "name", "dept_id"])
departments = spark.createDataFrame([(101, "HR"), (102, "IT")], ["dept_id", "dept_name"])
print(spark.conf.get("spark.sql.autoBroadcastJoinThreshold")) # 10 MB by default
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1) # turn automatic broadcasting off
employees.join(departments, "dept_id").explain() # now a SortMergeJoin
employees.join(broadcast(departments), "dept_id").explain() # an explicit hint still broadcasts
```
Output:
```
hide
10485760b
== Physical Plan ==
AdaptiveSparkPlan isFinalPlan=false
+- Project [dept_id#2L, emp_id#0L, name#1, dept_name#7]
+- SortMergeJoin [dept_id#2L], [dept_id#6L], Inner
:- Sort [dept_id#2L ASC NULLS FIRST], false, 0
: +- Exchange hashpartitioning(dept_id#2L, 200), ENSURE_REQUIREMENTS, [plan_id=25]
: +- Filter isnotnull(dept_id#2L)
: +- Scan ExistingRDD[emp_id#0L,name#1,dept_id#2L]
+- Sort [dept_id#6L ASC NULLS FIRST], false, 0
+- Exchange hashpartitioning(dept_id#6L, 200), ENSURE_REQUIREMENTS, [plan_id=26]
+- Filter isnotnull(dept_id#6L)
+- Scan ExistingRDD[dept_id#6L,dept_name#7]
== Physical Plan ==
AdaptiveSparkPlan isFinalPlan=false
+- Project [dept_id#2L, emp_id#0L, name#1, dept_name#7]
+- BroadcastHashJoin [dept_id#2L], [dept_id#6L], Inner, BuildRight, false
:- Filter isnotnull(dept_id#2L)
: +- Scan ExistingRDD[emp_id#0L,name#1,dept_id#2L]
+- BroadcastExchange HashedRelationBroadcastMode(List(input[0, bigint, false]),false), [plan_id=55]
+- Filter isnotnull(dept_id#6L)
+- Scan ExistingRDD[dept_id#6L,dept_name#7]
```
With adaptive query execution (AQE) on, Spark can also switch a join to a broadcast at run time once it knows the real size of a side; that limit is spark.sql.adaptive.autoBroadcastJoinThreshold, which defaults to the same value. Other join hints: df.hint("broadcast") is the same as broadcast(df), and hint("merge") or hint("shuffle_hash") ask for a sort-merge or shuffle hash join instead.
When to broadcast, and when not to
- Good fit: a small dimension table (country codes, product categories, departments) joined to a large fact table; star schemas where each small dimension can be broadcast.
- Driver and executor memory: the broadcast side is first collected on the driver, then a copy is held by every executor. A table that is larger than expected can cause out-of-memory errors on either. Spark refuses to broadcast a table larger than 8 GB.
- Broadcast timeout:
spark.sql.broadcastTimeout(300 seconds by default) fails the job if the broadcast takes too long, which usually means the table is too big to broadcast. - Both sides large: forcing a broadcast of a 1 GB table to join a 10 GB one copies 1 GB to every executor; a shuffle join is the better choice.
- Check the plan:
explain()showsBroadcastHashJoinwhen a side is broadcast andSortMergeJoinwhen both sides are shuffled.
2. Skewed joins
A join is skewed when a few key values hold most of the rows. In a shuffle join every row with the same key goes to the same partition, so one task processes far more data than the rest: the stage waits for one straggler, and that task can run out of memory. Typical causes are a default or unknown value, one very large customer or country, or nulls.
Find the skew
Count rows per join key. The example data below has 100,000 orders, 90% of them for customer 1:
```
python
from pyspark.sql import SparkSession
from pyspark.sql import functions as F
spark = SparkSession.builder.appName("SkewedJoin").getOrCreate()
# 100,000 orders: 9 in 10 belong to customer 1, the rest are spread over customers 0-99
orders = spark.range(100000).select(
F.col("id").alias("order_id"),
F.when(F.col("id") % 10 == 9, F.col("id") % 100).otherwise(F.lit(1)).alias("customer_id"),
(F.col("id") % 50).alias("amount"))
customers = spark.range(100).select(
F.col("id").alias("customer_id"),
F.concat(F.lit("customer_"), F.col("id")).alias("customer_name"))
orders.groupBy("customer_id").count().orderBy(F.desc("count"), "customer_id").show(5)
```
Output:
```
hide
+-----------+-----+
|customer_id|count|
+-----------+-----+
| 1|90000|
| 9| 1000|
| 19| 1000|
| 29| 1000|
| 39| 1000|
+-----------+-----+
only showing top 5 rows
```
One key with 90,000 rows against 1,000 for the others is severe skew. In the Spark UI the same problem shows as one task in the join stage with far more input and duration than the median.
Fix 1: let AQE split the skewed partition
Adaptive query execution (Spark 3.x, on by default since Spark 3.2) can detect a skewed partition of a sort-merge join at run time and split it into smaller tasks, reading the matching partition of the other side once per split. These settings control it:
```
python
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("AqeSettings").getOrCreate()
for key in ["spark.sql.adaptive.enabled",
"spark.sql.adaptive.skewJoin.enabled",
"spark.sql.adaptive.skewJoin.skewedPartitionFactor",
"spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes",
"spark.sql.adaptive.advisoryPartitionSizeInBytes"]:
print(key, "=", spark.conf.get(key))
```
Output:
```
hide
spark.sql.adaptive.enabled = true
spark.sql.adaptive.skewJoin.enabled = true
spark.sql.adaptive.skewJoin.skewedPartitionFactor = 5.0
spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes = 268435456b
spark.sql.adaptive.advisoryPartitionSizeInBytes = 67108864b
```
A partition counts as skewed when it is larger than skewedPartitionFactor times the median partition and larger than skewedPartitionThresholdInBytes (5 and 256 MB by default). Small example data never reaches 256 MB, so the next example lowers the thresholds to show AQE at work:
```
python
from pyspark.sql import SparkSession
from pyspark.sql import functions as F
spark = SparkSession.builder.appName("SkewedJoin").getOrCreate()
# 100,000 orders: 9 in 10 belong to customer 1, the rest are spread over customers 0-99
orders = spark.range(100000).select(
F.col("id").alias("order_id"),
F.when(F.col("id") % 10 == 9, F.col("id") % 100).otherwise(F.lit(1)).alias("customer_id"),
(F.col("id") % 50).alias("amount"))
customers = spark.range(100).select(
F.col("id").alias("customer_id"),
F.concat(F.lit("customer_"), F.col("id")).alias("customer_name"))
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1) # force a shuffle join
spark.conf.set("spark.sql.shuffle.partitions", 8)
# Demo-sized thresholds so 100,000 rows count as skewed (the defaults are 256 MB and 64 MB)
spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes", "64KB")
spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", "64KB")
result = orders.join(customers, "customer_id")
rows = result.collect() # run the join
print(len(rows))
result.explain() # the plan Spark actually ran
```
Output:
```
hide
100000
== Physical Plan ==
AdaptiveSparkPlan isFinalPlan=true
+- == Final Plan ==
*(5) Project [customer_id#3L, order_id#2L, amount#4L, customer_name#11]
+- *(5) SortMergeJoin(skew=true) [customer_id#3L], [customer_id#10L], Inner
:- *(3) Sort [customer_id#3L ASC NULLS FIRST], false, 0
: +- AQEShuffleRead coalesced and skewed
: +- ShuffleQueryStage 0
: +- Exchange hashpartitioning(customer_id#3L, 8), ENSURE_REQUIREMENTS, [plan_id=43]
: +- *(1) Project [id#0L AS order_id#2L, CASE WHEN ((id#0L % 10) = 9) THEN (id#0L % 100) ELSE 1 END AS customer_id#3L, (id#0L % 50) AS amount#4L]
: +- *(1) Filter CASE WHEN ((id#0L % 10) = 9) THEN isnotnull((id#0L % 100)) ELSE true END
: +- *(1) Range (0, 100000, step=1, splits=2)
+- *(4) Sort [customer_id#10L ASC NULLS FIRST], false, 0
+- AQEShuffleRead coalesced
+- ShuffleQueryStage 1
+- Exchange hashpartitioning(customer_id#10L, 8), ENSURE_REQUIREMENTS, [plan_id=55]
+- *(2) Project [id#8L AS customer_id#10L, concat(customer_, cast(id#8L as string)) AS customer_name#11]
+- *(2) Range (0, 100, step=1, splits=2)
+- == Initial Plan ==
Project [customer_id#3L, order_id#2L, amount#4L, customer_name#11]
+- SortMergeJoin [customer_id#3L], [customer_id#10L], Inner
:- Sort [customer_id#3L ASC NULLS FIRST], false, 0
: +- Exchange hashpartitioning(customer_id#3L, 8), ENSURE_REQUIREMENTS, [plan_id=29]
: +- Project [id#0L AS order_id#2L, CASE WHEN ((id#0L % 10) = 9) THEN (id#0L % 100) ELSE 1 END AS customer_id#3L, (id#0L % 50) AS amount#4L]
: +- Filter CASE WHEN ((id#0L % 10) = 9) THEN isnotnull((id#0L % 100)) ELSE true END
: +- Range (0, 100000, step=1, splits=2)
+- Sort [customer_id#10L ASC NULLS FIRST], false, 0
+- Exchange hashpartitioning(customer_id#10L, 8), ENSURE_REQUIREMENTS, [plan_id=30]
+- Project [id#8L AS customer_id#10L, concat(customer_, cast(id#8L as string)) AS customer_name#11]
+- Range (0, 100, step=1, splits=2)
```
isFinalPlan=true is the plan Spark actually ran. SortMergeJoin(skew=true) and AQEShuffleRead coalesced and skewed show that AQE split the oversized partition of customer 1. The lowered thresholds are for the demo only; keep the defaults in production unless the Spark UI shows skewed tasks that AQE leaves alone.
Fix 2: salt the hot key
When AQE is off or cannot help, for example in a full outer join, which its skew handling does not cover, salting spreads the hot key over several join keys:
- Add a salt to the large side: a number from 0 to N-1 on every row, here derived from a hash of the order id so the result is the same on every run (
F.rand()works too). - Replicate the small side: cross join it with the numbers 0 to N-1, so every customer exists once for each salt value.
- Join on the key and the salt: customer 1 now becomes N separate join keys that go to different partitions.
- Drop the salt: the result has exactly the rows of the plain join.
```
python
from pyspark.sql import SparkSession
from pyspark.sql import functions as F
spark = SparkSession.builder.appName("SkewedJoin").getOrCreate()
# 100,000 orders: 9 in 10 belong to customer 1, the rest are spread over customers 0-99
orders = spark.range(100000).select(
F.col("id").alias("order_id"),
F.when(F.col("id") % 10 == 9, F.col("id") % 100).otherwise(F.lit(1)).alias("customer_id"),
(F.col("id") % 50).alias("amount"))
customers = spark.range(100).select(
F.col("id").alias("customer_id"),
F.concat(F.lit("customer_"), F.col("id")).alias("customer_name"))
N = 8 # salt buckets: the hot key's rows are spread over N join keys
salted_orders = orders.withColumn("salt", F.abs(F.hash("order_id")) % N)
salted_customers = customers.crossJoin(spark.range(N).withColumnRenamed("id", "salt"))
salted = salted_orders.join(salted_customers, ["customer_id", "salt"]).drop("salt")
salted_orders.filter("customer_id = 1").groupBy("salt").count().orderBy("salt").show()
print(salted.count(), orders.join(customers, "customer_id").count()) # same number of rows
```
Output:
```
hide
+----+-----+
|salt|count|
+----+-----+
| 0|11227|
| 1|11256|
| 2|11237|
| 3|11247|
| 4|11173|
| 5|11269|
| 6|11178|
| 7|11413|
+----+-----+
100000 100000
```
Customer 1's 90,000 rows are now spread evenly over 8 salt values, and the salted join returns the same 100,000 rows as the plain join.
Salting has costs: the small side grows N times, the code needs extra columns, and N has to be chosen. Salting only the known hot keys (and joining the rest normally) keeps the replication small.
Other ways to reduce skew
- Broadcast the small side: a broadcast join never shuffles the large side by key, so a skewed key cannot overload one partition. It is the first thing to try when one side is small.
- Handle hot keys separately: filter the few huge keys out, join or aggregate them on their own, and union the results; filter out keys that are not needed at all, such as nulls.
- Aggregate before joining: when the join only feeds an aggregation, group the large table by the join key first and join the much smaller result.
- More shuffle partitions: raising
spark.sql.shuffle.partitionshelps uneven but not single-key skew, because all rows of one key still land in one partition.
Which technique to use
- One side small: broadcast it (
broadcast()or let the 10 MB threshold do it). - Both sides large, some keys heavy: keep AQE on and check the plan for
SortMergeJoin(skew=true). - Skew that AQE does not fix: salt the hot keys, or process them separately.
Practice
- Optimize a small DataFrame join with broadcast
- Optimize a join using broadcast in AQE
- Handle data skew from high-frequency vehicles
- All broadcast join practice questions
More Spark tutorials
- Apache Spark Runtime Architecture
- RDD vs DataFrame vs Dataset in Spark: Differences
- Actions vs Transformations
- Lazy Evaluation in PySpark
- DataFrame Operations in PySpark
- Spark SQL Tutorial: Run SQL Queries in PySpark
PySpark tutorial: all lessons · All tutorials · Try the free PySpark compiler · Practice challenges