ERP Data Migration Playbook

ERP data migration is a structured, repeatable sequence of actions that transforms legacy data into a target ERP system, ensuring accuracy, consistency, and traceability. It matters when launching a new ERP, upgrading an existing one, or consolidating systems—particularly in complex environments with mixed source systems, heterogeneous data formats, and strict compliance requirements. This playbook is the operational guide for engineering teams executing migrations across large-scale, mission-critical ERP landscapes.

Pre-Migration: Assessment and Planning

Define Migration Scope and Data Mapping

Begin by identifying the core data entities to migrate: customer, vendor, item, chart of accounts, inventory, purchase orders, sales orders, and financial dimensions. Each entity has a source system, a target ERP model, and a set of transformation rules.

For example, a customer record in a legacy system might have:

{
  "custId": "CUST-98765",
  "name": "Acme Industries",
  "billingAddress": {
    "street": "123 Main St",
    "city": "Anytown",
    "state": "CA",
    "zip": "90210"
  },
  "taxId": "98-7654321",
  "creditLimit": 50000.00,
  "salesperson": "SP-201"
}

The target ERP expects a Customer entity with fields: AccountNumber, Name, Street, City, State, PostalCode, VatNumber, CreditLimit, and SalespersonId. The mapping is defined in a JSON file:

{
  "source": "legacy-customer",
  "target": "erp-customer",
  "mapping": [
    {
      "sourceField": "custId",
      "targetField": "AccountNumber",
      "transform": "trim"
    },
    {
      "sourceField": "name",
      "targetField": "Name",
      "transform": "upper"
    },
    {
      "sourceField": "billingAddress.street",
      "targetField": "Street",
      "transform": "concat",
      "separator": " "
    },
    {
      "sourceField": "taxId",
      "targetField": "VatNumber",
      "transform": "regex",
      "pattern": "^([0-9]{2})-([0-9]{9})$",
      "replacement": "$1-$2"
    },
    {
      "sourceField": "creditLimit",
      "targetField": "CreditLimit",
      "transform": "round",
      "precision": 2
    }
  ]
}

This mapping file is versioned in Git and used by both data engineers and ERP developers.

Establish Source System Access and Data Quality

Ensure full access to source databases, APIs, and file systems. Use psql to query a PostgreSQL source:

psql -h legacy-db.example.com -U erp_user -d legacy_system -c "
SELECT custId, name, billingAddress, taxId, creditLimit
FROM customers
WHERE status = 'Active'
AND lastUpdated >= '2023-01-01'
LIMIT 10;
"

Run data quality checks using a script that emits a JSON report:

import json
import psycopg2

def run_data_quality_checks():
    conn = psycopg2.connect("host=legacy-db.example.com dbname=legacy_system user=erp_user")
    cursor = conn.cursor()
    cursor.execute("""
        SELECT 
            COUNT(*) AS total_records,
            COUNT(DISTINCT custId) AS unique_ids,
            COUNT(*) FILTER (WHERE name IS NULL) AS missing_names,
            COUNT(*) FILTER (WHERE taxId IS NULL) AS missing_tax_ids,
            AVG(creditLimit) AS avg_credit_limit
        FROM customers
        WHERE status = 'Active'
    """)

    result = cursor.fetchone()
    report = {
        "total_records": result[0],
        "unique_ids": result[1],
        "duplicates": result[0] - result[1],
        "missing_names": result[2],
        "missing_tax_ids": result[3],
        "avg_credit_limit": round(result[4], 2)
    }

    with open("/tmp/dq-report.json", "w") as f:
        json.dump(report, f, indent=2)

    return report

Fail the migration pipeline if duplicates > 0.1 * total_records or missing_tax_ids > 0.15 * total_records.

Migration Execution: Bulk and Incremental

Bulk Migration with ETL Pipeline

Use a standard ETL tool with a declarative configuration. The pipeline reads from source systems, applies transformations, and loads into the target ERP via a REST API.

A pipeline.yaml defines the full migration:

name: "customer-migration"
source:
  type: "postgres"
  connection: "host=legacy-db.example.com dbname=legacy_system user=erp_user"
  query: |
    SELECT custId, name, billingAddress, taxId, creditLimit, salesperson
    FROM customers
    WHERE status = 'Active'
    ORDER BY custId
  batch_size: 1000
transform:
  - name: "map-fields"
    type: "json"
    mapping_file: "mappings/customer.json"
  - name: "validate"
    type: "json-schema"
    schema: "schemas/customer.schema.json"
  - name: "enrich"
    type: "lookup"
    lookup_table: "salespeople"
    key_field: "salesperson"
    target_fields: ["salespersonName", "region"]
load:
  type: "rest"
  endpoint: "https://erp.example.com/api/customers"
  auth:
    type: "bearer"
    token: "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
  method: "POST"
  batch_size: 500
  retry_strategy:
    max_attempts: 5
    backoff: "exponential"
    base_delay: 1000
    multiplier: 2
  error_handling:
    strategy: "partial"
    error_log: "/var/log/erp-migration/errors.jsonl"

This pipeline is orchestrated with docker run:

docker run \
  --volume ./pipeline.yaml:/config/pipeline.yaml \
  --volume ./mappings:/mappings \
  --volume ./schemas:/schemas \
  --volume ./logs:/var/log \
  --env AWS_ACCESS_KEY_ID=AKIA123 \
  --env AWS_SECRET_ACCESS_KEY=secret123 \
  ghcr.io/erp-etl-tool:latest \
  --config /config/pipeline.yaml \
  --output /var/log/execution.log

The tool logs progress in real time and emits a summary at completion:

{
  "pipeline": "customer-migration",
  "start_time": "2023-10-05T08:30:00Z",
  "end_time": "2023-10-05T09:15:20Z",
  "duration_seconds": 2720,
  "total_records": 12437,
  "successful": 12381,
  "failed": 56,
  "errors": [
    {
      "record_id": "CUST-10012",
      "error": "Validation failed: missing required field 'VatNumber'",
      "details": {
        "expected": ["VatNumber", "CreditLimit"],
        "actual": ["CreditLimit"]
      }
    },
    {
      "record_id": "CUST-10023",
      "error": "HTTP 429 Too Many Requests",
      "retryable": true
    }
  ]
}

Incremental Migration and Sync

After bulk load, enable incremental sync to keep target ERP in sync with source systems.

Use a sync command that pulls new and updated records since a given timestamp:

./migrate sync \
  --source postgres \
  --connection "host=legacy-db.example.com dbname=legacy_system user=erp_user" \
  --query "
    SELECT custId, name, billingAddress, taxId, creditLimit, lastUpdated
    FROM customers
    WHERE lastUpdated > '2023-10-05T09:15:20Z'
    ORDER BY lastUpdated
  " \
  --target rest \
  --endpoint "https://erp.example.com/api/customers" \
  --auth "bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  --batch-size 250 \
  --poll-interval 300 \
  --resume-from "checkpoint-20231005-091520.json"

The checkpoint file is a JSON file with the last processed timestamp and metadata:

{
  "last_sync": "2023-10-05T09:15:20Z",
  "processed_records": 12437,
  "last_record_id": "CUST-10012",
  "total_errors": 56
}

The sync command updates this file after each batch and resumes from it on restart.

Error Handling and Reconciliation

Common Failure Modes

• Partial Success with Retryable Errors: HTTP 429, 503, or 504 during load. The tool retries up to 5 times with exponential backoff. If all retries fail, the record is logged in a JSONL file:

  {"record_id": "CUST-10012", "error": "HTTP 503 Service Unavailable", "attempt": 5, "timestamp": "2023-10-05T09:17:32Z"}

• Validation Failures: Schema mismatch or transformation errors. The tool stops the pipeline on critical validation errors but continues for non-critical ones.

• Duplicate Key Errors: When creating a customer with AccountNumber = "CUST-98765", but that key already exists, the target ERP returns 409 Conflict. The ETL tool must handle this:

  {
    "error": "Conflict",
    "code": 409,
    "message": "Customer with AccountNumber 'CUST-98765' already exists",
    "detail": {
      "existingId": "CUST-98765",
      "existingName": "Acme Industries",
      "existingCreated": "2023-01-15T10:00:00Z"
    }
  }

The tool can be configured to:

• Update the existing record (UPSERT)
• Skip the new record
• Log the conflict and send it to a reconciliation queue

• Schema Drift in Source: A new field preferredContactMethod appears in the source but is not in the target schema. The tool logs this as a warning and applies a default mapping if defined.

Reconciliation Workflow

Reconciliation is the process of comparing source and target data after migration.

Use a reconcile command that:

• Queries both source and target for the same set of entities
• Compares fields using a set of rules
• Outputs a delta report

./reconcile \
  --source postgres \
  --connection "host=legacy-db.example.com dbname=legacy_system user=erp_user" \
  --target rest \
  --endpoint "https://erp.example.com/api/customers" \
  --auth "bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  --entities "customer" \
  --match-on "AccountNumber" \
  --compare-fields [
    "Name",
    "Street",
    "City",
    "State",
    "VatNumber",
    "CreditLimit"
  ] \
  --output-format "json" \
  --output "/var/reports/reconciliation-20231005.json"

The output is a JSON file listing differences:

[
  {
    "entity_type": "customer",
    "source_id": "CUST-98765",
    "target_id": "CUST-98765",
    "match": true,
    "differences": [
      {
        "field": "Name",
        "source_value": "Acme Industries",
        "target_value": "Acme Inc.",
        "status": "mismatch"
      },
      {
        "field": "Street",
        "source_value": "123 Main St",
        "target_value": "123 Main Street",
        "status": "mismatch"
      },
      {
        "field": "VatNumber",
        "source_value": "98-7654321",
        "target_value": "98-7654321",
        "status": "match"
      }
    ],
    "status": "partially-matched"
  }
]

This report is used in post-migration review meetings and to generate a list of data remediation tasks.

Post-Migration: Validation and Go-Live

Pre-Go-Live Validation Suite

Before cutover, run a validation suite that checks:

• Data completeness (no nulls in critical fields)
• Business logic correctness (e.g., all sales orders have valid customer and item)
• Integration readiness (e.g., ERP can publish invoices to external accounting system)

Use a validate command that runs a series of tests defined in a YAML file:

tests:
  - name: "Check all customers have VAT number"
    type: "sql"
    source: "postgres"
    query: |
      SELECT COUNT(*) FROM customers
      WHERE taxId IS NULL
    expected: 0
    failure_message: "Found {count} customers without VAT number"

  - name: "Verify item prices in ERP"
    type: "rest"
    endpoint: "https://erp.example.com/api/items"
    method: "GET"
    query_params:
      "status": "Active"
    expected_count: 500
    assertion: "body.length >= expected_count"

  - name: "Test invoice creation"
    type: "http"
    endpoint: "https://erp.example.com/api/invoices"
    method: "POST"
    body: |
      {
        "customerId": "CUST-98765",
        "items": [
          { "itemId": "ITEM-001", "quantity": 10, "unitPrice": 15.99 }
        ]
      }
    expected_status: 201
    response_assertions:
      - "body.id is string"
      - "body.status == 'Draft'"
      - "body.totalAmount > 150"

This suite is run as a curl-based script:

#!/bin/bash
set -e

for test in $(ls tests/*.yaml); do
  echo "Running $test"
  result=$(./validate --config "$test")
  if [ $? -ne 0 ]; then
    echo "Test failed: $test"
    exit 1
  fi
done

echo "All validation tests passed."

Go-Live: Cutover with Data Freeze

During cutover, activate the ERP system and freeze source data. Use a freeze command to signal the end of data entry in the legacy system:

./freeze \
  --source postgres \
  --connection "host=legacy-db.example.com dbname=legacy_system user=erp_user" \
  --table "customers" \
  --timestamp "2023-10-05T10:00:00Z" \
  --note "Final data sync before ERP go-live"

This command:

• Updates a system_status table with a freeze_timestamp
• Sends a notification to all users via curl to a messaging API:

curl -X POST https://messaging.example.com/api/notify \
  -H "Content-Type: application/json" \
  -d '{
    "title": "ERP Go-Live: Data Freeze",
    "body": "All source systems are frozen. No new data will be accepted until after go-live.",
    "priority": "high",
    "channels": ["email", "slack"]
  }'

After go-live, run a final reconciliation report and archive the migration artifacts, including:

• Pipeline configuration
• Mapping files
• Checkpoint files
• Error logs
• Validation results

Common Pitfalls and Pro Tips

• Confusing batch_size and max_batch_size: The former controls how many records are grouped per API call; the latter limits the size of a single batch in memory or file. Use batch_size for API payload size and max_batch_size for memory or storage limits.

• Ignoring lastUpdated in incremental sync: If the source system only updates a modified timestamp but not updated, the sync will miss changes. Always use updated or lastModified for consistency.

• Overlooking null handling: A null VatNumber in the source may be "" (empty string) in the target, or vice versa. Define a canonical null value (e.g., null, "", N/A) and normalize during transformation.

• Forgetting to validate the ETL tool itself: Run the ETL tool on a small dataset before full migration. Use psql to dump a test dataset and verify the output.

• Using jsonl for error logs: It’s efficient, streamable, and easily parsed by tools like jq and awk. Avoid csv for complex nested errors.

• Mapping via json and yaml: Use json for configuration and yaml for human-readable, versioned mappings. Tools like jq and yq make this workflow seamless.

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.