The problem: the same customer is in your CRM as Jonathan Smith, in billing as John D. Smith and in the ERP as Jon Smyth. The emails differ, one phone number has brackets and spaces, and one address says "St" where another says "Street". A GROUP BY email finds nothing. Marketing emails them three times, support sees a third of their history, and your customer count is wrong.
We build SynapCores, and the last part of this page runs in it. Everything before that is plain SQL (run on SQLite) and a few lines of Python, so you can follow it without installing anything of ours.
Why duplicate customer records slip past exact matching
Duplicates differ in four ways, and each needs a different tool:
| How the records differ | Example from the data below | What catches it |
|---|---|---|
| Formatting | JSmith@Acme.com vs jsmith@acme.com; (415) 555 0142 vs 415-555-0142 |
Normalize, then match exactly |
| Typos | Jon Smyth vs Jon Smith; O'Neil vs ONeill | Spelling similarity (edit distance, trigrams) |
| Variants | Bob vs Robert; Harbour Rd vs Harbor Road; Rome vs Roma | Nickname and abbreviation lists, or similarity by meaning |
| Different people who look alike | Jonathan Smith in San Francisco and a Jon Smith in Austin | Not a matching problem: blocking, more evidence, and a person who reviews |
The first three are about finding more matches. The last one is about not merging people who aren't the same, and it's where most dedup projects go wrong.
The data
Twelve records from three systems. We know the answer, which is the point of a test set: they are 7 real people. Jonathan Smith appears three times, and Robert Garcia, Katherine O'Neil and Mia Rossi twice each. The Jon Smith in Austin is someone else.
CREATE TABLE customers (
id TEXT PRIMARY KEY,
source TEXT,
full_name TEXT,
email TEXT,
phone TEXT,
street TEXT,
city TEXT,
postcode TEXT
);
INSERT INTO customers (id, source, full_name, email, phone, street, city, postcode) VALUES
('CRM-101', 'crm', 'Jonathan Smith', 'jsmith@acme.com', '415-555-0142', '100 Mission St', 'San Francisco', '94105'),
('ERP-202', 'erp', 'Jon Smyth', 'jonathan.smith@acmeholdings.com', '(415) 555 0142', '100 Mission Street', 'San Francisco', '94105'),
('BILL-303', 'billing', 'John D. Smith', ' JSmith@Acme.com ', NULL, '100 Mission St #201', 'SF', '94105'),
('CRM-104', 'crm', 'Robert Garcia', 'rgarcia@fastmail.com', '312-555-0199', '742 Evergreen Terrace', 'Chicago', '60614'),
('ERP-205', 'erp', 'Bob Garcia', 'bob.g@work.io', NULL, '742 Evergreen Ter.', 'Chicago', '60614'),
('CRM-107', 'crm', 'Katherine O''Neil', 'koneil@harbourlaw.com', '617-555-0110', '18 Harbour Rd', 'Boston', '02110'),
('ERP-208', 'erp', 'Catherine ONeill', 'k.oneil@harbourlaw.com', '+1 617 555 0110', '18 Harbor Road', 'Boston', '02110'),
('CRM-109', 'crm', 'Jon Smith', 'jon.smith@outlook.com', '512-555-0177', '9 Congress Ave', 'Austin', '78701'),
('CRM-110', 'crm', 'Mia Rossi', 'mia.rossi@studio.io', '+39 06 555 0011', '20 Via Roma', 'Rome', '00184'),
('BILL-311', 'billing', 'Mia R. Rossi', 'Mia.Rossi@studio.io', NULL, 'Via Roma 20', 'Roma', '00184'),
('CRM-112', 'crm', 'Leo Park', 'leo@parks.dev', '416-555-0150', '44 King St W', 'Toronto', 'M5H 1A1'),
('ERP-213', 'erp', 'Eli Tanaka', 'eli.t@example.com', NULL, '9-1 Roppongi', 'Tokyo', '106-0032');
Sections 1 to 5 were run on SQLite 3.43.2 (macOS) and Python 3.9.6 (standard library only), and the outputs are pasted from that run. The SQL is standard, with one exception: in MySQL, || means OR unless PIPES_AS_CONCAT is set, so use CONCAT() there.
1. Exact matches: GROUP BY
SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
(0 rows)
Zero. jsmith@acme.com and JSmith@Acme.com are different strings, and so are mia.rossi@studio.io and Mia.Rossi@studio.io.
2. Normalize, then match exactly
Most "fuzzy" duplicates are formatting. Lowercase and trim the email. Keep only the digits of the phone, then the last 10 digits, so +1 617 555 0110 and 617-555-0110 agree. (Last 10 is a shortcut for North American numbers. For international data, parse numbers into E.164 format with a library such as libphonenumber.)
CREATE VIEW customers_norm AS
SELECT id, full_name, street, city, postcode,
LOWER(TRIM(email)) AS email_norm,
SUBSTR(digits, LENGTH(digits) - 9) AS phone_norm
FROM (
SELECT *,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, ' ', ''), '-', ''), '(', ''), ')', ''), '+', '') AS digits
FROM customers
);
SELECT a.id AS id_a, b.id AS id_b,
CASE WHEN a.email_norm = b.email_norm THEN 'email' ELSE 'phone' END AS matched_on
FROM customers_norm a
JOIN customers_norm b
ON a.id < b.id
AND (a.email_norm = b.email_norm OR a.phone_norm = b.phone_norm)
ORDER BY a.id, b.id;
id_a id_b matched_on
-------- ------- ----------
BILL-303 CRM-101 email
BILL-311 CRM-110 email
CRM-101 ERP-202 phone
CRM-107 ERP-208 phone
Four pairs, and every one is right. These are your safest merges, because an exact email or phone is strong evidence. (Shared family phones and work inboxes do exist, so even here, log why two records were merged.) a.id < b.id keeps each pair once and stops a record matching itself.
Robert and Bob Garcia are still apart: different emails, and Bob has no phone. Only their names and address connect them.
3. Blocking: only compare records that could match
To compare names you have to compare pairs, and pairs grow fast: n records make n(n-1)/2 of them. Blocking means only comparing records that share a cheap key. Here, the postcode:
SELECT
(SELECT COUNT(*) FROM customers a JOIN customers b ON a.id < b.id) AS all_pairs,
(SELECT COUNT(*) FROM customers a JOIN customers b
ON a.id < b.id AND a.postcode = b.postcode) AS blocked_pairs;
all_pairs blocked_pairs
--------- -------------
66 6
66 pairs become 6. At a million customers, it's about 500 billion pairs against however many share a postcode, and the join can use an ordinary index.
Blocking also prevents false matches, as the end of section 4 and the SynapCores part show. Its cost is that a duplicate with a typo in the blocking key is never compared. Use two or three different keys (postcode; email domain plus first letter of the surname; last four digits of the phone) and take the union of the candidate pairs.
4. Score the pairs: spelling similarity
SQLite has no edit-distance function, so this step is a few lines of Python using difflib from the standard library. (In Postgres, similarity() from pg_trgm and levenshtein() from fuzzystrmatch do the same job inside the database.) It scores each blocked pair from 0 to 1 on the name, the street, and the whole "name, street, city" string:
import sqlite3
from difflib import SequenceMatcher
def norm(s):
return " ".join(s.lower().replace(".", " ").replace("'", "").replace("#", " ").split())
def ratio(a, b):
return SequenceMatcher(None, norm(a), norm(b)).ratio()
db = sqlite3.connect("learn.db")
pairs = db.execute("""
SELECT a.id, a.full_name, a.street, b.id, b.full_name, b.street,
a.full_name || ', ' || a.street || ', ' || a.city,
b.full_name || ', ' || b.street || ', ' || b.city
FROM customers a
JOIN customers b ON a.postcode = b.postcode AND a.id < b.id
""").fetchall()
rows = []
for id_a, name_a, street_a, id_b, name_b, street_b, ident_a, ident_b in pairs:
rows.append((ratio(name_a, name_b), ratio(street_a, street_b), ratio(ident_a, ident_b),
id_a, name_a, id_b, name_b))
print(f"{'id_a':<9} {'name_a':<17} {'id_b':<8} {'name_b':<17} {'name':>5} {'street':>6} {'whole':>5}")
for name_sim, street_sim, whole_sim, id_a, name_a, id_b, name_b in sorted(rows, reverse=True):
print(f"{id_a:<9} {name_a:<17} {id_b:<8} {name_b:<17} {name_sim:>5.2f} {street_sim:>6.2f} {whole_sim:>5.2f}")
id_a name_a id_b name_b name street whole
CRM-107 Katherine O'Neil ERP-208 Catherine ONeill 0.90 0.89 0.92
BILL-311 Mia R. Rossi CRM-110 Mia Rossi 0.90 0.73 0.83
CRM-104 Robert Garcia ERP-205 Bob Garcia 0.78 0.89 0.88
BILL-303 John D. Smith CRM-101 Jonathan Smith 0.77 0.88 0.74
BILL-303 John D. Smith ERP-202 Jon Smyth 0.76 0.78 0.70
CRM-101 Jonathan Smith ERP-202 Jon Smyth 0.70 0.88 0.88
All six blocked pairs are real duplicates. Read the table three ways:
- Every true pair scores 0.70 or more on name, and the street scores back them up.
- Bob Garcia is caught by his surname, not his first name. On first names alone,
bobvsrobertscores 0.44. Spelling similarity doesn't know nicknames. - Score fields separately. The "whole string" column mixes the name with the address, so a shared address lifts the score whoever lives there. Score each field on its own and combine them with rules you can read.
Without blocking, the Jon Smith in Austin would be compared with Jonathan Smith in San Francisco: 0.78, higher than the real Jonathan Smith / Jon Smyth pair at 0.70.
5. From pairs to customers: clusters
Pairs aren't customers. CRM-101 matches BILL-303 and ERP-202, so all three are one person, even though BILL-303 and ERP-202 never matched each other directly. Save the accepted pairs and follow them transitively:
CREATE TABLE dup_pairs (id_a TEXT, id_b TEXT);
INSERT INTO dup_pairs VALUES
('BILL-303', 'CRM-101'),
('BILL-311', 'CRM-110'),
('CRM-101', 'ERP-202'),
('CRM-107', 'ERP-208');
WITH RECURSIVE
edges (x, y) AS (
SELECT id_a, id_b FROM dup_pairs
UNION
SELECT id_b, id_a FROM dup_pairs
),
reach (id, member) AS (
SELECT id, id FROM customers
UNION
SELECT r.id, e.y FROM reach r JOIN edges e ON e.x = r.member
)
SELECT MIN(member) AS cluster_id, id
FROM reach
GROUP BY id
HAVING COUNT(*) > 1
ORDER BY cluster_id, id;
cluster_id id
---------- --------
BILL-303 BILL-303
BILL-303 CRM-101
BILL-303 ERP-202
BILL-311 BILL-311
BILL-311 CRM-110
CRM-107 CRM-107
CRM-107 ERP-208
dup_pairs holds the four exact-key pairs from section 2. Add Robert and Bob after a review and the 12 records become 7 customers. UNION, not UNION ALL, is what stops the recursion going round in circles.
Transitivity is also a risk: one weak link can chain two households into one. Check the size of every cluster, and send any cluster above two or three records to a person.
How it fits together
sources (CRM, billing, ERP)
│
▼
normalize ── email lowercased, phone digits, address abbreviations
│
├──► exact match on normalized keys ──► merge (log the key)
│
▼
block (postcode, email domain + initial, phone last 4) ──► candidate pairs
│
▼
score each field: name, street (spelling or meaning) ──► rank candidates
│
├── high on name and address ──► review queue (a person)
└── low ──► different
│
▼
cluster approved pairs ──► one customer id per cluster, with the reason logged
How the options compare
| Normalize + exact | Spelling similarity (difflib, pg_trgm, levenshtein) | Phonetic (Soundex, Metaphone) | Embeddings computed by your app (pgvector or a vector DB) | EMBED in the database (SynapCores) | Record-linkage libraries (Splink, dedupe, Zingg) | |
|---|---|---|---|---|---|---|
| Formatting differences | Yes | Yes | Partly | Yes | Yes (addresses: 0.81 to 0.95 on this page) | Yes |
| Typos (Smyth/Smith) | No | Yes | Often | Depends on the model; not tested here | Matched on the exact keys in the same query (Jon Smyth: phone) | Yes |
| Nicknames (Bob/Robert) | No | No | No | Depends on the model; not tested here | Yes here: Bob Garcia / Robert Garcia 0.87 | With a nickname list or labelled pairs |
| Where it runs | Any SQL | In the database (Postgres extensions) or in your app | SQL Server, MySQL, Postgres extension | Your app or an API computes vectors; the database compares | One SQL statement | Python, on DuckDB or Spark |
| Learns from your data | No | No | No | No | No | Yes: dedupe from pairs you label; Splink estimates field weights from the data |
Start with normalize-and-exact; it's often most of the win. If your variants are typos, pg_trgm plus a blocking key on Postgres is the default answer for most teams. For millions of records and many fields, a record-linkage library such as Splink weighs each field by how much agreement on it really tells you, if you can label a few hundred pairs.
What EMBED in the database changes is where the meaning score comes from: the model runs inside the engine, so a name or address similarity is an ordinary expression in the same blocked self-join as the exact keys. On this page it caught the nickname that spelling missed (Bob Garcia / Robert Garcia, 0.87), in one statement with the blocking and the exact-key evidence.