Migrating twenty years of transformation logic out of a legacy ETL tool is a governance exercise wearing an engineering costume. Here is the sequence that works.
Key takeaways
- Never migrate logic you have not first proven is still used. Typically a third of it is not.
- Parallel running with automated reconciliation is not optional — it is the entire risk strategy.
- The hard part is undocumented business rules, not SQL translation.
- Decommissioning is a phase with a budget, not an afterthought.
Why organisations migrate at all
Legacy ETL platforms are not bad software. Many of them are extremely capable, and the estates built on them have run reliably for a decade or more. Organisations move for four reasons, usually in combination: licence cost that scales badly, a shrinking pool of people who can maintain the tooling, an inability to version and review transformation logic like ordinary code, and the fact that the target warehouse can now do the transformation work faster than the ETL server can.
What none of those reasons justify is a rewrite for its own sake. If your existing pipelines are stable, understood and affordable, the correct decision is frequently to leave them alone and spend the money on something with a return.
The real problem is archaeology, not translation
Translating a transformation from one syntax to another is mechanical work. The difficulty in every ETL migration we have run is that a meaningful proportion of the logic encodes business rules that exist nowhere else — a currency conversion that uses a rate captured on the third working day, a customer-type reclassification agreed with a regional director in 2014, an exclusion filter added after a specific audit finding.
Nobody remembers why. The person who wrote it left. The rule is nevertheless load-bearing, and removing it will change a number that somebody is measured on.
Assume that every unexplained filter in a legacy pipeline is correct until you can prove otherwise, and that proving otherwise takes a conversation with a human, not a code review.
Phase one: inventory and usage analysis
Before any code is written, build two lists.
The first is the job inventory: every scheduled pipeline, its sources, its targets, its schedule, its owner of record and its last successful run. This usually exists in some form and is usually wrong in interesting ways.
The second, and far more valuable, is the consumption map: for every table the pipelines produce, who or what actually queries it, and when. Warehouse query logs give you this directly. The results are consistently sobering. On a recent engagement covering 340 scheduled jobs, 112 produced tables that no user or downstream process had queried in six months.
That is a third of the estate that does not need migrating. It needs a conversation and then a decommissioning ticket. Doing this analysis first is the single highest-return activity in the entire programme, and it takes about a week.
Classify what remains
| Class | Definition | Action |
|---|---|---|
| Retire | No consumption in six months, no regulatory retention need | Confirm with owner, switch off, delete after a grace period |
| Lift | Simple extract-and-load with no meaningful transformation | Replace with a managed connector; do not rewrite |
| Rewrite | Real transformation logic, actively consumed | Reimplement as tested, version-controlled SQL models |
| Rethink | Logic that exists only to work around a limitation of the old tool | Redesign against the target architecture |
The Rethink category is where the genuine wins hide. A large share of legacy ETL complexity is compensating for constraints that no longer apply — row-by-row processing because the engine could not do set-based operations, staging files because memory was limited, custom scheduling because the orchestrator could not express a dependency.
Phase two: build alongside, never in place
The new pipeline runs in parallel with the old one, writing to a separate schema, for a period long enough to cover a full business cycle. For most enterprises that means at least one month-end, and for anything touching financial consolidation, one quarter-end.
Parallel running only works if reconciliation is automated. A person eyeballing two spreadsheets will not catch a 0.02% variance in a single dimension member, and that variance is exactly what will be discovered by an auditor four months later.
What the reconciliation harness should check
- Row counts by partition, for every table, every run.
- Aggregate sums for every numeric measure, sliced by each conformed dimension independently.
- Row-level hashing on the natural key plus the measures, so mismatched rows can be listed rather than merely counted.
- Distribution comparison on categorical columns, which catches mapping errors that preserve totals.
- Timing, so you know the new pipeline meets the same SLA before you rely on it.
Every discrepancy gets a ticket and one of three dispositions: the new pipeline is wrong and must be fixed; the old pipeline was wrong and the business needs to know; or the difference is intentional and must be documented. That third category matters. Migrations are the moment when historic errors surface, and the reconciliation log becomes the audit trail explaining why a restated figure changed.
Phase three: cut over by consumer, not by pipeline
The temptation is to cut over pipeline by pipeline. Resist it — that leaves consumers reading from a mixture of old and new tables, which is precisely the state in which inconsistencies become invisible.
Cut over by consumer domain: move the finance reporting suite, in full, once every table it reads has passed reconciliation. Then sales. Then operations. Each domain has a single, dated cutover with a named owner who signs it off, and a documented rollback that has been tested rather than assumed.
Phase four: decommission with a budget
Decommissioning is where migrations quietly fail. The new pipelines run, the consumers are migrated, and then the old platform stays switched on "just in case" — for years, still consuming licences, still needing patching, still requiring the one remaining person who understands it.
Treat it as a phase with its own plan:
- Freeze change on the legacy estate the day the first domain cuts over.
- Switch off jobs domain by domain, immediately after sign-off, keeping the platform available but idle.
- Retain the platform read-only for one full regulatory cycle if retention rules demand it.
- Archive the transformation logic to version control as documentation — it is the only record of some of those business rules.
- Terminate licences on a diarised date with a named accountable owner.
What this actually costs in time
For a mid-size estate — 200 to 400 scheduled jobs, three to five consumer domains — a realistic plan is six to nine months end to end: three weeks of inventory and usage analysis, three to four months of build and parallel run, six to ten weeks of staged cutover, and a decommissioning tail that runs to the next licence renewal.
Programmes that promise three months are almost always excluding the parallel run, which is to say they are excluding the risk management. That is a decision an organisation can make deliberately. It should not make it by accident.
The payoff, stated honestly
You will not get a dramatic performance improvement from ELT alone — modern warehouses are fast, but so were well-tuned ETL servers. What you get is different in kind: transformation logic that is reviewable in a pull request, tested on every commit, documented by construction, and understandable by anyone who reads SQL. Combined with the third of the estate you retired, that is usually where the business case closes.
Frequently asked questions
Yes, and for stable estates it is often the better choice — retire the dead jobs, replace new development with ELT, and let the legacy estate shrink by attrition. It takes longer but avoids a large capital request and concentrates risk far less.
At minimum one full business cycle including a month-end. For financial consolidation, one quarter-end. Anything shorter and you have not exercised the reporting paths that only run periodically.
Keep it, document that it is unexplained, and flag it in the reconciliation log. Removing an unexplained rule during a migration means you cannot tell whether a resulting variance came from the rule or from a migration defect.
