Fuzzy Matching in ETL Pipelines: Algorithms & No-Code Implementation | DataFuseAI

What Is Fuzzy Matching?

Fuzzy matching is a technique for identifying strings that are similar but not identical, by computing a similarity score based on how many character-level edits separate the two values. Unlike exact matching, which returns results only when two values are character-for-character identical, fuzzy matching assigns each comparison a score — typically 0 to 100 — and treats any pair above a configured threshold as a match.

The "fuzzy" in the name refers to tolerance for variation, not imprecision in the method. The underlying algorithms are deterministic: given two strings and an algorithm, the score is always the same. What varies is the threshold. That is a domain judgment, not a technical default.

Where exact matching asks "are these the same?", fuzzy matching asks "how similar are these?" That difference matters when data comes from human input or multiple systems, where spelling inconsistency is normal rather than exceptional.

Fuzzy Matching vs. Exact Matching

Both methods compare values to find records that belong together. The difference is what qualifies as a match.

Exact matching treats any deviation as a non-match. One transposed letter, one missing character, one variation in how a name was recorded — the record doesn't surface. It produces zero false positives, which makes it the correct choice for clean, controlled fields: system-generated IDs, SKUs, product codes, account numbers.

Fuzzy matching operates at a configurable tolerance. It computes a similarity score and returns any pair that exceeds a threshold. That makes it appropriate wherever human input or multi-system data introduces variation that doesn't signal a genuinely different entity.

Use exact matching for identifiers, controlled-vocabulary fields, and anything system-generated. Use fuzzy matching wherever string values come from human input or multiple source systems, and where a single character difference doesn't mean a categorically different entity. Some no-code ETL platforms expose fuzzy matching as a configurable pipeline transformation — making it accessible without custom code.

How Fuzzy Matching Works: Edit Distance and Similarity Score

The most widely used fuzzy matching algorithms measure similarity through edit distance: how many single-character operations does it take to transform one string into the other? Fewer operations means more similar strings. A distance of 0 means the strings are identical.

Levenshtein distance counts three types of operations: insertion, deletion, and substitution. Here is how the calculation works step by step.

Take two strings: "app" (length 3) and "apple" (length 5). The longer string's length — 5 — is the denominator in the similarity score formula.

A matrix is constructed where rows represent characters of the first string and columns represent characters of the second. Each cell holds the minimum edits needed to match the string prefix up to that point.

Green diagonal cells = character matches (cost 0). Navy cell (bottom-right) = final edit distance: 2.

An edit distance of 2 means "app" needs two insertions to become "apple":

app  →  appl  →  apple
          +l        +e

The edit distance becomes a percentage by normalizing against the max length:

score = (1 - distance / max_length) × 100
score = (1 - 2 / 5) × 100
score = 60.0%

At a threshold of ≥60%, this pair passes. Set the threshold to ≥70%, and it fails — same algorithm, same strings, a different outcome determined entirely by the threshold value. That sensitivity explains why threshold calibration is a domain decision, not a technical default.

One reason edit-distance algorithms perform well in practice: a 1964 study by Fred Damerau^[1] found that more than 80% of human misspellings result from a single error — one insertion, deletion, substitution, or transposition. Most real spelling variation falls within an edit distance of 1 or 2, which is why a threshold in the 80–90% range captures a large share of genuine near-matches without requiring a setting loose enough to admit excessive noise.

Common Fuzzy Matching Algorithms

Most fuzzy matching implementations use edit distance algorithms — counting how many character-level changes separate two strings. DataFuseAI uses Levenshtein distance, the most widely deployed approach, which counts the minimum insertions, deletions, and substitutions needed to transform one string into another. The five algorithm families below differ in what types of variation they handle, which determines when each is the right choice.

Levenshtein distance is the foundational method, defined by Vladimir Levenshtein in 1965 and published in English translation the following year. Given any two strings, it returns the minimum number of single-character insertions, deletions, and substitutions needed to make them identical. It handles the most common spelling variations: missed letters, extra letters, wrong letters. That reach — from search engines to data pipelines — reflects how well the algorithm matches real error patterns.

Damerau-Levenshtein extends the standard algorithm by treating a swap of two adjacent characters as a single operation rather than two. Under standard Levenshtein, transforming "Micheal" into "Michael" — a transposition of e and a — costs 2 operations. Under Damerau-Levenshtein, it costs 1. Damerau's research found that transpositions account for a meaningful share of typing errors, making this variant better-calibrated for keyboard-input data.

Jaro-Winkler gives higher weight to matching characters near the beginning of a string, making it suited for short name fields where the first few characters carry the strongest signal. "John Smith" and "Jhon Smith" score higher under Jaro-Winkler than under Levenshtein because the early-position match is weighted more heavily.

Soundex and Metaphone are phonetic algorithms. Rather than comparing character sequences, they encode words by how they sound — so "Smith" and "Smyth" produce the same code. These are useful when data originates from spoken input or cross-language sources where the same name has multiple valid spellings. Metaphone handles English pronunciation rules with more precision than the older Soundex scheme.

N-gram similarity breaks strings into overlapping character sequences — "apple" as bigrams becomes {"ap", "pp", "pl", "le"} — and measures overlap between two strings' n-gram sets. Microsoft Power Query uses Jaccard similarity for its fuzzy merge operation, dividing the size of the intersection by the size of the union. N-gram methods are computationally efficient at scale and work well for partial substring matching tasks.

The right algorithm depends on the variation in your data. For a pipeline matching employee names from two HR systems, Levenshtein or Damerau-Levenshtein is the natural starting point. For data that originates from spoken-to-typed entry with multilingual variation, phonetic algorithms add signal that edit distance alone misses.

Where Fuzzy Matching Solves Real ETL Problems

Two PostgreSQL tables. The same employee appears in both under slightly different spellings. A standard JOIN returns zero results.

That is the scenario the demo pipeline addresses. employee_table_1 has 101 rows and stores the employee name as full_name. employee_table_2 has 49 rows and stores the same field as r_name. The same person appears as "Anika Banaarjee" in one table and "Anika Banerjee" in the other — same employer, same address (Aspen St 2687), same city (Ahmedabad), same email. The column name difference and the two-character spelling variation make an exact join return nothing.

Enlarge

Table 1: full_name = "Anika Banaarjee" — the double 'a' typo

Enlarge

Table 2: r_name = "Anika Banerjee" — correct spelling, different column name

Running a Fuzzy Filter at 85% threshold across the unioned dataset of 150 rows surfaces both records. The Fuzzy Join at the same threshold produces 16 cross-table matches from the full 101 × 49 row combination.

This pattern recurs whenever data from two or more systems needs to be linked without a guaranteed shared identifier. In customer operations, the same individual may appear across a CRM and an ERP with name variations introduced by different data entry staff — "Robert Smith" in one system, "Rob Smith" or "R. Smith" in another. A standard join finds nothing. A fuzzy join on the name column, combined with an exact match on a high-signal field like email where one is available, resolves the mismatch.

After merging two company databases, near-duplicate records that differ only in how names were formatted will inflate counts and corrupt analytics unless identified and collapsed. Post-merge deduplication with fuzzy matching finds those records systematically rather than leaving the cleanup to manual review.

Scale makes this problem structural. A study of 398,939 patient records with confirmed duplicates, published in the Journal of AHIMA,^[2] found that misspellings accounted for 53.14% of first-name discrepancies and 33.62% of last-name discrepancies across the dataset. Exact matching doesn't surface that problem — it obscures it.

At a larger scale, researchers at the Institute for Clinical Evaluative Sciences (ICES) in Ontario tested fuzzy matching against a dataset of 12 million individual health records as an alternative to probabilistic record linkage, which required extensive manual review of ambiguous cases.^[3] The fuzzy matching approach eliminated manual gray-area intervention while maintaining comparable linkage accuracy, with significant improvements to data timeliness and elimination of clerical review costs and human error introduced during manual disambiguation.

The pipeline walkthrough below covers how to implement both Fuzzy Filter and Fuzzy Join in DataFuseAI — the same transformations that sit within the broader no-code ETL platform architecture covered elsewhere.

Fuzzy Filter and Fuzzy Join in DataFuseAI: A Pipeline Walkthrough

Fuzzy Filter searches a single unified dataset for rows that approximate a target string. Fuzzy Join links two separate source tables by matching records across them based on string similarity between specified columns.

They solve different problems. Fuzzy Filter is a row selection operation: given a pool of records and a target value, return rows that are close enough. Fuzzy Join is a data integration operation: given two tables and a column pair, return matched records from each table based on string similarity. The walkthrough below uses both, built against the same two employee tables.

Enlarge

The complete Fuzzy Demo Pipeline — two sources, two branches, two sinks. Completed in 38 seconds on Databricks compute.

Add an RDBMS source node for employee_1. Connect it to the PostgreSQL profile, select the tch_pg_results database, public schema, and choose employee_table_1 (101 rows). Schema: id (integer), full_name (text), address, city, company, email, phone, age, score, category_id. All columns selected.

Enlarge

Source node: employee_1 — name column is full_name

Add a second RDBMS source node for employee_2. Same connection, same database and schema, this time selecting employee_table_2 (49 rows). Key difference: the name column is r_name, not full_name; the address column is r_address, not address.

Enlarge

Source node: employee_2 — name column is r_name, not full_name

Connect both source nodes to a Union transformation node. In the configuration panel, set Union By to Position — this maps r_name from employee_table_2 positionally to full_name in the output schema, aligning the two name columns without requiring them to share a column name. Enable "Remove duplicates (Union / Union All)." The Union output is 150 rows: 101 from table_1, 49 from table_2.

Enlarge

Union config: Position-based merge aligns r_name from table_2 to the full_name position in the output schema

Connect the Union output to a Fuzzy Filter node. The configuration panel lists available string fields: full_name, address, city, company, email, phone. Select full_name.

Set Fuzzy Text to "Anika Banaarjee" — the misspelled name from employee_table_1. Set the operator to >=, score threshold to 85, and Match Scoring Mode to Individual Score. Save and run.

Enlarge

Fuzzy Filter config: searching full_name for "Anika Banaarjee" at 85% Individual Score threshold

Output: 2 rows. Row 1 is id 101 ("Anika Banaarjee") from employee_table_1. Row 2 is id 2 ("Anika Banerjee") from employee_table_2. Both share the same address, city, company, and email — confirming the same person across both tables despite the name difference.

Enlarge

Fuzzy Filter output: 2 rows from 150 — both Anika variants, identical address/company/email confirm same person

The similarity score for this pair — calculated using score = (1 - distance / max_length) × 100, with a distance of 2 and a max_length of 15 — is approximately 86.7%. The pair passes the 85% threshold by 1.7 percentage points.

For the parallel branch, connect both source nodes directly to a Fuzzy Join node — no Union step needed, since Fuzzy Join operates across two tables directly.

In the Sources Structure tab, employee_table_1 appears on the left and employee_table_2 on the right. Set the join connector to Fuzzy Match. Select the output columns from each side. The output schema contains full_name, address, city, company, email, phone, and age from table_1, plus id_2, r_name, and r_address from table_2.

Enlarge

Fuzzy Join — Sources Structure tab: Fuzzy Match connector, merged output schema visible on right

Open the Fuzzy Match tab. Set Match Scoring Mode to Individual Score. Add the rule: full_name >= r_name, score threshold 85.

Enlarge

Fuzzy Join — Fuzzy Match tab: full_name >= r_name at 85%, Individual Score mode

The Fuzzy Join returns 16 matched pairs.

Row 16 in the output — highlighted in red — is the Anika match: id 101 ("Anika Banaarjee") linked to id_2 2 ("Anika Banerjee"), same address, same city, same company. Cross-table fuzzy match confirmed.

Row 14 is worth looking at separately: id 99 ("Priya Jain") matched with id_2 12 ("Riya Jain"). "Priya" and "Riya" differ by two characters — a substitution and a deletion — which puts the pair above 85%. Whether this represents the same person or two different employees with similar names is a domain question the algorithm cannot answer. The threshold is where that judgment gets encoded, which the next section covers.

Enlarge

Fuzzy Join output: 16 matches. Row 16 (red): Anika cross-table match confirmed. Row 14 (orange): Priya/Riya — the threshold sensitivity example.

The full pipeline — two sources, Union, Fuzzy Filter, Fuzzy Join, two sinks — completed in 38 seconds running on Databricks compute.

Setting Thresholds and Choosing a Scoring Mode

There is no correct default threshold. A threshold is a data quality decision: how much variation are you willing to accept as a match, and what does a false match cost in this context? In a customer deduplication pipeline, a false merge is recoverable. In a healthcare record linkage pipeline, a false merge can associate the wrong medication history with the wrong patient.

Threshold guidance is usually presented as a number. Starting points are useful, but they don't substitute for testing against actual data and deciding what a false positive costs in a specific domain.

"There is no correct default threshold. A threshold is a data quality decision — not a technical setting to accept as-is."

How the threshold affects the Anika match

The demo pair illustrates how narrow the margin can be. "Anika Banaarjee" (15 characters) vs "Anika Banerjee" (14 characters) has a Levenshtein distance of 2, producing a similarity score of approximately 86.7%. That's 1.7 percentage points above the 85% threshold. Set it to 87%, and this match disappears. Set it to 85%, and it surfaces.

When threshold miscalibration has consequences

A peer-reviewed study of financial sanctions screening published in Frontiers in Artificial Intelligence (2024)^[4] found that Levenshtein-based fuzzy matching produced false positive rates exceeding 90% of all alerts at operational thresholds. That figure is specific to name-matching against large watchlists, where common name combinations generate a high volume of incidental character-similarity matches — not a general property of fuzzy matching in ETL pipelines. It does, however, document what miscalibrated threshold logic looks like at scale.

The Priya/Riya pair from the demo pipeline is a smaller-scale version. "Priya Jain" and "Riya Jain" match at 85% because their names are character-similar. Whether they're the same person is a domain question the algorithm cannot resolve.

Individual Score vs. Combined Score

When configuring fuzzy matching across multiple column pairs — name and address, for example — the scoring mode determines how the threshold is applied.

Individual Score evaluates each column pair independently against its own threshold. A match is returned only when every configured pair meets its individual threshold. No column's strong score compensates for another column's weak score. This mode is conservative: harder to pass, but when a pair passes, every column contributed.

Combined Score aggregates all configured column pair scores into a single value before applying the threshold. A high similarity on one field can offset a lower similarity on another. This is more permissive and useful when columns have unequal reliability — if the name field is clean but the address field has significant formatting variation across sources, Combined Score lets the cleaner signal carry more weight.

The scoring mode choice reflects how much trust you place in each individual field as a matching signal. For building validation checks around fuzzy matching output within a broader pipeline quality framework, data quality validation in ETL pipelines covers the relevant checks in depth.

Why Fuzzy Matching Rarely Makes Sense on Numeric Data

Fuzzy matching quantifies character-level similarity. Applied to a string field like a name or address, character similarity is a reasonable proxy for "same entity." Applied to a numeric identifier, it is not.

Consider two IDs: 12 and 123. Under Levenshtein distance, these differ by one character — an insertion of '3' — with a max_length of 3. That produces a similarity score of approximately 66.7%. Mathematically valid. Operationally meaningless. ID 12 and ID 123 are not the same record, regardless of how similar their digit sequences appear.

This constraint is formalized in enterprise platforms. Most enterprise explicitly use and state that fuzzy matching precision applies only to string-type columns; integer, double, and datetime columns are locked to exact match and cannot be configured for fuzzy precision.

The practical rule: use exact matching for any field where identity is the point — IDs, foreign keys, product codes, account numbers. Use fuzzy matching for fields where human input introduces natural variation — names, addresses, company names, free-text descriptions. Applying the wrong method to the wrong category produces results that are technically computable but incorrect for the task.

Fuzzy Matching in ETL Pipelines: Common Questions