Fuzzy Matching with Explicit Limits
A fuzzy match is a claim with a confidence, not a fact – it only becomes useful through versioned thresholds, three decision zones, and a diffable report of the gray zone.
Fuzzy Matching with Explicit Limits
SELECT similarity('Schmidt Bau GmbH', 'Schmitt Bau GmbH');
-- 0.72
SELECT similarity('Bäckerei Hoffmann GmbH & Co. KG',
'BAECKEREI HOFFMANN DANKE RG 4711');
-- 0.34
The first value looks like a match and isn’t one: two different companies, one letter apart, both of them exist. The second value looks like noise and is the right customer – just the way it arrives in a bank transfer’s payment reference: in capitals, without umlauts, with an invoice number and a sign-off in the same field. Once you have seen these two numbers side by side, you never trust a similarity function blindly again. That is exactly the right state of mind for working with fuzzy matching.
The starting point: payment references against open items#
The context is the same private data project as the month before: reconciling invoice and payment data. On one side are open items with clean debtor names from master data maintenance. On the other side are bank transactions whose payment reference is a free-text field. People and accounting systems write whatever they want there: sometimes the invoice number, sometimes the company name in one of five spellings, sometimes both, sometimes neither.
As long as an invoice number is in the text, the reconciliation is trivial – a regular expression, an exact key, done. It gets interesting with the remaining payments, where only a company name is left as an anchor. “Müller & Söhne GmbH” arrives as “MUELLER SOEHNE,” “Mueller u. Soehne Bau,” or “M&S GmbH.” Exact matching finds nothing here, and that is exactly what fuzzy matching is for. The question isn’t whether to use it but how to prevent “similar” from silently turning into “equal.”
One sentence helped me here, which I wrote on the report early on: a match is a claim with a confidence, not a fact. Everything else in this post is the technical consequence of that sentence.
Normalization first#
Before any distance function runs, the most boring step pays off the most: normalization. A large part of the apparent fuzziness isn’t fuzziness at all – it consists of systematic differences that you can clear away deterministically. Legal forms such as GmbH, AG, or KG carry almost no distinguishing power but inflate every similarity calculation. In payment transactions, umlauts traditionally appear as ae/oe/ue. Upper and lower case, double spaces, punctuation: all noise.
import re
LEGAL_FORMS = re.compile(
r"\b(gmbh|ag|kg|ug|ohg|gbr|mbh|co)\b\.?",
re.IGNORECASE,
)
UMLAUT_MAP = str.maketrans(
{"ä": "ae", "ö": "oe", "ü": "ue", "ß": "ss"}
)
def normalize_name(raw: str) -> str:
text = raw.strip().lower().translate(UMLAUT_MAP)
text = LEGAL_FORMS.sub(" ", text)
text = re.sub(r"[^a-z0-9 ]+", " ", text)
return re.sub(r"\s+", " ", text).strip()
After this step, “Müller & Söhne GmbH” and “MUELLER SOEHNE” are both mueller soehne – an exact match, without any fuzzy method at all. In my data set, normalization alone turned roughly half of the previously “fuzzy” cases into exact matches. That is why I don’t see normalization as a preliminary stage of fuzzy matching but as a stage in its own right, with its own measure of success. Every case that becomes exact here doesn’t have to be defended with a confidence later.
It is important that normalize_name runs identically on both sides – on the debtor names just as on the payment references. As soon as two slightly different normalizations are in play, you end up chasing ghosts.
Distances, trigrams, phonetics#
For the rest, the classic methods remain, and they have different strengths. Levenshtein counts edit operations and is good for typos. Jaro-Winkler weights matches at the start of a word more heavily – a good fit for company names, where the beginning is usually stable and suffixes vary at the end. Trigram similarity breaks strings into groups of three characters and compares the sets; it is insensitive to word order and available directly in SQL in Postgres through the pg_trgm extension.
There is also a method that is mentioned too rarely in German-speaking countries: Kölner Phonetik (Cologne phonetics). It is the German counterpart to Soundex and maps words to a sound code designed for German pronunciation. “Meyer,” “Maier,” and “Mayr” end up with the same code – for distance functions, these are three different strings; to the ear, it is the same name. As the sole criterion, phonetics is too coarse; as an additional signal next to a distance, it is valuable.
In Python, rapidfuzz bundles the distance methods in a fast library. I combine two measures into one score, because no single method covers all types of errors:
from rapidfuzz import fuzz, distance
def score_pair(payment_name: str, debtor_name: str) -> float:
jaro_winkler = distance.JaroWinkler.similarity(
payment_name, debtor_name
)
token_sort = fuzz.token_sort_ratio(
payment_name, debtor_name
) / 100
return 0.6 * jaro_winkler + 0.4 * token_sort
token_sort_ratio sorts the words before comparing them and thus catches swapped word order (“bau schmidt” versus “schmidt bau”). The weights 0.6 and 0.4 aren’t a law of nature – they are a parameter that I calibrated on a hand-labeled slice of the data. That, too, is part of the method’s honesty: the score is a constructed quantity, not the truth.
Blocking against the quadratic trap#
If you compare every payment reference against every open item, you pay O(n²). With a few hundred records, that doesn’t matter; with tens of thousands of payments against thousands of debtors, it does. The standard answer is blocking: a cheap, coarse preselection that shrinks the set of candidates before the expensive comparisons run.
This is where pg_trgm plays its second strength. Trigram similarity is not only a function but also an indexable operator. With a GIN index on the normalized name, Postgres returns the most similar candidates without touching the whole table:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_open_invoice_debtor_trgm
ON open_invoice
USING gin (debtor_name_normalized gin_trgm_ops);
SET pg_trgm.similarity_threshold = 0.45;
SELECT i.invoice_no,
i.debtor_name_normalized,
similarity(i.debtor_name_normalized, :payment_name)
AS trigram_score
FROM open_invoice AS i
WHERE i.debtor_name_normalized % :payment_name
ORDER BY trigram_score DESC
LIMIT 10;
The % operator checks against the configured threshold and uses the index while doing so. The 0.45 is deliberately set low: at this stage, a missed candidate is worse than twenty superfluous ones, because whatever blocking discards, the detailed scoring never sees again. The actual decision is made later – with score_pair over the ten candidates, not in SQL.
This two-way split has proven itself: Postgres does the preselection coarsely and quickly, Python does the scoring precisely and traceably. Each stage has its own threshold with its own justification.
Three zones instead of one threshold#
Now to the actual point. Most fuzzy matching examples you find end with a single threshold: a score above X is a match, below it is no match. That is the point where a useful tool turns into a dangerous one, because a single threshold claims a precision of separation that the data doesn’t support. There is always a range in which the method simply doesn’t know whether two names belong together – and this not-knowing belongs in the architecture, not under the rug.
That is why my reconciliation has three zones instead of one threshold:
- Above the acceptance threshold, the assignment is made automatically, and it carries the score as metadata.
- Between the thresholds, the pair ends up on a review list – a human decides.
- Below the rejection threshold, nothing happens; the payment stays open.
THRESHOLDS = {
"version": "2024-09-03",
"accept": 0.93,
"reject": 0.72,
}
def classify(score: float) -> str:
if score >= THRESHOLDS["accept"]:
return "accept"
if score < THRESHOLDS["reject"]:
return "reject"
return "review"
The thresholds are versioned in the repository, not scattered as a constant somewhere in the code. Every change to them is a commit with a justification. That sounds bureaucratic, but it helped me after just two weeks: when I raised the acceptance threshold from 0.90 to 0.93 because the Schmidt/Schmitt pair from the introduction had slipped through, I could see exactly in the history which assignments had been made under which version of the rules.
flowchart LR
P[Verwendungszweck] --> N[normalize_name]
D[Debitorenname] --> N
N --> B[Blocking<br/>pg_trgm, Schwelle 0.45]
B --> S[Feinbewertung<br/>score_pair]
S --> Z{Score}
Z -->|ab 0.93| A[automatisch<br/>zuordnen]
Z -->|0.72 bis 0.93| R[Durchsicht<br/>durch Menschen]
Z -->|unter 0.72| X[offen lassen]
R --> AL[Alias-Regel<br/>versioniert]
AL --> NThe feedback loop at the bottom right of the diagram is not a detail. Every manual decision from the review is recorded as an alias rule – “M&S” is “Müller & Söhne” – and flows back into normalization as data. On the next run, the same case is an exact match. As a result, the gray zone shrinks over time, and it does so through documented decisions rather than bolder thresholds.
The gray zone report#
For me, the review list isn’t a dashboard but a Markdown file that every run generates deterministically: same input, same thresholds, same report. Sorted by score, with both names in their original and normalized form, the trigram value from blocking, and the combined score next to them.
The reason for Markdown is diffability. You can compare two runs with diff, and the difference tells a story: which cases disappeared from the gray zone thanks to new alias rules? Which new doubtful cases came in with the last bank statement? Did the threshold change make the zone bigger or smaller? A dashboard shows the latest state; a diffable report shows the movement. For a reconciliation system whose core question is “what changed and why,” that carries more weight than any UI.
Side Effect: the report is also the audit artifact. If I want to know in three months why a payment was assigned the way it was, the score, the version of the rules, and, where applicable, the manual decision are in the history.
Asymmetric costs#
That leaves the question of where the thresholds should be – and you don’t answer it with statistics alone but with the costs of the two types of error. Here, they are clearly asymmetric. An unassigned payment is a known problem: it shows up in the list of open cases, someone looks at it, assigns it, done. A wrongly assigned payment is an unknown problem: one customer’s open item counts as paid, and the other may get a dunning notice for a paid invoice. The error only gets noticed when someone complains – and then troubleshooting is expensive, because nobody knows anymore which run with which version of the rules produced the assignment.
That is why the acceptance threshold is deliberately high and the gray zone is wider than the pure hit rate would suggest. I would rather accept twenty manual reviews per month than one silent wrong assignment per quarter. That isn’t a technical decision but a business one – but it only becomes possible because the architecture makes the adjusting screw explicit. A single, hard-coded threshold doesn’t even offer this trade-off.
What I take away from this#
Fuzzy matching has a bad reputation among people who were once bitten by a wrong assignment, and too good a reputation among people who have only seen the demo. Both miss the point. The methods – Levenshtein, Jaro-Winkler, trigrams, Kölner Phonetik – have been understood for decades and reliably do what they promise: they measure similarity. What they can’t do is establish identity. The error almost never lies in the method but in the system boundary that silently turns a similarity value into a fact.
The whole thing becomes useful at exactly the moment the limits become explicit: normalization as a stage of its own, so that what is deterministic stays deterministic. Blocking with a deliberately low threshold, so that the expensive scoring stays affordable. Three zones with versioned thresholds, so that the method’s not-knowing has a defined place. A diffable report, so that the gray zone visibly shrinks instead of growing unnoticed. And thresholds that are guided by the asymmetric costs of errors rather than by the hit rate.
None of this is new, and that is exactly what I like about it. It is the same attitude that already paid off when modeling the invoice data the month before: don’t abstract uncertainty away; give it a name, a value, and a place in the data model. A match is a claim with a confidence. Systems that take this seriously can be trusted with automation.
Further reading#
- RapidFuzz on GitHub: https://github.com/rapidfuzz/RapidFuzz
- Postgres 16, pg_trgm extension: https://www.postgresql.org/docs/16/pgtrgm.html
- Kölner Phonetik (Wikipedia): https://de.wikipedia.org/wiki/K%C3%B6lner_Phonetik
Revised and translated from German with the help of AI.