ERP Data Migration

Plan and execute ERP data migrations that avoid data loss, corruption, and business disruption. Covers migration strategy, data mapping, validation frameworks, incremental loading, rollback planning, and the patterns that separate successful migrations from disasters.

ERP data migration is the process of transferring structured business data—customers, products, inventory, orders, financials, and master data—from legacy systems into a new ERP platform. It matters most during ERP implementations, upgrades, and system consolidations, when data quality, consistency, and integrity are non-negotiable. This page details the practical execution of ERP data migration, from planning and extraction to validation and cutover.

Planning the Migration Scope

Define the migration scope using a data mapping matrix. Each row represents a data entity (e.g., Customer, Product, GLAccount), with columns for source system, target field, transformation rule, and validation criteria.

Entity,Source,Target,Transformation,Validation
Customer,LegacyApp,ERP,concat(first_name, ' ', last_name),not null and unique on email
Product,InventoryDB,ERP,lower(trim(sku)),length(sku) <= 30 and exists in inventory
GLAccount,FinanceSystem,ERP,case when type = 'COGS' then '1200' else '1300',code matches pattern '1[0-9][0-9][0-9]'

Ensure every mapping includes a source-to-target key mapping. For example, Customer uses legacy_id from the source and maps it to external_id in the target ERP. This key enables traceability and reconciliation.

Use a migration runbook with versioned stages:

Extracting Data from Source Systems

Extract data using standard SQL queries executed via psql, sqlcmd, or curl to a database API. The source system may be a relational database, a flat file, or a REST API.


psql -h source-db.example.com -U erp_user -d legacy_db -c "
  SELECT
    id AS legacy_id,
    name AS customer_name,
    email,
    address,
    created_at,
    updated_at
  FROM customers
  WHERE status = 'active'
    AND updated_at > '2023-01-01'
  ORDER BY updated_at DESC
  LIMIT 100000
" -t -A -F ',' > customers.csv

When extracting from a REST API, use curl with pagination and error handling:

#!/bin/bash
# extract_customers.sh

API_URL="https://api.legacyapp.com/v1/customers"
OUTPUT_DIR="./data"
PAGE=1
MAX_PAGES=100

while [ $PAGE -le $MAX_PAGES ]; do
  curl -s -H "Authorization: Bearer $API_TOKEN" \
    "$API_URL?page=$PAGE&limit=100" \
    -o "$OUTPUT_DIR/customers_page_$PAGE.json" \
    -w "HTTP %{http_code} page $PAGE\n"

  # Check for rate limit exhaustion
  if grep -q '"rate_limit":' "$OUTPUT_DIR/customers_page_$PAGE.json"; then
    sleep 2
  fi

  PAGE=$((PAGE + 1))
done

Common extraction failure modes:

Transforming Data for the Target ERP

Transform data using SQL, shell scripts, or ETL tools like dbt, Apache NiFi, or Python with pandas.

Apply data cleansing and business logic in a transform/ directory:

# transform_products.py
import pandas as pd
from datetime import datetime

def transform_product(row):
    # Standardize product names
    name = row['name'].strip().title()
    # Map categories to ERP category codes
    category_map = {
        'Electronics': 'ELEC',
        'Furniture': 'FURN',
        'Clothing': 'CLTH',
        'Office': 'OFFC'
    }
    category = category_map.get(row['category'], 'GEN')
    # Generate SKU from product code and category
    sku = f"{category}-{row['code']}".upper()
    # Set default values
    return {
        'sku': sku,
        'name': name,
        'category_code': category,
        'created_date': datetime.now().isoformat(),
        'is_active': True,
        'unit_of_measure': 'EA'
    }

# Load and transform
df = pd.read_csv('products.csv')
df = df.apply(transform_product, axis=1, result_type='expand')
df.to_csv('products_transformed.csv', index=False)

Key transformation patterns:

Failures in transformation:

Loading Data into the Target ERP

Load data using the ERP’s standard data import interface: API, CSV bulk upload, or database staging tables.

Using the ERP’s REST API for bulk data import

// POST /api/v1/data-import
{
  "import_id": "20240405-migration-customer",
  "batch_size": 500,
  "mode": "upsert",
  "mapping": {
    "external_id": "legacy_id",
    "name": "customer_name",
    "email": "email",
    "billing_address": "address"
  },
  "data": [
    {
      "external_id": "CUST-1001",
      "name": "Acme Inc",
      "email": "[email protected]",
      "billing_address": "123 Main St, Anytown, CA 90210"
    },
    {
      "external_id": "CUST-1002",
      "name": "Global Corp",
      "email": "[email protected]",
      "billing_address": "456 Oak Ave, Somewhere, TX 75001"
    }
  ]
}

The ERP API supports mode values:

Error handling during load

The API returns a structured response on failure:

{
  "import_id": "20240405-migration-customer",
  "status": "failed",
  "errors": [
    {
      "record_id": "CUST-1001",
      "field": "email",
      "error": "invalid_email_format",
      "message": "Email '[email protected]' is malformed"
    },
    {
      "record_id": "CUST-1003",
      "field": "name",
      "error": "required_field_missing",
      "message": "Customer name is required"
    }
  ],
  "failed_records": 2,
  "total_records": 1000,
  "duration_seconds": 12.3
}

Common load issues:

Validating Data After Migration

Validation ensures data accuracy, completeness, and consistency. Run validation in a staging environment before cutover.

Use a validation script that compares source and target data:

#!/bin/bash
# validate_customers.sh

SOURCE_DB="postgresql://source_user@source-db:5432/legacy_db"
TARGET_DB="postgresql://erp_user@erp-db:5432/erp_db"

# Export target data
psql -h erp-db -U erp_user -d erp_db -c "
  SELECT
    external_id,
    name,
    email,
    billing_address,
    created_date
  FROM customers
  WHERE migration_source = '20240405-migration-customer'
" -t -A -F ',' > target_customers.csv

# Compare with source
diff -u <(sort customers.csv) <(sort target_customers.csv) > validation_diff.log

# Check for missing records
psql -h erp-db -U erp_user -d erp_db -c "
  SELECT COUNT(*) AS missing
  FROM (
    SELECT external_id
    FROM customers
    WHERE migration_source = '20240405-migration-customer'
    EXCEPT
    SELECT legacy_id
    FROM customers_source
  ) AS missing_customers
" -t -A -F ',' > missing_customers.txt

Validation results include:

Validation failure modes

Cutover and Final Data Freeze

Final data migration occurs during the cutover window. The process is:

  1. Data freeze: stop updates to source system.
  2. Final extract: extract all remaining data.
  3. Final load: load final data into ERP.
  4. Validation: run automated validation script.
  5. Go-live: switch users to new ERP.

Use a cutover script with atomic steps:

#!/bin/bash
# cutover.sh

set -euo pipefail

LOGFILE="/var/log/cutover-$(date +%Y%m%d-%H%M%S).log"

echo "Starting cutover" | tee -a $LOGFILE

# Step 1: Freeze data in source
curl -X POST https://api.legacyapp.com/v1/data/freeze \
  -H "Authorization: Bearer $API_TOKEN" \
  -d '{"freeze_until": "2024-04-05T12:00:00Z"}' \
  -w "\nHTTP %{http_code}\n" \
  -o freeze_response.json

# Step 2: Extract final data
./extract_customers.sh
./extract_orders.sh

# Step 3: Load final data
curl -X POST https://erp.example.com/api/v1/data-import \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $ERP_TOKEN" \
  -d @final_customers.json \
  -w "\nHTTP %{http_code}\n" \
  -o load_customers.log

# Step 4: Validate
./validate_customers.sh
./validate_orders.sh

# Step 5: Mark cutover complete
curl -X POST https://erp.example.com/api/v1/cutover/complete \
  -H "Authorization: Bearer $ERP_TOKEN" \
  -d '{"cutover_id": "20240405", "status": "complete"}'

echo "Cutover completed" | tee -a $LOGFILE

Critical cutover failure modes:

Common Pitfalls and Lessons

Data migration is not a one-off task but a repeatable, auditable process. Each migration should produce logs, validation reports, and error records. The final deliverable is not just “data loaded” but “data verified and traceable.”

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.