Deduping without destroying history

Duplicate customers are the sitcom plot of analytics. Two emails, three CRM records, one very real human who bought twice and now appears as five “new” users depending on which export you open. Someone says “just dedupe it,” which sounds like a button and behaves like a paper shredder if you are careless.

Deduping done well collapses false twins while keeping history you may need for audits, dispute trails, and learning why the twins appeared. Deduping done poorly deletes the only row that explained a chargeback, merges two different people who share a last name, or “fixes” a dashboard by making last quarter unreproducible.

This is Part 3 of Data quality for people who ship numbers. You already know uniqueness as a dimension (Part 1) and how to spot duplicate rates in a profile (Part 2). Now we talk keys, soft deletes, lineage, and validating merges before you bless them.

What you’ll learn

Natural keys vs surrogate keys in plain language
Why hard deletes are rarely the first move for analytics tables
How to keep lineage: survivor id, source ids, merge reason, as-of time
A validation checklist before and after merge
SQL and Python patterns for finding duplicates and staging merges safely

First: are these duplicates or history?

Not every repeated value is a bug. Repeated customer_id on an orders table is normal. Repeated full order rows might be a load bug. Two CRM contacts with the same email might be the same person, or a shared inbox for a whole team.

Write the grain in one sentence: “This table should have one row per ___.” If you cannot finish the sentence, you are not ready to merge. That sentence is more valuable than any fuzzy matching library.

This is the same grain discipline you practice when modeling tables after spreadsheets in From spreadsheets to real data and when joining carefully in the SQL series.

Natural keys vs surrogate keys

A natural key is an identifier that exists in the business world: email, government id, ISBN, invoice number issued by finance. A surrogate key is invented by a system for convenience: auto-increment id, UUID from the app database, warehouse hash keys.

Both matter. They answer different questions.

Key type	Example	Good for	Common failure
Natural	email, SKU, order number from ERP	Matching across systems; human debugging	Changes (people change email); shared values; typos
Surrogate	crm_contact_id, uuid	Stable joins inside one system; history tables	Different surrogates for the same real person across tools

When people say “dedupe customers,” they usually mean: multiple surrogate keys map to one natural-world entity. Your job is not only to pick a winner. It is to record that many system ids now point to one canonical person (or company) for analytics.

Rule of thumb:

Use surrogate keys for stable internal joins and slowly changing history.
Use natural keys (normalized) to propose matches across systems.
Never assume natural keys are perfect. Email can be shared. Phone numbers get recycled. Names are not keys.

Soft deletes beat silent shredding

A hard delete removes the row. A soft delete keeps the row but marks it inactive: is_current = false, merged_into_id = 123, deleted_at = …. For operational apps, product constraints may require hard deletes (privacy requests, legal). For analytics staging and dimension tables you control, soft patterns usually win.

Why analysts should care:

You can re-open a bad merge.
You can explain last month’s dashboard when someone asks why a customer vanished.
You can keep foreign keys from orders pointing at something that still exists.
You leave a trail for audits and for your future self on a Friday.

Soft delete is not an excuse to keep serving retired ids in “active customers” metrics. Your consuming models filter to current survivors. The retired rows stay in a map or history table.

Lineage: the merge artifact you actually need

At minimum, when two records become one for analytics, store:

survivor_id: the canonical key going forward
merged_id: the key that should no longer be treated as separate
match_rule: exact email, manual review, fuzzy name+phone, vendor id, etc.
matched_at and matched_by (user or job name)
confidence if anything was fuzzy (exact vs probable)

A simple bridge table beats a heroic one-off UPDATE you cannot reverse.

-- Conceptual lineage table
-- customer_id_map
-- survivor_customer_id | source_customer_id | match_rule | confidence | valid_from | valid_to

SELECT
  survivor_customer_id,
  source_customer_id,
  match_rule,
  confidence,
  valid_from,
  valid_to
FROM customer_id_map
WHERE source_customer_id = 88421;

Example output:

Example output: id map lineage

When an order still carries an old customer_id, you resolve through the map:

SELECT
  o.order_id,
  o.amount,
  COALESCE(m.survivor_customer_id, o.customer_id) AS customer_id_resolved
FROM orders o
LEFT JOIN customer_id_map m
  ON o.customer_id = m.source_customer_id
 AND m.valid_to IS NULL;

Now uniqueness at the person level does not require rewriting every historical fact in place on day one. You can migrate carefully. You can also show your work.

Worked example: two CRM rows, one buyer

Suppose these contacts exist:

crm_id	email	full_name	created_at	lifetime_orders
101	alex@example.com	Alex Ng	2024-01-05	3
204	alex@example.com	A. Ng	2025-11-02	1
309	alex+work@example.com	Alex Ng	2025-12-01	2

And orders:

order_id	crm_id	amount
9001	101	40
9002	101	55
9003	204	60
9004	309	25
9005	309	25

Exact email match says 101 and 204 are strong merge candidates. 309 is a plus-address variant; maybe same person, maybe not. Do not auto-merge 309 without a rule you can defend.

Find exact-key collisions

SELECT
  lower(trim(email)) AS email_norm,
  COUNT(*) AS n_contacts,
  ARRAY_AGG(crm_id ORDER BY created_at) AS crm_ids
FROM crm_contacts
GROUP BY 1
HAVING COUNT(*) > 1;

Example output:

Example output: duplicate emails

Stage a survivor policy

Example:

Dedupe policy card

Policies should be explicit. Examples: earliest created wins; most complete profile wins; highest lifetime value wins; manual review above a threshold. Document the policy in the map’s match_rule.

WITH ranked AS (
  SELECT
    crm_id,
    lower(trim(email)) AS email_norm,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY lower(trim(email))
      ORDER BY created_at ASC, crm_id ASC
    ) AS rn
  FROM crm_contacts
)
SELECT
  email_norm,
  MAX(crm_id) FILTER (WHERE rn = 1) AS survivor_crm_id,
  ARRAY_AGG(crm_id) FILTER (WHERE rn > 1) AS merge_candidates
FROM ranked
GROUP BY email_norm
HAVING COUNT(*) > 1;

In Python, same idea before you touch production-like tables:

import pandas as pd

contacts = pd.DataFrame(
    {
        "crm_id": [101, 204, 309],
        "email": ["alex@example.com", "alex@example.com", "alex+work@example.com"],
        "created_at": pd.to_datetime(["2024-01-05", "2025-11-02", "2025-12-01"]),
    }
)
contacts["email_norm"] = contacts["email"].str.lower().str.strip()

# Exact email candidates only
dup_emails = contacts.groupby("email_norm").filter(lambda g: len(g) > 1)
survivors = (
    contacts.sort_values(["email_norm", "created_at", "crm_id"])
    .groupby("email_norm", as_index=False)
    .first()[["email_norm", "crm_id"]]
    .rename(columns={"crm_id": "survivor_crm_id"})
)
merged = contacts.merge(survivors, on="email_norm")
merged["is_survivor"] = merged["crm_id"] == merged["survivor_crm_id"]
print(merged)

Validate before you merge

Before writing the map, compute impact metrics on a staging copy:

Distinct customers before vs after (should fall only as expected)
Order counts and revenue before vs after (should match if you only re-key, not drop orders)
Sample of merges for human review (especially high revenue)
Check that no survivor is also listed as a merged child in a way that creates cycles

-- Revenue should not disappear when resolving ids
WITH resolved AS (
  SELECT
    o.order_id,
    o.amount,
    COALESCE(m.survivor_crm_id, o.crm_id) AS crm_id_resolved
  FROM orders o
  LEFT JOIN staged_customer_map m
    ON o.crm_id = m.source_crm_id
)
SELECT
  (SELECT SUM(amount) FROM orders) AS revenue_before,
  (SELECT SUM(amount) FROM resolved) AS revenue_after,
  (SELECT COUNT(DISTINCT crm_id) FROM orders) AS customers_before_keys,
  (SELECT COUNT(DISTINCT crm_id_resolved) FROM resolved) AS customers_after_resolve;

If revenue_after diverges from revenue_before, you dropped or double-counted. Stop. If customer counts barely move but you expected a big cleanup, your match rule may be too timid. If customer counts collapse by half, your match rule may be matching on last name alone. Both are useful alarms.

Fuzzy matching: useful, dangerous, optional on day one

Fuzzy tools compare strings that are close: “Alex Ng” vs “A. Ng,” addresses with abbreviations, company names with “Inc.” They help when natural keys are weak. They also merge strangers who share a common name in a large city.

Practical guardrails:

Start with exact normalized keys (email, external account id, tax id where lawful and available).
Put fuzzy candidates in a review queue with scores, not an automatic production merge.
Never fuzzy-merge on name alone at scale.
Record confidence and reviewer on every accepted fuzzy merge.

Enterprise MDM platforms formalize this. You do not need a six-month program to keep a bridge table and a review spreadsheet for the top 200 collisions. Hands-on uniqueness beats ceremonial uniqueness.

Common mistakes

Deleting duplicate rows in the fact table. Often you need a map, not fewer orders.
Merging without a survivor policy. “Keep the newest” vs “keep the oldest” changes lifetime value stories.
Updating in place with no backup. Soft map first; rewrite later if needed.
Ignoring shared emails and family plans. Natural keys can be many-to-one in the wrong direction.
Changing ids in one dashboard extract only. Next week’s extract reintroduces ghosts.
Skipping validation totals. If revenue moves when you only re-keyed, you have a bug.
Treating plus-aliases and typos the same. Different rules, different confidence.

How to practice this week

On one entity table, compute duplicate rate for your best natural key and for the surrogate key.
Write a one-page survivor policy: which record wins and why.
Build a tiny map table (even in a spreadsheet) for ten real collisions. Include match_rule.
Recompute a metric with and without resolution. Confirm totals that should be invariant.
Skim pandas or SQL join refreshers via Python for analytics or Learn if the merge queries feel rusty.

Quick recap

Duplicates are a grain and identity problem, not a “delete button” problem.
Natural keys propose matches; surrogate keys stabilize systems; maps connect them.
Prefer soft retirement plus lineage over silent hard deletes in analytics.
Validate with invariant totals and samples before you bless a merge.
Next: standardizing categories and names so “US” and “usa” stop pretending to be different countries.

Identity work is quality work. It also touches the decision clarity from Analytics foundations: know what “one customer” means before you count them.

Sources

Kimball Group, discussion of slowly changing dimensions and durable keys (dimensional modeling context): https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/
PostgreSQL docs on window functions (ROW_NUMBER for survivor selection): https://www.postgresql.org/docs/current/tutorial-window.html
pandas documentation, DataFrame.duplicated: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.duplicated.html
pandas documentation, DataFrame.merge: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html
DAMA NL dimensions of data quality (uniqueness in a broader framework): https://www.dama-nl.org/wp-content/uploads/2020/09/DDQ-Dimensions-of-Data-Quality-Research-Paper-version-1.2-d.d.-3-Sept-2020.pdf