Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 36 ยท Integration and Data

Data Migration and Cleansing

Plan an ERP or CRM data migration: scope, extraction, mapping, cleansing, loading and reconciliation, with Python stand-ins for field mapping, validation and reconciliation checks.

When a company replaces its old systems, history and open business must move into the new one: customers, products, open orders, stock levels, balances. This is data migration, and it is among the most underestimated work in any ERP or CRM project. Poor data cannot be rescued by good software. A well-run migration is a project of its own, with an owner, a schedule, multiple test loads and clear acceptance criteria.

This lesson walks through the steps. The product is not available on this site, so we use Python stand-ins to show mapping, cleansing, validation and reconciliation.

Decide what to migrate

Not everything should move. Migrating more data means more cleaning, more loading time and more testing. Categories:

  • Master data (customers, suppliers, products, chart of accounts): migrate active records only; archive the rest.
  • Open transactions (open sales orders, open purchase orders, open invoices, stock balances): needed so that business continues on day one.
  • Balances (general ledger opening balances, asset values) rather than every historic posting.
  • History (closed invoices, old tickets): often kept in an archive or data warehouse, not in the live system.

A common rule: migrate what is needed to operate tomorrow and to comply with law; archive the rest where it can still be read.

The migration pipeline

Prepare

Scope What moves and what is archived.
Profile Measure the quality of source data.
Map Source field to target field.

Profiling first avoids nasty surprises.

1 / 3

Profiling the source

Data profiling measures the source before any design: how many records, how many nulls per field, how many distinct values, which formats occur, how many duplicates. It tells you the size of the cleaning job and prevents unpleasant discoveries during testing.

Python Profile a customer extract

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The profile shows four typical problems: missing values, inconsistent country codes, an invalid email and an exact duplicate.

Mapping and transformation

A mapping document lists each target field with its source field, a transformation rule, a default for missing values, and an owner. Typical transformations: change code values (old payment terms N30 to new NET30), split or join fields (one address line into street and number), convert units and currencies, fix date formats and combine records from several sources.

Python Map, cleanse and reject (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Rejected rows go to an exception report that business data owners fix in the source (or via approved rules). Never fix by hand in the target during the final load: that cannot be repeated and leaves no audit trail.

Trial loads and reconciliation

Run several trial migrations (commonly three or more) into a test system: the first finds mapping errors, later ones find data problems and measure runtime, the last is a full dress rehearsal. After each load, reconcile: compare counts and totals between source and target, and sample-check records.

Python Reconcile source against target

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Differences need an explanation: records rejected for good reasons (inactive customers) are fine if documented; unexplained differences block go-live. Also check business reconciliations: stock value equals the ledger, open receivables equal the customer balances, the trial balance sums to zero.

Practical rules

  • Freeze and delta loads: for the final cutover, load a big snapshot early and a small delta at the last moment.
  • Keep legacy IDs in the target (a cross-reference field) to link old documents and to support audits.
  • Sequence loads by dependency: organization and reference data, then master data, then open transactions, then balances.
  • Mind volumes and runtime: the final load must fit inside the cutover window (see testing, cutover and go-live).
  • Document and keep evidence: auditors may ask how balances were moved.
  • Personal data: migrate only what you may lawfully keep, and protect extracts (files with customer data are sensitive).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why profile source data before designing the migration?

    Profiling reveals missing values, duplicates and inconsistent formats while there is still time to plan for them.
  2. 2. What belongs in an exception report?

    The report lets data owners correct rejected records in a controlled, repeatable way.
  3. 3. What is the purpose of a trial migration?

    Each trial exposes new errors and gives realistic load durations for the cutover plan.
  4. 4. Which load order is correct?

    Later objects depend on earlier ones, for example an order needs a customer and a product that already exist.

Summary

  • Migrate what is needed to operate and to comply; archive the rest.
  • Profile, map, cleanse, load, validate and reconcile, repeating in trial loads.
  • Keep rules in code and rejected records in an exception report.
  • Reconcile counts, totals and business relationships.
  • Keep legacy IDs, evidence and a realistic runtime in the cutover plan.

Next: Integration Patterns and APIs