Excavating Legacy ETL: The AI Never Asserts a Fact It Could Look Up

Every platform modernisation programme has the same first act, and it is never the interesting one. Before anyone gets to write a line of dbt, somebody has to work out what the current thing actually does.

In the SAP BusinessObjects Data Services case — BODS, if you’ve had the pleasure — that means inheriting a few hundred dataflows whose authors left the organisation years ago. The logic lives in exported XML. The business rationale lived in the heads of people who are now at other companies. Nobody can tell you which flows are duplicates, which ones nothing reads any more, or what any given one is for. There is a spreadsheet somewhere claiming to be an inventory. It is wrong in ways nobody can enumerate.

This is archaeology. It is also, on paper, the exact shape of problem that large language models should eat for breakfast: mountains of semi-structured text, pattern recognition, summarisation into prose a human can read.

So I tried the obvious thing, and the obvious thing failed in a way that turned out to be the most useful finding of the whole exercise.

Why pointing an LLM at the XML doesn’t work

Paste a BODS dataflow export into a chat window and ask what it does, and you get an answer. It is fluent, well-structured, and mostly correct.

Mostly is the problem.

The output is a blend of two very different things: columns and mappings that are genuinely in the XML, and plausible-sounding business context that is not. AMOUNT_USD is a currency-normalised revenue measure — fine, that’s a reasonable inference. But it will also tell you, with identical confidence and no change of register, that the flow runs nightly, or that STATUS follows the standard active/inactive convention, or that a column feeds the finance dashboard. None of those facts are in the file. They are priors about what ETL usually looks like, rendered in the same voice as the facts.

For most LLM tasks that’s an acceptable cost. For this one it’s fatal, because on a migration the facts are the deliverable. A spec that gets the join predicate subtly wrong doesn’t fail loudly — it produces a fact table where every row has silently been stamped with the wrong foreign key. You find out during reconciliation, six weeks and a lot of goodwill later.

The failure mode isn’t that the model can’t read the XML. It reads it fine. The failure mode is that the output gives you no way to tell which sentences came from the file and which came from the model’s expectations, and a human reviewer holding a 40-page spec cannot re-derive every claim by hand. That’s the whole reason you asked for help in the first place.

So I stopped trying to make the model more accurate, and changed what it was allowed to do.

The one rule the whole design hangs on

The system I ended up with — etl-archaeologist — is built around a single constraint:

The AI never asserts a fact it could look up. The deterministic layer never interprets.

Two layers, one hard boundary. A plain Python layer parses the BODS exports into a fact store: sources, targets, columns, datatypes, mapping expressions, column lineage, join predicates, cross-flow dependencies. No model involved. Same input, same facts, every time.

An agent layer then reads that store and writes the parts a script genuinely cannot: what the pipeline is for, what the grain and keys probably are, whether to lift-and-shift it or redesign it, whether to migrate it at all.

That split sounds like process pedantry. It isn’t — it’s what makes the output reviewable. Every factual section of a finished spec traces to a row in a database. Every interpretive sentence carries (inferred — confirm with SME). A reviewer reading the spec knows, at every line, whether they’re checking a fact or challenging a judgement. Those are completely different cognitive tasks and mixing them is what makes AI-generated documentation exhausting to review.

Layer one: facts

The extractor is unglamorous on purpose. lxml in, DuckDB out, about 150 lines:

CREATE TABLE dataflow      (df_name TEXT, job TEXT, project TEXT, source_file TEXT);
CREATE TABLE reads         (df_name TEXT, object_key TEXT, datastore TEXT, table_name TEXT);
CREATE TABLE writes        (df_name TEXT, object_key TEXT, datastore TEXT, table_name TEXT);
CREATE TABLE column_def    (df_name TEXT, col_name TEXT, datatype TEXT, mapping_text TEXT);
CREATE TABLE column_lineage(df_name TEXT, target_col TEXT, src_table TEXT, src_col TEXT);
CREATE TABLE source_column (df_name TEXT, datastore TEXT, table_name TEXT, col_name TEXT);
CREATE TABLE predicate     (df_name TEXT, predicate_text TEXT);
CREATE TABLE predicate_ref (df_name TEXT, src_table TEXT, src_col TEXT);

Predicates get stored twice, which looks redundant until you need it. predicate keeps the join or filter expression as verbatim text, because that’s what has to be reproduced exactly on the target platform. predicate_ref holds the same expression exploded into the individual source columns it touches — the extractor regexes table.column references out of the predicate and keeps the ones that resolve to a real source table. Without that second table, any column used only as a join key looks unreferenced, and the dead-code query below would confidently recommend dropping the very columns holding the estate together.

The one piece that needed actual thought is column lineage. A BODS dataflow isn’t a single mapping step; it’s a chain of transforms, and most columns pass through several of them untouched before landing in the target. If you only record each hop, the final target column’s origin is a graph walk away. So the extractor threads origins forward hop by hop — a mapped column records the source columns its expression references, a generated column (a surrogate key, an SCD date) records that it was generated, and a pass-through column inherits whatever the upstream column already knew:

else:  # pass-through: inherit the upstream column's origin
    prev = acc.get(frm, {"src": set(), "expr": "«pass-through»", ...})
    info = {"src": set(prev["src"]), "expr": prev["expr"], ...}

By the last transform, every target column knows the source columns it came from and the expression that produced it. That’s the fact an SME can confirm or deny in five seconds, and it’s the one an LLM is most tempted to smooth over.

The thing I underrated going in: this layer is not just an input to the AI. It’s the test oracle. Because it’s exact and reproducible, you can later use it to check the agent’s homework — which turns out to be the entire trick.

Layer two: the questions one file can’t answer

A second deterministic pass looks across all pipelines at once, because there is a whole class of question that no amount of careful reading of a single export can answer. “Is this a duplicate?” is unanswerable from inside the file.

Four queries do most of the work. Dependencies are a join between who writes an object and who reads it. Duplicate candidates are pairs of flows writing the same target, scored by column-set Jaccard overlap. Dead-flow candidates are flows nothing else reads. And dead code — my favourite, because it’s pure profit — is source columns pulled into a flow and then never referenced by any mapping or any predicate, which is where predicate_ref earns its place:

SELECT sc.df_name, sc.table_name, sc.col_name
FROM source_column sc
WHERE NOT EXISTS (SELECT 1 FROM column_lineage cl
                  WHERE cl.df_name=sc.df_name AND cl.src_table=sc.table_name AND cl.src_col=sc.col_name)
  AND NOT EXISTS (SELECT 1 FROM predicate_ref pr
                  WHERE pr.df_name=sc.df_name AND pr.src_table=sc.table_name AND pr.src_col=sc.col_name)

Across seven pipelines that found seven columns being extracted for no reason at all. On a real estate that number is a rounding error on the migration effort you don’t have to spend.

Worth being precise about what “dead” means here, because it’s narrower than it sounds. The column never reaches the target, so nothing downstream of this flow can be reading it — that part is safe. What it doesn’t mean is that the column is dead in the source system. Another flow may well use it, and something outside BODS almost certainly does. The finding is “stop pulling this column into this pipeline”, not “drop this column”.

Jaccard — intersection over union — is about as crude as similarity metrics get, and I chose it for that. It needs no tuning and no threshold anyone will argue about in a workshop, and the hard part of the question has already been answered by the join: these two flows write the same table. The score only has to rank what’s left for a human to look at. I don’t know it’s the right metric. I know it hasn’t yet surfaced a pair that wasn’t worth two minutes of attention.

Notice what all four have in common: they’re detection, not judgement. Exact, exhaustive, auditable, and completely free of opinion. Which is exactly what you want feeding the next layer.

Layer three: making “look it up” cheaper than guessing

The boundary between the layers only holds if the agent has an easy way to actually look things up. So the fact store — the DuckDB file — gets a fact server in front of it: a read-only MCP interface exposing list_pipelines, get_pipeline_facts, rationalisation_report, and a general query for anything the first three don’t cover. Store is the data; server is the only door the agent gets to walk through, and the door is deliberately one-way:

def _query(sql: str):
    s = sql.strip().lower()
    if not (s.startswith("select") or s.startswith("with")):
        raise ValueError("read-only fact store: only SELECT / WITH queries are allowed")
    return _q(sql)

This is the part I’d most encourage people to steal. Telling a model “don’t hallucinate” is a wish. Giving it a cheap, obvious, structured way to get the real answer is a design. When the ground truth is one tool call away, the model reaches for it — and when it does invent something anyway, the same tool is sitting right there to catch it.

Layer four: the part that actually needs judgement

With facts handled, what’s left for the agent is genuinely interesting, and it’s more than prose polish.

Take the dead-flow signal. The detector flagged four candidates, all with identical structural evidence: nothing else in the corpus reads your output. Deterministically they are indistinguishable. And yet the right answer differs for every one of them, because “no downstream consumer in the BODS metadata” means completely different things depending on what the flow is:

• A monthly sales aggregate with no downstream reader isn’t dead. It’s terminal — the consumer is a BI tool the fact store cannot see. Keep, and go confirm the dashboard exists.
• A conformed product dimension with no downstream reader is the same story, only more so — a conformed dimension exists precisely so that things outside the warehouse can slice on it. Keep, verify.
• A customer SCD2 history table — same shape again. It’s a history mart. Terminal by design.
• A two-column pass-through in an isolated Legacy project, reading a source table literally named OLD_ACCOUNTS, writing to a staging area, referenced by nothing: that one is a genuine orphan. Retire.

One signal, four different verdicts, and the discriminator in every case is context that lives nowhere in the metadata — naming conventions, which datastore the target sits in, what kind of table it is, whether “terminal” is normal for that kind of table. That’s the work. The detector found the candidates; the agent adjudicated them; and it overruled the detector three times out of four, which is precisely what you want a judgement layer to do.

The second thing the agent is good at is smelling migration risk in an expression. From the order fact load:

ifthenelse(ORDERS.CURRENCY = 'USD', ORDERS.AMOUNT, ORDERS.AMOUNT * 0.66)

A hard-coded FX rate. One rate, for every currency, for all time. The agent flagged it — and then, correctly, refused to fix it: preserve 0.66 exactly through cutover so reconciliation is byte-for-byte, and raise the rate-table question as a deferred redesign afterwards. That’s the right call and it’s a slightly subtle one. The instinct on seeing bad code is to improve it. The instinct that survives a migration is to reproduce it faithfully and change it in a separate, visible step.

The find I liked most, though, came out of the duplicate pair. Two flows both writing DIM_CUSTOMER, 60% column overlap, obviously redundant. But look at how each one derives STATUS:

DF_DIM_CUSTOMER          ifthenelse(CUSTOMER.ACTIVE_FLG = 'Y', 'ACTIVE', 'INACTIVE')
DF_DIM_CUSTOMER_REFRESH  ifthenelse(CUSTOMER.ACTIVE_FLG = 'A', 'ACTIVE', 'INACTIVE')

Same column, same source, different sentinel. 'Y' in one, 'A' in the other. Merge those two flows by picking whichever expression you happened to be looking at, and you silently flip the active/inactive status of some portion of the customer base. It’s a two-character difference across two files, and it went straight into the report as a hard blocker on the merge. That’s the class of thing manual archaeology misses at 3pm on a Thursday, and it’s a good illustration of what the two-layer split buys you: the comparison was deterministic, the significance was judgement, and neither layer could have produced the finding alone.

Fixed templates, because consistency is a feature

Every agent-authored artefact fills a fixed template — one for pipeline specs, one for the portfolio report, one for verification verdicts. Same headings, same order, every run, with - none where a section is empty rather than the section quietly vanishing.

This is a small thing that pays continuously. Consistent structure means specs diff cleanly — against each other and against their own previous versions — reviewers build muscle memory, and changing the house style is a one-file edit instead of a re-prompt. Mostly it kills a whole category of drift you’d otherwise catch by feel, late, in review.

The gate: assume the spec is wrong

Here’s the honest bit. Everything above still leaves you with output from a non-deterministic process. Constraints reduce the error rate; they don’t take it to zero. Trusting the specs because the architecture is nice would be exactly the mistake I started out trying to avoid.

So the last step is an adversarial verification pass. One subagent per spec, with an explicit brief: try to break it. Assume every factual claim is wrong until ground truth proves it right. Do not read the spec and then go looking for support — establish ground truth independently first, from the fact store and the raw XML, then extract every factual claim from the spec and classify it: SUPPORTED, UNSUPPORTED, CONTRADICTED, or MISLABELLED.

The output is a claim-by-claim table with a PASS/FAIL verdict:

That second row is the one that made me trust the design. The verifier didn’t just check that the stated facts were right; it checked that a claim of absence was honest. “There are no filters here” is exactly the kind of statement that’s easy to write and easy to be wrong about.

The obvious objection is that I’ve now got a non-deterministic process checking a non-deterministic process, and that’s fair. The verifier is an agent too; it can miss things. What it can’t easily do is miss things in the same direction as the author, because it isn’t reading the author’s reasoning — it goes back to the fact store and the raw XML and builds its own picture first. Two independent passes over the same ground truth have to fail in correlated ways to let an error through, which is a meaningfully harder thing to do by accident. That’s a reduction in risk, not a proof of correctness, and it’s exactly why the gate needs calibrating rather than trusting.

Which is what the planted errors are for. Two things came out of running the gate for real. First, on a deliberate planted-error test it caught every injected error while still passing the clean spec — so it isn’t just manufacturing objections to look diligent. Second, and more usefully, it caught a defect I wasn’t looking for: a spec that had been correct when written and had gone stale, because a new pipeline was added to the corpus afterwards and its dependency list no longer reflected reality.

That reframed the whole thing for me. I built the gate expecting to catch hallucination. What it actually earns its keep on is staleness — documentation drifting behind the source. Which, if you think about it, is the failure mode that has plagued every hand-written data dictionary ever produced. The difference is that here regenerating and re-verifying costs minutes, so the documentation can be treated as derived rather than authored, and re-derived whenever the source moves.

What I’d tell someone starting this

Split facts from judgement before you write a single prompt — not as a review step afterwards, as an architecture. If a script can determine it, a script should determine it, and the model should be reading the script’s output rather than the raw source. Every other decision here fell out of that one, including the ones I didn’t see coming.

The corollary is that ground truth has to be cheap to reach. A read-only fact server does more for accuracy than any amount of prompt engineering about accuracy, because it changes the economics rather than the instructions: guessing has to be the more expensive option, or eventually it won’t be the one taken.

Then label inference at the sentence level. A disclaimer at the top of a document is decoration — a reviewer stops seeing it by page two. (inferred — confirm with SME) sitting in front of the specific claim is a routing instruction: this line is for the data owner, that line is for the migration engineer.

And when you verify, verify against the source rather than the summary. A checker that reads the spec and goes looking for support will find it every time; one that reconstructs ground truth independently and then hunts for contradictions is doing a completely different job, and only the second one tells you anything you didn’t already believe.

Then the caveats, because I said I’d be honest about them.

The corpus I’ve been describing is synthetic — deliberately planted with a known answer key so I could measure whether the method recovers the truths rather than just producing confident-looking documents. It’s a test of the approach, not a sample of any real estate, and no production data goes anywhere near it. Real BODS XML also nests its attributes differently to my samples, so pointing this at a live export means recalibrating the XPath in the extractor once. That’s the only part that changes — but it does have to change, and I’d expect it to be a fiddlier afternoon than it sounds.

The larger limit is that detection only sees what BODS records. It cannot see Tableau, Athena, or the analyst with a scheduled query nobody remembers writing. That’s precisely why every retire and merge recommendation is gated on a human plus external-consumer evidence rather than fired automatically — the tool is allowed to nominate, never to decide.

None of this made the model smarter. It made the model’s job smaller. The interesting judgement — is this dead, is this a duplicate, is that hard-coded rate a bug or a contract — is exactly as hard as it was before, and it’s still the reason anyone is doing the work. What changed is that it’s no longer tangled up with a hundred low-level facts a database can answer exactly, and a reviewer can finally tell the two apart at a glance.

Archaeology, done properly, is mostly about knowing which layer you’re digging in.