SELF-GUIDED BA PRACTICE Packages available · Access after confirmed paymentSee packages ↗

Data & SQL

Data Migration Definition: What It Means and How a Business Analyst Plans One

Data & SQL · Published · Updated · 9 min read · By BA Mentorship Editorial (AI-written)

Part of our guide to business analyst skills.

What data migration means

Data migration means moving data from one system to another when a business changes how it works. The old system is called the source. The new system is called the target. The target needs the data to work from its first day, so customers, products, accounts or patient records must arrive complete and usable.

The important word is one-time. A migration is a project with a start and an end. After it finishes, the old system is usually switched off or made read-only. This is different from an integration, which moves data between two live systems every day.

Common examples: a retailer moving customers into a new CRM, a bank moving accounts onto a new core platform, or a hospital moving records into a new electronic health record system. The data is the same business information, but the shape, the rules and the field names change.

Most migrations follow four activities:

  • Extract: read the data out of the source system, usually into a staging area.
  • Transform: clean, merge, split and reformat the data so it fits the target rules.
  • Load: write the data into the target system.
  • Reconcile: prove that what arrived matches what should have arrived. People often forget this part, and it is where a BA adds a lot of value. See data reconciliation for the basic idea.

Why should a business analyst care? Because data is where business rules hide. A field called status may mean one thing to sales and another to finance. Somebody has to ask what each value means, which records matter and who decides. That work uses many of the core skills in Business Analyst Skills: analytical thinking, SQL, stakeholder work and clear documentation.

Data migration vs data integration vs reporting ETL

Beginners often mix up three ideas that all move data. They use similar tools, but the purpose and the risks are different. Knowing which one you are working on changes the questions you ask.

Three ways data moves between systems

AspectData migrationData integrationETL for reporting
Main purposeMove data so a new system can replace an old oneKeep two live systems in step with each otherCopy data into a warehouse for analysis
How often it runsOnce for real, after several trial runsContinuously, in real time or in batchesOn a schedule, often nightly
Old system afterwardsRetired, archived or made read-onlyStays in useStays in use as the source
Biggest riskLost or wrong records on day oneFailed or duplicated messages between systemsReports that disagree with the source
Key BA outputsScope, field mapping, cleansing rules, reconciliationAPI contract, field mapping, error handling rulesData dictionary, metric definitions, data model
Migration is a one-off move to replace a system. Integration and reporting ETL keep running after go-live.

In real projects these often appear together. A new CRM may need a one-time migration of existing customers, an integration with the order system, and a nightly feed to the data warehouse. Treat each as its own piece of scope with its own requirements.

How to plan a data migration, step by step

Developers write the scripts, but the plan needs business decisions at every step. Here is the order a BA can follow on most projects.

Data migration in seven steps7 steps in order: 1. Agree scope: Decide which data moves, which stays behind and why. Name a business owner for each data area. 2. Profile the source: Count rows and check blanks, duplicates, formats and odd values before writing any rules. 3. Map fields and set rules: Map each source field to a target field and write the transformation and cleansing rules. 4. Run trial loads: Load a copy into a test environment, reconcile, fix the rules and repeat until clean. 5. Plan cutover: Agree the data freeze, load order, checks, the go or no-go owner and the rollback plan. 6. Load and reconcile: Run the final load, compare counts and samples, and get business sign-off. 7. Retire the old system: Make the source read-only or archive it, following the agreed retention rules.1Agree scopeDecide which data moves, which staysbehind and why. Name a business owner foreach data area.2Profile the sourceCount rows and check blanks, duplicates,formats and odd values before writing anyrules.3Map fields and set rulesMap each source field to a target fieldand write the transformation andcleansing rules.4Run trial loadsLoad a copy into a test environment,reconcile, fix the rules and repeat untilclean.5Plan cutoverAgree the data freeze, load order,checks, the go or no-go owner and therollback plan.6Load and reconcileRun the final load, compare counts andsamples, and get business sign-off.7Retire the old systemMake the source read-only or archive it,following the agreed retention rules.
Steps 3 and 4 repeat until a trial load reconciles cleanly. Only then is the team ready to plan the real cutover.
Diagram in words
  • 1. Agree scope: Decide which data moves, which stays behind and why. Name a business owner for each data area.
  • 2. Profile the source: Count rows and check blanks, duplicates, formats and odd values before writing any rules.
  • 3. Map fields and set rules: Map each source field to a target field and write the transformation and cleansing rules.
  • 4. Run trial loads: Load a copy into a test environment, reconcile, fix the rules and repeat until clean.
  • 5. Plan cutover: Agree the data freeze, load order, checks, the go or no-go owner and the rollback plan.
  • 6. Load and reconcile: Run the final load, compare counts and samples, and get business sign-off.
  • 7. Retire the old system: Make the source read-only or archive it, following the agreed retention rules.
  1. Agree the scope. Write down which entities move (customers, products, open orders), which records (active only, or the last few years), and what stays behind. Get the business owner to agree in writing.
  2. Profile the source data. Run simple queries to count rows, find blanks, spot duplicates and check formats. Data profiling tells you how dirty the data really is before anyone promises a date.
  3. Write the mapping and the rules. For each target field, record the source field, the transformation and any default value. A guide like How to Write a Data Mapping Document shows a usable layout.
  4. Agree cleansing rules with owners. Decide what happens to duplicates, missing values and invalid codes. Fix in the source, fix during transform, or leave behind. For duplicates, say clearly which record survives and which extra copies are merged into it. Each choice needs a named business owner.
  5. Run trial loads and reconcile. Calculate the expected count per entity before each load. After the load, compare counts, find missing rows and check key field values. Basic SQL for Business Analysts is enough for most of these checks.
  6. Plan the cutover. Agree when users stop entering data in the old system, the load order, who makes the go or no-go call and how to roll back if checks fail.
  7. Validate, sign off and retire. Business users check samples in the new system. When they sign off, the old system becomes read-only or is archived.

Keep every decision in one place, such as a migration page with a decision log. When someone asks in three months why closed accounts did not move, you can show the answer and who agreed it.

Worked example: moving customers to a new CRM

Lotus Home Appliances, a fictional retailer, is replacing its old CRM. Nadia is the business analyst, Mei is the lead developer and Sunil is the operations lead whose team uses customer records every day. Only customer records are in scope. Order history stays in the old system, which will become read-only.

Week 1: profiling. Nadia profiled the source customer table. It held 1,200 rows. She found:

  • 50 extra copies of customers who already had another row (same email and same phone number). Each of these customers keeps one surviving row, so the 50 count only the extra copies, not the originals.
  • 30 rows for accounts the business had closed. None of these were duplicate copies.
  • Every row had a phone number filled in.

Week 2: mapping and rules. Nadia wrote the field mapping and agreed two rules with Sunil. First, each extra copy is merged into its surviving record and then dropped, so no customer is lost. In the staging table, Mei set the flag is_duplicate to 1 on the 50 extra copies only; surviving records keep 0. Second, closed accounts are not migrated. So the expected count in the new CRM is 1,200 minus 50 minus 30, which is 1,120 rows.

Week 3: trial loads. Mei ran trial load one on Monday. The new CRM held 1,110 rows, 10 fewer than the expected 1,120. The load had rejected 10 in-scope customers because their phone numbers contained spaces, and the new CRM accepts digits only. Profiling had checked that phone numbers were present, but not their format.

Reviewing trial load one

Lotus Home Appliances, a fictional retailer, on Tuesday of week 3, the day after the first trial load into the new CRM.

  1. Mei Lead developer

    Trial load one finished yesterday. The old CRM has 1,200 customer rows and the new CRM has 1,110.

  2. Nadia Business analyst

    Our expected count is 1,120, not 1,200. We agreed to drop the 50 extra duplicate copies, after merging them into their surviving records, and the 30 closed accounts. So we are 10 short. Do we know which 10?

  3. Mei Lead developer

    Yes. The load rejected 10 in-scope rows because the phone field had spaces. The new CRM accepts digits only.

  4. Sunil Operations lead

    Those are active customers. My team calls them about deliveries, so they must come across.

  5. Nadia Business analyst

    Then I will add a transform rule: remove spaces from phone numbers before the load. Mei, can you rerun the trial after that?

  6. Mei Lead developer

    Yes, trial load two on Thursday. I will share the counts with you.

  7. Sunil Operations lead

    After that, I will check 20 moved records by hand against the old screens.

The people and the company in this scene are fictional.

The gap is small, but each missing row is a real customer. The team agrees a rule instead of a manual fix.

On Thursday, Mei ran trial load two with the new phone rule. Nadia used three reconciliation queries to prove the result.

Reconciliation queries after trial load two

Lotus Home Appliances: customer reconciliation

-- Reconciliation after trial load two (Thursday, week 3)

SELECT COUNT(*) AS expected_rows1

FROM staging_customers

WHERE is_duplicate = 0 AND status != 'CLOSED';2

-- Result: 1120

SELECT COUNT(*) AS loaded_rows FROM crm_customers;3

-- Result: 1120

SELECT s.customer_id FROM staging_customers s4

LEFT JOIN crm_customers c ON c.legacy_id = s.customer_id

WHERE c.legacy_id IS NULL

AND s.is_duplicate = 0 AND s.status != 'CLOSED';

-- Result: 0 rows, nothing missing

  1. Count what should arrive. Count from the staging copy of the source, not from the target. This gives the expected number after the agreed business rules.
  2. Rules written as filters. is_duplicate = 1 marks only the 50 extra copies; surviving records keep 0. The 30 closed accounts are also excluded. 1,200 minus 80 gives 1,120.
  3. Count what did arrive. Equal counts are a good sign but not proof. One missing row and one unexpected extra row would cancel each other out.
  4. Find the exact gaps. The LEFT JOIN lists in-scope source rows with no match in the target. After trial load one, this query would have listed the 10 rejected customers.
Two counts and one missing-row check. Together they show that the right customers arrived, not just the right number.

Both counts were 1,120 and the missing-row check returned 0 rows. Sunil checked 20 records by hand, including some surviving records that had absorbed a duplicate copy, and found no differences. Nadia added the phone rule to the mapping document and the decision log, and the team planned the cutover for the weekend of week 4.

Common data migration mistakes

Most migration problems are not caused by bad code. They come from missing decisions and weak checks. Watch for these.

  • Treating migration as a technical copy. If developers decide what a status code means, the business finds out on day one. Every rule needs a business owner.
  • Skipping profiling. Teams estimate effort before they look at the data, then discover duplicates and blanks late. Profile first, then plan.
  • Vague duplicate rules. Saying "remove duplicates" can delete the original customer as well as the copy. Count only the extra copies and say which record survives.
  • Checking presence but not format. A field can be filled in and still be invalid for the target, as the phone numbers were in the example. Check formats, lengths and allowed values.
  • Comparing totals only. Matching counts can hide a missing row and an extra row. Add a missing-row check and a sample of field values.
  • Moving everything just in case. Old, unused records add cleansing work and risk. Agree what history is really needed and archive the rest.
  • No rollback plan. If the final checks fail at midnight, the team needs a clear rule for who decides and how to go back.
  • Ignoring personal data rules. Moving customer or patient data can raise privacy and retention questions. Rules vary by country and industry, so check with your legal or compliance team and the official regulator.

Data migration checklist for business analysts

Use this list before each trial load and again before the final cutover. If you cannot tick an item, raise it as a risk early instead of hoping it will work out.

Before you load

  • Scope written down: which entities, which records, how much history and what stays behind.
  • Source profiled: row counts, blanks, duplicates, formats and invalid values recorded.
  • Every target field has a source, a default value or a written reason to stay empty.
  • Cleansing rules agreed with a named business owner, including which duplicate record survives.
  • Expected row counts calculated per entity before the load starts.
  • Reconciliation queries ready for counts, missing rows and key field values.
  • Cutover plan includes a data freeze, a go or no-go owner and a rollback step.
  • Business sample check booked after the load, with sign-off criteria agreed.
A short readiness check for any migration, small or large.
Silent clip, about 20 seconds. BA Lab screen recording: running a SQL query and reading the automatic check. All data in it is fictional.

Frequently asked questions

Is data migration the same as ETL?

Not quite. ETL (extract, transform, load) is a method. Data migration is a project that often uses ETL once to replace a system. ETL is also used for regular jobs, such as nightly loads into a data warehouse, which are not migrations.

What does a business analyst do in a data migration project?

The BA usually agrees the scope, profiles the data, writes the source to target mapping, captures cleansing and transformation rules with business owners, defines reconciliation checks and supports business testing and sign-off. Developers build and run the load scripts.

Do I need SQL to work on data migration?

Basic SQL helps a lot. Counting rows, filtering with WHERE, grouping to find duplicates and using a LEFT JOIN to find missing records cover most BA checks. You do not need to write the load scripts yourself.

What is the difference between data migration and data conversion?

People use the terms loosely. Conversion usually means changing the format or structure of data, such as dates or codes. Migration means moving data to a new system, and it often includes conversion as one part of the transform step.

How can I practise data migration skills without a job?

Take a small sample dataset, profile it, write a simple mapping to a new table design, load it into a free database such as SQLite and write reconciliation queries. Document your rules and results so the work can go into a portfolio.

This article was written by an AI system and published automatically after automated accuracy, safety and quality checks. All companies, people and numbers in the examples are fictional. BA Mentorship does not issue professional certifications and cannot guarantee any job or interview outcome. Spot an error? Email our support team and we will check and correct it.

Key terms in this article

Browse the full glossary

CHECK BEFORE CONTINUING

Keep your work safe