Skip to content
allanninal.dev/build
Collaborate

Part 2 of 7 · Duplicate contact merger series ~5 min read

How candidate pairs get found

The first problem in deduplication is arithmetic. Fourteen thousand contacts produce just under a hundred million pairs, and no amount of cheap comparison makes that tractable on a schedule. Blocking is how everybody solves it, and the whole skill is in not discarding the pairs that mattered.

Key takeaways

  • Fourteen thousand contacts is a hundred million pairs. Blocking is not optional.
  • Four keys: normalised email, normalised phone, postcode plus surname, and a name fingerprint.
  • Use several keys and take the union. One key always misses a category.
  • A key that produces huge blocks is worse than useless; cap block size and report it.
  • Blocking misses genuine duplicates, and the report says which categories it cannot see.

Four keys

KeyCatchesMisses
Normalised emailThe same address written differentlyAnyone with two addresses
Normalised phoneThe same number with different formattingAnyone with two numbers
Postcode + surnameHousehold and repeat customersHouse moves
Name fingerprintTypos and transpositionsName changes

Each key catches a category and misses a different one, which is why the answer is to run all four and take the union of the pairs they produce. Any single key, however clever, has a category it structurally cannot see.

How blocking runs

How candidate duplicate pairs are found by blockingA vertical chain of five steps entered by a box labelled All contacts, fourteen thousand of them. Step one computes four keys per record. Step two groups by each key, forming blocks that usually contain two to four records. Step three asks whether a block is too large, over thirty records; if so it exits to Drop it and report, because it is a useless key value. Step four forms pairs within blocks and takes the union across keys. Step five yields about nineteen hundred candidate pairs from ninety-eight million possible ones. A note says a block of four hundred records sharing a key is a bad key value rather than eighty thousand real candidates.AWS ACCOUNTAll contacts14,000Compute four keysper recordGroup by each keyrecords sharing a keyBlocksusually 2-4 recordsBlock too large?over 30 recordsDrop it, and reporta useless key valueyesPairs within blocksunion across keys~1,900 candidate pairsfrom 98 millionA block of 400 records sharing a key is a bad key value, not 80,000 real candidates.
Fig 1. How blocking reduces the problem. The oversized-block check is what stops one degenerate key value reintroducing the quadratic explosion.
  • Database
  • App integration
  • Machine learning
  • Security & identity
  • Management
  • Analytics
  • People

Oversized blocks

Every blocking scheme produces degenerate values. A normalised phone key will find four hundred records sharing the business’s own phone number, because somebody entered it as a placeholder. A postcode key will find every record at a large office building.

A block of four hundred records generates eighty thousand pairs on its own, which reintroduces exactly the problem blocking solved. So blocks above a size cap are dropped and reported, and the report is genuinely useful: a key value shared by four hundred records is almost always a data quality problem worth fixing at source.

Normalisation matters more than the key

A phone key that does not strip formatting finds nothing: 07700 900123 and +447700900123 are the same number and different strings. The same is true of email with dots and plus-addressing, and of postcodes with and without a space.

Getting normalisation right is most of what makes blocking work, and it is worth being conservative: normalising too aggressively creates false blocks, but under-normalising silently misses whole categories with no symptom at all.

What blocking misses

The duplicate that blocking cannot findA horizontal row of five boxes. Two addresses: no shared email. Two numbers: no shared phone. Moved house: no shared postcode. Changed name: no shared fingerprint. Invisible: and the report says so. A note says four keys and all four differ, and naming the limitation is better than implying coverage.THE DUPLICATE BLOCKING CANNOT SEETwo addressesno shared emailTwo numbersno shared phoneMoved houseno shared postcodeChanged nameno shared fingerprintInvisibleand the report says soFour keys and all four differ. Naming the limitation is better than implying coverage.
Fig 2. The category blocking structurally cannot find. Stating it is what stops a deduplication report being read as a claim that the database is now clean.
  • App integration
  • Management
  • Front-end & mobile
  • People

A person who changed their name, moved house, and uses a different email and phone from the one on their old record is genuinely undetectable by any blocking scheme, and pretending otherwise is how a deduplication project gets declared complete.

The honest handling is a line in the report: “this finds duplicates sharing at least one of email, phone, postcode with surname, or a name fingerprint. Records sharing none of those are not detectable and are not counted anywhere in this report.” That is one sentence and it keeps everybody’s expectations calibrated.

Next: scoring a pair.

All posts