← All Kits · SQL Kit

Deduplicating a Customer List Without Losing Anybody

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

You have found 340 duplicate customers. The instinct is to keep the newest and delete the rest, and it is wrong twice: the newest record often has the emptiest fields, and deleting rows breaks everything that points at them.

What you do: write the survivorship rules first, merge field by field, keep the losing rows marked rather than deleted, and prove with four counts that nothing was lost.

The short version. The surviving record is usually not one of the original rows. It is assembled from them.

The three duplicates you will find

The third row is where damage is done. A rule that merges on email address alone will collapse two legitimate contacts into one and quietly lose a relationship that somebody in the business depends on.

Survivorship rules, field by field

Write these down before running anything. They are business decisions, not technical ones, and they need somebody to own them:

The consent row is not optional. Merging a record that opted out into one that opted in, and keeping the permissive value, creates a compliance problem out of a data-tidying exercise.

Picking the survivor in SQL

WITH ranked AS (
  SELECT customer_id, match_key, updated_at, created_at,
         ROW_NUMBER() OVER (PARTITION BY match_key
                            ORDER BY created_at ASC, customer_id ASC) AS rn
  FROM customers
)
SELECT match_key,
       MIN(CASE WHEN rn = 1 THEN customer_id END) AS surviving_id
FROM ranked
GROUP BY match_key
HAVING COUNT(*) > 1;

ROW_NUMBER is the right function here rather than RANK, because a tie must still produce exactly one survivor. The tie-breaker on customer_id is what guarantees it, and it also makes the result reproducible, which matters when you re-run the job. The three ranking functions are compared in RANK vs DENSE_RANK vs ROW_NUMBER.

Then assemble the surviving values field by field, taking the first non-blank in your chosen order rather than everything from the surviving row.

Never delete the losers

ALTER TABLE customers ADD COLUMN merged_into INT;
ALTER TABLE customers ADD COLUMN merged_at    DATE;

Set merged_into on the losing rows and filter them out of reporting views. Everything keeps working: an old order pointing at customer 4102 still resolves, and it can be followed to the survivor. A deleted row leaves an orphan, and orphans are how a revenue total loses a few percent between two reports.

The four checks that prove nothing was lost

1. Record count. Active after plus merged equals total before. Exactly.
2. Revenue. Total revenue after equals total before, to the penny. Merging customers moves orders between parents; it never removes an order.
3. Orphans. Count child rows whose parent id is not in the customer table. It must be zero, before and after.
4. Sample review. Twenty merged pairs read by a human, including five from the high-confidence band that nobody would normally check.

-- check 3, the one that catches a delete you did not mean
SELECT COUNT(*) FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c
                  WHERE c.customer_id = o.customer_id);

NOT EXISTS rather than NOT IN, for the reason set out in the anti-join: one NULL and NOT IN returns zero rows, which reads as a clean result.

The merge log

One row per merge: losing id, surviving id, match key, method, score, who approved it, when. It is what lets you answer "why are these two the same customer" six months later, and it is what makes an incorrect merge reversible instead of permanent.

How to apply this to your own work

1. Write the survivorship rules and get them agreed by whoever owns the customer relationship, not by whoever owns the database.
2. Run the whole process on a copy first, and compare the four checks between copy and original.
3. Require two matching fields before any automatic merge.
4. Mark, never delete. Add the two columns before you start.
5. Review a sample of automatic merges as well as the manual band.

The one habit to keep

Reconcile the money. Record counts change on purpose during deduplication, so they cannot tell you whether something went wrong. Revenue must not change at all, which makes it the check that actually catches mistakes.

If a merge in your CRM turns out to be wrong tomorrow, could anybody undo it?