migrating-legacy-etllisted

# Migrating Legacy ETL ## When to use - Moving legacy ETL (stored procs, SSIS, Informatica, hand-rolled jobs) or an on-prem warehouse to a modern stack (dbt, Spark, cloud warehouse). - Rewriting procedural transformations into set-based SQL/models. - Validating that the new system matches the old before cutover. - Do NOT use for greenfield pipelines (use the building/authoring skills). ## Workflow ``` - [ ] Inventory jobs, dependencies, and consumers; find what is actually used - [ ] Migrate incrementally (strangler fig), not big-bang - [ ] Rewrite procedural logic into set-based, idempotent transformations - [ ] Run old and new in parallel; reconcile outputs - [ ] Cut over per domain once reconciliation passes; decommission the old path ``` 1. **Inventory and triage.** Map every job, its dependencies, and its downstream consumers. Many legacy jobs are dead or duplicated — don't migrate what nobody uses. 2. **Strangler-fig phasing.** Migrate one domain/table at a time, redirecting consumers as each piece is proven. Big-bang cutovers fail. 3. **Rewrite, don't transliterate.** Convert row-by-row cursors/procedures into set-based, idempotent SQL/models — a literal port keeps the old bottlenecks. 4. **Parallel run + reconcile.** Run old and new side by side and compare row counts, key sets, and aggregate sums until they match within tolerance. 5. **Cut over gradually** and decommission the legacy path only after reconciliation holds. ## Patterns **Recon