Draft Go-Live Data Mappings and Reconciliation SQL

Tier: Core · Workflow area: ERP Data & Close · ~40 minutes

Data migration fails at the seams: the field mapped by name instead of meaning, the value list that almost matches, the 2% of rows silently dropped by a join. Mapping specs and validation queries are exactly the artifacts LLMs draft well — both are structured transformations of structured inputs — and exactly the artifacts teams under deadline write too thin. The stance throughout: AI drafts the mapping and the checks; the checks run against real extracts; humans sign only on the numbers the checks produce.

When to use

• Any migration wave with a formal cutover sign-offlegacy → S/4HANA, acquisitions onto your ERP, CRM consolidations; wherever someone must attest "the data arrived," these queries are the attestation's evidence.
• Mapping workshops that stall on field archaeologya drafted mapping with confidence flags turns a blank-page workshop into a review meeting; disagreement with a draft is faster than consensus from nothing.
• Mock loads and rehearsal cutoversthe reconciliation pack earns its keep run repeatedly: every mock load rerun should produce the same report shape with shrinking exception counts.

When NOT to use

• Letting the AI decide business meaningwhether legacy CUST_CLASS = 'A' maps to a customer group or a credit segment is a business decision. The draft proposes with a confidence flag; the process owner disposes.
• Validating only what's easy to queryrow counts alone are a comfort blanket. If the pack lacks amount reconciliation and referential checks, it validates transport, not truth.
• Running validation SQL against production ERP mid-cutover without coordinationheavy validation queries during the cutover window compete with the load itself; run against staged extracts or agreed read replicas.

The recipe

1. 1.

   Extract both data dictionaries, not your memory of them. Source side: table/column lists with types, nullability and — gold when available — value-frequency profiles (SELECT col, COUNT(*) GROUP BY col on code fields). Target side: the migration template or API contract (S/4HANA migration-cockpit object structure, D365 data-entity schema, Fusion FBDI columns). Profiles beat dictionaries: what a column contains outranks what it was named for.

2. 2.

   Draft the mapping with confidence flags and open questions.

   Copy

   Draft a source-to-target mapping for customer master migration.
   Source: legacy dictionary + value profiles (attached).
   Target: S/4HANA Business Partner migration object structure (attached).
   
   For each target field: source field(s), transformation rule
   (verbatim, e.g. "trim; uppercase; map via value table VT-03"),
   and a confidence flag:
     HIGH   - name, type and values all align
     MEDIUM - plausible but needs owner confirmation
     LOW    - guessed; treat as an open question
   Unmapped target mandatories and unmapped source fields go in
   separate exception lists. Value mappings (code translations)
   output as their own tables — source value, target value, count
   from the profile, flag.
   

   Review order: LOW flags, then the exception lists, then MEDIUM with the process owner. HIGH still gets sampled — confidence flags are the model's self-assessment, not an oath.

3. 3.

   Generate the four-layer validation pack. DuckDB is ideal here: point it at source extracts and post-load target extracts as CSV/Parquet and run everything locally. Ask for the four layers by name:

   Copy

   -- 1. COMPLETENESS: every source row accounted for
   SELECT (SELECT COUNT(*) FROM src_customers WHERE migrate_flag='Y') AS source_rows,
          (SELECT COUNT(*) FROM tgt_partners)                         AS loaded_rows,
          (SELECT COUNT(*) FROM excl_log)                             AS excluded_rows;
   -- Pass condition: source_rows = loaded_rows + excluded_rows, each exclusion reason-coded.
   
   -- 2. REFERENTIAL: no orphans crossed the bridge
   SELECT t.partner_id FROM tgt_partners t
   LEFT JOIN tgt_company_codes c ON c.partner_id = t.partner_id
   WHERE c.partner_id IS NULL;          -- must return 0 rows
   
   -- 3. VALUE SETS: only agreed target values exist
   SELECT payment_terms, COUNT(*) FROM tgt_partners
   GROUP BY payment_terms
   HAVING payment_terms NOT IN (SELECT tgt_value FROM map_payment_terms);
   
   -- 4. AMOUNTS: balances reconcile per stratum
   SELECT s.company, ROUND(SUM(s.open_amount),2) AS src_total,
          ROUND(SUM(t.open_amount),2)            AS tgt_total,
          ROUND(SUM(s.open_amount)-SUM(t.open_amount),2) AS diff
   FROM src_open_items s JOIN tgt_open_items t USING (company)
   GROUP BY s.company HAVING ABS(diff) > 0.01;
   

   Every query carries its pass condition as a comment — a check without a written pass condition is a chart, not a control.

4. 4.

   Verify the verifiers on a poisoned sample. Before trusting the pack, break data deliberately: delete five rows, orphan two references, plant one off-list value and one shifted amount in a copy of the target extract. Every planted defect must be caught. A validation query that misses its planted defect is worse than none — it manufactures confidence. This step is non-negotiable and takes twenty minutes.

5. 5.

   Run on the mock load and drive exceptions to zero or to reasons. Execute the pack; every failing row gets a disposition — mapping fix, source cleanup, or documented accepted exception with owner and rationale. Rerun after each fix cycle. The trend across mock loads (247 exceptions → 31 → 0) is itself sign-off evidence.

6. 6.

   Assemble the sign-off sheet. One page per object: source/loaded/excluded counts, the four layer results with pass conditions, amount reconciliation table to the cent, accepted-exceptions register, extract timestamps and query-pack version. The cutover decision meeting reads numbers, not adjectives.

Make it yours

• SAPtarget structures come from the Migration Cockpit object modeler; run amount reconciliation against FI extracts (open items, G/L balances per company code) and reconcile to the legacy trial balance — finance will anyway, so beat them to it.
• Oracle FusionFBDI templates give exact target contracts; import-rejection logs are your excluded_rows feed and must reconcile into the completeness equation.
• Microsoft Dynamics 365data-entity staging tables expose load results cleanly; run the pack against staging-to-target as well, since DMF's two-hop load can drop rows at either hop.

Quality check

• Completeness closes as an equationsource = loaded + excluded, with every exclusion reason-coded; "roughly matches" is how the missing 2% ships.
• The pack survived the poisoned sampleall planted defects caught, documented with the run output.
• Amount reconciliation is stratifiedtotals that match globally can hide offsetting errors; per company code, currency and period, to the cent.
• Every LOW/MEDIUM mapping has a named human decisionthe mapping sheet shows who confirmed what; "the AI suggested it" appears nowhere in the lineage.
• The pack is rerunnable by someone elsescripts, extract instructions and pass conditions in the repo; a reconciliation only one laptop can produce is a bus-factor incident.