Sooner or later, every customer project hits the same afternoon. Two systems describe the same customers, one is the CRM and the other is billing, and nobody can say which "Acme" is which. The data is not broken in any one place. It is just kept by different people for different reasons. This guide walks through how to reconcile two lists like these without making things worse, and ends with a small matcher you can play with. Last reviewed in October 2026.
Look before you touch
Start by profiling, not fixing. For each field in each system, count how many are empty, how many are unique, and what the most common values look like. You are looking for surprises: phone numbers in four formats, names in capitals in one system and mixed case in the other, an ID field that is empty for a fifth of the rows, two different people sharing one email address.
Write down what you find and share it with the customer. Half of the surprises have an explanation that only they know, such as "that account was created by the migration in 2019." In SQL, a few grouped counts do most of this; the SQL Lab is a good place to practice them.
One spelling per fact
Normalizing means choosing one spelling for each kind of fact and applying it everywhere, in a new column next to the original. Typical rules:
- Names: lowercase, remove punctuation, collapse spaces, and drop company suffixes such as Inc, Corp and LLC. "Acme Corp." and "ACME CORPORATION" become "acme".
- Phones: keep digits only, and drop a leading country code if every number should have one. "(415) 555-0134" and "415.555.0134" become the same ten digits.
- Emails: lowercase and trim. Be careful about removing dots or plus signs, which some providers treat as part of the address and others do not.
- Dates and times: store one format and one time zone, and keep the original text beside it.
Keep the originals. If a rule is wrong, you want to see exactly what it changed. A Python dictionary keyed by the normalized value is often all you need to start; Dictionaries and Sets in the Forge course covers the idea.
Match by key, then by fuzz
Match in layers, strictest first. First use any shared ID. Then use normalized keys such as email, or name together with phone. Only after that, if the customer wants it, use fuzzy comparison (similar but not equal strings) and send those pairs to a person, never straight to the database.
A pair is confident only when independent facts agree. Two records with the same name and different phone numbers might be the same company with a typo, or two different companies with a common name. A rule that merges on name alone eventually joins strangers, and an unmerged duplicate is easier to fix than a wrong merge.
In the illustrative run above, the exact ID matched 612 of 1,000 records. Normalizing found 169 more, for 781 in total. A further 61 pairs were close but unsure and went to a person, and 158 records had no match, which is information too: those customers exist in one system only.
Try it: the matcher
The widget has seven invented accounts in each system. Turn on cleanup steps one by one and watch which pairs become confident and which still need a person. Then try matching on phone as well, and look at the last row: two companies that share a name once the suffix is dropped.
Invented records for practice. The 'true pairs' are known here; in a real project you learn them from the customer.
Let a person take the unsure ones
Build a small review sheet: the two records side by side, the reason they were paired, and three buttons: same, different, skip. A customer's account manager can clear sixty pairs in an afternoon, and their answers become test cases for the next run. Never let the review queue grow without an owner. It is the part that turns a script into a process.
Load with a way back
- Write results to a new table first and compare counts with the sources before anything is merged.
- Make the load idempotent: running it twice must not create duplicates. Idempotency Keys explains the idea for APIs.
- Record what was changed and by which run, so a wrong merge can be undone.
- Tell the customer which numbers will change when the merge goes live, before it does.
One habit saves more time than any technique: write the matching rules in plain words before you write them in code, and have someone who knows the customers read them. "Same company if the name matches and the phone matches" is a sentence a support lead can check in a minute. A forty-line function is not.
Where this fits
Data work is the part of the job nobody puts in the title. What a forward deployed engineer does shows where it sits in the loop, and Scoping a Vague Customer Problem shows how to find out early that you will need it.
What we know and what we do not
The steps here are common practice in data integration and are not a published method of any company. The records in the widget are invented, and the counts in the figure (612, 169, 61, 158) are illustrative: a real run depends on the data. Fuzzy matching, privacy rules about customer data and large volumes need more than this guide covers. Last reviewed in October 2026.
