I Learned the Hard Way: Data Migration Isn't What I Thought It Was

Three years ago, I was asked to help move customer data from an old CRM into a shiny new system. I thought I had this, extract some SQL, transform it, load it, done. I was confidently wrong. Six weeks later, staring at a production issue where 47 customer records had split into duplicates, I finally understood what I'd actually signed up for. It wasn't a technical problem. It was an orchestration problem, and I'd treated it like a script.

When you work in software long enough, you start building mental models of what "migration" means. For me, it was always the engineering part: the ETL pipeline, the data transformations, the clever SQL. What I missed entirely was the process, the governance, and the fact that someone needs to prove your work succeeded. That's the real migration.

What Actually Happens During a Migration

A migration moves a client's operational history from one system to another. It sounds simple until you realize what "complete, connected, and correct" really demands.

I used to think the hard part was the transformation logic. Getting dates from 03/04/24 into 2024-04-03 format. Parsing phone numbers with inconsistent formatting. Splitting names that were stored as one field but needed to be two. That's technical, it's solvable, and honestly it's the fun part.

But the real problem is earlier: understanding what's actually in the source data. The original article calls this "profiling," and I've learned it's not optional busywork, it's foundational. You cannot build a solid field mapping without seeing the real data, with all its inconsistencies and garbage. Clients always describe their data more optimistically than reality warrants.

The framework the article describes, eight stages with gates between them, matches every successful migration I've been part of. You don't start mapping until scope is locked. You don't load until the mapping is signed off. You don't go live until someone has actually looked at real records in the target system and confirmed it looks right. These gates feel bureaucratic on small projects. On real projects, they're the only thing preventing disaster.

Where It Gets Messy

I've seen four types of migrations, and they're not equally painful. Moving from one SaaS platform to another? Manageable. Two companies merging with overlapping data? Much harder. But the absolute worst is what happens when someone replaces spreadsheets with real software.

Spreadsheets have no schema, no validation, no rules. Someone put a phone number in a date field three years ago and nobody noticed. Currency values have currency symbols, or they don't. Customer names are title-cased, or they're not, or they're abbreviations that only make sense if you worked there in 2015. A spreadsheet tells you nothing about what's actually valid.

This is why de-duplication happens before the move, not after. You have to match records that represent the same real-world entity but are spelled differently, have different IDs, or got entered twice by different people. This is manual, tedious, and critically important. I've watched teams skip this and watch customer histories split in production. It's not recoverable after cutover.

What Actually Matters

Here's what I agree with completely: the two biggest failure modes are duplicates that survive the migration and migrations where nobody can prove the data actually arrived intact.

The fix for both is boring and unsexy, you count things repeatedly and write the numbers down. SELECT COUNT(*) FROM customers in the old system. SELECT COUNT(*) FROM customers in the new system. Compare. Then get more granular: count by status, by creation date, by region. Check that related records didn't lose their relationships. A document without a client is called an orphan, and in regulated industries, it's not a data quality issue, it's a compliance violation.

I use SQL constantly for this kind of work: profiling with GROUP BY to understand the shape of the data, COUNT DISTINCT to find duplicates, LEFT JOIN to find orphans. It's not complicated, but it has to be right, and it has to be documented.

The Real Skill

What I've realized is that migrations succeed or fail on investigation and verification, not on technical elegance. The best migration engineers I've met aren't the ones writing fancy transformation scripts, they're the ones asking relentless questions about what "success" actually means and then proving it with data.

Have you been through a real migration? What broke, and what would you do differently next time?

Source: This post was inspired by "What Data Migration Actually Is" by Dev.to. Read the original article