🗺️ 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 |
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.
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.
💻 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.
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
nullcustomer and city. You can now filter oncustomer IS NULLto 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:
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)
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.
👩💻 Meera's join audit
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
⚠️ Common join mistakes
crossJoin() explicitly.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..dropDuplicates(["key"]) on the dimension side before joining.🧠 Quiz — 3 questions
Now go audit your pipelines for missing broadcast() calls.
🎤 Interview Q&A
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.
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.
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.
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 →