🗺️ What this lesson covers

Joins are the most powerful — and most dangerous — operation in Spark. They combine two DataFrames on a shared key, but when both DataFrames are large, Spark must shuffle data across the network so rows with the same key land on the same executor. That network I/O is the single biggest source of slowness in production pipelines.

In this lesson you will learn:

  • ▸ The five join types in Spark and when to use each one
  • ▸ Why shuffles are expensive and what happens under the hood
  • ▸ How to eliminate shuffle entirely with broadcast()
  • ▸ Data skew — when one key has too many rows and overwhelms a single executor
  • ▸ The four most common join mistakes that silently wreck pipelines

The running example is SouthBank India's transaction and account data. Engineers Arjun and Meera use joins every day to reconcile payments, build customer dashboards, and detect fraud — and they have learned the hard way what shuffle costs look like at 500 million rows.

🔗 Join types overview

Spark supports the same join semantics as SQL. Choose the right type for your use case before writing a single line of code — the choice determines which rows survive the join.

Join type Rows returned When to use
inner Only rows with matching keys in both tables Default in Spark — use when you only want matched records
left All rows from left, nulls for unmatched right Most common in ETL — keeps every source record even if lookup fails
right All rows from right, nulls for unmatched left Less common — swap your DataFrames and use left instead for clarity
full / outer All rows from both, nulls where no match Data reconciliation — comparing two systems for discrepancies
cross Every row paired with every row (M × N rows) Use with extreme caution — 1 000 rows × 1 000 rows = 1 000 000 output rows
Spark default join type

When you call df1.join(df2, "key") without specifying a type, Spark defaults to inner. Be explicit: always pass the third argument ("inner", "left", etc.) so future readers of your code know exactly what you intended.

🌊 The shuffle problem

When you join two large DataFrames, Spark cannot just scan each partition independently — it must guarantee that all rows sharing the same join key end up on the same executor. To do that, it shuffles: every partition sends its rows over the network to the correct target executor, sorted by key.

Sort-Merge Shuffle Join txns 500 M rows · 32 partitions accounts 2 M rows · 32 partitions SHUFFLE Rows with same key routed to same executor over network Joined output Keys matched · local merge Shuffle = network I/O across executors. Expensive.

Every byte that crosses the network during a shuffle adds latency. In a cluster with 32 executors, joining two 10 GB DataFrames can move over 320 GB of data across the network — and that is before any actual computation happens. Shuffles also write intermediate data to disk to protect against executor failure, adding more I/O.

Rule of thumb at SouthBank India: Arjun measures every join in the Spark UI's "SQL" tab. A job with more than 5 shuffle stages on large tables is a refactoring candidate — either a broadcast opportunity was missed, or the data model needs rethinking.

💻 Code demo — inner, left, and broadcast joins

We create two DataFrames representing SouthBank India's transaction and account data, then run three join types. Notice that transaction T006 references account ACC999 — which does not exist. Watch how inner vs. left handles this orphan row.

joins_demo.py Lesson 07 · inner, left, broadcast joins
verified output
=== Inner Join === +------+------------+---------+----------+--------+ |txn_id| customer| city|amount_inr|txn_type| +------+------------+---------+----------+--------+ | T001|Arjun Sharma| Mumbai| 45000.0| credit| | T002| Priya Patel| Delhi| 12000.0| debit| | T003| Rohan Mehta|Bangalore| 73500.0| credit| | T004|Arjun Sharma| Mumbai| 8000.0| debit| | T005| Meera Singh| Mumbai| 95000.0| credit| +------+------------+---------+----------+--------+ === Left Join (T006 has null customer) === +------+------------+---------+----------+ |txn_id| customer| city|amount_inr| +------+------------+---------+----------+ | T001|Arjun Sharma| Mumbai| 45000.0| | T002| Priya Patel| Delhi| 12000.0| | T003| Rohan Mehta|Bangalore| 73500.0| | T004|Arjun Sharma| Mumbai| 8000.0| | T005| Meera Singh| Mumbai| 95000.0| | T006| null| null| 1000.0| +------+------------+---------+----------+ === Broadcast Join — city totals === +---------+---------+ | city|total_inr| +---------+---------+ | Mumbai| 148000.0| |Bangalore| 73500.0| | Delhi| 12000.0| +---------+---------+

Key observations from the output:

  • ▸ Inner join: T006 (ACC999) is silently dropped — no error, no null. If you are reconciling payments this is a data loss bug.
  • ▸ Left join: T006 appears with null customer and city. You can now filter on customer IS NULL to find all unmatched transactions — essential for fraud investigation.
  • ▸ Broadcast join: same result as inner, but zero network shuffle — the 4-row accounts table was sent to each executor in memory.

📡 Broadcast join — eliminate the shuffle

When the right side of a join is small, Spark can skip the shuffle entirely by broadcasting the small table to every executor. Each executor then performs the join locally against the broadcasted copy — no network traffic for the small table, and the large table is never reshuffled.

How it works

Calling broadcast(df) wraps the DataFrame in a hint that tells Spark's query planner: "this table is small — send a copy to every executor." Spark serialises the small table, sends it over the driver-to-executor channel (not executor-to-executor), and each executor holds it in memory for the duration of the job.

Approach What Spark does Network cost
Sort-merge join (default for large-large) Shuffles both DataFrames so matching keys land together, then sorts and merges High — both tables reshuffled across the cluster
Broadcast hash join Small table sent to every executor; large table scanned once locally Low — only the small table crosses the wire, once

Auto-broadcast threshold

Spark will automatically broadcast tables smaller than spark.sql.autoBroadcastJoinThreshold (default: 10 MB). You can raise this — a common production setting is 50 MB or even 100 MB for cluster environments with enough driver memory:

config snippet raise broadcast threshold

When to use broadcast joins

  • ▸ Lookup tables — city codes, region mappings, currency conversion rates
  • ▸ Product or account catalogs — thousands to low millions of rows, stable within a pipeline run
  • ▸ Dimension tables in a star schema — joining a large fact table to small dimensions
  • ▸ Fraud filter lists — blocked account IDs, suspicious merchant codes (typically hundreds of thousands of rows)
Driver memory limit

The broadcasted table must fit in the driver's memory before it is sent out. If you set the threshold to 500 MB and your driver has 2 GB heap, you have room for 4 concurrent broadcasts. For tables above 200 MB, prefer pre-partitioning or bucketing instead.

⚙️ When shuffle is unavoidable

Large-large joins — for example, 500 million transactions joined to 200 million customer events — cannot use broadcast joins. The shuffle is unavoidable. The question becomes: how do you make it as cheap as possible?

Strategies to minimise shuffle cost

1
Pre-partition both DataFrames on the join key
Call df.repartition(200, "account_id") on both sides before the join. Spark detects that the data is already partitioned by the join key and skips the re-shuffle stage. This is most effective when you will join the same DataFrames multiple times in the same pipeline.
2
Bucket tables in Delta Lake or Hive
Bucketing physically partitions tables on disk by the join key at write time. When Spark reads two bucketed tables with the same bucket key and bucket count, it can join them without any shuffle — the data is already co-located. This is the gold standard for repeated production joins.
3
Enable Adaptive Query Execution (AQE)
AQE (spark.sql.adaptive.enabled=true, the default since Spark 3.2) re-optimises the query plan at runtime using real partition statistics. It can dynamically coalesce small shuffle partitions, convert sort-merge joins to broadcast joins when a table turns out to be small after filtering, and apply skew join optimisation automatically.
4
Filter before joining, not after
Apply .filter() or .where() before the join, not after. Filtering first reduces the data volume that Spark has to shuffle. A join on 500 M rows becomes much cheaper if you can filter to 50 M relevant rows first.

👩‍💻 Meera's join audit

SouthBank India — Pipeline Engineering · Meera Singh

At SouthBank India, Meera audits every join in production pipelines during the quarterly pipeline review. Her rule is simple: if the right side of any join is under 50 MB, it gets a broadcast() hint — no exceptions. She also checks the Spark UI "SQL" tab for any sort-merge join stage that shows more than 200 GB shuffled; those go to the backlog for bucketing.

This discipline cut one pipeline's runtime from 45 minutes to 8 minutes — a single change: wrapping the city-to-region lookup table (3 MB, 5 000 rows) in broadcast(). The original author had not noticed because the pipeline ran overnight and "it always finished before morning." The latency was invisible until SouthBank moved to hourly refreshes and the 45-minute runtime broke the SLA.

⚖️ Skewed joins

Data skew happens when one join key has a disproportionate number of rows. In a financial dataset, a popular savings account or a high-traffic merchant ID might appear millions of times while all other keys appear a few hundred times. The executor assigned that key processes millions of rows while all other executors finish in seconds — and the entire job waits for the slow executor.

Signs of a skewed join

  • ▸ In the Spark UI, one task in a stage takes 10× longer than all others
  • ▸ The "max task duration" for a shuffle stage is far higher than the median
  • ▸ One executor shows 90%+ GC time while others are idle

Fixes

Salting the key

  • Add a random suffix (salt) to the hot key to distribute its rows across multiple executors
  • Replicate the right-side rows for that key with the same suffixes
  • Join on the salted key, then drop the salt column after
  • Manual but reliable — use when AQE is not enough

AQE skew join optimisation

  • With spark.sql.adaptive.skewJoin.enabled=true (default since Spark 3.2), AQE detects skewed partitions at runtime
  • Splits hot partitions into smaller sub-partitions and replicates the corresponding right-side partition
  • Automatic — no code changes needed in most cases
  • Configure sensitivity with spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes
At SouthBank India: The "SB_CURRENT_ACCOUNT_001" account held 18 million transactions — 36× more than the next busiest account. Before AQE, the reconciliation join took 2 hours. After enabling AQE skew join optimisation, the same job finished in 22 minutes.

⚠️ Common join mistakes

Joining on a nullable column
Nulls do not match nulls in Spark (or SQL). If your join key column contains nulls, those rows will be dropped silently in an inner join, and matched to every null on the other side in some implementations. Always filter or coalesce nulls before joining on a key column.
Cartesian product by mistake
A cross join with no condition or a join where the key condition is always true produces M × N rows — every row in the left table paired with every row in the right. 1 000 rows × 1 000 rows = 1 000 000 output rows. This can exhaust cluster memory silently. Spark will raise an error if you attempt a cross join without crossJoin() explicitly.
Not broadcasting small lookup tables
A lookup table with 5 000 city-to-region mappings will trigger a full sort-merge shuffle if you forget broadcast(). The overhead is invisible on small data but compounds to minutes on 500 M row fact tables. Make broadcasting small tables a code review checklist item.
Forgetting to deduplicate before join
If either side of the join contains duplicate keys, the join multiplies rows: 3 copies of key A on the left × 2 copies on the right = 6 output rows. This silent row explosion produces wrong aggregations downstream. Always .dropDuplicates(["key"]) on the dimension side before joining.

🧠 Quiz — 3 questions

Question 1 of 3
You are joining a DataFrame of 1 million transactions to a DataFrame of 200 rows (a city lookup table). What join type gives the best performance?
Question 2 of 3
In a left join, rows from the left table that have no matching key in the right table appear as:
Question 3 of 3
True or False: A shuffle join reads all data twice — once to partition it and once to join it.
0
/ 3
Join knowledge checked.

Now go audit your pipelines for missing broadcast() calls.

🎤 Interview Q&A

Medium What is a broadcast join and when would you use it?
+
A broadcast join is a Spark join strategy where one DataFrame — the smaller one — is serialised and sent in full to every executor in the cluster. Instead of shuffling both tables across the network (which is what a sort-merge join does), each executor receives a complete copy of the small table in memory and performs the join locally against its partition of the large table. The result is zero network shuffle for the small table and a single sequential scan of the large table. You use it when one side is small enough to fit in memory on each executor — typically under 10 MB by default (spark.sql.autoBroadcastJoinThreshold), though in practice you can raise this to 50–100 MB safely on modern clusters. Ideal candidates are lookup tables, dimension tables in a star schema, and small reference datasets like currency codes, region maps, or fraud block lists.
Medium How does Spark decide whether to use sort-merge join or broadcast hash join?
+
Spark's Catalyst query planner applies the following rules in order. First, it checks whether either table's estimated size is below spark.sql.autoBroadcastJoinThreshold (default 10 MB). If yes, it chooses a broadcast hash join — the smaller table is broadcast. Second, if both tables are large (above the threshold), Catalyst prefers sort-merge join because both tables can be sorted and merged partition by partition with bounded memory. Third, if spark.sql.adaptive.enabled is true (the default since Spark 3.2), Adaptive Query Execution re-evaluates at runtime: after filtering, a table that was initially estimated as large may turn out to be small, and AQE will convert the planned sort-merge join to a broadcast hash join dynamically. You can also override the planner explicitly with the broadcast() hint in your code, which forces a broadcast regardless of the estimated size — useful when the planner underestimates a table's post-filter size.
Hard What is data skew and how do you fix it in a join?
+
Data skew in a join occurs when the join key's value distribution is highly uneven — one or a few key values appear millions of times while most values appear only a handful of times. Because Spark partitions data by key hash during a shuffle join, the executor responsible for the hot key receives and must process a disproportionate number of rows. While all other executors finish in seconds, the skewed executor runs for minutes or hours, blocking the entire stage. The job appears "stuck at 99% complete." There are three main fixes: (1) AQE skew join optimisation — enabled by default since Spark 3.2 via spark.sql.adaptive.skewJoin.enabled=true. AQE detects skewed partitions at runtime, splits them into smaller sub-partitions, and replicates the corresponding right-side partition to match. No code change needed. (2) Salting — if AQE is not available or not sufficient, you manually add a random integer salt (0–N) as a suffix to the hot key on the left side, replicate the hot key's right-side rows with all salt values, join on the salted key, and then drop the salt. This distributes the hot key across N executors. (3) Isolate the hot key — split the join into two separate jobs: one join for all non-hot keys (normal shuffle), and one broadcast join specifically for the hot key's rows (where the right side for that one key is small enough to broadcast). Union the results.

✅ Key takeaways

  • Spark supports five join types: inner, left, right, full/outer, and cross. Always pass the join type explicitly — never rely on the default.
  • A shuffle join moves data across the network so rows with the same key land on the same executor. This is the most expensive operation in a Spark pipeline — minimise it or eliminate it wherever possible.
  • Use broadcast(df) whenever the right side of a join is small (under 10 MB by default, configurable). This eliminates the shuffle entirely and can cut pipeline runtimes by 5–10×.
  • Data skew — one key with millions of rows — causes one executor to do all the work while others sit idle. Enable AQE (spark.sql.adaptive.enabled=true) or use salting to distribute the load.
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

Build the pipelines that move India's money

Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer →