Diagnose Join Multiplication and Missing Matches

Understand the likely causes

• Many-to-many matches create one output row for each matching pair. A supposedly unique dimension may contain repeated or conflicting keys. Key type, whitespace, leading-zero loss, or missingness can prevent valid matches. pandas can match null keys to each other, unlike ordinary SQL equality joins; the tools must not be assumed identical at that boundary.

1. Declare expected grain and cardinality

Write what one row means on each side and whether the intended relationship is one-to-one, many-to-one, or genuinely many-to-many. An order-to-customer lookup usually expects at most one dimension row per customer key. Identify the measure whose sum should remain invariant and which unmatched records should stay visible. Without this expectation, row multiplication can look like harmless extra detail.

2. Audit keys before executing the main join

Count null keys and duplicate-key frequencies on both sides, then inspect whether repeated keys have identical or conflicting payloads. Do not apply DISTINCT merely to make the total smaller; that can erase valid events or choose an unexplained version. Resolve a dimension according to an authoritative rule or reject the conflict pending review. Preserve the original records and audit counts.

3. Enforce a reviewed lookup and reconcile totals

The complete example first exposes a duplicate raw key, then uses a separately reviewed unique dimension. A primary key enforces that contract in the isolated SQLite table. The left join must retain two order rows and total30. This is not an automatic deduplication algorithm: the reviewed mapping is explicit synthetic evidence supplied by the example.

from collections import Counter
from contextlib import closing
import sqlite3

raw_lookup = [("A", "north"), ("A", "north"), ("B", "south")]
duplicates = sorted(key for key, count in Counter(key for key, _ in raw_lookup).items() if count > 1)
print("duplicate raw keys", ",".join(duplicates))
reviewed_lookup = [("A", "north"), ("B", "south")]
with closing(sqlite3.connect(":memory:")) as connection:
    connection.execute("CREATE TABLE orders (id TEXT PRIMARY KEY, customer TEXT, amount INTEGER)")
    connection.execute("CREATE TABLE customers (customer TEXT PRIMARY KEY, region TEXT)")
    connection.executemany("INSERT INTO orders VALUES (?, ?, ?)", [("o1", "A", 10), ("o2", "B", 20)])
    connection.executemany("INSERT INTO customers VALUES (?, ?)", reviewed_lookup)
    rows = list(connection.execute("SELECT o.id, o.amount, c.region FROM orders AS o LEFT JOIN customers AS c ON o.customer=c.customer ORDER BY o.id"))
    assert len(rows) == 2 and sum(row[1] for row in rows) == 30
    print(f"joined={len(rows)} total={sum(row[1] for row in rows)}")

duplicate raw keys A
joined=2 total=30

4. Inspect unmatched and missing keys

Report unmatched keys with counts and controlled samples. Distinguish absent dimension coverage from malformed key representation. For SQL left joins, a right-table condition in WHERE can discard unmatched rows; place the condition in ON when that matches the intended eligibility contract. For pandas, use indicator and validate options where appropriate, and decide how null keys should behave before merging.

5. Audit the output at the original metric grain

Compare pre/post row counts, unique event counts, amounts, unmatched counts, and group totals. If the intended join is genuinely one-to-many, do not sum an event-level amount as though every output row were a new event. Aggregate child records deliberately or allocate measures under a justified rule. Sorting output stabilizes presentation but cannot repair cardinality.

Keep it from recurring

• Document table grain and key constraints, check cardinality before every important join, and retain unmatched/duplicate audits. Keep metric-level reconciliation in automated checks. Label SQL dialect and pandas version assumptions, especially missing-key semantics.