Merge with cardinality checks and unmatched evidence

A join can multiply rows when the right key is not unique. That can inflate totals even if the final chart looks reasonable. Validate the expected many-to-one relationship before treating the joined result as an enriched report.

Our left sales table keeps unmatched customers and records _match. Missing join keys are rejected on both sides because Pandas can match nulls to nulls, unlike the usual SQL null comparison. The application’s policy must be explicit at this boundary.

A left merge preserves every input observation, while an inner merge would discard unmatched customers. validate=many_to_one checks right-key uniqueness; indicator records whether each row matched. Neither setting replaces key validation or the report’s separate first-valid duplicate policy.

The general merge helper rejects duplicate right keys. The capstone first applies its established customer deduplication policy, then supplies a unique lookup to this helper. That sequence avoids silently choosing an arbitrary duplicate during enrichment.

import pandas as pd
left=pd.DataFrame({"id":["001","missing"]})
right=pd.DataFrame({"id":["001"],"name":["NA"]})
joined=left.merge(right,on="id",how="left",validate="many_to_one",indicator=True)
print(joined["_merge"].astype(str).tolist())
print(joined["name"].isna().tolist())

The _match indicator is evidence, not a replacement for the actual customer name. A name literally equal to NA is valid text, so default missing-token inference would corrupt the lookup. Actual pd.NA marks an unmatched result here.

The tests use a nondefault left index and repeated left customer keys. The output must preserve left row order and count while allowing many sales to share one unique customer. A duplicate right key is refused before it can inflate revenue.

import pandas as pd
left=pd.DataFrame({"key":[None]})
right=pd.DataFrame({"key":[None],"value":[7]})
print(len(left.merge(right,on="key")))
print("this application rejects missing keys before merging")

Implement the many-to-one left merge and inspect unmatched rows. Reject null and duplicate keys explicitly. Next reconcile the complete DataFrame audit against the existing records algorithm.