Duplicate record detection: tuning a fuzzy match

Duplicate record detection sounds like a solved problem until the duplicates stop being exact. Two people describe the same thing in different words, an import runs twice, a generated draft rephrases something already on file. A unique index cannot catch any of that. On a compliance platform I work on, the risk register had no screening at all, and the same exposure recorded twice was found later, by somebody totalling a register that double-counted. This post covers what I built to catch near-duplicates: what gets compared, how the fuzzy match threshold was tuned, why matches are held instead of flagged, and how a match earns a confidence score.

Why exact matching misses the duplicates that matter

Exact duplicates are the easy kind, and for those a database constraint is the right tool. I covered that in fixing race condition duplicate rows with a unique index. The duplicates that cost money in a register are reworded ones. Somebody writes "exposure while reconciling", somebody else writes "exposed during reconciliation", and now the same risk is counted twice.

So the check has to compare meaning loosely, and every loose comparison needs a threshold. That threshold is where most of the design time went.

Compare wording and subject separately

The register has no separate title field, so the title and the description are read off the same statement in two ways: once for wording, and once for subject. A risk reworded on the way in fails the first check and passes the second, and that is the case worth catching.

Only those two can raise a candidate. Category, owner and process only corroborate a match, because half a register shares a category. If shared category could raise a candidate on its own, every new risk would match dozens of old ones and reviewers would stop reading the list.

My general rule from this: decide which fields can start a match and which can only strengthen one. Fields with low variety (category, status, department) belong in the second group.

How do you choose a fuzzy matching threshold?

Start from the cost of each mistake, then test the threshold on real pairs that should match. A false candidate costs a reviewer a glance; a missed duplicate writes the record twice and corrupts every total built on it. When a miss is the expensive mistake, set the threshold lower and add a floor, such as a minimum number of shared words, so very short texts cannot match on noise.

Here is how that played out. The word overlap threshold started at 0.6, and it missed "exposure while reconciling" against "exposed during reconciliation", which share four words of seven. That is about 0.57, just under the line. It is now 0.5 with a floor of two shared words. A sketch of the check, with the normalisation step left out:

import re

def words(text: str) -> set[str]:
    return set(re.findall(r"\w+", text.lower()))

def is_candidate(a: str, b: str, threshold: float = 0.5, min_shared: int = 2) -> bool:
    wa, wb = words(a), words(b)
    shared = wa & wb
    if len(shared) < min_shared:
        return False
    return len(shared) / len(wa | wb) >= threshold

In a real register you also want to drop stop words and fold word forms together, or "exposure" and "exposed" never meet. If you would rather do the comparison in the database, Postgres ships pg_trgm, whose similarity() function returns a score from zero to one on character trigrams, with an adjustable threshold for its % operator. The tuning advice is the same either way: collect pairs you know are duplicates and pairs you know are not, and move the number until the misses stop.

Hold matches for review, never write and flag

When a candidate is found, the incoming risk is held. It is not written to the register with a warning badge. A flagged duplicate is still a duplicate in every report until someone gets round to it.

Holding needs a little structure to be fair to the reviewer:

• The whole incoming payload is stored, so nothing the person typed is lost while it waits.
• Candidates are snapshotted as they matched. Later rule tuning does not change what a reviewer is deciding on.
• Merge, link and update require a justification and lock the row, so two reviewers cannot resolve the same hold differently.

The check also sits on the entry path, not beside it. A screening step beside the entry path gets declined on the afternoon somebody is entering the fifteenth risk of the day. If the check is optional, assume it will be skipped exactly when it is needed. The broader version of this argument is in human-in-the-loop AI: where people should stay in the loop.

Run imports through the same check

Imports go through the same screening as manual entry, because an import is usually run twice: once to see what happens, and once for real. Without the check, the second run doubles the register.

Outcomes are counted separately, so the person importing sees what happened: how many risks were opened, how many were held as possible duplicates, and how many rows were unreadable, against the total. A single "import complete" message hides the held rows, and those are the ones that need a person.

Score match confidence from attribute agreement

The same matching later fed a step that drafts risks from control gaps. Two things changed there.

First, compare like with like. The first version searched a gap's raw requirement text against risk prose, and that could never match. Three gaps came back "no match" at full confidence before the cause was clear. Now the risk draft is written first and the draft is searched, so risk prose is compared with risk prose.

Second, confidence is built from which attributes agree, instead of being a single similarity number:

• wording alone lands at 35, and the match is held for a person;
• wording and subject together reach 70, and it proceeds;
• two candidates tied at the top score zero, because a tie means the system cannot tell which record it matched.

Tenants choose which attributes count. A client who never fills in an owner would otherwise see every unowned risk matching every other one on that empty field.

The live run made the value of all this concrete. Against 475 unimplemented controls, the first pull wrote 45 drafts and held 430 as possible duplicates. The second pull created nothing and attached all 45 without a duplicate link. That is the behavior you want from a re-run: no new rows, and nothing mistaken for a copy of itself.

If you are tempted to replace word overlap with embeddings, they help with paraphrase, and the trade-offs are the ones in hybrid search: combining keyword and vector search. The threshold and review questions stay the same.

Where I'd start

1. Find out how duplicates actually arrive: rewording, re-run imports or generated drafts.
2. Decide which fields can raise a candidate and which only corroborate.
3. Pick the threshold from the cost of a miss versus a false alarm, add a minimum-overlap floor, and test both on real pairs.
4. Hold candidates with the full payload and a snapshot of what matched.
5. Put imports through the same check and report held rows separately.

More of my backend and data work is on my projects page.