Skip to content
allanninal.dev/build
Collaborate

Part 1 of 7 · Duplicate contact merger series ~6 min read

A duplicate contact merger on AWS for a few dollars a month

Every contact database has duplicates and everybody knows it. What stops anybody fixing them is not finding them — a similarity search finds too many — it is that merging is destructive, the tools present it as a bulk operation, and one wrong merge is much worse than a hundred duplicates. This post walks through a small system built entirely around that asymmetry.

man working with tools
Photo by Spencer Davis on Unsplash

Key takeaways

  • Comparing every record with every other is impossible; blocking makes it tractable.
  • Pairs are scored on evidence, not on a single similarity number.
  • Only an exact strong-identifier match with no conflicts merges automatically.
  • Every proposal shows field by field what would change and what would be lost.
  • Designed on AWS for about $3 a month at tens of thousands of contacts.

The whole system on one page

Before any code, here is the shape of what we are designing.

System: contact records blocked, scored and proposed for mergingThree boxes across the top sit outside the AWS account. On the left, Contact records: your CRM or database. In the middle, Field rules: which field wins in a merge. On the right, Whoever owns the data: the person who confirms each merge. Each connects by an arrow to the AWS account container below. Records flow down into the account. The field rules feed in which value wins. A merge to confirm goes back out. Inside the AWS account are three components in a row. On the left, the Blocker, which produces candidate pairs rather than every pair. In the middle, the Scorer, weighing evidence for and evidence against. On the right, the Proposer, showing what changes and what is lost. A note at the bottom says both originals are kept in full, so every merge can be reversed.AWS ACCOUNTContact recordsyour CRM or databaseField ruleswhich field winsWhoever owns the dataconfirms each mergeBlockercandidate pairs,not every pairScorerevidence for,evidence againstProposerwhat changes,what is lostrecordswhich value winsa merge to confirmBoth originals are kept in full, so every merge can be reversed.
Fig 1. Three things outside the account, three pieces inside it. The scorer weighing evidence against as well as for is what separates this from a similarity search.

The asymmetry

A duplicate contact costs somebody two emails and a slightly wrong count. A wrong merge takes two customers’ records — their orders, their notes, their communication history, possibly their addresses — and combines them irreversibly into one. The customer who rings to ask why they can see somebody else’s order is not having a data quality conversation.

So the thresholds are set from that: automatic merging is reserved for cases where the evidence is effectively conclusive, and everything else is a proposal that takes a person about four seconds to confirm.

What runs (the inside)

  • The blocker. Ten thousand contacts is fifty million pairs, which cannot be compared. Blocking reduces that to a few thousand plausible pairs using cheap keys. Part 2 is about doing that without missing real duplicates.
  • The scorer. Weighs specific evidence rather than computing a single similarity. Two records sharing a phone number is strong; two records sharing a surname is nearly nothing. Part 3 covers what counts and what argues against.
  • The proposer. Builds the merged record according to the field rules and shows it side by side with both originals, with anything that would be lost highlighted. Part 4.

One pair, end to end

One duplicate pair from detection to reversible merge, in five stagesA horizontal row of five boxes joined by arrows. Blocked: identified as a plausible pair. Scored: with evidence both ways. Proposed: field by field. Confirmed: by a person. Merged: and reversible. A note says the fifth box is not the end, because a merge stays undoable for as long as you set.ONE DUPLICATE PAIR, END TO ENDBlockeda plausible pairScoredevidence both waysProposedfield by fieldConfirmedby a personMergedand reversibleThe fifth box is not the end. A merge stays undoable for as long as you set.
Fig 2. The same system as one line. The reversibility in the last stage is what makes confirming a merge a low-stakes decision rather than a permanent one.
  • Management
  • Analytics
  • People

In plain words

A business has about fourteen thousand contacts accumulated from a website form, an e-commerce platform, an old spreadsheet import and years of manual entry. Blocking produces about nineteen hundred candidate pairs.

Of those, four hundred and ten share an exact email address with no conflicting data, which is conclusive: those merge automatically. About six hundred score high enough to propose — same phone number and same surname, or same address and same first name — and each is a four-second confirmation. The remaining nine hundred score too low to be worth anybody’s time and are recorded rather than shown.

The interesting ones are in the proposals. Two records with the same phone number, same surname, different first names and different email addresses are a household rather than a duplicate, and a person spots that instantly from the side-by-side view. A similarity score would have put them at 0.88 and a bulk merge tool would have combined a married couple into one customer.

Design rules that shaped every decision

  • A wrong merge is much worse than a duplicate. Every threshold follows from that.
  • Score evidence, not similarity. Two records sharing a rare phone number is not the same as sharing a common surname.
  • Weigh evidence against as well as for. Different first names on a shared address is a household.
  • Automatic merging only on a conclusive identifier with no conflicts.
  • Show what would be lost, not just what would be kept. That is where the mistakes are visible.
  • Keep both originals in full, so undo is a restore rather than a reconstruction.

Why this shape

Deduplication tools mostly present a slider: raise it to merge fewer things, lower it to merge more. That framing hides the actual decision, which is not how similar two records are but what evidence exists that they are the same entity — and those are genuinely different questions.

So this design shows the evidence rather than the score, keeps the automatic band narrow enough to be uncontroversial, and makes confirming a proposal cheap enough that a person in the loop is not a bottleneck. Six hundred four-second confirmations is forty minutes, which is an afternoon’s work once and a handful a week thereafter.

The next four posts walk through each piece: how candidate pairs are found, how a pair is scored, what a merge proposal shows, and how a merge is reversed. One diagram per post, a cost breakdown, and an engineering reference at the end.

All posts