The bottleneck wasn't the warehouse: cleaning legacy master data with a local SLM

The client wanted to talk about the warehouse. It was old, it was slow, and everyone agreed it needed to be replaced. What nobody wanted to say out loud was that the warehouse was not really the problem. Every report that mattered sat on top of master data that was wrong in a dozen small ways, and no faster platform was going to fix that. Migrating dirty data to a modern warehouse just gives you the wrong answers faster.

So before touching the platform, I went after the data. The tool I used was the local SLM data cleaner I have been building in the open. This post is about what it did to a real legacy warehouse, and why a small model you own turned out to be the right instrument for the job.

What “dirty” meant here

The mess was the ordinary kind that every long-lived SAP-style warehouse accumulates. One supplier existed as three records with three spellings. Countries were written as “Germany”, “Deutschland”, and “DE” in the same column. Amounts and dates arrived in German and US formats, sometimes in the same table. Missing values were encoded five different ways, so half of them did not register as missing at all.

None of this is dramatic on its own. The damage is cumulative. A report that groups spend by supplier counts the same vendor three times. A join across two tables silently drops rows because one side says “DE” and the other says “Germany”. An aggregate over a date column quietly misreads a US date as a German one. The numbers on the dashboard looked precise and were wrong, and the people relying on them had stopped fully trusting them without being able to say why.

Why the usual fixes did not fit

There are two standard ways to clean this, and neither worked here.

The first is to write rules. Trim the whitespace, map the country names, normalise the formats. Rules are excellent at the cases you thought of, and they never finish, because the long tail of misspellings and odd abbreviations is effectively infinite. Every week of cleaning surfaced three new variants no rule anticipated.

The second is to point a large language model at it. Off the table. This is a German company, the data is customer and supplier master data, and sending it to a cloud API was not something anyone was willing to defend under GDPR. For this client it was not even primarily a legal calculation. It was a matter of posture. The data does not leave the building.

That left the option the SLM cleaner is built for: a model small enough to run in-house, fine-tuned to clean this specific standard, with rules kept as a safety net rather than the whole strategy.

What I deployed

The short version is that the company’s data standard is written down once as a readable file, the system invents thousands of synthetic dirty-to-clean examples from it, and a small model learns to clean records the way the rules would, plus the long tail no rule covers. Every record the model touches is re-checked against the rulebook, anything uncertain goes to a manual review queue, and every single change is written to an append-only audit log. I wrote up the tool itself, and how it grew from a laptop demo, in a separate post; the code is on GitHub.

For this deployment the relevant part is where it ran: entirely inside the client’s environment, offline, with the container’s network stack removed. The warehouse’s master data was cleaned without a single record leaving the premises.

What it changed

Because the deployment is air-gapped and the data is confidential, there is nothing here I can screenshot, and that is the point of the design rather than a limitation of it. What I can describe is the shape of the change.

The same supplier stopped appearing as three. Once entities were deduplicated and normalised, spend and exposure reports finally counted things once. Country, currency, date, and amount formats became consistent across tables, so joins stopped dropping rows and aggregates stopped disagreeing with each other for reasons nobody could trace. And for the first time the compliance team had lineage: every cleaning decision carried a record of what changed, with the exact version of the model and the convention that produced it. The old warehouse had never been able to answer “why does this value look like this”, and now it could.

The honest headline is that the migration everyone had been arguing about got easier, because it was no longer a migration of a mess. The data going into the new platform was data the business already trusted.

What I’d do differently next time

Three things.

Clean before you migrate, not after. It is tempting to lift the data first and clean it in the new warehouse where the tooling is nicer. That means your cutover, your validation, and your first months of reports all run on the bad data. Cleaning at the source, before the move, makes every later step easier and cheaper.

Set the review-queue thresholds per field, not globally. I started with one confidence threshold for everything, which sent far too much to manual review in the first weeks. Some fields deserve a hair-trigger, and some almost never need a human. Deciding that up front, field by field, would have saved a lot of early queue-clearing.

Start from the arguments, not the standard. The written data standard and the data people actually enter are two different documents. The convention file got good once I stopped seeding it from the official rulebook and started seeding it from the edge cases the data stewards already argued about. Those arguments are where the real standard lives.

---

If your reports sit on master data you no longer fully trust, and that data cannot go to the cloud, this is exactly the kind of work I take on. Get in touch.