Browse cheat sheets · PySpark
Join DataFrames in PySpark
Join PySpark DataFrames safely by validating key cardinality, choosing the right join type and strategy, avoiding row multiplication, and handling skew, nulls, and ambiguous columns.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Inner join
- 2. Left join
- 3. Right join
- 4. Full outer join
- 5. Left-semi join
- 6. Left-anti join
- 7. Join on multiple keys
- 8. Join when key names differ
- 9. Select output columns explicitly after a join
- 10. Validate the expected right-side uniqueness
- 11. Check both sides before a one-to-one join
- 12. Measure row count before and after an enrichment join
- 13. Check duplicate-key frequency
- 14. Show how many matches each left key receives
- 15. Understand many-to-many expansion
- 16. Deduplicate deliberately before a join
- 17. Use dropDuplicates() only when any duplicate row is equivalent
- 18. Handle null join keys deliberately
- 19. Use null-safe equality when required
- 20. Normalize join key types before joining
- 21. Normalize text keys carefully
- 22. Filter and project before joining
- 23. Inspect the physical join plan
- 24. Broadcast a small lookup table
- 25. Verify that broadcast happened
- 26. Avoid collecting lookup keys to the driver
- 27. Check for skewed join keys
- 28. Check both sides for skew
- 29. Avoid replacing all null keys with one sentinel before a join
- 30. Repartition deliberately before repeated key-based work
- 31. Understand bucketing vs repartitioning
- 32. Use a cross join only intentionally
- 33. Beware accidental cross joins
- 34. Use range/non-equality joins carefully
- 35. Validate unmatched left rows
- 36. Calculate a match rate
- 37. Reconcile totals after an enrichment join
- 38. Preserve grain after joining
- 39. Cache a reused small dimension only when justified
- 40. Compare plans before and after a tuning change
- What the result means
- What to look for
- Left join increases row count
- Totals increase after enrichment
- Many rows remain unmatched
- One Spark task runs much longer than peers
- Plan uses sort-merge join for a very small lookup
- Broadcast join causes memory problems
- Join uses huge shuffle despite small output
- Production notes
- Output grain must be documented
- Row count is not enough
- Broadcast decisions should consider bytes, not only rows
- Adaptive Query Execution can change join behavior
- Null keys deserve explicit treatment
- Many-to-many is not inherently wrong
- Join optimization depends on data distribution
- Common mistakes
- 1. Joining before checking key uniqueness
- 2. Using dropDuplicates() after a bad join
- 3. Broadcasting without checking size
- 4. Collecting lookup keys to Python
- 5. Joining wide DataFrames
- 6. Ignoring null join semantics
- 7. Replacing null keys with one sentinel
- 8. Using only row count to validate correctness
- 9. Assuming the optimizer picked the best plan for every case
- 10. Repartitioning blindly before every join
- Next step
- Related deep dives
Problem
Joining DataFrames is one of the most common and most expensive operations in Spark.
Production problems usually come from:
- wrong join type
- duplicate keys on one or both sides
- accidental many-to-many expansion
- null join keys
- ambiguous duplicate column names
- joining before filtering/projecting
- broadcasting a table that is not actually small
- skewed join keys
- collecting lookup keys to Python
- incorrect assumptions about output grain
The goal is to make the join relationship explicit before tuning performance.
Quick answer
Start by validating the intended relationship.
Example many-to-one enrichment:
from pyspark.sql import functions as F
duplicate_keys = (
merchants
.groupBy("merchant_id")
.count()
.filter(F.col("count") > 1)
)
if duplicate_keys.limit(1).count() > 0:
raise RuntimeError(
"merchant_id is not unique in merchants"
)
Then join:
result = (
sales.alias("s")
.join(
merchants.alias("m"),
on="merchant_id",
how="left",
)
.select(
"sale_id",
"merchant_id",
"amount",
"merchant_name",
)
)
Use this decision model:
Need matching rows only
→ inner
Need every left row
→ left
Need every right row
→ right
Need every row from both sides
→ full
Need only left rows that have a match
→ left_semi
Need only left rows with no match
→ left_anti
Before optimizing the join, verify that the output row count and grain are correct.
Code / commands
1. Inner join
result = sales.join(
merchants,
on="merchant_id",
how="inner",
)
Use when only matching keys should survive.
Be aware that unmatched rows from either side disappear.
2. Left join
result = sales.join(
merchants,
on="merchant_id",
how="left",
)
Use when every row from the left side must be preserved.
This is common for enrichment.
A left join can still increase row count if the right side has duplicate keys.
3. Right join
result = sales.join(
merchants,
on="merchant_id",
how="right",
)
Use when every row from the right side must be preserved.
If the logic reads awkwardly, consider swapping left/right inputs and using a left join for clarity.
4. Full outer join
result = left.join(
right,
on="customer_id",
how="full",
)
Use when unmatched rows from both sides matter.
This is useful for reconciliation.
Be deliberate about how nulls from unmatched sides are interpreted downstream.
5. Left-semi join
Keep only left rows that have a match:
matched_sales = sales.join(
merchants.select("merchant_id"),
on="merchant_id",
how="left_semi",
)
This is an existence filter.
It does not bring columns from the right side into the result.
6. Left-anti join
Keep only left rows with no match:
orphan_sales = sales.join(
merchants.select("merchant_id"),
on="merchant_id",
how="left_anti",
)
Use this for:
- unmatched foreign keys
- data-quality exceptions
- incremental new-key detection
- reconciliation
7. Join on multiple keys
result = left.join(
right,
on=[
"member_id",
"merchant_id",
"site_id",
],
how="left",
)
Use all columns that define the real business relationship.
Do not reduce a compound key to one column for convenience.
8. Join when key names differ
l = left.alias("l")
r = right.alias("r")
result = (
l.join(
r,
F.col("l.customer_id")
== F.col("r.client_id"),
"left",
)
)
When using an explicit condition, duplicate join-key columns can remain.
Select the desired output columns explicitly.
9. Select output columns explicitly after a join
result = (
sales.alias("s")
.join(
merchants.alias("m"),
F.col("s.merchant_id")
== F.col("m.merchant_id"),
"left",
)
.select(
F.col("s.sale_id"),
F.col("s.merchant_id"),
F.col("s.amount"),
F.col("m.merchant_name"),
)
)
This avoids:
- ambiguous columns
- duplicate key columns
- carrying unnecessary payload
10. Validate the expected right-side uniqueness
For a many-to-one join:
duplicate_right = (
right
.groupBy("customer_id")
.count()
.filter(F.col("count") > 1)
)
Fail if duplicates are not allowed:
if duplicate_right.limit(1).count() > 0:
raise RuntimeError(
"Expected unique customer_id on right side"
)
This is the Spark equivalent of making join cardinality assumptions explicit.
11. Check both sides before a one-to-one join
def has_duplicate_keys(df, keys):
return (
df.groupBy(*keys)
.count()
.filter(F.col("count") > 1)
.limit(1)
.count()
> 0
)
Then:
keys = ["customer_id"]
if has_duplicate_keys(left, keys):
raise RuntimeError(
"Left side is not unique"
)
if has_duplicate_keys(right, keys):
raise RuntimeError(
"Right side is not unique"
)
Use this for business-critical one-to-one assumptions.
For very large DataFrames, build a reusable validation pattern that avoids unnecessary repeated scans.
12. Measure row count before and after an enrichment join
before = sales.count()
result = sales.join(
merchants,
"merchant_id",
"left",
)
after = result.count()
print("Before:", before)
print("After:", after)
print("Difference:", after - before)
If a left enrichment join unexpectedly grows, check right-side duplicates first.
For production-scale pipelines, avoid repeated full counts unless the validation is worth the scan cost.
13. Check duplicate-key frequency
(
merchants
.groupBy("merchant_id")
.count()
.filter(F.col("count") > 1)
.orderBy(F.desc("count"))
.show(50, truncate=False)
)
This tells you whether duplication is:
- isolated
- widespread
- dominated by a few keys
14. Show how many matches each left key receives
match_counts = (
sales.alias("s")
.join(
merchants.alias("m"),
F.col("s.merchant_id")
== F.col("m.merchant_id"),
"left",
)
.groupBy(
F.col("s.merchant_id").alias("merchant_id")
)
.agg(
F.count("*").alias("joined_rows")
)
)
Use this carefully: if sales already has repeated merchant IDs, this is not a direct right-side match count.
For relationship analysis, check each side independently first.
15. Understand many-to-many expansion
Suppose left has:
customer_id = 1
2 rows
and right has:
customer_id = 1
3 rows
The join produces:
2 × 3 = 6 rows
This is not a Spark bug.
It is relational join semantics.
If that is not intended, fix the grain before joining.
16. Deduplicate deliberately before a join
If one row should survive:
from pyspark.sql import Window
w = (
Window
.partitionBy("merchant_id")
.orderBy(
F.col("updated_at").desc(),
F.col("source_sequence").desc(),
)
)
merchants_one_row = (
merchants
.withColumn(
"_rn",
F.row_number().over(w),
)
.filter(F.col("_rn") == 1)
.drop("_rn")
)
Then join.
Do not use arbitrary dropDuplicates() when the survivor matters.
17. Use dropDuplicates() only when any duplicate row is equivalent
unique_merchants = (
merchants
.dropDuplicates(
["merchant_id"]
)
)
This is safe only if all duplicate rows are equivalent for downstream use.
If values differ, define a deterministic winner.
18. Handle null join keys deliberately
Standard equality joins do not match null to null.
Example:
left.join(
right,
"customer_id",
"inner",
)
Rows with null customer_id do not match each other through normal equality.
If null-safe equality is truly the business rule, use Spark null-safe equality.
19. Use null-safe equality when required
result = (
left.alias("l")
.join(
right.alias("r"),
F.col("l.customer_id").eqNullSafe(
F.col("r.customer_id")
),
"inner",
)
)
Use this only when:
null on left
should deliberately match:
null on right
That is uncommon for real business keys.
20. Normalize join key types before joining
Inspect:
left.printSchema()
right.printSchema()
Normalize explicitly:
left = left.withColumn(
"customer_id",
F.col("customer_id").cast("string"),
)
right = right.withColumn(
"customer_id",
F.col("customer_id").cast("string"),
)
A join key should have consistent semantic and physical types.
Do not rely on accidental implicit casting.
21. Normalize text keys carefully
left = left.withColumn(
"customer_id_normalized",
F.trim(
F.col("customer_id")
),
)
If case-insensitive semantics are valid:
left = left.withColumn(
"customer_id_normalized",
F.lower(
F.trim(F.col("customer_id"))
),
)
Apply the same rule to both sides.
Do not lowercase or strip identifiers unless the business semantics permit it.
22. Filter and project before joining
Prefer:
sales_small = (
sales
.filter(
F.col("sale_date") >= "2026-10-01"
)
.select(
"merchant_id",
"sale_id",
"amount",
)
)
merchants_small = (
merchants
.select(
"merchant_id",
"merchant_name",
)
)
result = sales_small.join(
merchants_small,
"merchant_id",
"left",
)
Reduce data before shuffle whenever the logic allows it.
23. Inspect the physical join plan
result.explain("formatted")
Look for join operators such as:
BroadcastHashJoinSortMergeJoinShuffledHashJoin
The chosen strategy tells you how Spark plans to move and compare the data.
Do not force a strategy without understanding why the current one is slow.
24. Broadcast a small lookup table
result = sales.join(
F.broadcast(merchants),
on="merchant_id",
how="left",
)
Broadcasting can avoid shuffling the large side.
Use it when the lookup table is genuinely small enough for executor memory.
Do not broadcast a table simply because it has fewer rows than the fact table.
Row width matters too.
25. Verify that broadcast happened
result.explain("formatted")
Look for:
BroadcastHashJoin
Do not assume the hint or auto-broadcast threshold produced the plan you expected.
26. Avoid collecting lookup keys to the driver
Bad:
merchant_ids = [
row["merchant_id"]
for row in merchants.collect()
]
filtered = sales.filter(
F.col("merchant_id").isin(
merchant_ids
)
)
Use a semi join:
filtered = sales.join(
merchants.select("merchant_id"),
"merchant_id",
"left_semi",
)
This keeps the operation distributed.
27. Check for skewed join keys
(
sales
.groupBy("merchant_id")
.count()
.orderBy(F.desc("count"))
.show(50, truncate=False)
)
Look for keys that dominate the row distribution.
A hot key can cause one or a few tasks to run much longer than the rest.
28. Check both sides for skew
(
merchants
.groupBy("merchant_id")
.count()
.orderBy(F.desc("count"))
.show(50, truncate=False)
)
Skew on either side can affect the join.
Common hot keys include:
- null replacement values
"UNKNOWN"0- one very large tenant/customer
- malformed records collapsing to one key
29. Avoid replacing all null keys with one sentinel before a join
Risky:
df = df.fillna(
{
"merchant_id": 0
}
)
Then joining on merchant_id can create a huge hot-key group on 0.
Only use sentinel replacement when the business rule requires it and the skew impact is understood.
30. Repartition deliberately before repeated key-based work
sales_by_merchant = sales.repartition(
"merchant_id"
)
This creates a shuffle.
Use it only when the resulting partitioning can be reused enough to justify the cost.
Do not repartition before every join by habit.
31. Understand bucketing vs repartitioning
repartition() affects the current DataFrame execution.
Persistent bucketing/table-layout strategies are different and depend on the Spark/Fabric storage path and supported table features.
Do not assume one repartition() call permanently organizes the Delta table on disk.
32. Use a cross join only intentionally
result = left.crossJoin(
right
)
A cross join produces:
left_rows × right_rows
Use only when Cartesian multiplication is the actual requirement.
For large inputs, this can explode quickly.
33. Beware accidental cross joins
An incorrect join condition can produce unexpected Cartesian behavior or massive row growth.
Always validate:
join keys
join condition
output row count
business grain
after adding or changing join logic.
34. Use range/non-equality joins carefully
Example:
result = (
events.alias("e")
.join(
rates.alias("r"),
(
F.col("e.event_time")
>= F.col("r.valid_from")
)
& (
F.col("e.event_time")
< F.col("r.valid_to")
),
"left",
)
)
Range joins can be expensive because equality-based hash joins may not apply directly.
Reduce both sides aggressively and validate row multiplication.
35. Validate unmatched left rows
unmatched = sales.join(
merchants.select("merchant_id"),
"merchant_id",
"left_anti",
)
Inspect:
unmatched.show(
100,
truncate=False,
)
This is often the fastest way to diagnose missing reference data.
36. Calculate a match rate
total = sales.count()
matched = (
sales.join(
merchants.select("merchant_id"),
"merchant_id",
"left_semi",
)
.count()
)
match_rate = (
matched / total
if total
else None
)
print(match_rate)
There is no universal acceptable match rate.
Compare with the source contract and historical behavior.
37. Reconcile totals after an enrichment join
Suppose the join should not change sales amount totals.
Before:
before = (
sales
.agg(
F.sum("amount").alias("amount")
)
.first()["amount"]
)
After:
after = (
result
.agg(
F.sum("amount").alias("amount")
)
.first()["amount"]
)
If the total increases unexpectedly, investigate row multiplication.
38. Preserve grain after joining
Document:
Input sales grain:
one row per sale_id
Expected output grain:
one row per sale_id
Then validate:
duplicates = (
result
.groupBy("sale_id")
.count()
.filter(F.col("count") > 1)
)
if duplicates.limit(1).count() > 0:
raise RuntimeError(
"Join changed sale_id grain"
)
This is more meaningful than row count alone.
39. Cache a reused small dimension only when justified
merchants_small = (
merchants
.select(
"merchant_id",
"merchant_name",
)
.cache()
)
merchants_small.count()
If many joins reuse the same expensive-to-build dimension, caching can help.
If it is read once, caching adds unnecessary work and memory usage.
40. Compare plans before and after a tuning change
Before:
result.explain("formatted")
After adding a broadcast or changing projections:
optimized_result.explain("formatted")
Then measure a real Spark action.
Do not call a change successful because the code looks cleaner.
Validate:
- physical plan
- runtime
- shuffle
- output correctness
What the result means
| Join type | Keeps |
|---|---|
inner |
matching rows only |
left |
all left rows + matches |
right |
all right rows + matches |
full |
all rows from both sides |
left_semi |
left rows that have a match |
left_anti |
left rows that do not have a match |
| cross join | every left row paired with every right row |
Common physical strategies:
| Strategy | Typical meaning |
|---|---|
BroadcastHashJoin |
one side is sent to executors; large-side shuffle can be avoided |
SortMergeJoin |
both sides are shuffled/sorted by join keys |
ShuffledHashJoin |
hash join after shuffle |
The most important distinction is:
logical join correctness
comes before
physical join optimization
What to look for
Left join increases row count
Likely causes:
- duplicate keys on the right
- true one-to-many relationship
- wrong join key
Do not add dropDuplicates() after the join until the relationship is understood.
Totals increase after enrichment
This is a strong sign of row multiplication.
Check key uniqueness and grain.
Many rows remain unmatched
Check:
- dtype
- whitespace
- case
- leading zeros
- missing reference rows
- composite-key completeness
One Spark task runs much longer than peers
Check key skew.
One dominant key can control total join duration.
Plan uses sort-merge join for a very small lookup
Check whether broadcast is appropriate and whether Spark recognizes the small side.
Do not force broadcast before checking actual size.
Broadcast join causes memory problems
The broadcast side may not be small enough in bytes.
Remove the hint or reduce the dimension further.
Join uses huge shuffle despite small output
Filter and project earlier.
The output size does not reflect how much data Spark had to move.
Production notes
Output grain must be documented
For every important join, write down:
left grain
right grain
expected output grain
This catches more bugs than tuning settings.
Row count is not enough
A join can keep the same row count and still attach the wrong reference row.
Also validate:
- business keys
- unmatched counts
- important totals
- sample keys
- effective-date logic
Broadcast decisions should consider bytes, not only rows
A lookup with few rows but very wide payloads can still be expensive to broadcast.
Project only required columns before broadcasting.
Adaptive Query Execution can change join behavior
Spark may adjust plans at runtime depending on collected statistics and configuration.
Inspect the actual executed/physical behavior rather than assuming the initial logical shape tells the full story.
Null keys deserve explicit treatment
For normal equality joins, null does not equal null.
Do not replace nulls with a single sentinel casually because that can create both semantic errors and skew.
Many-to-many is not inherently wrong
Some workloads legitimately require many-to-many joins.
The problem is when the relationship was expected to be one-to-one or many-to-one.
Join optimization depends on data distribution
There is no universal best join strategy.
Measure with representative data.
Common mistakes
1. Joining before checking key uniqueness
This is the fastest path to accidental row multiplication.
2. Using dropDuplicates() after a bad join
This hides the cardinality problem and can discard valid rows.
3. Broadcasting without checking size
Broadcast is not automatically better.
4. Collecting lookup keys to Python
Use semi/anti joins instead.
5. Joining wide DataFrames
Project only required columns before shuffle.
6. Ignoring null join semantics
Nulls do not match under normal equality.
7. Replacing null keys with one sentinel
This can create a massive hot key.
8. Using only row count to validate correctness
Validate grain and business totals too.
9. Assuming the optimizer picked the best plan for every case
Usually it does well, but skew, stale stats, unusual data shape, or hints can change outcomes.
Inspect the plan when performance matters.
10. Repartitioning blindly before every join
A repartition is itself a shuffle.
Only pay that cost when there is a clear benefit.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.