Excavating Legacy ETL: The AI Never Asserts a Fact It Could Look Up A practitioner who inherited several hundred SAP BusinessObjects Data Services (BODS) dataflows built etl-archaeologist, an open-source tool that splits ETL migration analysis into a deterministic Python layer that parses BODS XML exports into a fact store and an agent layer that only interprets those facts, after finding that pasting BODS exports into a chat window produced fluent specs mixing real XML mappings with invented business context such as nightly run schedules and finance-dashboard consumers. The tool enforces the rule that "The AI never asserts a fact it could look up," tagging every interpretive sentence with "(inferred - confirm with SME)" so reviewers can tell facts from judgements. The author argues the failure mode is not misreading XML but the inability to distinguish file-derived claims from model priors, which on a migration can silently stamp a fact table with the wrong foreign key and surface only at reconciliation. Assisted by Claude. The migration work, the design and the conclusions are mine. 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 https://www.getdbt.com/ , somebody has to work out what the current thing actually does . In the SAP BusinessObjects Data Services https://help.sap.com/docs/SAP 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. 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 system I ended up with — etl-archaeologist https://github.com/justinf555/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. The extractor is unglamorous on purpose. lxml in, DuckDB https://duckdb.org/ 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. 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 nameFROM source column scWHERE 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. 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 https://modelcontextprotocol.io/ 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: python 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. 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: 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. 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. 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: CLAIM: Dead code: OLD ACCOUNTS.CLOSED DT read but never mappedSTATUS: SUPPORTEDEVIDENCE: facts unused source columns = {OLD ACCOUNTS, CLOSED DT} ; XML declares CLOSED DT in