FuzzyMatch
Use the FuzzyMatch gem to identify non-identical duplicates in your data.
Input and Output
| Table | Description |
|---|---|
| in0 | Includes the table on which duplicates will be checked. Note: FuzzyMatch only allows one input. |
| out | Generates one record per fuzzy match. |
Parameters
Configuration
| Parameter | Description |
|---|---|
| Merge/Purge Mode | Records are either compared from a single source (Purge) or across multiple sources (Merge). Merge mode assumes that multiple sources exist in the same table in0. |
| Source ID Field | Unique identifier for each source when using Merge mode. This is necessary because the different sources exist in the same table in0. |
| Record ID Field | Unique identifier for each record. |
| Match threshold percentage | If the match score is less than the threshold, the record does not qualify as a match. |
| Include similarity score | Checkbox to enable for an additional output column that includes the similarity score. |
Match Fields
| Parameter | Description |
|---|---|
| Field name | Name of the column that you want to check for duplicates. |
| Match function | The method that generates the similarity score. |
Example
One common use case for the FuzzyMatch gem is to match similarly spelled names. Here's a table with two entries for Alex Taylor, whose phone number was updated.
| id | phone | first_name | last_name | date_added | |
|---|---|---|---|---|---|
| 1 | alex.t@example.com | 123-456-7890 | Alex | Taylor | 2023-01-01 |
| 2 | alex.t@example.com | 123-456-9542 | Alex | Ttaylor | 2023-07-01 |
| 3 | sam.p@example.com | 987-654-3210 | Sam | Patel | 2024-03-15 |
| 4 | casey.l@example.com | 555-111-2222 | Casey | Lee | 2024-05-01 |
You can use the FuzzyMatch gem to find the closely spelled name. In the gem configuration:
- Set the Merge/Purge Mode to Purge mode.
- For the Record ID, use the id column.
- Keep the threshold at
80percent. - Enable the Include similarity score column checkbox.
- In the Match Fields tab, add a match field for the last_name column.
- Set the Match Function to Name.
- Save and run the gem.
Result
The output includes the Record IDs of the records with fuzzy matches above the defined threshold.
| id | id2 | similarityScore |
|---|---|---|
| 1 | 2 | 0.9111111111111111 |
info
Depending on your SQL provider, you might see different similarity scores based on the algorithm that runs under the hood.
tip
To view the names per record, join the FuzzyMatch output with the original dataset.