What is data cleaning before migration?

It is detecting and correcting or removing inaccurate, duplicate, incomplete, or inconsistently formatted records from a source dataset before loading it into a target system. The goal is to prevent constraint violations, broken relationships, and misleading reports after cutover. Common checks include nulls, type mismatches, and duplicate keys.

When do you need to clean data before migrating rather than after?

Clean before migration when the target has stricter constraints, such as unique indexes, foreign keys, or required fields the source lacks. Loading dirty data first can cause partial imports, rollback loops, and silent truncation. Clean after only when you can stage raw data and validate it without affecting production.

What are the most common data quality issues to check before a migration?

Nulls in required columns, duplicate rows, inconsistent date and phone formats, orphaned foreign keys, and values that exceed target column length or type limits. Run COUNT, GROUP BY, and MIN/MAX length queries per column to quantify each issue before writing transformation rules.

How do you profile source data before cleaning for a migration?

Run aggregate queries against the source: row count, distinct count per candidate key, null ratio per column, min/max length, and value frequency. Compare results against the target schema’s constraints and indexes. This profile tells you which columns need transformation and how many rows each rule will affect.

How do you deduplicate records before migration without losing history?

Identify duplicates with a deterministic match key such as lowercased email or concatenated business fields. Keep the canonical record, then merge related child rows under it or mark duplicates as archived in a staging table. Back up the full source first and test the merge logic on a copy.

How do you verify cleaned data is correct before loading it into the new system?

Compare source and cleaned row counts per rule, recalculate checksums or hashes on key columns, and run a sampled join between source and staging data. Confirm every transformation reduced the expected error count and introduced no new nulls or constraint violations. Do this before any production load.

What happens if you skip data cleaning before a migration?

The import can fail mid-run on unique key or foreign key violations, leaving partial data and rolled-back transactions. Some systems silently truncate or coerce bad values, corrupting dates or numbers. You may then spend longer reconciling the target than the original cleaning would have taken.

How do you handle personally identifiable information during data cleaning for migration?

Clean PII only in an environment with the same access controls as production. Mask or tokenise sensitive fields before any non-production copy. Keep an audit log of which rows were changed and why, and ensure the target system's encryption and retention rules match the source before transferring data.

How much operational overhead does data cleaning add to a migration?

Cleaning typically adds 20–40% of total migration effort, driven by the number of rules, exception review, and re-profiling cycles. Automating simple transformations with SQL or ETL scripts reduces this, but manual review of ambiguous duplicates and incomplete addresses remains a cost driver. Plan for at least one dry-run cycle.

What are alternatives to cleaning all data before a migration?

You can stage raw data in the target, run validation and cleaning there, then promote only rows that pass. For large datasets, clean only active or recent records and archive the rest. Another option is to migrate dirty data as-is into a quarantine table and resolve exceptions after cutover. Each shifts when, not whether, cleaning happens.

Data Cleaning Before Migration: Rules That Actually Hold

Cleaning data before it goes into a new system

Data cleaning before migration is the work of finding and fixing duplicate, missing, inconsistent and wrongly formatted records before they load into the target system. It combines profiling, deduplication, standardisation and validation. Skip it, and the new system inherits every bad row — only now it is harder to trace.

Key Takeaways

Profiling finds the duplicates, nulls and format drift you cannot see in a spreadsheet.
Clean in a staging copy, never in the live source, and mark records before you delete them.
Deduplication needs explicit match keys — email, phone or tax ID — or you will merge unrelated records.
Standardise formats and map values to the target schema before the load, not after.
Validation is the gate: row counts, referential checks and a signed-off sample before cutover.
Cleaning after go-live is customer-facing and harder to reverse than cleaning now.
Document every rule; the next person to maintain the system needs the decision log.

The five stages of data cleaning before migration — profile, deduplicate, standardise, validate and load — run in order so each rule builds on measured reality rather than assumption.

Why does dirty data break a migration so badly?

The damage usually shows up after go-live, not during the load. A duplicate customer means two IDs for one person, so orders and invoices split across both. A null in a required field fails silently in staging but blocks a report later. The fix after cutover costs more because the bad rows are now live.

Data cleaning combines four jobs. Profiling reports what each column actually holds. Deduplication merges records that refer to the same real-world thing. Standardisation makes one value look the same everywhere. Validation checks that every row satisfies the target system's rules. You cannot skip any of them safely, but the order matters.

We have seen a single duplicate product SKU make an e-commerce import fail on a foreign-key constraint at 2 a.m., and a phone column holding three formats make a CRM's dedupe feature merge two different companies. The common thread: the source tolerated mess, and the target did not. Cleaning before migration is how you stop paying for that tolerance later.

When is deep data cleaning worth the effort?

Deep cleaning pays off when the target enforces relationships the source never had, or when the data feeds billing, reporting or customer-facing records. A small contacts list moving to a CRM needs light standardisation. A product catalogue joining inventory, pricing and a web storefront needs a full dedupe and mapping pass.

Three signals tell you to go deep. First, the data has accumulated for years with no owner and no validation. Second, more than one system has been writing to it. Third, the new system will expose the data to customers or use it for automated billing. When none of those apply, a light pass — trim spaces, fix dates, fill obvious nulls — is enough, and faster.

The system you choose also shapes this. An off-the-shelf package with a strict schema forces more mapping up front, while custom software versus off-the-shelf gives you room to bend the schema, but only if you decide that deliberately before the load.

How does data profiling find what you cannot see?

Profiling inspects every column and reports what is actually there: null counts, distinct values, minimum and maximum lengths, date ranges and value frequency. You run it before writing any cleaning rule, because the rule has to match the real shape of the data. A column labelled "phone" may hold three formats and two kinds of placeholder.

A fast pass with COUNT(DISTINCT) and IS NULL tells you most of it. Pandas users get the same from .describe() and value_counts(), and the library has first-party duplicate detection helpers. A null count near zero in a supposedly optional column means the field is effectively required. A date range that starts in 1970 means defaults are polluting the data.

What is the step-by-step cleaning workflow?

The workflow moves from a raw export to a staging copy, through profile, dedupe, standardise and validate, then to a signed-off load. Never clean in the live source. You need a staging table or file you can rebuild, and you should mark records for deletion before you remove anything.

Export from every source system into a staging area. Load it into Postgres or a dataframe using a documented bulk import path such as PostgreSQL COPY. Never touch the live source directly.
Profile every column: nulls, distinct values, lengths, date ranges and outliers.
Resolve nulls. Decide per field whether to keep null, apply a default or drop the row, and record that decision.
Deduplicate using explicit match keys. Mark candidates first, confirm with the business, then merge.
Standardise formats: dates, phone numbers, currency, country codes and booleans.
Map source values to the target schema's allowed list, field by field.
Validate referential integrity, row counts and a sample of changed records.
Freeze the staging copy, get sign-off, load, and keep a rollback copy until the new system is verified.

-- Find near-duplicates before writing any merge rule
SELECT email, COUNT(*)
FROM customers_staging
GROUP BY email
HAVING COUNT(*) > 1;

Mark candidates with an UPDATE that keeps the MIN(id) per email and sets a status flag on the rest. Back up the staging table before any physical DELETE, and never run deletes against the source.

Which deduplication rules actually work?

Match keys decide what counts as the same record. Email is a strong key if it is lowercased and trimmed; phone numbers need normalising first. Name plus address works only after both are standardised. The rule must also name the survivor: keep the most recently updated row, or the one with the most complete fields.

Exact matching on a normalised key catches the easy duplicates. Fuzzy matching on names catches more but creates false merges, so treat its output as candidates for a person to confirm. Keep a merge log that records which ID won and which IDs were folded in — you will need it when finance asks where an old invoice went.

How do you standardise and map values for the target system?

Standardisation makes one value look the same everywhere: dates as ISO 8601, phone numbers in E.164, country as ISO codes, true/false as booleans. Mapping then rewrites source values to the target's allowed list, so "NPL", "Nepal" and "NP" all become the same country code before the load.

Keep the mapping in a simple two-column table — old value, new value — and version it. This is the same discipline as a redirect map for a website migration; the map is the audit trail. When a value has no target equivalent, do not invent one silently. Set it aside and ask the business owner.

How do you verify cleaned data before cutover?

Verification is not spot-checking a few rows. Compare row counts before and after each transform, check that every foreign key resolves, and re-run the duplicate query to confirm zero near-duplicates remain. Then have a business owner sign off on a sample of changed records.

Write the checks as queries you can re-run, because you will re-run them after the final load too. Row count should only change by the number of deliberate merges. Sample fifty changed rows and read them like a human; that is where wrong defaults and bad merges surface.

What breaks during cleaning, and how do you debug it?

The classic failure is over-cleaning: a rule that merges two genuinely different customers, or a default that silently overwrites a real value. The symptom is data that looks tidy but is wrong. Debug by tracing a specific lost record back through the transform log to see which rule touched it.

Keep every transform as a versioned script, not a set of clicks in a spreadsheet, so you can re-run and diff. Watch for silent truncation when a source text field is longer than the target allows, and for character encoding problems when old data is not UTF-8. If a record disappears, check your joins before blaming the dedupe rule.

What does cleaning cost, and where does the overhead go?

Most of the cost is engineer and business time, not tooling: someone has to read the profiler output, decide the rules and confirm the awkward edge cases. Volume, the number of source systems and how many exceptions need a human decision drive the effort more than the tool you pick.

A staging copy costs almost nothing and saves you from irreversible mistakes. Resist the urge to automate every rule on day one; write the obvious transforms, then hand the uncertain ones to a person. If you are not sure where the risky rows are, our team can help you profile a dataset and plan the pass before you commit to a cutover date.

Cleaning before the load keeps mistakes reversible and out of customer view; cleaning after go-live turns every bad row into a support ticket.

How do you keep the data safe while cleaning it?

Cleaning usually means copying production data to a staging environment, which is where leaks happen. Treat the extract as production data: restrict access, mask or drop what you do not need, and delete the staging copy after the migration is signed off.

If the data contains customer details, strip identifiers you do not need for cleaning and do not email exports around. Keep the staging database in the same account and network boundary as production, with the same access controls. The cleaning copy is a second production system, just shorter-lived.

What are the most common cleaning mistakes?

The most common mistake is cleaning the live source instead of a copy, which leaves no way to undo a bad rule. Next is skipping business sign-off on duplicates, so the team merges records finance still needed. Many teams also forget to document rules, which makes the migration unrepeatable.

Deleting the source data before the new system is verified end to end.
Treating null and empty string as the same thing when the target distinguishes them.
Cleaning so aggressively that the data no longer matches the paper records the business recognises.
Loading into the target, then discovering the mapping missed a required field and patching it live.

A realistic cleaning run, end to end

Take a Kathmandu retailer moving 40,000 customers and 12,000 products from spreadsheets and an old desktop system into a new online store. Profiling finds 3,100 duplicate customers, 900 products with no SKU, and four date formats. The pass takes a week of profiling, a day to write rules, and two days of business confirmation.

They load everything into a staging database, dedupe customers on lowercased email plus normalised phone, generate SKUs from category and a sequence, and map the four date formats to ISO 8601. Validation shows 22 products still have no category; the owner fills those in by hand. The load goes ahead only after a sample of 200 records is signed off. If the target is a custom web store, the schema decisions shape this mapping; building software with the cleaning pass in mind saves a second round of corrections later.

A realistic timeline for data cleaning before migration: profiler output becomes rules, rules become dedupe and mapping, and human sign-off gates the final load.

Where should you clean: source, staging or target?

Cleaning in the target after load is tempting because the new system is already there, but it means users see the mess during early access. Cleaning in staging keeps bad rows out of production and keeps a rebuild path. Cleaning in the source is only safe for non-destructive standardisation.

Where	Blast radius	Operational overhead	Best when
In the source	Highest — live users affected	Low to start, hard to audit	Trims and format fixes only
In a staging copy	Contained — rebuildable	Moderate, mostly scripted	Dedupe, mapping, validation
In the target after load	Customer-facing	High to fix later	Minor corrections post-launch

In practice, the staging copy is where the real work belongs. It gives you the freedom to run a rule, diff the result, and re-run it without anyone watching. Use the source only for trims that are safe by definition, and treat the target as a place for corrections you were willing to defer, not for the heavy pass.

In short: profile first, clean in a rebuildable staging copy, mark before you delete, standardise and map to the target schema, validate with real checks, and get a human to sign off on the changed rows. Data cleaning before migration is boring, deliberate work — and it is the difference between a system people trust and one they quietly stop using.

People also search for

Clean data is a prerequisite, not an afterthought, and it is easy to underestimate until a bad merge or a missing SKU blocks the cutover. If you are planning a migration and want someone to profile the data, set the dedupe rules and run the validation checks before you commit, talk to our team — or see how we have handled similar builds for businesses in Nepal and abroad.

Frequently asked questions

It is detecting and correcting or removing inaccurate, duplicate, incomplete, or inconsistently formatted records from a source dataset before loading it into a target system. The goal is to prevent constraint violations, broken relationships, and misleading reports after cutover. Common checks include nulls, type mismatches, and duplicate keys.
Clean before migration when the target has stricter constraints, such as unique indexes, foreign keys, or required fields the source lacks. Loading dirty data first can cause partial imports, rollback loops, and silent truncation. Clean after only when you can stage raw data and validate it without affecting production.
Nulls in required columns, duplicate rows, inconsistent date and phone formats, orphaned foreign keys, and values that exceed target column length or type limits. Run COUNT, GROUP BY, and MIN/MAX length queries per column to quantify each issue before writing transformation rules.
Run aggregate queries against the source: row count, distinct count per candidate key, null ratio per column, min/max length, and value frequency. Compare results against the target schema’s constraints and indexes. This profile tells you which columns need transformation and how many rows each rule will affect.
Identify duplicates with a deterministic match key such as lowercased email or concatenated business fields. Keep the canonical record, then merge related child rows under it or mark duplicates as archived in a staging table. Back up the full source first and test the merge logic on a copy.
Compare source and cleaned row counts per rule, recalculate checksums or hashes on key columns, and run a sampled join between source and staging data. Confirm every transformation reduced the expected error count and introduced no new nulls or constraint violations. Do this before any production load.
The import can fail mid-run on unique key or foreign key violations, leaving partial data and rolled-back transactions. Some systems silently truncate or coerce bad values, corrupting dates or numbers. You may then spend longer reconciling the target than the original cleaning would have taken.
Clean PII only in an environment with the same access controls as production. Mask or tokenise sensitive fields before any non-production copy. Keep an audit log of which rows were changed and why, and ensure the target system's encryption and retention rules match the source before transferring data.
Cleaning typically adds 20–40% of total migration effort, driven by the number of rules, exception review, and re-profiling cycles. Automating simple transformations with SQL or ETL scripts reduces this, but manual review of ambiguous duplicates and incomplete addresses remains a cost driver. Plan for at least one dry-run cycle.
You can stage raw data in the target, run validation and cleaning there, then promote only rows that pass. For large datasets, clean only active or recent records and archive the rest. Another option is to migrate dirty data as-is into a quarantine table and resolve exceptions after cutover. Each shifts when, not whether, cleaning happens.