The Phone Call I Never Want to Get Again

A few years ago, a distribution company I was advising went live on a new ERP system over a holiday weekend. Monday morning, their AP team discovered that forty vendor invoices had migrated twice — same invoice number, same amount, posted as two separate liabilities. Their sales team found the opposite problem: nearly three hundred customer records had merged into duplicates during the import, so open orders were scattered across two different account numbers for the same client. It took six weeks of manual cleanup, two frustrated all-staff meetings, and one lost customer relationship to fully untangle it.

None of this happened because the new software was bad. It happened because nobody treated the migration itself as a project with its own plan, testing, and go/no-go criteria. Data migration for SMBs is usually treated as a checkbox on a bigger software rollout — when it deserves to be the thing you worry about most.

I have run this process enough times — ERP swaps, CRM consolidations, custom app replacements — to have a clear opinion: the migration itself is usually harder than either system involved. Here is the framework I use to make sure a switch does not turn into a cleanup project.

Why Migrations Break in the First Place

Before I get to the framework, it is worth naming the actual failure points, because they are almost never about the technology being unreliable.

  • Nobody owns data quality in the source system. Years of manual entry and "we'll fix it later" leave duplicates, inconsistent formats, and orphaned records.
  • Field mapping is treated as a technical afterthought. A "status" field in the old CRM rarely means the same thing as "status" in the new one, and nobody checks until data is already loaded.
  • Testing runs on a sample, not the full dataset. Edge cases — the customer with three tax IDs, the order with a negative quantity — hide in the last 5% of records and only surface in production.
  • There is no real rollback plan. Teams assume the old system will "still be there" if something goes wrong, without a tested way to actually revert.

Industry estimates on this vary, but most agree that well over half of migration projects miss their original budget or timeline. Whatever the exact number, the pattern is consistent: migrations fail on process, not tooling.

Step 1: Audit and Clean the Source Data First

I do not let a client start mapping fields until we have profiled the source data. That means running reports on duplicate detection, null-value rates in required fields, orphaned foreign keys, and inconsistent formats (three different date formats in one column is more common than you would think).

This is also the moment to make hard decisions about what actually needs to migrate. Not every record from a fifteen-year-old system deserves a seat in the new one:

  • Archive inactive customers and closed orders older than your legal retention requirement instead of migrating them live.
  • Deduplicate before mapping, not after — merging duplicates post-load means untangling transaction history too.
  • Assign a data owner on the business side, not just IT, to make judgment calls on ambiguous records.

Skipping this step is the single most common reason migrations run over budget, because cleanup discovered mid-project always costs more than cleanup done up front.

Step 2: Map Fields and Build the ETL Process Deliberately

Once the source data is clean, map every field between old and new systems explicitly — including the fields that seem obvious. I have seen a "customer type" dropdown with four options in the old system map to a field with seven options in the new one, silently defaulting unmapped values to whatever came first alphabetically. Nobody noticed until sales commissions were calculated wrong a month later.

A solid extract-transform-load process for an SMB migration typically involves:

  • Extract data from the source system into a neutral staging format (usually flat files or a staging database) rather than transforming directly system-to-system.
  • Transform data in that staging layer — normalizing formats, applying business rules, resolving duplicates — where you can inspect and re-run it safely.
  • Load in controlled batches, starting with reference data (product catalogs, chart of accounts) before transactional data (orders, invoices) that depends on it.

Legacy systems with no usable API become a real bottleneck here — if the old platform only exports through a clunky manual report, extraction stays slow and error-prone no matter how good your transform logic is. I cover this dependency in legacy system modernization, and the integration constraints I describe in custom API integration apply directly to migration extraction work.

Step 3: Run Both Systems in Parallel Before You Commit

This is the step SMBs skip most often, usually because of cost or impatience, and it is the one I push back on hardest when clients want to cut it.

A parallel run means operating the old and new systems side by side for a defined window — I recommend at least one full business cycle (a full month-end close, a full billing cycle, a full payroll run) — and having actual end users work in both systems and compare results. It costs more in the short term. It saves you from finding out your tax calculations are wrong after tax season.

For a smaller SMB migration, a compressed parallel run of two to three weeks focused on the highest-risk transaction types (invoicing, payroll, inventory counts) is often enough, as long as you pick the transactions that would hurt the most if they were wrong.

Step 4: Reconcile the Data, Don't Just Eyeball It

Validation needs to be systematic, not a manager scanning a spreadsheet for anything that looks off. At minimum, build:

  • Row-count reconciliation between source and target for every table or entity.
  • Checksum or total-value comparisons on financial data — sum of open invoices, total inventory value, total accounts receivable — before and after migration.
  • Referential integrity checks to confirm every order still points to a valid customer, every line item to a valid product.
  • Spot-check business rules, not just data presence — does the new system calculate late fees, discounts, and tax the same way the old one did on identical inputs?

Set explicit acceptance thresholds before you start — for example, financial totals must match exactly, and record counts must match within a documented, explained variance. Vague criteria like "looks right" are how migrations get approved with silent data loss baked in.

Step 5: Plan the Cutover and the Rollback Before Go-Live Day

Cutover should be scheduled to the hour, with a written runbook: who freezes data entry in the old system, who kicks off the final incremental sync, who signs off before users are let back in, and in what order systems come back online.

Just as important — decide your abort criteria in advance. If reconciliation shows a 3% discrepancy in inventory counts an hour before go-live, do you proceed or roll back? Deciding that under pressure, in the moment, is how bad calls get made. I recommend keeping the old system in a read-only, accessible state for two to four weeks after cutover, purely as an insurance policy while the new system proves itself under real load.

Making the Switch Without the Horror Story

The distribution company I mentioned earlier eventually got their new ERP working well — but the six weeks of cleanup, and the customer they lost along the way, were entirely avoidable. A disciplined migration process costs more time and money up front than "just importing the data and fixing issues as they come up." It is dramatically cheaper than the alternative.

If you are planning a system switch and want a second set of eyes on the migration plan before you commit to a go-live date, let's talk through your situation.