Lab 02 · Legacy migration sprint

The procedure hides four classic migration traps (a NULL-excluding filter, an ISNULL default, a cursor computing running totals, and a legacy date-format conversion), and the lab quantifies what happens when you "helpfully fix" legacy behavior mid-migration: parity collapses by hundreds of rows. The discipline this trains is the one services teams bill for — transpiler-first, LLM-refinement-second, and never declare a migration done on vibes: parity is a number, and the number must be zero.

Time & tier

~150 minutes hands-on · Core tier (maps to module M2 · Legacy Modernization with AI).

Setup

Everything runs locally — no SQL Server, no warehouse, $0. The legacy system is simulated: a generator script produces the input tables and the procedure's expected output computed with faithful T-SQL semantics, playing the role of the production run you would normally diff against.

mkdir migration-lab && cd migration-lab
python3 -m venv .venv && source .venv/bin/activate
pip install duckdb

Save the legacy procedure as legacy/usp_monthly_customer_summary.sql. In production this pattern runs 600+ lines; the lab version is condensed but keeps every semantic branch you would fight in the real one:

CREATE PROCEDURE dbo.usp_monthly_customer_summary
AS
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#monthly') IS NOT NULL DROP TABLE #monthly;

    SELECT
        t.customer_id,
        CONVERT(varchar(6), t.txn_date, 112) AS month_key,
        SUM(CASE WHEN t.txn_type = 'SALE'   THEN ISNULL(t.amount, 0)
                 WHEN t.txn_type = 'REFUND' THEN -ISNULL(t.amount, 0)
                 ELSE 0 END)                AS net_amount,
        SUM(CASE WHEN t.txn_type = 'FEE'    THEN ISNULL(t.amount, 0)
                 ELSE 0 END)                AS fee_total,
        COUNT(*)                            AS txn_count
    INTO #monthly
    FROM dbo.Transactions t
    WHERE t.region <> 'TEST'
    GROUP BY t.customer_id, CONVERT(varchar(6), t.txn_date, 112);

    IF OBJECT_ID('dbo.MonthlyCustomerSummary') IS NOT NULL
        TRUNCATE TABLE dbo.MonthlyCustomerSummary;

    DECLARE @cust INT, @month varchar(6), @net DECIMAL(18,2),
            @fees DECIMAL(18,2), @cnt INT;
    DECLARE @running DECIMAL(18,2) = 0, @prev_cust INT = -1;

    DECLARE month_cursor CURSOR FOR
        SELECT customer_id, month_key, net_amount, fee_total, txn_count
        FROM #monthly
        ORDER BY customer_id, month_key;

    OPEN month_cursor;
    FETCH NEXT FROM month_cursor INTO @cust, @month, @net, @fees, @cnt;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @cust <> @prev_cust
            SET @running = 0;
        SET @running = @running + @net;
        SET @prev_cust = @cust;

        INSERT INTO dbo.MonthlyCustomerSummary
            (customer_id, month_key, net_amount, fee_total, txn_count, is_vip)
        VALUES
            (@cust, @month, ROUND(@net, 2), ROUND(@fees, 2), @cnt,
             CASE WHEN @running >= 5000 THEN 1 ELSE 0 END);

        FETCH NEXT FROM month_cursor INTO @cust, @month, @net, @fees, @cnt;
    END
    CLOSE month_cursor;
    DEALLOCATE month_cursor;
END

Now make_legacy_data.py — it generates transactions.csv, customers.csv, and legacy_expected_output.csv (the procedure's faithful output, your parity target):

"""Generate legacy inputs AND the legacy expected output (faithful to the T-SQL semantics)."""
import csv, random, os
from collections import defaultdict
random.seed(7)
OUT = os.path.dirname(os.path.abspath(__file__))

N_CUST, N_TXN = 120, 6000
tiers = ["STD", "GOLD", "STD", "STD", "SILVER"]
with open(f"{OUT}/customers.csv", "w", newline="") as f:
    w = csv.writer(f); w.writerow(["customer_id", "tier", "active_flag"])
    for c in range(1, N_CUST + 1):
        w.writerow([c, random.choice(tiers), random.choice([1, 1, 1, 0])])

types = ["SALE"] * 78 + ["REFUND"] * 12 + ["FEE"] * 10
regions = ["NORTH", "SOUTH", "EAST", "WEST", "TEST", ""]  # "" -> NULL region
txns = []
for t in range(1, N_TXN + 1):
    cid = random.randint(1, N_CUST)
    month = random.randint(1, 6); day = random.randint(1, 28)
    amount = round(random.uniform(5, 900), 2)
    if random.random() < 0.01:
        amount = ""  # NULL amount
    txns.append([t, cid, f"2025-{month:02d}-{day:02d}", amount,
                 random.choice(types), random.choice(regions)])
with open(f"{OUT}/transactions.csv", "w", newline="") as f:
    w = csv.writer(f); w.writerow(["txn_id", "customer_id", "txn_date", "amount", "txn_type", "region"])
    w.writerows(txns)

# ---- legacy semantics, faithfully ----
agg = defaultdict(lambda: {"net": 0.0, "fees": 0.0, "n": 0})
for t in txns:
    _, cid, date, amount, ttype, region = t
    if region == "" or region == "TEST":   # NULL fails <> 'TEST' in T-SQL
        continue
    amt = float(amount) if amount != "" else 0.0   # ISNULL
    key = (cid, date[:4] + date[5:7])              # CONVERT(varchar(6), date, 112) = YYYYMM
    if ttype == "SALE":   agg[key]["net"] += amt
    elif ttype == "REFUND": agg[key]["net"] -= amt
    else: agg[key]["fees"] += amt
    agg[key]["n"] += 1

rows = []
cum = defaultdict(float)
for (cid, month) in sorted(agg.keys(), key=lambda k: (k[0], k[1])):
    a = agg[(cid, month)]
    cum[cid] += a["net"]
    rows.append([cid, month, round(a["net"], 2), round(a["fees"], 2), a["n"],
                 1 if cum[cid] >= 5000 else 0])
with open(f"{OUT}/legacy_expected_output.csv", "w", newline="") as f:
    w = csv.writer(f)
    w.writerow(["customer_id", "month_key", "net_amount", "fee_total", "txn_count", "is_vip"])
    w.writerows(rows)
print(f"rows in legacy output: {len(rows)}")
print(f"total net: {round(sum(r[2] for r in rows),2)}  vip rows: {sum(r[5] for r in rows)}")

Run it: python make_legacy_data.py. Expected: rows in legacy output: 716, total net: 1236057.82 vip rows: 399. Those three numbers are your parity target.

Guided steps

1. 1.

   Archaeology before conversion. Paste the full procedure into your assistant (Claude, ChatGPT, or Gemini) with this prompt: "Explain this stored procedure for a migration assessment. Produce: (1) a plain-English summary, (2) every business rule as a numbered list, (3) all NULL-handling behavior, (4) what the cursor computes and its modern-SQL equivalent, (5) any behavior a naive rewrite would silently change." Save the answer as explanation.md. Grade it before trusting it: the four things it must surface are the NULL-region exclusion, the ISNULL-to-zero on amounts, the per-customer running total resetting on customer change, and the YYYYMM month key. If any is missing, ask a follow-up — an explanation doc that misses a rule becomes a conversion that misses a rule.

2. 2.

   Decide the behavior-preservation policy and write it down. The WHERE t.region <> 'TEST' clause excludes NULL-region rows as a side effect of three-valued logic. Is that a bug or a feature? For migration purposes it does not matter: phase 1 preserves behavior exactly; behavior changes are a separately approved phase 2. Add that sentence to explanation.md. This single policy decision is what separates a parity-provable migration from an argument with the client.

3. 3.

   Convert. Write convert.py — load the CSVs into DuckDB, build the model, and land it as monthly_customer_summary. The reference conversion (write yours first, then compare):

   Copy

   import duckdb
   con = duckdb.connect("migration.duckdb")
   con.sql("create or replace table transactions as select * from read_csv_auto('transactions.csv')")
   con.sql("""create or replace table legacy_expected as
              select * from read_csv_auto('legacy_expected_output.csv', types={'month_key':'VARCHAR'})""")
   con.sql("""
   create or replace table monthly_customer_summary as
   with base as (
       select customer_id,
              strftime(txn_date, '%Y%m')            as month_key,
              coalesce(amount, 0)                   as amount,
              txn_type
       from transactions
       where region is not null and region <> 'TEST'  -- legacy NULL exclusion, made EXPLICIT
   ),
   monthly as (
       select customer_id, month_key,
              round(sum(case when txn_type = 'SALE' then amount
                             when txn_type = 'REFUND' then -amount
                             else 0 end), 2)        as net_amount,
              round(sum(case when txn_type = 'FEE' then amount else 0 end), 2) as fee_total,
              count(*)                              as txn_count
       from base group by 1, 2
   )
   select customer_id, month_key, net_amount, fee_total, txn_count,
          case when sum(net_amount) over (partition by customer_id order by month_key) >= 5000
               then 1 else 0 end                    as is_vip
   from monthly
   """)
   

   Note the two translation moves that matter: the cursor becomes a window function (sum() over (partition by customer_id order by month_key) reproduces "running total, reset per customer"), and the NULL exclusion is written explicitly (region is not null and region <> 'TEST') so the preserved legacy behavior is visible in code review instead of hiding in tri-state logic.

4. 4.

   Generate the reconciliation suite. Ask your assistant: "Generate a three-level parity test suite in SQL comparing tables monthly_customer_summary and legacy_expected with identical schemas: level 1 row counts, level 2 per-column checksums (SUM of each numeric, SUM of the flag), level 3 full-row EXCEPT in both directions." Review what comes back — both EXCEPT directions is the item assistants most often drop, and one direction alone can hide extra rows. Append to convert.py:

   Copy

   print("counts:", con.sql("""select (select count(*) from monthly_customer_summary),
                                      (select count(*) from legacy_expected)""").fetchall())
   print("checksums A:", con.sql("""select round(sum(net_amount),2), round(sum(fee_total),2),
                                          sum(txn_count), sum(is_vip) from monthly_customer_summary""").fetchall())
   print("checksums B:", con.sql("""select round(sum(net_amount),2), round(sum(fee_total),2),
                                          sum(txn_count), sum(is_vip) from legacy_expected""").fetchall())
   print("except A-B:", con.sql("""select count(*) from (select * from monthly_customer_summary
                                   except select * from legacy_expected)""").fetchall())
   print("except B-A:", con.sql("""select count(*) from (select * from legacy_expected
                                   except select * from monthly_customer_summary)""").fetchall())
   

5. 5.

   Run parity. python convert.py. Target: counts 716 | 716, checksums identical on both sides (1236057.82, 170850.6, 4009, 399), and both EXCEPT counts exactly 0. If you hit zero on the first run, excellent — now break it on purpose so you learn what each parity level catches.

6. 6.

   Break it two ways and record the blast radius. In parity-report.md, run and document both classic wrong conversions. (a) The helpful fix: change the filter to coalesce(region,'UNKNOWN') <> 'TEST' to "stop dropping NULL regions" — parity explodes to 546 differing rows, because you changed net amounts, counts, and VIP flags across the board. (b) The cursor misread: convert the running total as a lifetime total, sum(net_amount) over (partition by customer_id) >= 5000 — 301 differing rows, every early month of every eventually-VIP customer flagged a year too early. Note in the report which parity level caught each one first (the row count catches neither! both fail only at checksum/EXCEPT level — this is why counts alone are not parity).

7. 7.

   Package the migration. Assemble migration-package/: explanation.md, the converted SQL, convert.py, and parity-report.md showing the passing run plus both deliberate failures. Finish the report with a phase-2 recommendations section: the NULL-region exclusion and the ISNULL-to-zero defaults, documented as candidate behavior changes for the client to approve — after cutover, never during.

Verification

• •
  explanation.md names all four semantic traps (NULL region filter, ISNULL defaults, cursor running total with per-customer reset, YYYYMM key).
• •
  python convert.py prints counts 716 = 716, matching checksums, and except A-B: 0, except B-A: 0.
• •
  parity-report.md documents both deliberate breaks with their measured diff counts (546 and 301) and which parity level caught them.
• •
  The converted SQL contains an explicit, commented region is not null clause — preserved behavior is visible, not accidental.

Completion artifact

The migration-package/ folder: explanation doc, converted model, parity harness, and parity report showing 100% match. Self-grading rubric (pass = 8/10):

• Archaeology quality (0–2): explanation doc captures all four traps, graded against the source rather than accepted as-is = 2.
• Behavior preservation (0–2): legacy NULL semantics preserved and made explicit; phase-1/phase-2 policy stated in writing = 2.
• Cursor translation (0–2): running total converted with a correctly framed window function (partition + order), not a lifetime aggregate = 2.
• Parity rigor (0–3): all three levels implemented, EXCEPT run in both directions, zero diff achieved = 3.
• Break analysis (0–1): both deliberate breaks run, diff counts recorded, catching level identified = 1.

Stuck?

• •
  read_csv_auto types month_key as an integer and EXCEPT reports everything different: force types={'month_key':'VARCHAR'} when loading the expected output, and check your conversion emits a string (strftime, not a numeric expression).
• •
  Checksums differ by cents: you rounded in a different place than the legacy proc — round the monthly aggregates (as the proc does at INSERT time), not the raw amounts.
• •
  VIP counts off by a handful: your window function is missing order by month_key (lifetime total) or missing the partition (global running total).
• •
  EXCEPT shows rows that look identical: float formatting — cast both sides' numerics to DECIMAL(18,2) in the comparison.

Client-safe variant

Migration work is the highest-risk quadrant for client-data exposure because the code itself is client IP. The workflow survives intact under restrictions: run transpilers locally (Lakebridge is open source; SnowConvert AI runs on your machine), route the LLM explanation and refinement steps through the client's approved tenancy (Amazon Bedrock or Azure OpenAI) or a local model via Ollama, and if the procedure embeds sensitive literals or table names, apply a scripted rename map before prompting and reverse it after. The parity harness never touches an LLM and produces the deliverable your engagement letter actually promises: a reconciliation report the client's auditors can re-run themselves.

Answer key (don't read before attempting)

1. 1NULL-region exclusion:WHERE region <> 'TEST' silently drops NULL-region rows (three-valued logic). Preserve it explicitly; "fixing" it changes 546 of 716 output rows.
2. 2ISNULL(amount, 0): NULL amounts count as zero but still increment txn_count. Dropping those rows instead would shift counts and averages.
3. 3Cursor = running total with per-customer reset: correct modern form is sum(net_amount) over (partition by customer_id order by month_key). The lifetime-total misread changes 301 rows of is_vip.
4. 4CONVERT(varchar(6), date, 112): legacy style 112 is YYYYMMDD, truncated to YYYYMM at varchar(6) — modern equivalent strftime(txn_date, '%Y%m'), and it must stay a string or set comparison breaks.