Fuzzy Match
Fuzzy Match links rows from two tables when text columns are similar, not identical. Each pair of values is scored from 0 to 100, ignoring case and outer spaces. Rows link when every pair reaches the Threshold. Matched has the linked rows and a _match_score column. Left and Right have the rows that found no match. Blocking Keys compare only rows that share an exact value, which is much faster on large tables. Keep decides whether each Left row gets its best match or all of them.
Match vendor names
The Left input has three vendors and the Right input has three invoices. Names are compared with Threshold 60, only within the same city.
Fuzzy Match interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| company | city |
|---|---|
| Harbor Foods | Lima |
- The preview opens on OUTPUT LEFT with one row: Harbor Foods has no invoice similar enough.
- Select OUTPUT MATCHED above the preview, or the middle output port on the block. It has two pairs: Acme Ltd with ACME Limited, scored about 93, and Mesa Supply with Mesa Suply, about 98.
- Select OUTPUT RIGHT. Tidewater Paper is the invoice that matched nothing.
- Change Threshold to
95and select OUTPUT MATCHED. Only Mesa Supply remains, and Acme Ltd moves to OUTPUT LEFT.
When to use
Use Fuzzy Match when the same thing is written differently in two tables, such as ACME Limited and Acme Ltd, and there's no shared ID. Add a blocking key such as city or country to avoid comparing every row with every other row. For exact keys, use Join. To search one table for a typed term, use Fuzzy Search.
For exact key matches, use Join.
To rank one table against a typed term, use Fuzzy Search.
Inputs and outputs
Inputs
2 ports- LeftRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
- RightRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
3 ports- Left
- Matched
- Right
Configure the block
- Match Fields
Text column pairs to compare. A row links only when every pair clears the threshold.
- Blocking Keys
Optional column pairs that must be exactly equal for two rows to be compared at all. Leave empty to compare every Left row against every Right row.
- Similarity
Jaro-Winkler favors matching prefixes and suits names. Edit distance counts insert/delete/substitute/transpose edits and suits typos. Character overlap ignores order and suits reordered tokens.
Default:
Jaro-WinklerOptions: Jaro-Winkler, Edit distance, Character overlap.
- Threshold
Minimum similarity (0-100) a pair must reach to count as a match. Higher is stricter.
Default:
85- Keep
Best match keeps only the closest Right row for each Left row. All matches keeps every Right row above the threshold.
Default:
Best matchOptions: Best match, All matches.
- Columns
Shape the Matched output: keep, drop, reorder, rename or cast each column, including `_match_score`. Use the output names: the right blocking key is dropped, and shared names end in _left or _right. Leave empty to keep every column and the score. Left and Right don't change.
Fuzzy Match FAQs
- What does _match_score mean?
- It's the lowest Match Fields similarity for the pair, from 0 to 100. A row links only when every Match Fields pair reaches the Threshold.
- What do Blocking Keys do?
- They compare only rows that share an exact value in the chosen columns, which is much faster on large tables. Leave them empty to compare every Left row with every Right row.
- What is the difference between Best match and All matches?
- Best match keeps only the closest Right row for each Left row. All matches keeps every Right row at or above the Threshold.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.