A nightly de-duplication comparing every column reports nothing removed, yet each customer still appears twice — why?

## What "nothing removed" actually proved A de-duplication removes rows that match on the columns it was told to compare. When it removes nothing, exactly one thing has been established: **no two rows in this table match on that column set**. It has established nothing at all about whether the table holds the same customer twice. Comparing on every column is the widest possible definition of a repeat, and width works against you here. Two rows count as the same row only if they agree everywhere, so a single differing value anywhere — in a column nobody downstream reads, in a column the loader added, in a column that exists for auditing — is enough to make them distinct. The wider the comparison, the fewer repeats it can see. "Compare on all columns" is the setting that removes the least, and it is also the one people reach for when they have not decided what a row is supposed to mean. ## Finding the column that is defeating it The diagnosis is short and does not require reading much data: 1. **Take one affected customer** and pull their two rows. 2. **Compare them column by column** and list the columns whose values differ. 3. **Classify each differing column**: is it something about the customer, or something about how the row got here? The answer is nearly always in the second group: - an **ingestion or load timestamp** stamped when the row was written, so every reload differs by milliseconds; - a **batch, run or file identifier** recording which delivery brought the row; - a **sequence or line number** the loader assigned on the way in; - a **processed-by** or **environment** marker added by the pipeline. None of these say anything about the customer. They are provenance about the row's journey, sitting in the same rectangle as the facts, and a comparison that treats them as part of the record's identity is asking whether two rows arrived the same way rather than whether they describe the same thing. ## The columns that define a repeat are a choice There is no inherent identity column set. You choose one, and the choice decides which defects you can see and which removals are safe. Both directions have a failure mode, and neither announces itself: | | Comparison too wide (all columns) | Comparison too narrow | |---|---|---| | What it does | Removes nothing that differs anywhere, including on provenance | Collapses rows that are genuinely different records | | How it reports | Cleanly — zero or few rows removed | Cleanly — a large, satisfying removal count | | The tell | The repeat is still visible downstream in totals and counts | Totals fall, and history or per-period rows vanish | | Who notices | A consumer counting customers | Often nobody, until a comparison against the source is run | | The fix | Nominate the identifying columns explicitly | Add back the columns that legitimately distinguish two rows | The too-narrow direction is the more dangerous of the two, because its damage is a silent loss rather than a surviving surplus. Comparing a table of daily balances on the account alone, for example, collapses a year of history into one row per account and reports it as a successful clean-up. ## Choosing the set deliberately Say what one row of this table is supposed to mean, in a sentence, and the column set follows from it: - "One row per customer" gives the customer identifier alone. - "One row per customer per day" gives the customer identifier plus the date, and two rows for one customer on different days are then not repeats at all. - "One row per customer per version of their details" gives the identifier plus the version, and the repeats you are hunting are two rows claiming the same version. Everything outside that set is a value, not identity — including the provenance columns, which should never appear in it. ## What changes once you narrow it Narrowing the comparison moves you from the easy case to the hard one. Previously nothing matched, so nothing had to be decided. Now the two rows for each customer do match on the identifying columns while disagreeing elsewhere — on the balance, on the address, on the provenance columns at minimum. Removing one is no longer information-preserving, so the reduction needs a stated rule for which occurrence survives, on a column that genuinely encodes precedence, rather than whatever arrangement the previous step left behind. It is also worth checking, before writing that rule, whether two rows per customer were legitimate all along. If the producer emits a row per customer per delivery by design, the table means "one row per customer per delivery", the identifying set should include the delivery, and there was never a repeat to remove — only a misunderstanding about what the table holds. Discovering that is a better outcome than a correct de-duplication, because it removes the step rather than fixing it. ## The measurement to take alongside Whatever you conclude, take the two numbers that settle it: the row count, and the number of distinct combinations of the columns you nominated as identifying. Their ratio is how many rows per subject the table actually holds, and it is the number to quote in the ticket — far more informative than "the de-duplication removed nothing", which is what started this.