Field notes · 02 · July 2026
Modelling data
nobody agrees on
Nine units, nine spreadsheets, nine spellings of the same field office. The dashboard everyone wanted was never the hard part. The hard part was getting nine owners of nine truths to accept one.
The request sounded simple: one screen showing what equipment we had, where it was deployed, and what had changed. The data existed — that was never in doubt. It lived in nine different units' workbooks, each in its own shape, each internally consistent, and each quietly incompatible with the other eight.
One list called a site by its town name. Another used a sector code. A third used an abbreviation somebody invented years ago that had since become "official" through repetition. None of these were wrong, exactly. Each unit's spreadsheet worked fine for that unit. The incompatibility only became visible the moment someone asked a question that crossed a boundary — and management questions always cross boundaries.
Every column is a claim
The slowest and least glamorous part of the job was going unit by unit and working out what each column actually meant. Not what its header said — what the people filling it in understood it to mean. A column called "location" might hold where a thing is, where it was issued, or where its owner sits, and I found all three. You cannot join what you have not understood, and a dashboard built on misread columns produces confident-looking wrong numbers, which is worse than no dashboard at all.
The location list is the real project
Once the columns were understood, the fix was structurally boring: one reference table for locations, holding the authoritative name of every site, and every inventory list rewritten to point at it by lookup instead of typed text. Typed text is where the nine spellings came from; a lookup makes the tenth spelling impossible rather than merely discouraged.
But getting there was not a data task. Getting two units to accept one spelling of a place is a negotiation. Somebody's habitual name loses. The reference table is really a small treaty, and it holds for the same reason treaties hold — because the parties were in the room when it was written. I stopped thinking of it as cleaning data and started thinking of it as chairing an agreement, and the work went better after that.
Make the reference tables boring and rigid, early
Every week you delay locking the reference tables, more rows accumulate that will need rewriting later. Rigid feels unfriendly at the start — people want the freedom to type what they've always typed. But freedom in reference data is just deferred reconciliation, and deferred reconciliation compounds. The kindest thing you can do for your future self is make the boring tables boring as early as possible.
Layers keep the peace
The structure that came out of it has three layers: reference tables that hold the agreed vocabulary — locations, services, asset types; inventory tables, one per equipment class, that point into the reference layer by lookup; and control tables that drive the dashboard itself. The layering isn't architectural vanity. It means a unit can change what it tracks without renegotiating the vocabulary, and the vocabulary can be corrected in one place without touching anyone's inventory. Arguments get smaller when the thing being argued about is smaller.
What I got wrong
I underestimated how much of the job was conversations. My first plan budgeted most of the time for building and a little for "data cleaning", as if the spellings would surrender once I found them all. In practice the build was the last five per cent. If I planned it again I would budget the majority of the time for the reconciliation conversations — and I would start the location negotiation on day one, because everything else waits on it.