Chapter 37: Migrating Legacy Pipelines

"The migration was approved on the understanding that nothing would change. Nine weeks in we had found thirty-one places where the numbers were wrong, and every one of them was a conversation about whether we were allowed to fix it."

Overview

This is the work most data engineers are actually hired to do, and it is the least written about, for a reason that is worth naming immediately: a migration produces no new features, and its success condition is that nothing changes.

Everything in Parts I through VI assumed a greenfield. Real platforms are built next to something that already runs — cron scripts, stored procedures, a GUI ETL tool nobody has credentials for, and a spreadsheet that turns out to be load-bearing. All four are in Kestrel's estate, and this chapter migrates them.

Four things this chapter makes measurable, via code/migration_lab.py:

The order. Twelve legacy jobs, scored by reach, risk, and difficulty. The recommended first job is neither the biggest nor the easiest, and the instinctive orderings are both close to the worst available (§37.4).

The differences. Sixty days of shadow running, 687 differences, classified. More of them are the legacy system being wrong than the new one — 196 against 146 — and what you do about that is a decision you must make before you start, because it is political (§37.7).

Readiness. A cutover rule with weights, and the item that is always deferred and matters most: a rehearsed rollback (§37.9).

And the cost. Planned overlap 60 days, actual 147. $9,348 becomes $22,902.60, of which the engineering line exceeds the compute line — and the overrun alone takes 579 days of steady-state savings to repay (§37.10).

37.1 Why This Is Hard, and It Is Not the Code

The technical work is usually the easy part. Rewriting a 240-line shell script as a dbt model and an Airflow DAG is a week. Four things make a migration hard, and none of them is the rewrite.

Nobody knows what it does. Not "the documentation is out of date" — there is no documentation, the author left, and the only specification is the output.

Nobody knows who uses it. A cron job writes a CSV to a share. Chapter 25 §25.12's usage measurement does not reach a file share, and the consumer you did not find is the one who escalates.

The old system is wrong in ways people have adapted to. Downstream reports have correction factors. Analysts know to ignore Tuesdays. Fixing the bug breaks the adaptation (§37.7).

And there is no new feature to point at. A migration is a request for engineering time whose entire benefit is "the same numbers, from a system we can change." That is a real benefit and it is a hard sell, and §37.13 is about that.

37.2 Take the Inventory Before You Read Anything

The first instinct is to read the legacy code. It is the wrong first move, and it is how a migration spends its first quarter.

Twelve jobs at 400 lines each is 4,800 lines of undocumented shell and SQL. Reading it takes weeks, and at the end you know what the code does and still not who depends on it or which parts matter.

Everything in the inventory below is discoverable without reading a line:

kind            cron | stored proc | GUI tool | spreadsheet
consumers       who reads its output? (grep, query logs, ask)
has_owner       is there a name?
has_tests       is there anything that fails?
size            lines, or "unknown" for a GUI tool
schedule        how often, how long
undoc_deps      inputs not in any config -- found by running it
critical        would a person notice within a day?

Kestrel's estate, in three weeks:

by kind:    cron 5, stored proc 4, GUI 2, spreadsheet 1
no owner:   10 of 12
no tests:   11 of 12
undocumented dependencies, total: 36

🔎 Read the Plan — how to find the consumers you did not know about

The consumer count is the single most important field and the hardest to get right. Four methods, in increasing order of what they catch:

Grep the codebase for the table or file name. Catches other pipelines and misses everything else.

Query the warehouse's access history (Chapter 30 Case Study 2's method). Catches dashboards, notebooks, and service accounts — and misses anything that reads a file rather than a table.

Instrument the output. Add logging to the file read, or a SELECT trigger, or — for a file share — watch the access timestamps. Catches the long tail and takes a month.

Break it on purpose. Rename the output for four hours on a Tuesday and see who complains.

The fourth method is the only one that finds everybody, it is genuinely used, and it is irresponsible on anything business-critical. Kestrel used it on two jobs, both non-critical, both announced in advance to a channel nobody read — and both found a consumer the other three methods had missed.

The defensible version: schedule the outage, announce it properly, and make it short. A pre-announced four-hour outage of a non-critical output is a cheap, honest census. What is not defensible is doing it silently, which is what makes the technique controversial and is not what makes it useful.

37.3 The Four Kinds, and What Each Costs

A cron script is the good case. It is text, it is in version control or can be, and its behaviour is readable.

A stored procedure is readable and coupled. 1,420 lines of PL/pgSQL that also does the transaction management, the error handling, and — usually — some of the business logic that nobody realized was there.

A GUI ETL tool is the hard case, and it is worth being precise about why: the logic is not in a file you can read. It is in a proprietary repository, expressed as boxes and arrows, and the only faithful specification is the tool's own execution. Kestrel scores its two Informatica workflows at risk 25 and 21, the highest in the estate, with no lines of code to count.

And a spreadsheet is a different problem entirely, because a person is part of the pipeline.

⚠️ Failure Mode — the load-bearing spreadsheet

finance_close.xlsx has three consumers, an owner, and is business-critical. It is the only job in the estate with a named owner, which is not a coincidence: a person maintains it by hand every month.

What it does: pulls two exports, applies a set of adjustments, and produces the number that goes in the month-end close.

What makes it hard is not the formulas. It is:

• The adjustments encode judgment. Some are rules ("exclude test accounts"); some are decisions made monthly by a person who knows which accruals are real. Only the first kind can be migrated.
• The spreadsheet is the specification. There is no document describing what it should do; there is a spreadsheet that does it, and cells that reference cells.
• The owner is not the blocker; the owner is the asset. They know why every adjustment exists, and they are the only source for it.

The failure is treating this as a technical migration. Kestrel's first attempt rebuilt the formulas as a dbt model, matched the historical numbers, and was wrong the first month it ran, because that month's judgment adjustments had no equivalent.

The version that worked split the job in two: the rules became a model, and the judgment became an explicit input — a small, versioned table the finance owner fills in, with a comment field per row. The spreadsheet went away; the person did not, and was never supposed to.

The generalizable move: separate the parts that are rules from the parts that are decisions, and migrate only the rules. A "manual step" that survives a migration is not a failure of the migration — it is the correct home for a decision, and dressing it up as automation just hides who is making it.

37.4 The Order

--inventory scores each job and orders by (reach × risk) / difficulty:

job                          kind          reach  risk   diff    value
──────────────────────────────────────────────────────────────────────
vendor_sftp_pull.sh          cron             18    12    3.8     56.8
nightly_revenue.sh           cron             24    14    6.9     48.7
informatica_wf_inventory     gui              14    25   17.0     20.6
finance_close.xlsx           spreadsheet      12    11    7.0     18.9
sp_load_orders               storedproc       20    18   21.7     16.6
...
hourly_stock_sync.py         cron              6     2    4.6      2.6

The recommended first job is vendor_sftp_pull.sh, which is not the highest-reach job (nightly_revenue.sh, at 24) and not the easiest (legacy_ab_test.py, at 3.4).

📐 Design Decision — order by learning per week, not by size or by ease

Two instinctive orderings, and both are bad for the same reason: they optimize for the wrong thing in the first month, which is when you know least.

"Biggest first" — start with sp_load_orders, 1,420 lines, reach 20. The argument is that it is the most valuable and the rest is easier afterwards. What actually happens: the hardest job is attempted with the least knowledge of the estate, it takes a quarter, and the project has produced nothing visible when its first review arrives.

"Easiest first" — start with legacy_ab_test.py, one consumer, nobody would notice. The argument is momentum. What actually happens: you migrate something nobody cares about, learn nothing that transfers, and the second job surprises you exactly as much as the first would have.

(reach × risk) / difficulty optimizes for something else: how much you learn per week.

• High reach means the job matters, so migrating it is worth something on its own.
• High risk means it has undocumented dependencies, no owner, no tests — which is where the surprises live, and you want the surprises early, while there is schedule left to absorb them.
• Low difficulty means you find out soon.

vendor_sftp_pull.sh is 80 lines, has six consumers, is business-critical, has no owner, no tests, and two undocumented dependencies. It is a week of work that touches the ingestion path, the credentials story, the scheduling, and the reconciliation — every mechanism the rest of the migration will need, at small scale, on something that matters enough to be taken seriously.

And the last row is the honest one. hourly_stock_sync.py has an owner and tests, scores 2.6, and should be migrated last or never — it is the one legacy job that is fine.

37.5 The Strangler Fig

The pattern, and the reason it is named after a plant: the new system grows around the old one, taking over one output at a time, until the old one is doing nothing and can be removed.

Concretely, per job:

1. build the new pipeline, writing to a DIFFERENT output
2. run both, on the same schedule, from the same inputs
3. reconcile, daily, and classify every difference (37.7)
4. when the readiness rule passes (37.9), swap the consumers
5. leave the legacy job running, unread, for one more cycle
6. decommission -- and actually delete it

Step 5 is the cheap insurance — a month of a job nobody reads, against the cost of discovering a consumer you missed.

Step 6 is the one that does not happen. A legacy job left running "just in case" is a job that will still be running in three years, costing money, holding credentials, and appearing in every audit. Kestrel's rule: a decommission date is set at cutover, and the job is deleted, not disabled.

The alternative — a big-bang cutover — is defensible in exactly one case: when running both is impossible. A source system that can only feed one consumer, a license that permits one instance. If you can run both, run both, and §37.10 prices what that costs.

37.6 Reconciling: What "The Same" Means

"The outputs must match" is not a specification. Three things have to be decided before the first comparison:

At what grain? Row-for-row is strongest and often impossible — the new pipeline may legitimately produce different surrogate keys. Kestrel reconciles at the natural key, plus totals.

With what tolerance? Zero is the right default for counts and integer money. For anything derived from a float, a tolerance is unavoidable — and it must be a number, decided in advance, not "close enough."

Over what window? A daily comparison catches a systematic difference. A monthly one catches a boundary difference the daily one hides. Run both.

-- the daily reconciliation, per job
SELECT COALESCE(o.natural_key, n.natural_key) AS k,
       o.amount_cents AS old_amount,
       n.amount_cents AS new_amount,
       n.amount_cents - o.amount_cents AS delta
  FROM legacy_output o
  FULL OUTER JOIN new_output n USING (natural_key)
 WHERE o.natural_key IS NULL
    OR n.natural_key IS NULL
    OR o.amount_cents <> n.amount_cents;

The FULL OUTER JOIN is not optional. An inner join compares the rows both systems produced and is blind to the rows one system produced and the other did not — which is the most common difference and, per Chapter 36 Case Study 1, the one that hides.

37.7 The Differences, and Who Is Wrong

Sixty days of shadow running across the estate, 394,500 rows compared, 687 differences:

difference kind                count   share   verdict
──────────────────────────────────────────────────────────────
new pipeline bug                 146   21.3%   fix the new one
LEGACY bug                       196   28.5%   decide -- see below
known rounding                    91   13.2%   accept, document
timezone / boundary               81   11.8%   accept, document
legacy non-determinism           127   18.5%   cannot reconcile
source data changed               46    6.7%   neither is wrong
──────────────────────────────────────────────────────────────
TOTAL                            687

first 7 days:  238 differences
last 7 days:    17 differences

Two findings, and the first is the one that surprises everybody.

⚠️ Failure Mode — more differences are the legacy system's fault than the new one's

196 against 146 — a ratio of 1.34. The system you are migrating away from is wrong more often than the one you are building, and nobody plans for this because the migration was approved on the premise that nothing would change.

Why it happens is not mysterious. The legacy job has run for years without tests, without an owner, and without anything comparing it to an independent source. Chapter 23's entire argument, applied retroactively: nothing was measuring it, so its errors accumulated undetected.

What makes this hard is not technical. Each one is a conversation:

text "the new pipeline says revenue was $412,880 and the old one said $409,100. The new one is right. Do we: a) match the old one, so nothing changes ("bug-for-bug compatible") b) ship the correct number, and explain the step change c) fix the old one too, so they agree and both are right d) hold the migration until finance decides"

All four are chosen in practice, and (a) is chosen most often — because the migration's mandate was "nothing changes", and a step change in a reported number is exactly the thing that mandate forbids.

Bug-for-bug compatibility is a legitimate choice and a terrible default. It means shipping a new pipeline that deliberately reproduces a known error, with a comment explaining why, and a promise to fix it later that will not be kept because after cutover there is no forcing function.

What Kestrel decided, in advance, before the first shadow run — and deciding in advance is the whole recommendation:

• Correctness differences under a stated materiality threshold: ship correct, note it.
• Above it: the number's business owner decides, and the migration is not blocked while they do — the new pipeline emits both, in two columns, until they answer.
• In every case, the difference is documented, with the date and the decision.

The two-column trick is the part worth copying. It converts a blocking decision into a non-blocking one, and it makes the difference visible to the person who has to decide rather than described to them in a meeting.

And the second finding: the differences decay but never reach zero.

📏 Scale Note — 50.2% of the differences are irreducible

238 differences in the first week, 17 in the last. The decay is what tells you the migration is converging.

But the floor is not zero. Rounding, timezone boundaries, legacy non-determinism, and genuine source changes account for 345 of 687 — 50.2% — and no amount of work removes them.

Legacy non-determinism is the largest single irreducible category (127, 18.5%) and the most frustrating: a legacy job whose output depends on the order rows came back in, which depends on the query plan, which changes. You cannot reproduce it, so you cannot match it, and the only honest reconciliation is against a property rather than against the legacy output — totals, distributions, row counts.

The consequence for §37.9: a cutover rule that requires zero differences never fires. The rule must be "only differences of accepted kinds, all documented" — and getting that right in advance is what prevents a migration that is technically complete and organizationally stuck at 99.6%.

37.8 The Job Nobody Owns and Nobody Can Read

Two of Kestrel's twelve jobs are GUI ETL workflows. No source file, no version control, and — as it turned out — no license to open the designer, which had lapsed.

Three approaches, in the order they were tried:

Read the generated SQL. Many GUI tools log the SQL they execute. This worked for one of the two and produced a specification good enough to rebuild from.

Treat it as a black box and match the output. Feed known inputs, capture outputs, and build something that reproduces the mapping. Slow, and it only covers the input space you exercised.

Ask the business what it is supposed to do. This is the one that worked for the second workflow, and the outcome is the point: the reconstructed requirement was substantially simpler than the workflow, because eleven years of accumulated special cases included several for conditions that no longer occur.

🏭 From the Pipeline — the workflow that was 40% dead code

informatica_wf_inventory had six undocumented dependencies and 44 minutes of runtime. The rebuild, specified from the business requirement rather than from the workflow, ran in 6 minutes and produced identical output on 60 days of shadow running.

The difference was not optimization. It was that the workflow contained:

• Three branches for a supplier integration retired in 2021. Still executed, still cost time, produced no rows.
• A currency conversion for a market Kestrel exited. Zero rows for four years.
• A "temporary" reprocessing step added during a 2019 incident, never removed.

None of this was findable by reading the workflow, even had the license been current — it all looks like real logic. It was findable by asking what the output is for, which took two meetings.

The general lesson, and it applies to every kind of legacy job: a migration is the only time anybody asks what a pipeline is supposed to do. Kestrel's estate shrank by two jobs entirely — one output had no consumers left, one duplicated another — and this one by 40% of its logic.

Budget for that discovery rather than being surprised by it. The right question at the start of every job is not "how does this work?" but "what is this for, and is that still true?"

37.9 Cutover Readiness

"It looks good" is not a cutover criterion. --ready implements a weighted rule:

  [x] (3) differences in the last 14 days are only accepted kinds
  [x] (3) every accepted difference is documented with a reason
  [x] (2) the new pipeline has run green for 14 consecutive days
  [x] (2) consumers have been told, with a date
  [x] (3) the legacy job can be restarted within 1 hour
  [ ] (3) a rollback has been REHEARSED, not just written down
  [x] (1) the legacy job's owner (or its absence) is recorded

  score 14 / 17
  BLOCKED by: a rollback has been REHEARSED, not just written down

📐 Design Decision — the rehearsed rollback is worth more than everything else on the list

It is also the item that is always deferred, because rehearsing it costs a day and every other item is a checkbox you can tick from a dashboard.

What "rehearsed" means, precisely: in a real environment, someone points the consumers back at the legacy output, confirms they work, and records how long it took. Not "we could revert the DAG" — an actual, timed, executed reversal.

Three things a rehearsal finds that a written plan does not, all from Kestrel's:

• The legacy job had not run for 23 days and its credentials had expired. The rollback plan said "re-enable the cron entry", which would have failed at the worst possible moment.
• Two consumers had been repointed by editing a config in a place the rollback plan did not mention, because the plan was written before those consumers were migrated.
• The rollback took 3 hours 40 minutes, not the "under an hour" the plan asserted — mostly backfilling the gap in the legacy output while it had been idle.

All three would have been discovered during an incident, at 2am, by someone who did not write the plan.

And the ratchet the rehearsal creates is the real value: having measured 3h40, the team made the legacy job keep running through the overlap rather than idling it — which is §37.5's step 5, arrived at by evidence rather than by advice, and which took the rehearsed rollback to under 20 minutes.

This is Chapter 34's "a capability you never exercise is a capability you do not have," and Chapter 26's runbook problem, in the one place where the cost of being wrong is a business-visible outage.

37.10 What the Overlap Costs

Shadow running means paying for both. --cost prices it against Chapter 33's rate card:

          days   compute        engineering     TOTAL
planned     60   $ 3,588.00     $ 5,760.00      $ 9,348.00
actual     147   $ 8,790.60     $14,112.00     $22,902.60

overrun                                        $13,554.60   (2.45x)
steady-state saving per day                    $23.40
days to repay the overrun                      579

💸 Cost Check — the engineering line is bigger than the compute line, and nobody budgets it

Migration proposals price the compute. Two pipelines instead of one, for sixty days: $3,588. It is the visible number and it is 38% of the truth.

The invisible line is triaging differences. Kestrel spent about four engineer-days a month for the whole overlap, reading reconciliation output, classifying, and having the conversations §37.7 describes. At 147 days that is $14,112, larger than all the compute.

And the overlap was 2.45× the plan. Not through incompetence — the 87 extra days were almost entirely §37.7's conversations, waiting for a business owner to decide whether a corrected number could ship. The engineering was done; the decisions were not.

The conclusion that matters for how you argue for a migration: the overrun alone takes 579 days of steady-state savings to repay.

So do not justify a migration on cost. It usually does not pay back inside a planning horizon, and arguing it on cost invites a comparison you will lose. Justify it on what Kestrel actually gained — jobs that have owners, tests, lineage, and can be changed by someone who joined last month — and put the cost saving in as a footnote, honestly labelled as small.

37.11 Decommissioning

A migration is not finished at cutover. It is finished when the old thing is gone, and the gap between those two moments is where most migrations quietly stop.

Four things to actually remove, and each is a separate act:

The job. Deleted from the scheduler, not disabled. A disabled job is a job somebody re-enables.

The code. Deleted from the repository. Git remembers it; you do not need to.

The credentials. Chapter 30 Case Study 2's finding — a service account for a decommissioned integration is exactly the unattributable identity that survives for years.

The infrastructure. The server, the license, the database user, the S3 prefix. Chapter 33 Case Study 1's unattributed spend is largely this, accumulated one migration at a time.

Set the date at cutover, in a ticket, with an owner. Kestrel's twelve jobs produced fourteen decommission tickets — two jobs had infrastructure that outlived them — and eleven were closed within the quarter. The three that were not are the ones that would have become permanent.

37.12 The Migration That Should Not Happen

Not every legacy job should be migrated, and a plan that migrates all of them has not made a decision.

Kestrel's estate produced four different answers, and only one of them was "rebuild it":

rebuild        8 jobs   the default, and the expensive one
delete         2 jobs   no consumers, or a duplicate of another output
leave alone    1 job    hourly_stock_sync.py -- it has an owner and tests
absorb         1 job    its output became a column on an existing model

Two of twelve were deleted, which is the cheapest possible outcome and was found by §37.8's question rather than by any technical analysis.

One was absorbed. sp_product_hierarchy produced a standalone table that three models joined to; during the rebuild it became four columns on gold.dim_product. The migration removed a job rather than replacing one, which does not appear in any migration methodology and is frequently the right answer.

And one was left alone. hourly_stock_sync.py scores 2.6 — six reach, risk 2, because it has an owner and tests. It is a legacy job in the sense that it predates the platform and in no other sense.

📐 Design Decision — "legacy" is not a synonym for "old"

The word does a lot of unexamined work in these projects, and the unexamination is expensive.

A system is legacy when it cannot be safely changed — no tests, no owner, no understanding — and that is a property of your relationship with it rather than of its age or its technology.

hourly_stock_sync.py is a Python cron script written four years ago. It has an owner, it has tests, it runs every hour, and it has not failed in eighteen months. Migrating it to Airflow and dbt would produce a job with an owner and tests that runs every hour, at a cost of a week, and would change nothing that matters.

The pressure to migrate it anyway is real and comes from two places:

• Consistency. "Everything should be on the platform." This is a genuine benefit — one place to look, one set of conventions — and it is worth a week only if the estate is otherwise finished.
• Completeness. A migration plan with eleven of twelve jobs done looks unfinished, and a status report is a bad reason to spend a week.

The test Kestrel applied to each job before scheduling it: what specifically will we be able to do afterwards that we cannot do now? §30.1's question, aimed at a migration.

• For eight jobs: change it, test it, know who owns it, see its lineage. Real.
• For hourly_stock_sync.py: nothing. It was scheduled last and then dropped, and it is still running.

The honest cost of leaving it: one job outside the platform's conventions, which somebody will eventually have to learn. That is a real cost and it is smaller than a week, and writing down that the decision was made — rather than leaving it looking forgotten — is what makes it a decision.

37.13 The Political Problem

A migration produces no new features, and its success condition is that nothing changes. That makes it uniquely hard to fund and uniquely easy to interrupt.

Four things that help, in Kestrel's experience:

Publish the inventory early. "Ten of twelve production jobs have no owner and eleven have no tests" is a more persuasive artifact than any plan. It is also just true, took three weeks, and reframes the migration as a response to a measured problem.

Deliver visibly and early. §37.4's ordering does this on purpose: vendor_sftp_pull.sh is done in week one and it is business-critical, so the first status report says something happened.

Report the differences as findings, not as problems. 196 legacy bugs found is a benefit of the migration and reads as one if you present it that way — and reads as "the migration is going badly" if you do not.

And never promise that nothing will change. It is not true (§37.7), you will discover it is not true in week two, and having promised it is what turns each of 196 findings into a negotiation about your credibility rather than about the number.

🎓 Interview Angle — "how would you approach migrating a legacy data platform?"

A very common question, and the failing answer is a tooling plan — "lift the stored procs into dbt, orchestrate with Airflow, cut over." It answers the easy part.

The answer that works leads with the unknowns:

"The first thing I'd want is an inventory — not the code, the metadata: what each job is, who consumes it, whether it has an owner or tests, and what it depends on that isn't written down. That usually takes a few weeks and it's the artifact everything else depends on.

Then I'd sequence by where the surprises are, not by size — something with high reach and high uncertainty but small enough to finish, so I learn the estate early while there's schedule to absorb it.

I'd run old and new in parallel and reconcile daily. And I'd decide before starting what happens when the legacy system turns out to be the wrong one, because that's the conversation that actually delays these projects — in my experience more of the differences are the old system's fault than the new one's."

If you want one sentence that signals you have done this: "I wouldn't promise that nothing changes, because it won't be true by week two."

🎓 Interview Angle — "how would you approach this migration?"

A senior-level question, and the trap is answering it as a rewrite.

The strong answer reframes it:

"A migration is mostly a reconciliation project with some rewriting in it. The rewrite is the easy half — proving the new thing produces the same numbers is the work. So: an inventory first, because every estate has jobs with no identifiable consumer and at least one with no owner. Then order by risk and reversibility rather than by size. Run both in parallel for at least one full business cycle including a month end, reconcile in both directions at the finest grain available, and give every difference a disposition — intended, a defect in the new one, or a defect in the old one. That third category is what makes migrations slow and it's the one people don't plan for."

Four things that answer does. It states what the work actually is. It leads with the inventory, which is where the surprises are. It specifies the parallel-run length in business terms rather than in weeks. And it names the third disposition, which is the detail that signals experience.

The follow-ups:

"How long do you run in parallel?" — a full cycle including a month end, because the differences that matter appear at boundaries. A candidate who says "two weeks" has not been through one.

"What if the old system is wrong?" — it frequently is, and it has to be decided rather than assumed. Somebody signs off that the new number is the correct one, and that somebody is not you.

"What do you not migrate?" — the best answers propose decommissioning a meaningful fraction. Exercise 25.18's read audit is the evidence, and a plan that migrates everything has not been thought about.

And "how do you roll back?" — with a rollback that has been executed, at least once, on a non-production copy. An untested rollback is a hypothesis (Exercise 28.23c's lesson, applied to a migration), and it is the control most likely to be needed at the worst moment.

🧱 Kestrel Platform — the estate, scored and ordered

text job consumers reversible? understood? score order ───────────────────────────────────────────────────────────────────────────── nightly_revenue.sh 22 no partly 9.1 4 customer_export.py 1 yes yes 2.0 1 inventory_sync.sh 4 yes partly 4.6 2 a GUI workflow, "Weekly Partner Feed" 2 NO NO 10.0 5 legacy_churn_features.py 0 yes no 1.2 - reconcile_payments.sh 3 no yes 5.8 3

Score = consumers × (2 if irreversible) × (2 if not understood), normalised. Higher is riskier; migrate in ascending order, so the first migration is the one where a mistake costs least.

Three things the table decides.

customer_export.py goes first — one consumer, reversible, fully understood. It is not the most valuable migration and it is the one that proves the process, which is what a first migration is for.

legacy_churn_features.py is not migrated at all. Zero consumers over a 400-day read audit (Exercise 25.18). It is deprecated: renamed, left for a cycle, then deleted — and this row is the cheapest win in the estate.

And the GUI workflow is last, deliberately, because it scores worst on the two dimensions that matter: it cannot be rolled back easily and nobody can read its logic (Exercise 37.12). It needs the black-box approach — capture inputs and outputs, reconstruct behaviour, reconcile — and doing that first would consume the whole appetite for the programme.

What the table does not include, and should be added at your own site: a decommission date per row. A migration plan without one produces two systems where there was one, which is Chapter 37's most common failure and the one nobody counts as a failure.

🧭 Version Note — what you are usually migrating from

Every legacy estate is a fossil record of the era it was built in, and knowing the era tells you what to expect before you open anything.

```text era of the legacy system what you will find ───────────────────────────────────────────────────────────────────────── ~2005, an ETL GUI logic in a proprietary graph, not in text. (Informatica, SSIS, No diff, no review history, no way to grep. DataStage) Exercise 37.12's black-box approach.

~2012, cron + shell + readable, undocumented, and dependent on a Perl/Python specific machine's crontab and filesystem. The DEPENDENCY GRAPH is in the times (ch 24).

~2015, Hadoop MapReduce or Hive QL, and a cluster somebody is afraid to turn off. Frequently the data is fine and the compute is the problem.

~2018, a first-generation scheduled notebooks, or a vendor connector cloud stack whose configuration lives in a web UI. Same problem as 2005, newer logo.

~2020, "the old dbt readable and reviewable. This is the easy project" case and it is increasingly the common one. ```

The pattern across four of the five rows: the logic is not in a file you can read. That is the single best predictor of migration difficulty (Chapter 5's exit-cost table), and it is orthogonal to how old the system is — a 2018 GUI workflow is harder to migrate than a 2012 shell script.

Two things that are consistently easier than expected, and worth saying because they lower the perceived cost:

The data itself is usually fine. Most migrations move compute and keep the data where it is.

And a meaningful fraction of the estate does not need migrating at all (Exercise 37.14). The read audit is the cheapest work on the project and it removes jobs from the list, which is the only thing that makes a large estate tractable.

🔁 Idempotency Check — the parallel run doubles every write

For the length of the parallel run, two systems are writing, and the interaction is the part nobody plans.

text what both systems do the problem ───────────────────────────────────────────────────────────────────── write to the same target table they overwrite each other, and the reconciliation compares a table to itself write to different targets correct, and it DOUBLES the storage and the compute for the period both consume the same source the source sees 2x the load; on a with a watermark guest database (§7.1) this is the thing you promised not to do both write to the same DOWNSTREAM system the reverse-ETL push, the partner feed, the notification -- SENT TWICE, and it leaves the building

The last row is the one that produces an incident during a migration, and it is easy to miss because everyone is focused on the tables.

Three rules for the parallel period:

Separate targets, always. gold_new.fct_order_line beside gold.fct_order_line. A shared target makes the reconciliation meaningless and it is the most common way a parallel run is set up wrongly.

The new system's outbound side is disabled. No reverse ETL, no partner feed, no alerts. It computes and it does not act, which is Chapter 27's shadow deploy applied to a whole pipeline.

And both systems' extracts share one landing. The new pipeline reads bronze rather than the source, so the source sees exactly one extract — which also makes the reconciliation cleaner, because a difference is then genuinely a transformation difference rather than two reads of a moving table.

The cost of the period is roughly double for its duration (Exercise 37.10), and it should be priced in the plan — because the alternative to being told is discovering it in the month's bill, where it looks like a cost regression rather than a planned expense.

🔐 Privacy & Governance — a migration is the best chance you will get to delete things

Nobody approves a project to delete data. Everybody approves a migration, and a migration is a deletion project with a different name on it.

text the migration asks the governance answer you get for free ───────────────────────────────────────────────────────────────────── what does this job produce? an inventory of every dataset who reads it? a consumer map (Exercise 25.18) what does it retain, and where? a retention audit nobody funded what does it hold about people? a classification pass over the estate what are we NOT migrating? a deletion list, with a reason each

The last row is the one to protect. Exercise 37.14 asks students to decide what not to migrate, and the honest answer is usually a meaningful fraction — jobs with no consumer, outputs nobody reads, and copies made for a project that finished.

Three specific things worth doing while the inventory is open, because they will not be funded separately:

Classify as you inventory. You are already opening every job and reading its output; adding a "does this hold personal data" column costs a minute per job and produces the artifact Chapter 31's manifest generator needs.

Set a retention on the legacy outputs you are decommissioning, rather than leaving them in place "just in case." A decommissioned pipeline's output with no retention is the most common unclassified personal-data copy in any estate, and it is created by the migration itself.

And record the reason for each non-migration. "No consumer in 400 days" is a defensible deletion; "we didn't get to it" is a system that will be rediscovered in two years by somebody who assumes it matters.

The framing to use with a sponsor: the migration's scope reduction is its largest saving, and it is also its largest privacy improvement. Those are the same work, argued to two different audiences.

37.14 The Kestrel Platform

platform/migration/
  inventory.yml           # 12 jobs, scored, ordered
  reconcile/
    <job>.sql             # FULL OUTER JOIN per job, daily
    classify.py           # difference -> one of six kinds
    ledger.md             # every accepted difference, dated, with a reason
  readiness.py            # the weighted rule; blocks a cutover
  rollback/
    <job>.md              # and a REHEARSAL DATE, which is the field that matters
  decommission.yml        # 14 tickets, owner and date

Two jobs were deleted rather than migrated, which is the cheapest possible outcome and was found by §37.8's question — what is this for, and is that still true?

And hourly_stock_sync.py was never migrated at all, at a score of 2.6. It had an owner and tests and was fine. Scheduling it last cost nothing; scheduling it first would have cost a week of learning nothing — and dropping it, as a recorded decision rather than an oversight, cost nothing either.

37.15 Summary

This is the work most data engineers are hired to do, produces no new features, and succeeds when nothing changes — which is what makes it hard to fund and easy to interrupt.

The technical work is the easy part. Nobody knows what the job does, nobody knows who uses it, the old system is wrong in ways people have adapted to, and there is no feature to point at.

🔎 Inventory before you read anything. Kind, consumers, owner, tests, size, schedule, undocumented deps, criticality — all discoverable without reading a line. Kestrel: 10 of 12 with no owner, 11 of 12 with no tests, 36 undocumented dependencies, in three weeks.

Finding consumers takes four methods, and only the fourth — breaking it on purpose, announced and short — finds everybody. Kestrel used it twice, on non-critical jobs, and both times found a consumer the other three methods missed.

⚠️ The load-bearing spreadsheet is not a technical migration. Separate the rules from the decisions, migrate the rules, and give the decisions an explicit versioned input. A manual step that survives is the correct home for a judgment — the first rebuild matched history perfectly and was wrong its first live month.

📐 Order by (reach × risk) / difficulty — learning per week. Not biggest-first (the hardest job attempted with the least knowledge, nothing visible at the first review) and not easiest-first (learn nothing that transfers). Kestrel's first job was 80 lines, business-critical, ownerless, and touched every mechanism the rest would need.

Strangler fig, per job, and step 6 is the one that does not happen: delete the legacy job, do not disable it. A job left running "just in case" will still be running in three years.

Reconcile with a FULL OUTER JOIN — an inner join is blind to rows one system produced and the other did not, which is the most common difference and the one that hides.

⚠️ More differences are the legacy system's fault than the new one's — 196 against 146. Nothing was measuring it for years, so its errors accumulated. Decide in advance what happens then, because each one is a political conversation. Bug-for-bug compatibility is legitimate and a terrible default, and the promise to fix it later will not be kept. Emit both columns to convert a blocking decision into a non-blocking one.

📏 50.2% of differences are irreducible — rounding, boundaries, legacy non-determinism (18.5%, the largest), and genuine source changes. A cutover rule requiring zero never fires; require only accepted kinds, all documented.

🏭 A migration is the only time anybody asks what a pipeline is for. One workflow was 40% dead code — branches for a supplier retired in 2021, a currency for an exited market, a "temporary" 2019 step — none findable by reading it, all findable by asking. Two of twelve jobs were deleted outright.

📐 The rehearsed rollback is worth more than every other readiness item and is always deferred. Kestrel's rehearsal found expired credentials on a job idle 23 days, two consumers the plan did not mention, and a real duration of 3h40 against a claimed "under an hour" — all of which would otherwise have been found at 2am by someone who did not write the plan.

💸 The engineering line exceeds the compute line and nobody budgets it. $14,112 of difference-triage against $8,790 of double-running — and the overlap ran 2.45× the plan, almost entirely waiting for business decisions rather than for engineering. The overrun takes 579 days of steady-state savings to repay, so do not justify a migration on cost — justify it on jobs that have owners, tests, lineage, and can be changed by someone who joined last month.

📐 Not every legacy job should be migrated. Kestrel: 8 rebuilt, 2 deleted, 1 absorbed into an existing model, 1 left alone. "Legacy" is not a synonym for "old" — a system is legacy when it cannot be safely changed, which is a property of your relationship with it. Ask of each job: what specifically will we be able to do afterwards that we cannot do now? For one of Kestrel's twelve the answer was nothing, and it is still running.

🎓 And never promise that nothing will change. It will not be true by week two, and having promised it turns each of 196 findings into a negotiation about your credibility.

Part VII ends here

Four patterns, and the chapter order was deliberate. Chapter 34's medallion is nearly free and applies almost everywhere. Chapter 35's mesh is expensive and applies rarely — and Kestrel's honest answer was no. Chapter 36's event-driven design is a real inversion with a measurable payoff and a real cost. Chapter 37 is what happens when any of them meets a system that already runs.

The through-line: every pattern in this part has a precondition, and the precondition is more important than the pattern. A medallion without enforcement is three directories. A mesh without domain teams is a hiring plan. Event sourcing without maintained projections is a log nobody folds. And a migration without an inventory is a quarter spent reading code.

Part VIII is synthesis. Chapter 38 is the capstone — the whole platform, end to end, judged by reconciliation. Chapter 39 is the interview. Chapter 40 is the career.

Key terms: strangler fig · shadow running · parallel run · reconciliation · cutover · rollback rehearsal · bug-for-bug compatibility · legacy non-determinism · undocumented dependency · decommissioning · load-bearing spreadsheet