ERP Data Migration: Field Mapping & Validation Playbook

Step-by-step guide for ERP data migration. Covers data profiling, field mapping, transformation rules, validation testing, cutover planning, and common pitfalls.

ERP data migration is the process of moving structured business data—such as customer master records, inventory items, financial GL accounts, and sales orders—from legacy systems into a modern ERP platform. This playbook applies when you are mapping source fields to target fields and validating the migrated data for accuracy, completeness, and consistency. It is used during the data migration phase of an ERP implementation, especially when transitioning from multiple siloed systems (e.g., SAP, Oracle, FileMaker, or flat CSVs) into a unified ERP like Microsoft Dynamics 365 Finance & Operations, SAP S/4HANA, or Oracle Fusion.

Field Mapping: From Source to Target

Field mapping defines how data from the source system is transformed and placed into the corresponding fields in the ERP target. The goal is to ensure that every business attribute has a correct, reliable, and traceable path from source to target.

Define Mapping Rules

Each field mapping is defined by a source field, target field, and transformation logic. The source field may be a column in a CSV, a database column in a legacy system, or a field in a JSON API response. The target field is a database column in the ERP, often defined in a table like CustTable or InventTable.

Mapping rules are stored in a mapping configuration file that describes:

A minimal configuration in JSON:

{
  "mappingId": "cust-migration-2024",
  "sourceSystem": "CSV-2023-12",
  "targetEntity": "CustTable",
  "mappings": [
    {
      "sourceField": "CustomerID",
      "targetField": "AccountNumber",
      "transformation": "trim",
      "required": true
    },
    {
      "sourceField": "Name",
      "targetField": "Name",
      "transformation": "uppercase",
      "required": true
    },
    {
      "sourceField": "BillingCity",
      "targetField": "City",
      "transformation": "formatDate:YYYY-MM-DD",
      "defaultValue": "Unknown"
    }
  ]
}

Apply Transformations

Transformations are applied sequentially. Common ones include:

For lookup, the source system must provide a lookup table. For example, to map CountryCode to CountryName, the system reads a Country table from the legacy system and uses it as a lookup.

The transformation engine executes on each row of input data. A transformation failure results in a mapping error, logged in a MappingErrorLog table with:

Handle Missing or Invalid Source Data

When a required field is missing or malformed:

Example error in logs:

[ERROR] Mapping failed for row 2034 in batch 'cust-migration-2024'
  Source: CustomerID = "C00123 "
  Target: AccountNumber = "C00123"
  Transformation: trim → failed: expected "C00123", got "C00123 " (trailing space)
  Reason: Source field 'CustomerID' is required but value is null or empty

Data Validation: Ensuring Quality Post-Migration

Validation verifies that migrated data meets business rules, integrity constraints, and consistency criteria. It runs after the field mapping phase and before cutover.

Validate Against Business Rules

Each validation rule is a SQL predicate or JSON-based condition that evaluates data in the target ERP. Rules are defined in a ValidationRule table:

ruleIddescriptionqueryenabledseverity
cust-101Customer must have at least one addressSELECT * FROM CustTable t JOIN CustTableAddress a ON t.RecId = a.CustTable WHERE a.RecId IS NOT NULLtruewarning
item-203Item cost must be positiveSELECT * FROM InventTable WHERE CostPrice <= 0trueerror

The validation engine executes all enabled rules and records results in ValidationResult:

{
  "ruleId": "item-203",
  "status": "failed",
  "message": "Found 12 items with cost price <= 0",
  "affectedRows": [
    { "itemId": "MAT-001", "costPrice": 0 },
    { "itemId": "LAB-005", "costPrice": -5.75 }
  ],
  "executionTime": "2024-04-05T14:33:22Z"
}

Validate Data Completeness

Completeness is checked by counting records and verifying required fields.

A completeness check is defined by:

The validation engine runs:

SELECT
  COUNT(*) AS totalRecords,
  COUNT(CASE WHEN Name IS NOT NULL THEN 1 END) AS nameFilled,
  COUNT(CASE WHEN AccountNumber IS NOT NULL THEN 1 END) AS accountNumberFilled
FROM CustTable
WHERE Status = 'Active';

If totalRecords < threshold or any requiredField has NULL values, the validation fails.

Validate Data Consistency

Consistency checks ensure referential integrity and business logic.

Example: Validate that every SalesOrderHeader has a CustAccount that exists in CustTable.

The engine runs:

SELECT
  so.RecId,
  so.CustAccount,
  COUNT(*) AS missingCustomerCount
FROM SalesOrderHeader so
LEFT JOIN CustTable ct ON so.CustAccount = ct.AccountNumber
WHERE ct.RecId IS NULL
GROUP BY so.RecId, so.CustAccount;

If the result has any rows, the validation fails with a list of missing customer accounts.

Generate and Review Validation Reports

A ValidationReport is generated after all rules are executed. It includes:

The report is published to a shared location (e.g., /reports/migration/2024-04-05/validation-report.json), and consumed by the ERP’s MigrationDashboard.

Common Failure Modes and Mitigation

Silent Failures in Field Mapping

Validation Execution Issues

Mapping and Validation Configuration Drift

Iterative Migration Workflow

  1. Extract: pull data from source systems into staging (e.g., staging/cust-migration-2024.csv)
  2. Map: apply field mappings using the configuration file
  3. Transform: execute transformations (trim, lookup, format, etc.)
  4. Load: insert into ERP target tables (e.g., INSERT INTO CustTable (...) VALUES (...))
  5. Validate: run all validation rules against the loaded data
  6. Report: generate ValidationReport.json and publish to shared storage
  7. Review: team reviews report, fixes issues in source data or mapping rules
  8. Re-run: repeat steps 2–6 with updated configuration or data

The workflow is orchestrated using a script:

#!/bin/bash


MAPPING_FILE="config/mappings/cust-migration-2024.json"
SOURCE_DATA="staging/cust-migration-2024.csv"
TARGET_BATCH="cust-migration-2024"

# Step 1: Map and transform
./map-data.sh \
  --input "$SOURCE_DATA" \
  --mapping "$MAPPING_FILE" \
  --output "transformed/cust-migration-2024.json"

# Step 2: Load into ERP
curl -X POST \
  -H "Content-Type: application/json" \
  -d @transformed/cust-migration-2024.json \
  http://erp/api/v1/data/load/custtable

# Step 3: Validate
./validate-data.sh \
  --batch "$TARGET_BATCH" \
  --output "reports/validation-report-$(date +%Y-%m-%d).json"

# Step 4: Publish
aws s3 cp reports/validation-report-*.json s3://erp-migration/reports/

# Step 5: Mark batch as complete
psql -c "UPDATE MigrationBatch SET status = 'completed', reportPath = 'reports/validation-report-*.json' WHERE batchId = '$TARGET_BATCH'"

This workflow runs in CI/CD, ensuring consistency across multiple migration cycles.

Field Mapping and Validation Checklist

This playbook is the foundation of reliable, traceable, and maintainable ERP data migration.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.