This module installs the migration factory: assess before you promise, let a deterministic transpiler do the bulk, spend LLM and human judgment only on the residue, and gate every object on generated parity evidence a client's auditors can read.
Legacy migration — stored procedures to dbt, Informatica to lakehouse, Control-M to Airflow — is the largest single body of data engineering work in the services industry, and it has just changed shape. This module teaches the workflow that changed it: deterministic transpilers doing the bulk conversion, LLMs handling the residue and the archaeology, and generated parity suites providing the proof that lets a client sign off.
The estimate that lost. Meena is a senior data engineer at a Pune services firm. In March, her delivery unit bid on migrating a US insurer's Teradata estate — 1,180 BTEQ scripts and stored procedures, 400+ Informatica mappings — to Databricks. They estimated the way the last decade taught them: procedures per engineer per week, measured on the previous project, where engineers read each proc line by line, rewrote it by hand, and eyeballed a few row counts afterward. The bid came out at 30 months and a headcount the client read as a price tag. A competitor bid 11 months. Meena's firm lost, and the postmortem note said what everyone already knew: the competitor wasn't bidding cheaper engineers, it was bidding a different workflow.
The project that won. In June, the same unit bids a comparable modernization — a bank moving Teradata and SSIS to Snowflake — with the workflow this module teaches.
Assessment, not conversion. SnowConvert AI's assessment run inventories every object, scores conversion complexity, and flags the constructs automation won't handle. Output: a spreadsheet of 1,340 objects, 71% marked as clean automated conversion.
The transpiler converts those 950 objects deterministically; engineers never open most of them.
The residue is where the humans and LLMs go: a 1,100-line pricing procedure with cursor loops and session-variable tricks lands on Meena's desk.
A long-context LLM pass has produced what she used to spend two days building by hand — a control-flow summary, a table of fourteen extracted business rules, a dependency list, and a note that lines 214–260 are dead code guarded by a flag retired in 2019.
She verifies the rules against the client SME — the conversation that actually needs a human.
The proc is rewritten as four dbt models.
Every converted table flows through generated reconciliation queries comparing row counts, per-column aggregates, and sampled rows between Teradata and Snowflake, with the results landing in a report the client's auditors can read.
The migration finishes. The parity report — 1,340 objects, 100% row-count match, 14 documented and client-approved intentional differences — is what turns "we moved your code" into "you can decommission the old system."
Same firm, same engineers. The difference is that conversion became a factory, and the engineers moved to the two stations that pay: judgment on the residue, and proof at the gate.
The core workflow, in order, with no steps skipped:
Why not skip the transpiler and let an LLM convert everything? Because at estate scale, determinism is the feature. An LLM converts each of 1,000 procedures slightly differently, its occasional quiet semantic drift multiplies across objects, and no two runs are identical — which makes auditing a per-object job instead of a per-rule job. The transpiler handles the boring 70–80% identically every time; the LLM's judgment is spent only where judgment is needed. Both flagship tools are free — SnowConvert AI at no cost from Snowflake, Lakebridge open source — which matters when the workflow itself is the bid differentiator.
| Work | Who does it | Why |
|---|---|---|
| Bulk dialect/DDL conversion, mapping-tool translation | Transpiler | Deterministic, auditable, fix-once semantics |
| Residual constructs, procedural rewrites, idiom translation | LLM + engineer | Needs judgment per object; context pack makes it reviewable |
| Business-rule verification, intentional-difference decisions | Engineer + client SME | Nobody else has the authority |
| Parity evidence | Generated suites | Volume makes manual checking a fiction |
Transpiler for the deterministic bulk, LLM for the residue, humans for judgment, parity for proof — in that order, and the assessment report before any of it.
A concrete residue case shows the division of labor. The transpiler converts this Oracle idiom and flags what it can't prove:
-- Source (Oracle): sessionized events via MODEL clause — transpiler flags:
-- "MODEL clause not supported; manual conversion required"
SELECT account_id, event_ts, session_id
FROM events
MODEL PARTITION BY (account_id) DIMENSION BY (rn) MEASURES (event_ts, 0 AS session_id)
RULES (session_id[ANY] = CASE WHEN event_ts[cv()] - event_ts[cv()-1] > 1800
THEN session_id[cv()-1] + 1 ELSE session_id[cv()-1] END);
The LLM refinement prompt gets the flagged source, the transpiler's note, the target platform, and — critically — the intent: "this sessionizes events with a 30-minute gap rule." The refined output is the standard window-function pattern every modern warehouse handles (LAG to detect gaps, running SUM over gap flags to number sessions), plus a generated equivalence test on a fixture with boundary cases: a gap of exactly 1,800 seconds, a single-event account, events with identical timestamps. Stating intent matters because idiom-for-idiom translation preserves structure, and what you want preserved is behavior — the LLM translating "what this achieves" produces better target code than the LLM translating "what this says."
The recurring dialect traps deserve a standing checklist in your refinement prompts, because they are semantic rather than syntactic:
DATE + 1 means a day in one dialect and an error in another.QUALIFY availabilitywhether the target dialect supports the clause at all.'' as null; almost nobody else does.Each is exactly the kind of quiet difference that survives review, passes a row-count check, and fails a per-column aggregate — which is why the parity suite in the next section checks aggregates, not just counts.
The migration's hardest objects aren't hard because of syntax; they're hard because the business logic inside them is undocumented, the author left in 2016, and the proc has been patched by eleven hands since. Before rewriting anything, run the archaeology pass. The prompt, against a long-context model:
Here is a 1,100-line Teradata stored procedure: <full source>
Produce, in order:
1. CONTROL FLOW: numbered summary of execution paths, including every
branch condition and what makes each path fire.
2. BUSINESS RULES: a table — rule, plain-English meaning, line numbers,
confidence (high/medium/low). Flag any rule that looks like a bug
preserved as behavior.
3. SIDE EFFECTS: every table written, session variable set, external call.
4. DEPENDENCIES: objects read, objects that likely read this proc's outputs.
5. DEAD CODE: unreachable or flag-disabled sections, with evidence.
6. QUESTIONS: what you cannot determine from the source alone.
Two things about the output discipline. First, the confidence column and the QUESTIONS section are not decoration — they are the interface between the LLM's reading and the client SME conversation, and the medium/low-confidence rules are precisely the meeting agenda. Second, the extracted business-rules table is a deliverable, not scaffolding: clients pay for documented business logic recovered from opaque code, and the rules doc outlives the migration. "Flag any rule that looks like a bug preserved as behavior" earns its place — every legacy estate contains behavior that is wrong, known to be wrong, and load-bearing, and the migration's most expensive mistake is silently fixing it.
The archaeology pass feeds design, not codegen. You decide whether the proc becomes four dbt models or one, which rules are still wanted, and what dies with the legacy system; only then does conversion start, with the rules table in the context pack. SnowConvert AI's assessment reports and Lakebridge's analyzer output slot in as structural context — they tell you what the proc touches; the LLM pass tells you what it means.
Run the archaeology prompt on two procs the team already understands deeply before trusting it on unknowns — the errors it makes on known code calibrate how much to trust its confidence column on unknown code.
A migration without reconciliation evidence is an opinion. The parity suite is generated — at 1,340 objects, hand-writing comparisons is a fiction nobody staffs — and it runs at three levels per table:
SUM, MIN, MAX, null count for every numeric; distinct count and null count for every key and category column.The generation is metadata-driven — feed the LLM the table's schema and ask for the suite, or use the tools built for exactly this: Lakebridge's reconcile module, datacompy for programmatic diffs, Great Expectations when you want the checks living on as permanent tests. A generated aggregate check looks like:
SELECT 'claims_monthly' AS tbl, 'target' AS side,
COUNT(*) AS row_ct,
SUM(paid_amount) AS sum_paid, COUNT(DISTINCT claim_id) AS claims,
SUM(CASE WHEN paid_amount IS NULL THEN 1 ELSE 0 END) AS null_paid,
MIN(service_date) AS min_dt, MAX(service_date) AS max_dt
FROM analytics.claims_monthly
WHERE service_date >= '2024-01-01';
-- Generated per-table from schema metadata; run both sides, diff the rows
The suite needs a tolerance policy written before results arrive, because cross-platform migration produces legitimate differences that will otherwise be litigated one Slack thread at a time:
CHAR semantics versus Snowflake's.Each difference class gets a ruling — tolerance band, normalization applied before comparison, or "intentional difference" logged with a client sign-off. That log of intentional differences is half the value of the parity report: it is the documented answer to "why doesn't this match?" for every future auditor.
A parity mismatch discovered by the client after cutover costs more than the entire generated test suite — parity is the definition of done for every object, not a QA phase scheduled after the interesting work.
Job schedulers age worse than code: a 15-year-old Control-M or AutoSys estate encodes the company's actual operational dependencies in a format three people can read. The migration workflow to Airflow 3 or Dagster:
The trap in scheduler migrations is implicit coupling — Job B was scheduled at 03:00 because Job A "always finishes by 02:40," a dependency that exists in nobody's config. Ask the LLM to flag time-adjacent jobs sharing tables as suspected implicit dependencies, then confirm with humans; making implicit dependencies explicit is the lasting payoff of the whole exercise, because the new DAGs encode reality instead of folklore.
For India services firms, this workflow changes the commercial math, not just the engineering. Estimates now start from the assessment report, not from historical throughput: run SnowConvert AI or Lakebridge assessment over the (client-approved) source inventory during pre-sales, and build the estimate from the complexity distribution it returns.
| Bucket (from assessment) | Effort model | Who |
|---|---|---|
| Clean automated conversion | Minutes per object — transpile, parity, done | Factory line, junior-heavy |
| Automated with flagged warnings | Review + touch-up per object | Mid-level engineers |
| Residual: procedural/dynamic/vendor-specific | LLM-refinement workflow, hours-to-days per object | Senior engineers |
| Archaeology-grade (undocumented, business-critical) | Archaeology pass + SME cycles + redesign | Senior + client SME time — the schedule risk lives here |
| Parity + cutover | Per-table generated suites + tolerance rulings | Runs continuously, priced explicitly |
Three estimating rules:
Client-data compliance is a first-class constraint, not a footnote. Most MSAs prohibit pasting client schemas, code, or data into public AI tools, so establish the approved channel before the first prompt:
For pre-sales work before any channel is approved, use synthetic stand-ins — rehearse prompts on invented schemas that mirror the shapes, never the content. Where pipelines touch personal data, DPDP Act obligations (and the client's own regimes — GDPR, HIPAA, RBI/SEBI localization for BFSI clients, the largest Indian services vertical) apply to migration work too: parity testing on real rows is personal-data processing, so scope it to the approved environment, or run reconciliation on masked or aggregate data where the MSA demands it.
The approved-channel hierarchy — client-tenancy AI, employer gateway, local model, abstain — is decided before the first prompt containing anything client-derived, and "abstain" is a legitimate rung, not a failure.
Your first 15 minutes on a migration Monday — if you have an assigned proc you don't fully understand, run the archaeology prompt on it and take the extracted business-rules table (with its confidence column) into your next SME call; if every proc is already understood, generate the parity aggregate suite for the table you're about to cut over.
Check yourself: The estate has 1,180 stored procedures and a frontier LLM could convert each one. Why does the winning workflow run a deterministic transpiler first and reserve the LLM for the residue?
This module anchors LAB-02 — Legacy migration sprint: reverse-engineer a 600-line legacy T-SQL stored procedure with an LLM, convert it to dbt models transpiler-first with LLM refinement, and prove parity with generated reconciliation queries. The artifact — an explanation doc, converted models, and a parity report showing a 100% match — is a miniature of the client deliverable this module's workflow produces at estate scale, and a directly portfolio-usable one. Do this at work this week → pb-05 Reverse-Engineer a Legacy Stored Procedure Before You Rewrite It, pb-07 Migrate a Legacy ETL Platform with Transpilers plus LLM Refinement, and pb-08 Generate Migration Parity and Reconciliation Test Suites.