A merge multiplies rows or misses expected matches

Understand the likely causes

• Reference keys may be duplicated, implicit shared columns may change the join key, or key types/normalization may differ. Null-key matching and an incomplete reference range can also change the population.

Capture both input contracts before repairing output

Preserve the exact synthetic source versions, row grains, selected join keys, key dtypes and pandas version. State whether the intended relationship is many-to-one, one-to-one or genuinely many-to-many. Inspect the explicit on/left_on/right_on choices; relying on all shared column names can change meaning after an upstream schema addition. Existing indexes are not automatically the join key for a column merge. Record expected output identities and counts independently. Do not apply drop_duplicates to the joined frame merely to recover a familiar row count, because that destroys evidence about which source relationship produced the expansion.

Locate multiplicative keys and unmatched coverage

Count occurrences of each nonmissing key on both sides before interpreting aggregate totals. A key appearing twice on the left and three times on the right can produce six output pairs. The local arithmetic below exposes this mechanism only, without performing a pandas merge. Separately inspect expected left-only and right-only identities through an indicator or equivalent coverage check. A successful many_to_one validation proves the relevant uniqueness condition, not that every left key matched. pandas null keys can match one another, unlike usual SQL expectations, so isolate unknown keys when the business relationship forbids that match.

left_frequency = {'A': 2}
right_frequency = {'A': 3}
expected_pairs = left_frequency['A'] * right_frequency['A']
assert expected_pairs == 6
print(expected_pairs)

6

Repair the source relationship rather than choosing arbitrarily

Duplicated reference keys can mean a source defect, an omitted depot/version key or legitimate time-dependent records. Determine which attribute row is authoritative for each movement before selecting one. An arbitrary keep-first or keep-last rule can be deterministic and still wrong. Compare leading zeros, whitespace and case under the actual identifier contract; normalization can create false matches or collisions. Do not convert every key to an integer or string solely to make the merge run. Preserve original values and conflicting payloads so the correction remains auditable and does not hide a changed population.

Reconcile identity, attributes and measures together

Use explicit keys and the intended validate relationship, then inspect unmatched coverage and the resulting row grain. Compare unique movement IDs, relevant sums and matched reference attributes against independently derived expectations. Include a duplicate reference key, a missing key, a literal leading-zero identifier and a null on both sides in a small authorized runtime test. This guide performs no pandas execution and accesses no database or real file. Its standard-library count is a narrow cardinality model; actual dtype compatibility, indicator behavior and MergeError diagnostics remain untested engine properties rather than observed fixes.

Keep it from recurring

• Keep grain, uniqueness, missing-key policy and reference-version rules in the join contract. Validate the reference before enrichment, count unmatched identities and test schema additions that could accidentally alter implicit keys.