Data Deduplication and Fuzzy Matching Patterns

Practical patterns for handling duplicate records across CSV exports, Airtable, and BigQuery. Covers exact matching, fuzzy matching with rapidfuzz, and household deduplication for large datasets.

---

Quick Reference: When to Use What

Data Size	Exact Match	Fuzzy Match	Tool
< 500 records	Manual review	Airtable Script	Airtable Scripting Extension
500-10,000 records	Formula-based	Python + rapidfuzz	Local Python script
10,000+ records	Database JOIN	Blocking + fuzzy	BigQuery or specialized tools

---

Core Concept: Exact vs. Fuzzy Matching

Exact Matching:

• Case-insensitive comparison
• Trimmed whitespace
• Normalized domains (e.g., Gmail dot rules)
• Good for: emails, IDs, standardized fields

Fuzzy Matching:

• String similarity algorithms (Levenshtein, ratio)
• Threshold-based (typically 85-95)
• Good for: names, addresses, company names with typos

---

Python Approach: rapidfuzz + pandas

When to Use

• CSV exports from Airtable, HubSpot, CRMs, etc.
• Need to identify likely duplicates for manual review
• Data has typos in name fields (donors, organizations, contacts)

Setup

pip3 install pandas rapidfuzz

Basic Name Deduplication Script

import pandas as pd
from rapidfuzz import fuzz

# Load CSV file
df = pd.read_csv('contacts.csv')

# Get unique, non-empty names
names = df['Name'].dropna().unique()
matches = []

# Compare every name with every other (O(n^2) - fine for < 10K records)
for i, name1 in enumerate(names):
    for j in range(i + 1, len(names)):
        name2 = names[j]
        score = fuzz.ratio(name1, name2)
        if score >= 85:  # Adjust threshold: 90+ for strict, 80 for loose
            matches.append((name1, name2, score))

# Output matches
match_df = pd.DataFrame(matches, columns=['Name 1', 'Name 2', 'Match Score'])
match_df.to_csv('possible_duplicates.csv', index=False)
print(f"Done! Found {len(matches)} possible duplicate pairs.")

Script with Canonical Name Assignment

Use when you want to automatically assign a "correct" name to each match group:

import pandas as pd
from rapidfuzz import fuzz

df = pd.read_csv('contacts.csv')
names_df = df[['Name', 'RecordID']].dropna().drop_duplicates()
names = names_df['Name'].unique()
matches = []

for i, name1 in enumerate(names):
    for j in range(i + 1, len(names)):
        name2 = names[j]
        score = fuzz.ratio(name1, name2)
        if score >= 85:
            matches.append((name1, name2, score))

# Assign canonical name.
# FOOTGUN: choosing the SHORTEST string (min(..., key=len)) is a bad default -- shorter
# is not more correct or more complete ("Bob" vs "Robert Smith"). Prefer the most frequent
# value, or the most complete one. Pick the rule deliberately, don't default to length.
canonical_map = {}
for name1, name2, _ in matches:
    canon = max(name1, name2, key=len)  # placeholder: swap in frequency/completeness logic
    canonical_map[name1] = canon
    canonical_map[name2] = canon

# Map back to records
df['Canonical Name'] = df['Name'].map(canonical_map).fillna(df['Name'])
output_df = df[['RecordID', 'Canonical Name']]
output_df.to_csv('record_id_to_canonical_name.csv', index=False)
print("Canonical name mapping saved!")

Re-importing to Airtable

1. Upload the CSV to a new table or use "Merge with CSV"
2. Match on RecordID
3. Update a new field called Canonical Name
4. Group/rollup by canonical name for aggregations

---

Airtable Script Approach

When to Use

• Working entirely within Airtable
• Smaller datasets (< 2,000 records)
• Want to avoid external tools

Levenshtein Distance Script (Airtable Scripting Extension)

const table = base.getTable("YourTableName");
const query = await table.selectRecordsAsync();
const records = query.records.map(r => r.getCellValue("Name"));

function levenshtein(a, b) {
    const matrix = Array.from({ length: b.length + 1 }, (_, i) =>
        Array.from({ length: a.length + 1 }, (_, j) => (i === 0 ? j : j === 0 ? i : 0))
    );

    for (let i = 1; i <= b.length; i++) {
        for (let j = 1; j <= a.length; j++) {
            matrix[i][j] = b[i - 1] === a[j - 1]
                ? matrix[i - 1][j - 1]
                : Math.min(
                    matrix[i - 1][j - 1] + 1,
                    matrix[i][j - 1] + 1,
                    matrix[i - 1][j] + 1
                );
        }
    }
    return matrix[b.length][a.length];
}

for (let i = 0; i < records.length; i++) {
    for (let j = i + 1; j < records.length; j++) {
        const distance = levenshtein(records[i], records[j]);
        if (distance < 4) {  // Lower = more similar
            output.text(`${records[i]} <-> ${records[j]} (distance: ${distance})`);
        }
    }
}

---

Email Deduplication

Gmail Normalization

Gmail ignores dots and plus-tags: john.doe@gmail.com, johndoe@gmail.com, and john.doe+newsletter@gmail.com are all the same inbox. Other providers treat dots as significant. Always normalize Gmail addresses before comparing.

---

Address/Household Deduplication (BigQuery)

When to Use

• Large contact databases or voter files
• Need to group by household
• De-duplicate addresses for direct mail

Address Standardization Pattern

WITH Contacts AS (
  SELECT
    ContactId,
    LastName,
    FirstName,
    TRIM(CONCAT(
      UPPER(Address1), ', ',
      UPPER(City), ', ',
      UPPER(State), ' ',
      CAST(Zip AS STRING)
    )) AS HouseholdAddress
  FROM `dataset.ContactData`
),

Households AS (
  SELECT
    HouseholdAddress,
    ANY_VALUE(LastName) AS CommonLastName,
    COUNT(*) AS NumberOfContacts
  FROM Contacts
  GROUP BY HouseholdAddress
)

SELECT
  HouseholdAddress,
  CONCAT(CommonLastName, ' Family') AS HouseholdName,
  NumberOfContacts
FROM Households;

Key Standardization Steps

1. UPPER() or LOWER() for case consistency
2. TRIM() to remove leading/trailing spaces
3. Consistent formatting: "123 MAIN ST, GREENVILLE, SC 29601"
4. Group by standardized address to find households

---

Scaling Beyond 10K Rows: Blocking

Blocking is the single biggest lever once you hit real data volume, so treat it as a first-class technique, not a footnote. Naive all-pairs fuzzy matching is O(n^2) — fine under ~10K rows, but it explodes beyond that (100K rows = 5 billion comparisons). Blocking groups records by a cheap key (first letter, zip code, a phonetic code, first token) and only compares within each block, cutting the comparison count by orders of magnitude while missing almost no true duplicates.

# Block by first letter of name, then fuzzy-match only within each block
from collections import defaultdict
blocks = defaultdict(list)
for name in names:
    blocks[name[0].upper()].append(name)
# for block in blocks.values(): fuzzy-compare pairs within block only

Choose a blocking key that duplicates will almost always share (zip for addresses, first token for org names). If a key is too coarse, blocks stay huge; too fine, and true dupes land in different blocks.

Common Pitfalls

1. Threshold Too Strict or Loose

• 85 is a good starting point
• 90+ catches only obvious typos
• 80 or below catches too many false positives

Start with 85, review results, adjust based on data quality.

2. Not Handling Blanks

Empty strings, None, null values will crash string comparisons.

Always filter blanks first:

names = df['Name'].dropna().unique()

3. Gmail Normalization Forgotten

john.doe@gmail.com and johndoe@gmail.com are the same person.

Always normalize Gmail addresses (strip dots, remove +tags).

4. Overwriting Original Data

Deduplication can destroy data if done carelessly.

Create a new "Canonical Name" column rather than overwriting the original. Keep record IDs for audit trail.

5. Known False-Positive Shapes

Some near-matches score high but are genuinely different records. Watch for and hand-review these shapes before auto-merging:

• Prefix/suffix variants — "Acme Corp" vs "Acme Corp - West"; "Smith LLC" vs "Smith LLP". Same stem, different entity.
• Abbreviation vs. full name — "Intl Business Machines" vs "International Business Machines" score low (true dupe missed), while "First National" vs "First National Bank" score high across two different orgs.
• Multi-word institutional typos — a one-character typo in a long organizational name reads as a match to a similarly-named-but-distinct org.

The lesson: fuzzy score is a candidate generator, not a verdict. Route high-scoring pairs that fit these shapes to human review rather than merging automatically.

---

Workflow Summary

1. Export data with unique IDs (RecordID, ContactId, etc.)
2. Choose approach based on data size and tool comfort
3. Normalize text fields (trim, case, Gmail rules)
4. Run matching (exact or fuzzy based on field type)
5. Review results in spreadsheet
6. Assign canonical values manually or with rules
7. Re-import using ID column to update original records
8. Keep original field for audit trail

---

License

CC-BY-4.0