Informatica, SSIS, and DataStage estates are the revenue engine of services-company data work, and the modern migration stack is hybrid: deterministic converters (Lakebridge, SnowConvert AI's SSIS-to-dbt path, BladeBridge-class transpilers) do the bulk mechanical conversion, an LLM pass handles residual and custom logic, and generated parity tests prove the result.
This playbook is the end-to-end operating procedure for one migration wave — assessment through parity sign-off — with the LLM in a bounded, verifiable role.
Use this when a mapped scope of legacy ETL jobs must land on dbt or a lakehouse, and the estate is too large for hand-rewriting but too custom for pure transpilation. It assumes you already know how to reverse-engineer individual gnarly components (see the stored-procedure archaeology playbook) and generate parity suites (see the reconciliation playbook) — this one sequences the whole wave.
Triggers:
Assess and slice into waves. Run the transpiler's analyzer (Lakebridge analyzer, SnowConvert assessment) across the estate to get per-job complexity and feature usage. Slice waves by dependency cluster, not alphabetically: a wave must be independently testable end-to-end. Park the top 5% most complex jobs for engineer-led rework — they will eat your schedule if hidden inside a wave.
Transpile the wave and disposition the report. Every job lands in one of three buckets: clean conversion (goes straight to parity testing), flagged residue (LLM refinement, next step), or redesign (custom scripts, external calls, platform-specific components with no target equivalent — human design first).
LLM-refine the residue with source semantics pinned. For each flagged component, give the assistant the legacy component's exported definition (XML/JSON) plus the transpiler's partial output:
This SSIS Derived Column transformation was partially converted.
Below: the original component XML, the transpiler's dbt output, and
the transpiler's warning.
Complete the conversion to dbt SQL. Rules:
- Replicate SSIS semantics exactly, including its NULL propagation
and type-cast behavior; comment each place target defaults differ.
- Preserve the transpiler's naming and structure — you are completing
its output, not replacing it.
- If the component's behavior cannot be determined from the XML alone
(e.g., a variable set at runtime), stop and list what you need.
[paste XML + partial output + warning]
Verify each refinement immediately with a component-level fixture: run representative input rows through the new SQL and compare against the legacy component's recorded output for the same rows. Do not defer verification to wave-level testing — a wrong component hides inside aggregate parity for weeks.
Reconstruct orchestration deliberately. Control-flow (precedence constraints, event handlers, retry logic) does not transpile cleanly into DAGs. Have the LLM draft the dependency graph from the package definitions, then review it against the legacy scheduler's actual run history — the documented dependencies and the real ones often differ:
-- real execution order from legacy run logs
SELECT job_name, MIN(start_time) AS typical_start
FROM legacy_run_history
WHERE run_date = DATE '2026-07-01'
GROUP BY job_name ORDER BY typical_start;
Run wave-level parity. Execute legacy and new pipelines against the same frozen source snapshot; compare every target table with the three-level pattern (row counts, aggregate fingerprints, sampled full rows — see the reconciliation playbook for generation). Mismatches route back to step 3 scoped to the offending component, never to wholesale regeneration.
Parallel-run before cutover. Both systems live on real daily data for an agreed window (typically 5–10 business days), with automated daily parity checks and a divergence log. Every divergence gets a written disposition: translation bug (fix), legacy bug faithfully not reproduced (client decision), or timing artifact (documented). Cutover requires N consecutive clean days — put the number in the plan.
Vendor automation rates are measured on syntax, not on your estate's custom scripts, external commands, and embedded business logic — which is precisely where migrations slip. Budget the residue at 3–5x the effort per job of the clean bucket, and protect the parallel-run window from schedule compression; it is the only stage that proves the migration rather than asserting it.
Legacy ETL definitions encode client business logic end to end, so the default posture is everything-in-tenancy: run Lakebridge/SnowConvert inside the client environment (both work offline), and route LLM refinement through Bedrock or Azure OpenAI in the client's own cloud with logging enabled — this is the pattern most modernization MSAs accept. Where no in-tenancy LLM exists, genericize exported component definitions with a scripted identifier mapping before prompting a local Ollama model, and restore names on output. Snapshots, fixtures, and the parallel-run divergence log all live in the client's platform, forming an audit trail that stands on its own regardless of which AI assisted.