Every master-data team has the same backlog: accounts that are the same company typed twice. “Hotel Royal SL”, “HOTEL ROYAL S.L.” and “Royal Hotel (Madrid)” with three different ids, three different owners and three different payment terms. The MDM tool catches the easy ones. What is left is the pile nobody wants: thousands of pairs that might be duplicates, each one needing a human to look at two records and decide.
This is how I turned that pile into a queue a person can actually work through, with Python doing the search and a local language model doing the judgement.
The shape of the problem
Duplicate detection (record linkage, entity resolution — pick your name) has two halves that need very different tools:
- Finding candidate pairs. With n records there are n²/2 possible pairs. You cannot look at all of them, and you do not need to: most pairs are obviously different. This half is about recall — do not let a real duplicate escape.
- Deciding on each candidate. Given two records that look similar, are they the same entity? This half is about precision and about explaining the call, because a merge in MDM is expensive to undo.
Rules are excellent at the first half and mediocre at the second. Language models are the opposite. So the pipeline splits along that line.
Half one: rules generate the candidates
Plain Python, no model involved:
- Normalise every field once: lowercase, strip accents, collapse whitespace, remove legal forms (
S.L.,SLU,GmbH,Ltd), expand the usual abbreviations (hotel/htl,avenida/avda). - Block so we only compare records that could plausibly match: same country and postcode prefix, or same tax id root, or same first token of the normalised name. Each block is small; the union of blocks is the candidate set.
- Score each candidate with cheap similarity (token overlap on the name, string distance on the address, exact match on tax id, phone, email domain). Pairs above a high threshold are auto-flagged as duplicates; pairs below a low threshold are dropped. Everything in between — the grey zone — goes to half two.
This step alone removes more than 95 % of the pairs. It is also fully deterministic, which matters when someone asks “why did this pair come up?”.
Half two: a local LLM decides, with a reason
For each grey-zone pair, the model gets both records side by side and a strict instruction: answer with a JSON object, nothing else.
{
"same_entity": true,
"confidence": "high",
"reason": "Same tax id root and postcode; name differs only by legal form and word order."
}Things that made it work:
- Structured output, validated. The reply is parsed as JSON and checked against a schema. If it does not parse, the pair is retried once and then routed to a human. The model is never allowed to “explain in prose”.
- A short, explicit prompt. Small models follow a ten-line system prompt better than a page of guidance. The rules of the game (what counts as the same company, what a subsidiary is, what to do with a missing field) are written as bullet points.
- The reason is the product. The
reasonfield is what the reviewer reads first. A wrong decision with a clear reason is fixed in seconds; a right decision without one still gets checked. - Batches, not chat. Pairs go through in batches with a fixed prompt, so results are reproducible and the throughput is predictable.
Why a local model
The records are customer data. Sending them to a hosted API meant a data-processing review, a contract and a budget line — for a pipeline that runs a few times a month. A local model via Ollama (a small qwen2.5) sidestepped all of it: the data never left the laptop, and the cost was zero.
The constraint was hardware: a work laptop with 32 GB of RAM and no GPU. That rules out big models, but a 3B-class model on CPU answers a pair in a few seconds, and the grey zone after blocking is small enough that an overnight run covers it. The rules are what make a small model enough: the model never sees the easy pairs, only the ones where judgement is needed.
The review queue
Nothing merges automatically. The output is a queue sorted by confidence and impact (how many transactions hang off the accounts), with the two records, the score, the model’s decision and its reason. A steward accepts or rejects; the merge itself happens in the MDM tool, where it belongs. Rejections are kept — they are the best test set for the next version of the prompt and the thresholds.
What goes wrong
- Subsidiaries and branches. “Hotel Royal Madrid” and “Hotel Royal Barcelona” share owner, tax id root and name. Whether they are one account or two is a business rule, not a similarity question. It is written into the prompt and into the blocking rules — and it is still the main source of human overrides.
- Homonyms. Two genuinely different companies with the same common name in different cities. Blocking by geography catches most; the model’s reason catches the rest (“same name, different tax ids and cities”).
- Missing fields. A record with no tax id and no address cannot be matched with confidence. The model is told to say
confidence: lowrather than guess, and those go to the top of the review queue.
What I would tell someone starting this
- Spend your first week on normalisation and blocking. That is where recall lives, and no model fixes bad candidates.
- Force structured output from day one and validate it. Free text from a model is not data.
- Keep a human in the loop and keep their decisions. The review log is your evaluation set.
- A small local model plus good rules beats a big remote model plus no rules — and it is the version you are allowed to run on customer data.