The pipeline that moves your production numbers into the reporting database was probably written by someone who no longer works there. It runs on a Windows box under a service account nobody remembers the password for. It is a SQL Server Agent job that calls a stored procedure that calls three more, and somewhere in that chain is a WHERE clause that quietly drops any well with a null API number. You found that out during close, the hard way.
That is legacy ETL. Not the application the data lives in, but the plumbing that shovels it from one place to another. SSIS packages, stored-procedure chains, hand-rolled Python on a cron, Access queries someone scheduled through Task Scheduler in 2016. It works until it doesn’t, and when it doesn’t, exactly one person can fix it. This post is about replacing that plumbing with something you can test, watch, and hand off, and doing it without a weekend where everything is down and everyone is praying.
How legacy ETL actually fails
The failures are boring, which is why they persist. Nobody funds a rewrite of a job that runs fine most months.
Start with the fact that none of it is in version control. The SSIS package lives on the SQL Server, exported to a .dtsx file if you are lucky. The stored procedures are in the database. You cannot git diff any of it, so you cannot see what changed between the month it worked and the month it didn’t. When two people edit the same proc, the second one wins and the first one’s change is gone with no record it ever existed.
Then there is lineage, or the total absence of it. A number lands in a report and you have no way to trace it back to the source column it came from. When an auditor or a buyer’s diligence team asks “where did this production figure come from,” the honest answer is a shrug and forty minutes of reading procedure bodies.
There are no tests. The logic that allocates production across working interests, the daily-to-monthly rollup, the currency conversion: none of it is checked against known inputs. You find out it broke when the numbers look wrong, assuming anyone looks. And the logic itself is trapped. It is half in a stored procedure, half in the head of the engineer who wrote it, and the two halves do not always agree.
The worst part is the silence. A legacy job that fails loudly is a gift. Most of them fail quietly: the extract pulls zero rows because the source API changed a field name, the load succeeds because zero rows is technically valid, and the report shows last month’s numbers with this month’s date. Nothing alerts. Nobody knows until someone downstream notices the total didn’t move.
What you are moving toward
The target is not a single magic tool. It is a set of patterns that, together, fix the failure modes above. We have built this shape for operators enough times that it has stopped feeling like a decision and started feeling like the default.
Separate orchestration from transformation. Airflow decides when things run and in what order. It does not contain your business logic. The transformation lives in dbt, where each model is SQL you can read, test, and trace. This split does the most work of anything in the migration, because it means the “when” and the “what” can change independently. We make the case for this separation in decoupled Airflow, and it holds just as hard here.
Containerize the load tasks. Each extract-and-load job is a purpose-built Docker image that takes its source and target as arguments. You can run it on your laptop with the same command Airflow uses. No more “it only works on the ETL server.” We walk through building one of these in the containerized Meltano guide.
Config as code, secrets in a vault. The connection strings, the thresholds, the schedule: all of it in the repo, reviewed in a pull request, not set through a UI where it silently drifts. Credentials go in a secrets manager and get injected at runtime, never baked into a script or a package. If your Airflow config still lives in someone’s memory, that is its own time bomb.
Migrate by wrapping, not rewriting
Here is the part people get wrong. They look at the legacy pipeline, decide it is garbage, and schedule a cutover: freeze the old system, build the new one, flip the switch. In upstream that switch usually lands the week before monthly close, because that is when someone finally has time, and that is exactly when you cannot afford to be wrong.
Do not do the big-bang cutover. Displace the pipeline incrementally instead.
The sequence we run:
- Wrap the legacy job. Before you change anything, put the existing job under orchestration as-is. Run the SSIS package or the stored proc from an Airflow task, unchanged. You gain scheduling, logging, and alerting immediately, and you have changed nothing about the logic. This alone kills the “nobody knew it failed” problem.
- Build the replacement alongside it. Now write the new containerized extract and the dbt models, targeting a parallel schema. The old job keeps writing to production. The new one writes to a shadow.
- Run both in parallel and diff the output. For a few close cycles, both pipelines run. Every day you compare the new output against the old, row by row, column by column. This is where the real work is, and it is where you learn what the old logic actually did versus what everyone believed it did.
- Cut over, then retire. Once the outputs match for long enough that you trust it, point the consumers at the new tables. Leave the old job wrapped and idle for a cycle or two as insurance. Then delete it.
The parallel-run phase costs you compute and attention for a month or two. That is the price of not betting your close on a rewrite you have never seen produce a correct number.
The output diff is where the truth lives
The equivalence check deserves its own paragraph because teams skip it and regret it. You are not trying to prove the new pipeline is correct in the abstract. You are proving it produces the same numbers as the thing the business already trusts, and then explaining every place it doesn’t.
Some differences are the new pipeline being right where the old one was wrong. That well the old proc silently dropped for a null API? It shows up now. The total is different, and the difference is a bug you inherited, not one you introduced. You need to be able to point at each discrepancy and say which system is correct, and you need the business to agree before you cut over. Wire the diff into the pipeline itself as a quality gate so it runs every cycle, not as a one-time spreadsheet exercise. We go deeper on quality gates in an Airflow and dbt stack.
Preserve the logic, don’t blindly copy it
The stored procedure that has run for eight years encodes real business knowledge. Somewhere in that CASE statement is a rule about how a particular purchaser reports volumes, and it is there because someone got burned once. Throw the proc away and reimplement from a clean spec and you will reintroduce the exact bug that rule was written to prevent.
Do not blindly port it either. A thousand lines of nested procedure translated verbatim into a thousand lines of dbt is not a migration, it is a lateral move with extra steps.
The approach that works: treat the legacy code as the authoritative record of the requirements, and decompose it before you rebuild. Read the proc. Pull out each discrete rule and write it down in plain language. What does this join do, what does this filter exclude, why does this column get coalesced. That written decomposition becomes your spec, and the old code stays authoritative until the new model reproduces its output. Then you rebuild from the spec as clean dbt models, and the output diff tells you whether you captured every rule. The logic survives. The mess does not.
What actually gets better
The pitch for this work is not “modern stack” for its own sake. It is a short list of concrete things that improve, and you can check each one.
Testability comes first. Your allocation model now has unit tests that assert on known inputs. Change it, run the tests, know in seconds whether you broke the working-interest math. The old proc offered nothing like that.
Observability comes next. Every run has logs, timing, and row counts. A freshness check fires when the source goes stale. The silent failure that used to surface at close now pages someone the morning it happens.
Then onboarding. A new engineer clones the repo and reads it. The pipeline is dbt models, containerized loads, and Airflow DAGs that are mostly configuration. There is a history in git of why every non-obvious decision was made. Compare that to “let me introduce you to Dave, he’s the only one who understands the reporting job.” That kind of dependency on one person is the thing we design against, and it is the whole argument for building for the next engineer.
We ran this exact migration for an upstream operator whose data moved between twenty-plus vendor systems on a pile of ad hoc scripts. Replacing that plumbing with containerized, orchestrated pipelines is documented in our case study on centralizing a fragmented upstream data estate. The point worth stealing from it: the value did not come from any one tool. It came from resolving the logic once, in a place everyone could see, instead of re-deriving it in every script and spreadsheet forever.
If the pipeline that produces your close numbers is a black box that one person maintains, you do not have a data pipeline. You have a liability that happens to still be running. Wrap it first. You can decide what to rebuild after you can finally see it.