Browse cheat sheets · PySpark

Technology: PySparkCategory: Data TransformationDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Inner join
  5. 2. Left join
  6. 3. Right join
  7. 4. Full outer join
  8. 5. Left-semi join
  9. 6. Left-anti join
  10. 7. Join on multiple keys
  11. 8. Join when key names differ
  12. 9. Select output columns explicitly after a join
  13. 10. Validate the expected right-side uniqueness
  14. 11. Check both sides before a one-to-one join
  15. 12. Measure row count before and after an enrichment join
  16. 13. Check duplicate-key frequency
  17. 14. Show how many matches each left key receives
  18. 15. Understand many-to-many expansion
  19. 16. Deduplicate deliberately before a join
  20. 17. Use dropDuplicates() only when any duplicate row is equivalent
  21. 18. Handle null join keys deliberately
  22. 19. Use null-safe equality when required
  23. 20. Normalize join key types before joining
  24. 21. Normalize text keys carefully
  25. 22. Filter and project before joining
  26. 23. Inspect the physical join plan
  27. 24. Broadcast a small lookup table
  28. 25. Verify that broadcast happened
  29. 26. Avoid collecting lookup keys to the driver
  30. 27. Check for skewed join keys
  31. 28. Check both sides for skew
  32. 29. Avoid replacing all null keys with one sentinel before a join
  33. 30. Repartition deliberately before repeated key-based work
  34. 31. Understand bucketing vs repartitioning
  35. 32. Use a cross join only intentionally
  36. 33. Beware accidental cross joins
  37. 34. Use range/non-equality joins carefully
  38. 35. Validate unmatched left rows
  39. 36. Calculate a match rate
  40. 37. Reconcile totals after an enrichment join
  41. 38. Preserve grain after joining
  42. 39. Cache a reused small dimension only when justified
  43. 40. Compare plans before and after a tuning change
  44. What the result means
  45. What to look for
  46. Left join increases row count
  47. Totals increase after enrichment
  48. Many rows remain unmatched
  49. One Spark task runs much longer than peers
  50. Plan uses sort-merge join for a very small lookup
  51. Broadcast join causes memory problems
  52. Join uses huge shuffle despite small output
  53. Production notes
  54. Output grain must be documented
  55. Row count is not enough
  56. Broadcast decisions should consider bytes, not only rows
  57. Adaptive Query Execution can change join behavior
  58. Null keys deserve explicit treatment
  59. Many-to-many is not inherently wrong
  60. Join optimization depends on data distribution
  61. Common mistakes
  62. 1. Joining before checking key uniqueness
  63. 2. Using dropDuplicates() after a bad join
  64. 3. Broadcasting without checking size
  65. 4. Collecting lookup keys to Python
  66. 5. Joining wide DataFrames
  67. 6. Ignoring null join semantics
  68. 7. Replacing null keys with one sentinel
  69. 8. Using only row count to validate correctness
  70. 9. Assuming the optimizer picked the best plan for every case
  71. 10. Repartitioning blindly before every join
  72. Next step
  73. 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:

  • BroadcastHashJoin
  • SortMergeJoin
  • ShuffledHashJoin

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.

Next step

Use the symptom to choose the next action.

  • Left join row count increased → check right-side duplicate keys.
  • Many rows unmatched → compare key types, formatting, and composite-key completeness.
  • Totals inflated → inspect many-to-many expansion.
  • One task dominates runtime → investigate skewed join keys.
  • Lookup is small → test broadcast and verify the plan.
  • Shuffle is large → filter/project both sides earlier.
  • Null keys behave unexpectedly → define null semantics explicitly.
  • Output grain changed → fix relationship logic before performance tuning.
  • Join is still slow after correctness is proven → move to PySpark Performance Quick Reference and inspect Spark stages/shuffle.

← All cheat sheets