Join two files without merging unrelated entities
A join connects rows according to a rule. It does not establish that similar names identify the same entity. Write the rule, exceptions and expected outcome before modifying data.
Define what each row represents
Identify the object in each file: person, establishment, company, contract or observation. Files may use a company identifier while their rows describe establishments.
Fictional example: a company has two establishments. Connecting its name to one address could remove useful information. Record object levels in the dictionary.
Choose a key rather than a resemblance
Prefer documented identifiers where sources provide them. Preserve format, leading zeros and origin. For multi-field matching, specify which fields must agree and in which context.
A name and town may locate a candidate without confirming identity. Retain the candidate and matching reason for review when evidence is insufficient.
Count matches before combining
For each row, establish whether the rule produces zero, one or multiple matches. Inspect multiple matches before the full join: they can multiply rows and inflate totals.
On ten fictional contracts, check the contract count after matching. If one contract matches two references, do not count two contracts; document ambiguity or a genuine multiple relationship.
Retain unmatched rows and decisions
Keep unmatched rows, ambiguous matches and the rule version. No match does not justify deleting the entity.
Make the first join on a copy. Retain before/after counts, field provenance and manual decisions so another person can explain the result.
A record to keep with the decision
| Item | Evidence |
|---|---|
| Objects | Level and meaning of rows in each file. |
| Keys | Identifiers, formats and matching rule. |
| Cardinality | Zero, one or multiple matches per row. |
| Decisions | Ambiguities, unmatched rows and evidence. |
Download the worksheet to fill in (CSV)
Frequently asked questions
Can records be merged on a shared name?
A shared name alone does not establish identity. Find identifiers and context or retain the case for review.
Why does a total increase after joining?
One row may match several rows in the other file. Check cardinality before aggregation.
Reference material
The practical checklist is an editorial synthesis to adapt to your service. It does not constitute a certification or an audit result.