SQL ETL Migration Playbook (Legacy → New Dual‑SCD2 Platform)

How to turn messy legacy pipelines into a scalable, transparent, and future‑ready data foundation.

If your organization has been around for more than a decade, chances are your ETL landscape is packed with SQL code that nobody wants to touch. Layers of historization logic, business rules, and data quality fixes often live in the same queries—making them fragile, hard to debug, and expensive to maintain.

But there’s good news: migrating to a modern dual‑SCD2 platform isn’t just a technical rewrite. It’s an opportunity to reset your data foundation—bringing clarity, agility, and long‑term cost savings.

Business‑Friendly Summary

Migrating complex legacy ETL SQL to a new data platform isn’t just a technical rewrite—it’s a business transformation. In many organizations, decades of SQL code have grown into layered, hard‑to‑maintain pipelines that mix historization logic, business transformations, and data quality fixes all in the same queries. This makes scaling, onboarding new sources, or ensuring consistent outputs across reports a real challenge.

The new platform changes the game. Instead of piecing together snapshots and custom SCD2 timelines, it applies a unified dual‑SCD2 historization approach. This means:

Data from all sources is historized consistently.
Time filters and as‑of logic are simplified and reusable.
Redundant intermediate timeline joins disappear.

On top of that, renaming and casting of columns—once buried inside legacy ETL code—are now handled by Source Access Views generated from metadata. This keeps integration queries clean and focused on business logic only.

To ensure future‑proof SQL, the migration enforces modern best practices: modular CTE design, explicit column naming, clean joins, and no more hidden transformations. Automated validation checks confirm that the new queries:

Use the same sources and produce the same results as the old ones.
Follow all optimization rules.
Provide clear metadata for lineage and dependencies.

For the business, the outcome is:

More reliable reporting: no hidden differences in historization logic.
Greater agility: new sources and rules can be added faster.
Better governance: lineage and dependencies are clear and machine‑readable.
Lower maintenance cost: cleaner, more modular SQL that’s easier to adapt.

In short: this migration is not just about moving SQL from one platform to another—it’s about delivering a scalable, transparent, and future‑ready data foundation for analytics and reporting.

1) Objectives & Scope

Migrate functionality, not just code: preserve semantics of legacy transformations while conforming to new architecture (zones/layers) and best practices.
Standardize time filtering: remove bespoke snapshot/SCD2 timeline logic; use a unified dual‑SCD2 as‑of filter in source CTEs (not in JOIN predicates).
Refactor structure: replace “curation” with Source Access Views (SAVs) that only rename/cast, generated from legacy JSON metadata.
Harden SQL quality: enforce modular CTE design, explicit selects, pure UNION ALL, etc.
Automate validation: verify sources, outputs, and rule compliance with SQLGlot and Python checks.
Emit metadata required by the new ETL framework (lineage/dependencies, mapping).

2) Architecture Mapping

Legacy → Target

Source snapshots → Raw (dual‑SCD2 historized) in target.
Legacy raw (snapshot historized) → Target raw dual‑SCD2 (ingestion change handled upstream).
Legacy curation (rename/cast + snapshot historized) → SAVs (views). No separate curation zone in target.
Legacy integration (layered + SCD2 result sets + complex timeline joins) → Target integration with:
- Source filtering in CTEs using a single reporting period placeholder.
- Clean JOIN predicates (no time filters) and simplified lineage (no intermediate timeline‑stitching CTEs when redundant under dual‑SCD2).

3) Best‑Practice Rules (to enforce during conversion)

Exclude inline UNION ALL inside transformations. Keep unions in a dedicated CTE.
Keep UNION ALL pure: upstream CTEs normalize schemas; downstream CTE performs the union only.
Modular CTE design: each CTE has one responsibility (filter, derive, aggregate, etc.).
No transformations in GROUP BY: pre‑derive expressions in CTEs and group by aliases/columns.
Pre‑processed joins: filter and shape inputs in source CTEs; joins match keys only.
Push derivations left: compute derived columns before joins/aggregations.
Specify columns explicitly: no SELECT * in integration.
Use CTEs for subqueries: name and reuse logic; avoid nested selects.
Time filters in source CTEs only: never in JOIN conditions.
Remove legacy timeline‑stitching CTEs where dual‑SCD2 renders them redundant.

4) Migration Rules (mechanical changes)

Schema renames: apply mapping legacy_schema → target_schema for source & integration objects.
Reporting period placeholder rename: e.g., ${REPORT_DATE} → ${AS_OF_TS} (use your actual names).
Remove snapshot selection logic: delete legacy “pick latest snapshot” helpers and mixed snapshot/SCD2 timelines.
Insert dual‑SCD2 as‑of filter pattern into each source CTE (see §5).
Strip time filters from JOINs; ensure they’re handled in the feeding CTEs.
Cull intermediate CTEs whose only purpose was timeline alignment; update all

1) Objectives & Scope

Migrate functionality, not just code: preserve semantics of legacy transformations while conforming to new architecture (zones/layers) and best practices.
Standardize time filtering: remove bespoke snapshot/SCD2 timeline logic; use a unified dual‑SCD2 as‑of filter in source CTEs (not in JOIN predicates).
Refactor structure: replace “curation” with Source Access Views (SAVs) that only rename/cast, generated from legacy JSON metadata.
Harden SQL quality: enforce modular CTE design, explicit selects, pure UNION ALL, etc.
Automate validation: verify sources, outputs, and rule compliance with SQLGlot and Python checks.
Emit metadata required by the new ETL framework (lineage/dependencies, mapping).

2) Architecture Mapping

Legacy → Target

Source snapshots → Raw (dual‑SCD2 historized) in target.
Legacy raw (snapshot historized) → Target raw dual‑SCD2 (ingestion change handled upstream).
Legacy curation (rename/cast + snapshot historized) → SAVs (views). No separate curation zone in target.
Legacy integration (layered + SCD2 result sets + complex timeline joins) → Target integration with:
- Source filtering in CTEs using a single reporting period placeholder.
- Clean JOIN predicates (no time filters) and simplified lineage (no intermediate timeline‑stitching CTEs when redundant under dual‑SCD2).

3) Best‑Practice Rules (to enforce during conversion)

Exclude inline UNION ALL inside transformations. Keep unions in a dedicated CTE.
Keep UNION ALL pure: upstream CTEs normalize schemas; downstream CTE performs the union only.
Modular CTE design: each CTE has one responsibility (filter, derive, aggregate, etc.).
No transformations in GROUP BY: pre‑derive expressions in CTEs and group by aliases/columns.
Pre‑processed joins: filter and shape inputs in source CTEs; joins match keys only.
Push derivations left: compute derived columns before joins/aggregations.
Specify columns explicitly: no SELECT * in integration.
Use CTEs for subqueries: name and reuse logic; avoid nested selects.
Time filters in source CTEs only: never in JOIN conditions.
Remove legacy timeline‑stitching CTEs where dual‑SCD2 renders them redundant.

4) Migration Rules (mechanical changes)

Schema renames: apply mapping legacy_schema → target_schema for source & integration objects.
Reporting period placeholder rename: e.g., ${REPORT_DATE} → ${AS_OF_TS} (use your actual names).
Remove snapshot selection logic: delete legacy “pick latest snapshot” helpers and mixed snapshot/SCD2 timelines.
Insert dual‑SCD2 as‑of filter pattern into each source CTE (see §5).
Strip time filters from JOINs; ensure they’re handled in the feeding CTEs.
Cull intermediate CTEs whose only purpose was timeline alignment; update all join references accordingly.
Replace curation with SAVs: generate views from JSON metadata for rename/cast; integration queries must read from these SAVs.

5) Target SQL Skeleton (Dual‑SCD2 friendly)

WITH
params AS (
  SELECT CAST(${AS_OF_TS} AS TIMESTAMP) AS as_of_ts
),
-- Example source CTE using dual‑SCD2 historization
src_customer AS (
  SELECT /* explicit columns */
         c.customer_id,
         c.name,
         c.status,
         c.valid_from,
         c.valid_to,
         c.is_current
  FROM tgt_raw.customer_scd2 c, params p
  WHERE c.valid_from <= p.as_of_ts
    AND (c.valid_to  >  p.as_of_ts OR c.valid_to IS NULL)
),
src_orders AS (
  SELECT o.order_id, o.customer_id, o.amount, o.valid_from, o.valid_to, o.is_current
  FROM tgt_raw.orders_scd2 o, params p
  WHERE o.valid_from <= p.as_of_ts
    AND (o.valid_to  >  p.as_of_ts OR o.valid_to IS NULL)
),
-- Derivations before joins
d_customer AS (
  SELECT customer_id,
         UPPER(name) AS name_u,
         status
  FROM src_customer
),
-- Pure join without time predicates
joined AS (
  SELECT j.customer_id,
         j.name_u,
         s.order_id,
         s.amount
  FROM d_customer j
  JOIN src_orders s ON s.customer_id = j.customer_id
),
-- Aggregations with no expressions in GROUP BY
agg AS (
  SELECT customer_id,
         name_u,
         SUM(amount) AS total_amount,
         COUNT(*)    AS order_cnt
  FROM joined
  GROUP BY customer_id, name_u
)
SELECT customer_id, name_u, total_amount, order_cnt
FROM agg;

Notes

Time filtering is centralized in src_* CTEs and driven by params.as_of_ts.
No SELECT * beyond raw exploration.
Joins are clean key joins; derivations happen in dedicated CTEs.

6) Source Access Views (SAVs) from JSON Metadata

Goal: Replace legacy curation by generating views that only rename and cast columns.

Example JSON snippet (legacy callable metadata)

{
  "source": "legacy_raw.customer",
  "mappings": [
    {"from": "cust_id",   "to": "customer_id", "type": "BIGINT"},
    {"from": "cust_name", "to": "name",        "type": "VARCHAR(255)"},
    {"from": "status_cd", "to": "status",      "type": "VARCHAR(20)"}
  ]
}

Generated View (target)

CREATE OR REPLACE VIEW sav.customer AS
SELECT
  CAST(cust_id   AS BIGINT)        AS customer_id,
  CAST(cust_name AS VARCHAR(255))  AS name,
  CAST(status_cd AS VARCHAR(20))   AS status
FROM tgt_raw.customer_scd2; -- or the appropriate raw table

SAVs must not implement business logic, dedup, unions, or time filtering.

7) Prompt Template for SQL Conversion (ChatGPT)

Use this systematic prompt to convert each legacy integration SQL:

You are migrating a legacy SQL integration query to a new platform with dual‑SCD2 historization and no curation zone. Apply ALL rules:

[Best Practices]
- Exclude inline UNION ALL; keep union pure.
- Modularize with CTEs; one concern each.
- No transformations in GROUP BY; derive earlier.
- Pre‑process joins; filter/derive in source CTEs.
- Push derivations left; specify columns explicitly; replace subqueries with named CTEs.

[Migration Rules]
- Apply schema rename map: {LEGACY_SCHEMA→TARGET_SCHEMA, ...}.
- Rename reporting placeholder: ${OLD_PERIOD}→${AS_OF_TS}.
- Remove snapshot/SCD2 stitching and any time filters inside JOINs.
- Introduce dual‑SCD2 as‑of filter in each source CTE using valid_from/valid_to.
- Remove redundant timeline CTEs and their joins.
- Read renamed/cast columns from SAVs (generated separately).

[Deliver]
- Final SQL only.
- CTE order: params → src_* (filtered) → derive_* → join_* → agg_* → final SELECT.
- No SELECT *.

Optional: Provide a small before/after example to the model to anchor expectations.

8) Automated Validation Pipeline (Python + SQLGlot)

Checks we must pass:

Source parity: the set of base source tables (after schema mapping) in legacy vs. new query is equal (or intentional differences documented).
Output parity: same output column names/types and row counts (on a shared test snapshot/as‑of point).
Rule compliance: no time predicates in JOINs, no SELECT *, pure UNION ALL, etc.

Pseudocode sketch

from sqlglot import parse_one
from sqlglot.expressions import Join, Select, Union

LEGACY = open("legacy.sql").read()
NEW = open("new.sql").read()

legacy_ast = parse_one(LEGACY, read="ansi")
new_ast    = parse_one(NEW, read="ansi")

# 1) Extract base tables
legacy_tables = {t.name for t in legacy_ast.find_all("Table")}
new_tables    = {t.name for t in new_ast.find_all("Table")}

# 2) Ensure no `*` in final projection
final_select = list(new_ast.find_all(Select))[-1]
assert all(not any(s.find("Star")) for s in [final_select]), "SELECT * found in final projection"

# 3) No time filters in JOINs
for j in new_ast.find_all(Join):
    on = j.on
    if on and any(k in on.sql().lower() for k in ["valid_from", "valid_to", "as_of_ts"]):
        raise AssertionError("Time predicates detected inside JOIN")

# 4) UNION ALL purity
for u in new_ast.find_all(Union):
    assert u.kind == "ALL", "Use UNION ALL, not UNION"
    # Optionally ensure parents are simple select nodes without transforms.

# 5) Column list extract (schemas/types if available)
# Compare with expected mapping derived from legacy parser + SAV metadata.

Extend with schema‑aware typing by introspecting catalog or by pre‑loading a JSON schema registry. Add custom detectors for GROUP BY expressions vs. aliases.

9) Data Equivalence Tests

Prepare a fixed test harness:
- Choose a canonical ${AS_OF_TS} (e.g., 2025-03-31 23:59:59).
- Materialize legacy outputs and new outputs into temp tables.
Compare:
- Column names/types identical.
- Row count match.
- Hash‑diff each row on business key (e.g., MD5(CONCAT_WS('|', cols...))).
- Tolerate known deltas only where timeline simplification intentionally changes semantics.

Example ANSI-ish test

-- Assume both outputs materialized as legacy_out and new_out
SELECT COUNT(*) FROM legacy_out EXCEPT SELECT COUNT(*) FROM new_out; -- expect 0

-- Keyed diff\ nSELECT k.*
FROM (
  SELECT COALESCE(l.pk, n.pk) pk,
         MD5(l.payload) AS lhash,
         MD5(n.payload) AS nhash
  FROM (
    SELECT pk, CONCAT_WS('|', col1, col2, col3) AS payload FROM legacy_out
  ) l
  FULL OUTER JOIN (
    SELECT pk, CONCAT_WS('|', col1, col2, col3) AS payload FROM new_out
  ) n USING (pk)
) k
WHERE k.lhash IS DISTINCT FROM k.nhash;

10) Metadata Extraction (for ETL framework)

Outputs needed:

Dependencies: list of source tables/views each integration query reads (post‑migration names).
Column mapping: for every integration output column, the originating source columns (lineage), plus transformations.

Suggested JSON schema

{
  "object": "integration.sales_summary",
  "depends_on": ["sav.customer", "sav.orders"],
  "columns": [
    {"name": "customer_id", "lineage": ["sav.customer.customer_id"]},
    {"name": "order_cnt",   "lineage": ["sav.orders.order_id"], "transform": "COUNT"},
    {"name": "total_amount", "lineage": ["sav.orders.amount"], "transform": "SUM"}
  ]
}

Implementation hint: SQLGlot’s lineage utilities (or custom traversal) to map Select expressions to base Column nodes; pair with the SAV generator to normalize names.

11) Developer Checklist (per query)

Applied schema rename map.
Replaced reporting placeholder.
Removed snapshot/stitching helpers.
Introduced dual‑SCD2 filters in every src_* CTE.
No time predicates in JOINs.
Removed redundant timeline CTEs and references.
Reads via SAVs (rename/cast only).
No SELECT *; explicit column lists.
UNION ALL purity; modular CTEs; no transforms in GROUP BY.
Automated checks executed and green.
Data equivalence tests executed; diffs reviewed.
Metadata JSON emitted and registered.

12) Rollout Plan

Inventory legacy queries + source JSON metadata.
Generate SAVs from metadata; deploy.
Convert integration SQL via the prompt template.
Run automated static checks (SQLGlot).
Execute data equivalence tests on controlled as‑of.
Emit and register metadata (dependencies/mapping).
Peer review and approve.
Promote to higher envs with the same harness.

13) Optional Enhancements

Build a small CLI to:
- Parse legacy SQL → apply mechanical transforms (schema/placeholder changes, removal of helpers).
- Inject the §5 skeleton (params/src/derive/join/agg) where patterns are detected.
- Run SQLGlot checks and produce a markdown compliance report.
Maintain a catalog of raw SCD2 tables (valid_from/valid_to column names, PKs) to standardize the as‑of filter generator.

Appendix A: Dual‑SCD2 Filter Macro

AS_OF_FILTER(table_alias, as_of_ts) :=
  {table_alias}.valid_from <= {as_of_ts}
  AND ({table_alias}.valid_to > {as_of_ts} OR {table_alias}.valid_to IS NULL)

Use this macro inside source CTEs only.

Appendix B: GROUP BY Guardrail

Disallow GROUP BY expressions containing ( or operators; require grouping by column names or ordinals paired with pre‑computed aliases.

Appendix C: UNION ALL Purity Guardrail

Validate each branch of a union is a plain SELECT from a designated CTE with identical schema; no inline derivations.

End of Playbook

Conclusion

Migrating SQL code can look like a daunting, purely technical exercise. But when done right, it becomes much more: an organizational reset that cleans up the past, creates consistency for the present, and enables agility for the future. By combining dual‑SCD2 historization, source access views, and best‑practice SQL design, you don’t just modernize your ETL—you give your business a robust data foundation that will stand the test of time.

If you’re planning such a migration, treat it as more than code conversion. It’s your chance to finally align business logic, data architecture, and governance in one clean, modern approach.