Synthetic reconciliation report for merging duplicate customers: every row and reference accounted for

The invented rules

Everything below is invented for illustration. It is not a measurement of any real system and not a customer delivery. The table is customers. Two rows are the same customer when their emails match after trimming and lower-casing; rows with an empty email never match. The survivor is the row with the most orders; ties go to the oldest. Two rows are marked "do not change" by the data owner; neither shares its email with any other row, so they sit outside every duplicate group.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

match rule:     lower(trim(email)) equal, email not empty
survivor rule:  most orders; tie -> oldest created_at
excluded:       2 customer ids named by the data owner, in no duplicate group
dependent tables: orders, invoices, support_tickets (3, all declared foreign keys)

The report

All numbers below are invented. The report is produced on a restored copy.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

duplicate groups on production / on the masked copy     312 / 312   (equal)

customers before                       100,000
duplicate groups found                     312
rows in those groups                       701
survivors kept (one per group)             312
rows removed (extras)                      389
customers after                         99,611
rows excluded (data owner), unchanged        2

removed rows archived in full                389

references repointed (repoint log rows 2,301)
  orders            1,204
  invoices            956
  support_tickets     141

checks
  orders pointing at a removed customer          0
  invoices pointing at a removed customer        0
  tickets pointing at a removed customer         0
  related rows per table, before vs after   equal
  duplicate emails after                         0
  guard built concurrently with inserts running: valid unique index
  insert of a duplicate email on the copy:  rejected

reverse run on the copy (rows added by the insert script removed, then guard removed)
  389 archived rows restored, 2,301 references set back
  table checksums, customers / orders / invoices / support_tickets,
  equal to the checksums taken before the merge: yes, all four

Arithmetic that must close

A reviewer can check the report without the data.

• 701 rows in groups, minus 312 survivors, equals 389 removed. The 2 excluded rows are not in any group.
• 100,000 minus 389 equals 99,611 customers after.
• 1,204 + 956 + 141 equals the 2,301 lines in the repoint log.
• Each removed id appears once in the archive and once in the mapping to its survivor, so 389 archived rows.
• Related-row counts per dependent table match before and after because references were repointed, not deleted.

Limits, and the priced enquiry

The figures are invented and describe no real table. A deterministic rule cannot judge whether two similar-looking people are the same; that needs a person per case and is outside the fixed job. The reverse run undoes the merge on the copy; edits made to the data after a production merge are reported by the reverse script and left alone. The fixed-scope duplicate-merge job produces a report of this shape for one real table on a restored copy, starting from £695 as an untested proposal and paid after you sign off. It never runs on production; you run the script after a restore point you have tested.