Skip to content
Contact →

Data Cleansing Strategies

Updated
25 Sept 2026
5 min read
9 sections
Page 14 of 29
Data Migration Masterclass

Section 3: Data Assessment and Preparation. Lesson 14.

Data cleansing, also called data cleaning or data scrubbing, is the systematic process of detecting, correcting, standardising and removing inaccurate, incomplete, duplicate, inconsistent or invalid data from a dataset in a migration project.

It is one of the most critical preparation activities before a migration. Loading poor-quality data into a new system creates operational problems, inaccurate reporting, higher compliance risk and lower user trust. The goal is to improve data quality before the migration happens.

Where cleansing fits

text
Profile (lesson 12) → Identify issues (lesson 13) → Cleanse (this lesson) → Validate → Migrate

Profiling and quality assessment surface the issues: missing values, duplicate records, inconsistent formats, invalid values and outdated information. The video game dataset from earlier lessons showed missing values and duplicates. Cleansing then decides what to do about each one.

Duplicates rarely appear on their own. They usually come from manual data entry, repeated imports, merging systems, or integrations that retry on failure.

Objectives of a cleansing strategy

  • Improve data accuracy
  • Remove duplicate records
  • Correct invalid values
  • Fill in missing information wherever possible
  • Standardise data formats
  • Improve consistency across integrated systems
  • Support legal and compliance requirements
  • Prepare the data for a successful migration

The lecture suggests duplicates matter less today than invalid values, missing data and standardisation, because modern systems enforce unique keys. That holds for exact duplicate rows in well-built databases, but near-duplicates (the same customer with slightly different spellings or emails) remain very common, especially in CRMs and after mergers. Keep deduplication in the plan.

Cleansing is not just deleting bad records

This is the most important rule of the lesson. In the Excel demo, three game rows have some fields missing but the rest of their statistics intact. Selecting and deleting them would make the sheet look clean, but it would permanently throw away valid data.

A record with gaps often still holds valuable business information. A customer with no phone number may still have a perfectly valid purchase history, and a game with no platform title may still have complete sales figures. Such records should be corrected, not removed.

Trace back to the source

Instead of deleting, trace each problem record back to where it came from (sometimes called back-tracing, or following its data lineage):

  • Check the original source system, earlier exports or backups for the missing value.
  • Look it up in another system of record, such as a platform name from a product catalogue.
  • Ask the data owner or the team that created the record.
  • If it cannot be recovered, apply a predefined business rule, such as "Unknown" or a documented default, and flag it.

Decision guide for a problem record

Never hard-delete during cleansing. Keep the original, write cleansed data to a staging copy, and log every change so decisions can be audited and reversed.

Collaboration between technical and business teams

A data analyst does not cleanse data alone:

  • Technical teams identify structural anomalies and implement the correction logic.
  • Business users provide the domain knowledge: which value is correct, and how inconsistencies should be resolved.

Combining technical analysis with business validation keeps cleansing decisions aligned with organisational objectives. Record each agreed rule, such as "customers with no order in 7 years are archived", and who approved it.

Core cleansing techniques

  • Parsing: split combined fields, such as a full name into first and last.
  • Standardisation: common formats for dates, phone numbers, units, casing and codes (covered in lesson 16).
  • Validation and correction: enforce type and range rules and fix typos with lookup tables.
  • Enrichment: fill gaps from trusted reference data, such as postcode to city.
  • Deduplication: exact and fuzzy matching, then merge with survivorship rules (covered in lesson 15).
  • Missing-value handling: recover, default, flag or exclude (covered in lesson 15).
  • Outlier review: confirm or correct extreme values rather than deleting them automatically.

Example in pandas

python
import pandas as pd

raw = pd.read_csv("video_games.csv")
df = raw.copy()                      # never cleanse the original

# Standardise text
df["platform"] = df["platform"].str.strip().replace({"ps4": "PlayStation 4", "PS-4": "PlayStation 4"})

# Invalid values: numbers in a text column become missing, then get flagged
bad_title = df["title"].astype(str).str.fullmatch(r"\d+")
df.loc[bad_title, "title"] = pd.NA

# Keep incomplete rows, but flag them instead of deleting
df["needs_review"] = df[["title", "platform"]].isna().any(axis=1)

# Remove exact duplicates only
df = df.drop_duplicates()

# Drop fully empty helper columns (blank headers)
df = df.loc[:, ~df.columns.str.startswith("Unnamed")]

df.to_csv("video_games_clean.csv", index=False)
print(len(raw), "->", len(df), "rows;", df["needs_review"].sum(), "flagged")

Tools

  • OpenRefine: free, open-source desktop tool for exploring and cleaning messy data, with clustering to find near-duplicate spellings.
  • HubSpot: built-in duplicate management and property cleanup for CRM data.
  • Excel and Power Query: good for small datasets and one-off fixes.
  • Python (pandas) and SQL: scriptable and repeatable, and the default for real migrations.
  • Enterprise tools: Informatica Data Quality, Talend, Trifacta and Alteryx, AWS Glue DataBrew.
  • AI assistants: suggest corrections, mappings and rules quickly, following the same data-sensitivity cautions as in lesson 13.

Manual versus automated cleansing

Cleansing used to rely on heavy manual effort at almost every step, which was slow and prone to typing errors. Automated tools and AI have greatly reduced that effort and made the process more efficient. Human judgement still matters: business users approve the rules, and edge cases flagged for review still need a person to decide. The best approach automates the repeatable rules and reserves people for the exceptions.

Try it: Cleansing Decision Desk

Six problem records from a staging area: a blank title with intact sales, three spellings of one platform, a customer with no phone but a real order history, an exact duplicate, a leftover test account, and a negative paid invoice. It opens with an example first attempt that deletes two rows. Change the actions, then check them against the recommended call and its reasoning. The tiles track how many records you kept and how many you deleted.

Cleansing Decision DeskOpen full size ↗

Key takeaways

  • Cleansing detects, corrects, standardises and removes bad data before migration.
  • Don't just delete: records with gaps often hold valuable data. Trace back, correct, default or flag.
  • Technical teams implement the logic; business users decide what is correct.
  • Work on a copy, log every change, and automate repeatable rules.

Next: 15. Handling Missing and Duplicate Data.