Reconciling 1,757 records across seven authorities

When I took this on, the dataset already existed. It had been inherited, extended by hand for years, and nobody could tell me which parts of it were still true. Roughly 1,800 entities, each belonging to one of seven independent authorities that publish their own membership separately, in their own formats, on their own schedules.

It was wrong in both directions, and that's what makes this kind of work hard. Missing entries are annoying but survivable — someone eventually asks for one and you notice. Entries that shouldn't be there are the real problem. They look completely valid. They pass every format check you throw at them. And they quietly waste somebody's time every single time the dataset gets used, because nothing ever appears broken.

Anything easy to check was already out of date. Anything current had to be read off a page that was never meant to be read by a machine.

My first instinct was the obvious one: take each authority's published membership list, merge them, done. It failed immediately. Those lists were stale in all seven — every single one. They're maintained for administrative reasons and they lag reality by months, sometimes years. An entity can sit happily on a membership list long after it's stopped doing the thing the dataset is actually about.

So I threw out “compile from lists” and replaced it with four rules. Each one cost me time. Each one caught something the old way had waved straight through.

• Never confirm from a membership list alone Everything got confirmed against a primary source: the entity's own site, or its grouping's own published activity record. A list tells you someone belongs. An activity record shows them actually turning up. Only the second one is evidence.
• Confirm every removal twice, independently Deleting a record is destructive and nearly impossible to undo once other work has moved on. So every removal had to fail two separate checks, against two different sources, before I'd act on it. Slow on purpose.
• Flag, never fill If a contact address wasn't published, I flagged the row as incomplete. I never guessed it from the obvious pattern in that organisation's other addresses — and it was often very obvious. A convincing made-up value is worse than an honest gap, because six months later nobody can tell which is which. Including me.
• Report completeness per source, never averaged One headline percentage would have buried the fact that coverage swings wildly between authorities. Averaging hides your weakest sources behind your strongest, which is a lovely way to mislead yourself.

1. Rebuild the taxonomy before the records The groupings were reorganising while I was building. Entities moved, groupings merged, several renamed outright. So I rebuilt the structure first and dated every change — because a record filed under a grouping that no longer exists isn't a small error, it's an invisible one.
2. Split the work and run it all at once Each grouping is independent of the others, so the research never needed to be sequential. Once that clicked, the build went from something I was measuring in months to something I measured in days.
3. Confirm each entity against its own primary source One pass per entity, capturing the source URL and the confirmation date alongside the answer — so anyone (usually future me) can re-check a row later without redoing the search that found it.
4. Recount independently A separate pass re-derived every total without ever seeing my results. Where the two disagreed, the work stopped until I'd resolved it. This caught more errors than anything else I did, and it's the step almost everyone skips.
5. Resolve the awkward ones by hand About a hundred records flatly refused to be one-to-one. Consortiums that operate jointly are one record, not three. Multi-campus systems whose campuses are separate members are several, not one. Around sixty name collisions needed disambiguating. And sometimes an entity's affiliation is different for one specific offering — which no membership list anywhere bothers to record.
6. File announced changes under present reality Plenty of changes were publicly announced but not yet in effect. Those get filed under this year's state, with the effective date recorded. Filing on announcement instead of effect would have made the dataset wrong for the entire period anyone was actually using it.

1,757 records across 161 groupings and 7 authorities, every one carrying the source it was confirmed from and the date I confirmed it. Sixty-two entries came out — each confirmed twice, against independent sources, as not qualifying at all — and thirteen that did qualify were missing entirely and went in.

Completeness per authority rather than averaged. 1,147 rows clean, 610 flagged for follow-up — reported as a known state, not tidied away.

The gaps are real, and they're not mine

Coverage runs from about 95% at the best-resourced authority down to about 43% at the smallest. That spread isn't a failure of the search, it tracks publishing practice. On hundreds of pages a primary contact's address is published and the secondary contact's field just sits there empty. I'd much rather say that plainly than average it away, because it tells anyone using this exactly where they'll have to do the work by hand.

The build worked. The container didn't — and I didn't find out until I ran a structural audit, long after I'd finished congratulating myself.

What I'd actually built, without quite noticing, was a four-table relational database living inside a spreadsheet:

Entities        one row per organisation
   └─< Offerings   one row per offering        links by entity_id
          └─< Roles  one row per person × offering  links by offering_id
                 └─ People  one row per person       links by person_id

A spreadsheet will hold this structure quite happily. It will not enforce a single one of those links, and it will never mention that to you.

Two things went wrong because of it, and both were completely silent.

A bulk re-identification split records in half

A batch operation handed new identifiers to around 199 entities without moving their children or clearing out the originals. What I ended up with was 157 duplicate clusters — the same organisation existing twice, with its children divided between the two copies. Neither copy was complete. And nothing looked broken, because every individual row was perfectly valid. Any query against either copy just returned a confident, partial answer.

Names were copied onto child rows instead of looked up

Every child row carried its own copy of the parent's name instead of pointing at it. The moment one copy got hand-edited, they drifted apart — and the same organisation started appearing under several spellings that no exact-match join was ever going to put back together.

How I found them, and what actually fixed it

Names couldn't identify the duplicates, because the drifting names were the problem. Coordinates could. Two records sharing a coordinate pair are the same physical organisation no matter how anyone typed the name, and that's what I built the merge map from: 157 high-confidence clusters, plus 65 I held back for manual review — most of which turned out to be genuinely different organisations that just happened to round to the same spot.

The permanent fix was adopting a national registry identifier as the key. A stable external ID that no hand-edit can knock out of alignment, rather than one I'd generated myself.

The three things I'd change

• Choose the identity key before you write the first row Not after 1,757 of them. If a stable external identifier exists for the things you're tracking, take it on day one. Retrofitting one across a dataset that's already grown children costs an order of magnitude more, and I paid every bit of that.
• Put relational data somewhere that enforces relationships The spreadsheet was genuinely right for the people reading and editing this every day, and I wouldn't dream of taking it off them. But it should have been a window onto something that enforces its own integrity — not the system of record itself.
• Audit structure on a schedule, not on suspicion I verified the contents obsessively. Twice, independently, from primary sources. And I never once checked the shape. Every single one of those four failures would have turned up in week one from a scheduled check doing nothing cleverer than counting rows that point at parents which don't exist. I run that check on a schedule now.

I'd built a rigorous verification process for my data, and absolutely none for the thing holding it.