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

Reconciliation harness — compare old vs new for the same window:

SELECT 'row_count' metric, (SELECT COUNT(*) FROM legacy.fct_orders) legacy,
                           (SELECT COUNT(*) FROM new.fct_orders)    new_
UNION ALL
SELECT 'sum_amount', (SELECT SUM(amount) FROM legacy.fct_orders),
                     (SELECT SUM(amount) FROM new.fct_orders);

Investigate every non-matching metric; differences are usually NULL handling, timezone, rounding, or dedup logic that the legacy system did implicitly.

Capture business rules buried in procedures as tested dbt models with descriptions, so tribal logic becomes documented and version-controlled.

Common pitfalls

• Big-bang cutover — high risk, no rollback; migrate domain by domain.
• Literal transliteration — porting cursors/temp-table hops keeps legacy inefficiency; re-express as set-based logic.
• No parallel-run reconciliation — subtle logic differences ship as data bugs; compare counts/sums before trusting the new path.
• Migrating dead jobs — wasted effort; verify each job has real consumers.
• Losing implicit rules — legacy handling of NULLs/timezones/dedup is often undocumented; reconcile to surface and encode it.
• No decommission step — old and new both run forever, doubling cost and confusion.