Lab 11 · ERP data migration and reconciliation

Lakshmi is the data lead on an S/4HANA migration where the system integrator's dashboard says "load complete, 99.4% success." In ERP migration, 99.4% is not a grade, it is a confession: 0.6% of financial line items is an audit finding with a currency attached. The discipline this lab installs is the one migration factories actually run — a validation suite where completeness, referential integrity, value sets, and amount totals by currency must reconcile exactly, and where "the totals tie out to the rupee" is the sentence that closes the cutover call.

Time & tier

~120 minutes hands-on · Core tier (maps to module M7 · AI-Accelerated ERP Development, Testing & Migration).

Setup

DuckDB and Python, $0. Generate the source extracts — SAP-flavored column names, deterministic seed — with make_extracts.py:

import csv, random
random.seed(42)
# Customer master (KNA1-style)
with open("src_customers.csv","w",newline="") as f:
    w = csv.writer(f); w.writerow(["KUNNR","NAME1","LAND1","ORT01"])
    for i in range(1, 1201):
        land = random.choice(["IN","IN","IN","US","JP","DE",""])  # some blank
        w.writerow([f"{i:06d}", f"Customer {i}", land, "City"+str(i%40)])
# Vendor master (LFA1-style): full file + a delta file with overlap
vendors = [[f"V{i:05d}", f"Vendor {i}", random.choice(["IN","JP","US"])]
           for i in range(1, 501)]
with open("src_vendors_full.csv","w",newline="") as f:
    w = csv.writer(f); w.writerow(["LIFNR","NAME1","LAND1"]); w.writerows(vendors)
with open("src_vendors_delta.csv","w",newline="") as f:
    w = csv.writer(f); w.writerow(["LIFNR","NAME1","LAND1"])
    w.writerows(vendors[475:500])            # 25 vendors re-sent in the delta
# Financial line items (BSEG-style; WRBTR has implied decimals per SAP:
#   2 for INR/USD/EUR, 0 for JPY)
with open("src_lineitems.csv","w",newline="") as f:
    w = csv.writer(f)
    w.writerow(["BELNR","BUKRS","KUNNR","WAERS","WRBTR","BUDAT"])
    for i in range(1, 20001):
        curr = random.choice(["INR","INR","INR","USD","JPY","EUR"])
        amt  = random.randint(1000, 9000000)
        bukrs = random.choice(["1000","1000","2000"])
        day = random.randint(1, 28)
        w.writerow([f"{i:010d}", bukrs, f"{random.randint(1,1200):06d}",
                    curr, amt, f"2026-06-{day:02d}"])
print("extracts written")

The integrator's load script, load_target.sql — run as delivered; the four defects live here:

create table t_customers as
  select KUNNR as customer_id, NAME1 as name, LAND1 as country, ORT01 as city
  from read_csv_auto('src_customers.csv', all_varchar=true)
  where LAND1 is not null and LAND1 <> '';        -- "cleanup" filter

create table t_vendors as
  select LIFNR as vendor_id, NAME1 as name, LAND1 as country
  from read_csv_auto('src_vendors_full.csv', all_varchar=true)
  union all
  select LIFNR, NAME1, LAND1
  from read_csv_auto('src_vendors_delta.csv', all_varchar=true);

create table t_lineitems as
  select BELNR as doc_no, BUKRS as company_code, KUNNR as customer_id,
         WAERS as currency,
         cast(WRBTR as decimal(15,2)) / 100.0 as amount,   -- de-shift decimals
         case when BUKRS = '2000'
              then cast(BUDAT as date) + interval 1 day    -- "UTC alignment"
              else cast(BUDAT as date) end as posting_date
  from read_csv_auto('src_lineitems.csv', all_varchar=true);

Run: python3 make_extracts.py, then duckdb erp.duckdb < load_target.sql.

Guided steps

1. 1.

   Have AI draft the source-to-target mapping spec. Give the assistant the source headers, ten sample rows per file, and the target DDL (from the load script), and ask for a field-mapping table: source field, meaning (it should recognize KUNNR/LIFNR/WAERS/WRBTR/BUDAT), target field, transformation, and — the clause that earns its keep — "list every SAP storage convention that requires a transformation, including currency decimal handling." Expected: a competent draft that flags WRBTR's implied-decimal convention. Verify its claim against the convention stated in the generator comment: 2 implied decimals for INR/USD/EUR, 0 for JPY — SAP stores amounts shifted by the currency's decimal count, and any load that divides everything by 100 is wrong for zero-decimal currencies. Save as mapping-spec.md, with your verification note.

2. 2.

   Have AI draft the validation suite — four families, then harden it. Prompt: "Write DuckDB validation SQL comparing source extracts to target tables: (1) completeness — row counts per entity with difference; (2) referential integrity — line items referencing missing customers; (3) value sets — currencies, company codes, countries; (4) amount reconciliation — totals by currency and by company code, source vs target, exact difference." Review the draft with one rule: every check must output source figure, target figure, difference — a check that prints only "OK" is a check that hides the size of the miss. Save the hardened suite as validate.sql.

3. 3.

   Run completeness — catch defect 1. Expected output shape:

   Copy

   entity      source_rows  target_rows  diff
   customers        1200        1015      -185
   vendors           500         525       +25
   lineitems       20000       20000         0
   

   (Exact customer diff depends on the seed's blank-country draw — the point is it is large and negative.) Drill into which rows: source-minus-target on customer_id shows every dropped row has blank LAND1 — the load's "cleanup" filter silently deleted customers. Classify: dropped rows; disposition: blank country is a data-quality issue to fix in a mapping rule (default country per business decision), never a row filter.

4. 4.

   Catch defect 3 in the same pass. Vendors show +25: select vendor_id, count(*) from t_vendors group by 1 having count(*) > 1 — 25 vendors loaded twice because the full file and the delta overlap. Classify: duplicated master data; disposition: delta loads must upsert on LIFNR, not union.

5. 5.

   Run referential integrity. Line items referencing customers that no longer exist in the target — expected: a large count, and every orphan traces to a customer dropped in defect 1 (secondary damage: the dropped rows broke RI downstream). This is why RI checks run after completeness is explained, and why fixing defect 1 must clear this check too.

6. 6.

   Run amount reconciliation — catch defect 2, to the rupee. Totals by currency, source (with the correct per-currency decimal shift applied in the check itself) versus target:

   Copy

   with src as (
     select WAERS as currency,
            sum(cast(WRBTR as decimal(18,2)) /
                case when WAERS = 'JPY' then 1 else 100 end) as src_total
     from read_csv_auto('src_lineitems.csv', all_varchar=true) group by 1),
   tgt as (select currency, sum(amount) as tgt_total from t_lineitems group by 1)
   select s.currency, s.src_total, t.tgt_total,
          s.src_total - t.tgt_total as diff
   from src s join tgt t using (currency) order by 1;
   

   Expected: INR, USD, EUR tie to 0.00 exactly; JPY differs by a factor of 100 — the load divided JPY by 100 when JPY carries zero implied decimals. Classify: currency mis-scaling; state the blast radius (every JPY invoice understated 100x — the kind of defect that survives percentage-based sampling because JPY is 1/6th of rows and "looks like numbers").

7. 7.

   Run date reconciliation — catch defect 4. Min/max/count of posting dates by company code, source vs target. Expected: company 1000 ties exactly; company 2000 is shifted +1 day (target has 2026-06-02..29 against source 2026-06-01..28, same counts). The load's "UTC alignment" cast moved posting dates — in ERP finance, posting date drives the period; a +1 shift at month-end moves revenue across fiscal periods. Classify: date shift; disposition: posting dates are business dates, not timestamps; no timezone arithmetic, ever.

8. 8.

   Fix the load, re-run everything, sign the report. Author load_target_fixed.sql (keep blank-country rows with a defaulting rule, upsert vendors on LIFNR, per-currency decimal shift, no date arithmetic), rebuild, re-run validate.sql. Expected: every diff column reads 0 / 0.00. Write reconciliation-report.md: the four defects — each with the detecting check, the exact figures before fix, root cause, disposition — the all-green after-run, and the sign-off line: "Row counts, RI, value sets and amount totals reconcile exactly; amounts tie out to the rupee (and the yen). Approved for cutover." Signed, dated.

Verify

• •
  All four defects appear in the report with before-fix figures from your own validation output, not from this document.
• •
  The amount reconciliation shows exact 0.00 differences per currency after the fix — no tolerance thresholds, no "within 1%".
• •
  The RI orphans are explained as secondary damage from defect 1 and clear when it is fixed.
• •
  validate.sql checks always emit source/target/difference triples.
• •
  The mapping spec contains the AI's currency-convention claim plus your verification note.

Self-grading rubric (pass = 8/10): mapping spec drafted and the decimal convention verified, not trusted (0–2); validation suite covers all four families with figure-diff output (0–2); all four defects caught with correct classification and root cause (0–4, one each); fixed load reconciles exactly and the report carries a dated sign-off (0–2).