The short answer
Data cleaning is unglamorous and is usually the difference between a migration that works and one that produces confusing results for years.
It is also mostly free tooling and patience rather than anything specialised.
What usually needs fixing
| Problem | Typical approach |
|---|---|
| Duplicate records | Match on several fields, review before merging |
| Inconsistent formats | Standardise dates, phone numbers, postcodes |
| Same value written differently | Map variants to one canonical value |
| Missing values | Decide what empty means, per field |
| Data in the wrong column | Find by pattern, not by eye |
The third row is the largest job in most datasets. Company names, job titles and locations accumulate dozens of variants that all mean one thing.
Work safely
- Never clean the original, always a copy.
- Fix one class of problem at a time.
- Record what you changed and why.
- Count rows before and after every step.
- Keep the intermediate versions until you are finished.
Counting rows after each step catches the most damaging mistake, which is silently dropping records through a filter or a join.
Decide what empty means
Blank can mean not applicable, not known, or nobody filled it in, and those are different. Decide per field before filling anything, because the decision affects how the data can be used.
Filling blanks with a default value destroys the distinction permanently, so do it deliberately or not at all.
Check the result
- Row counts reconcile against the source
- Sample records compared field by field
- Value distributions look plausible
- Dates fall in a sensible range
- Someone who knows the data has looked at it
The last check finds things no rule will. Someone familiar with the data spots an implausible value immediately where a validation rule passes it.