Per-field authority

The live admin system knew who the clients were. A separate working spreadsheet knew things about them the admin system had never captured. Both were in daily use, both had been edited by hand for a long time, and they had drifted apart.

The brief was to make one of them the single source of truth. That sounds like a merge. It isn't — because each system was more correct than the other about different things, and a merge has to know which.

“Make this the source of truth” is a goal, not a specification. The specification is: for each field, which system is allowed to be right?

The identity problem underneath it was worse. The same client could appear twice under a full name and a nickname, under a current and a previous surname, or — the one that surprised me — once under their own address and once under a guardian's. Two rows, two different contact details, one client. No exact match anywhere would ever catch it.

• Assign authority per field, not per system For each column, one system is authoritative and the other is ignored — stated explicitly rather than resolved by whichever row happened to be newer. Where the authority has no value, the field goes blank, not stale. A field that quietly keeps an old value is indistinguishable from a field that is current.
• Capture the data the system actually holds, not the page it draws Rather than parsing rendered screens, the extractor signs in with a real browser and listens to the responses the application itself receives, keeping the underlying records. No layout change can break it and nothing is lost to formatting. Credentials come from the environment and are never written down.
• Cluster identities on several weak signals, not one strong one No single field identifies a person reliably here. Several imperfect signals agreeing is stronger evidence than one field matching exactly.
• Stage the work, and keep a snapshot at every stage Each transformation is a separate, named, reversible step with its own saved copy — so any single stage can be undone without rerunning the others or restoring from the beginning.

Identity: four signals, then transitive clustering

Matches are then merged transitively using a union-find structure: if A matches B on address and B matches C on name, all three are one client. That matters because the signals don't overlap — the row linked by a guardian's address often shares nothing else with the row linked by a nickname, and pairwise matching alone would leave the cluster in pieces.

Merging: coalesce field by field, don't pick a survivor row

The obvious merge keeps the “best” row and discards the rest, which throws away every value the other rows held that the winner didn't. The survivor is assembled instead — for each field, take the most complete value available across the whole cluster. A cluster of three half-populated rows produces one row better than any of its inputs.

Applying authority: one column, one source, nothing else touched

The narrowest script in the project rewrites exactly one column using only the authoritative system's values, blanks it where that system has nothing, and explicitly leaves every other column alone. It resolves each row through the same cascade of signals — address, then name plus cohort, then name — so a client is found even when the two systems spell them differently.

One column, one authority, one reversible step. It is boring, and that is the feature: you can read it in a minute and say with confidence what it cannot do.

Staging: a saved copy at every step

Five snapshots, one per stage. Ten duplicates out of 331 — around 3%, every one of which would have meant a real client being contacted twice, or worse, being treated as two people with half a history each.

One master list of 321 clients, where each field's origin is a stated decision rather than an accident of edit order. Ten duplicate clusters resolved, including several that no exact match on any single column would have found. Every stage is individually reversible.

The part I'd defend in a review is the blank-rather-than-stale rule. It makes the file look worse — there are empty cells now that used to hold something. But a blank says “the authority doesn't know this”, which is true and actionable, while the value it replaced said “this is current”, which wasn't. The same instinct as flagging an absent contact instead of guessing it: an honest gap beats a confident wrong answer, every time.

I already had the answer to the real problem and didn't apply it

This was a reconciliation — a one-time event with a date on it. The two systems started drifting again the day after, because they are both still in daily use and nothing watches them.

I'd built continuous change detection for a different dataset weeks earlier, and it did not occur to me to point the same idea at this one.

The right deliverable was never a clean file. It was a scheduled comparison that reports what has diverged since the last run — new on one side, missing on the other, changed where the authority says it shouldn't have. The identity clustering that made the merge work is exactly the machinery that job needs, and it was already written. I built a snapshot when I could have built a monitor.

The nickname map is a hand-written list, and it fails silently

First names are resolved through a dictionary of short forms I typed out myself. Every entry in it works. The problem is everything not in it: an unlisted short form produces no match, no warning and no duplicate found. The failure is invisible by construction, and the list can never be complete.

It should be a string-similarity measure producing candidate clusters for review, with the hand-written map kept only for the cases similarity genuinely can't reach. And at minimum it should count how often each rule fires, so I'd know whether that stage is doing real work or decorating.

I saved snapshots but not diffs

There is a file at every stage, so I can go back. But to answer “what did the deduplication actually do?” someone has to compare two files by hand. The interesting artefact isn't the state, it's the change: these ten clusters merged, on these signals, and here is the field-by-field result.

A merge report per stage would have made the whole thing reviewable by the person who owns the data rather than only by the person who wrote the script — which is the difference between an audit trail and a backup.

And one thing I'd keep exactly as it is

The single-column rewrite. It does one thing, to one field, from one source, and says so in its first line. Every temptation during that build was to make it “while I'm here” fix three more columns. Keeping it narrow is why it could be run, checked and trusted in an afternoon.