Shuffles & how joins execute (sort-merge, broadcast hash)

Apache Spark · Aggregations & Joins

Theory

Spark has several physical join strategies and Catalyst picks one based on table sizes, join type, hints and keys. A broadcast hash join sends the small side to every executor and needs no shuffle. A sort-merge join, the default for two large tables, shuffles both sides by the join key into spark.sql.shuffle.partitions partitions, sorts each partition by the key and then walks both sorted lists together, like merging two sorted files. A shuffle hash join shuffles both sides but builds a hash table from the smaller side of each partition instead of sorting. Joins without an equality key fall back to a broadcast nested loop join or a cartesian product, which are slow.

Adaptive query execution (on by default) re-plans at runtime using real shuffle sizes: it can switch a sort-merge join to a broadcast join, coalesce many tiny shuffle partitions and split skewed partitions. For example, with auto-broadcast disabled, orders join customers shows Exchange hashpartitioning(customer_id) and Sort on both sides followed by SortMergeJoin.

Why we use it

Joins are where most Spark jobs spend time and fail. Reading the strategy in explain() tells you whether data is shuffled, sorted or broadcast, and points to the fix: broadcast a small side, filter earlier, repartition by key or handle skew.

When to use it

Check the plan of every heavy join. Expect sort-merge for big-to-big joins, broadcast hash for big-to-small, and avoid nested loop joins on large data. Use hints (BROADCAST, MERGE, SHUFFLE_HASH) only after measuring, and let AQE handle runtime changes.

How it works

  1. Equi-join between two DataFrames
  2. Is one side small or hinted for broadcast? Yes: Broadcast hash join (no shuffle). No: Shuffle both sides by the join key.
  3. Hinted shuffle_hash (or sort not worth it)? Yes: Shuffle hash join: hash the smaller side per partition. No: Sort each partition by key.
  4. Merge the sorted partitions (sort-merge join)
  5. AQE may re-plan using real sizes
  6. Joined output

Syntax

df.explain()          # BroadcastHashJoin / SortMergeJoin / ShuffledHashJoin
a.join(b.hint("merge"), "k");  a.join(b.hint("shuffle_hash"), "k")
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", "-1")
spark.sql.adaptive.enabled  (AQE, on by default)

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")

spark.conf.set("spark.sql.autoBroadcastJoinThreshold", "-1")   # no automatic broadcast
spark.conf.set("spark.sql.adaptive.enabled", "false")          # keep the static plan for the demo

print("--- sort-merge join: shuffle + sort on both sides ---")
orders.join(customers, "customer_id").explain()

print("--- broadcast hash join via hint ---")
orders.join(customers.hint("broadcast"), "customer_id").explain()

print("--- shuffle hash join via hint ---")
orders.join(customers.hint("shuffle_hash"), "customer_id").explain()

# Output:
# --- sort-merge join: shuffle + sort on both sides ---
# == Physical Plan ==
# *(5) Project [customer_id#1, order_id#0, region#2, product#3, qty#4, amount#5, order_date#7, name#9, city#10, segment#11]
# +- *(5) SortMergeJoin [customer_id#1], [customer_id#8], Inner
#    :- *(2) Sort [customer_id#1 ASC NULLS FIRST], false, 0
#    :  +- Exchange hashpartitioning(customer_id#1, 200), ENSURE_REQUIREMENTS, [plan_id=35]
#    :     +- *(1) 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]
#    :        +- *(1) Filter isnotnull(customer_id#1)
#    :           +- *(1) Scan ExistingRDD[order_id#0,customer_id#1,region#2,product#3,qty#4,amount#5,order_date#6]
#    +- *(4) Sort [customer_id#8 ASC NULLS FIRST], false, 0
#       +- Exchange hashpartitioning(customer_id#8, 200), ENSURE_REQUIREMENTS, [plan_id=41]
#          +- *(3) Filter isnotnull(customer_id#8)
#             +- *(3) Scan ExistingRDD[customer_id#8,name#9,city#10,segment#11]
#
#
# --- broadcast hash join via hint ---
# == Physical Plan ==
# *(2) Project [customer_id#1, order_id#0, region#2, product#3, qty#4, amount#5, order_date#7, name#9, city#10, segment#11]
# +- *(2) BroadcastHashJoin [customer_id#1], [customer_id#8], Inner, BuildRight, false
#    :- *(2) 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]
#    :  +- *(2) Filter isnotnull(customer_id#1)
#    :     +- *(2) 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=96]
#       +- *(1) Filter isnotnull(customer_id#8)
#          +- *(1) Scan ExistingRDD[customer_id#8,name#9,city#10,segment#11]
#
#
# --- shuffle hash join via hint ---
# == Physical Plan ==
# *(3) Project [customer_id#1, order_id#0, region#2, product#3, qty#4, amount#5, order_date#7, name#9, city#10, segment#11]
# +- *(3) ShuffledHashJoin [customer_id#1], [customer_id#8], Inner, BuildRight
#    :- Exchange hashpartitioning(customer_id#1, 200), ENSURE_REQUIREMENTS, [plan_id=140]
#    :  +- *(1) 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]
#    :     +- *(1) Filter isnotnull(customer_id#1)
#    :        +- *(1) Scan ExistingRDD[order_id#0,customer_id#1,region#2,product#3,qty#4,amount#5,order_date#6]
#    +- Exchange hashpartitioning(customer_id#8, 200), ENSURE_REQUIREMENTS, [plan_id=143]
#       +- *(2) Filter isnotnull(customer_id#8)
#          +- *(2) Scan ExistingRDD[customer_id#8,name#9,city#10,segment#11]

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