Data Cleansing: Fixing Duplicate and Inconsistent Records

"Asha Traders", "ASHA TRADERS PVT LTD" and "Asha Trader's" are, in all likelihood, the same customer. In your database they are three customers, each with part of the order history, two different phone numbers and an outstanding balance split across all three. Multiply that across thousands of records and reports become unreliable, marketing emails go out twice, and staff stop trusting the system. Data cleansing (also called data cleaning or scrubbing) is the work of finding and fixing these problems, and then putting controls in place so they do not return.

What data cleansing fixes

Before fixing anything, it helps to name the kinds of problems you are looking for:

• Exact duplicates: the same record entered twice, often by a double form submission or a repeated import.
• Near duplicates: the same real-world entity recorded with small differences in spelling, spacing or punctuation.
• Inconsistent formats: phone numbers as "+91 98765 43210", "09876543210" and "9876543210"; dates as text in several styles.
• Inconsistent categories: "Maharashtra", "MH", "Maharastra" and "maharashtra" in a state column.
• Missing values: blanks where a value is required, or placeholders such as "NA", "-" or "test@test.com".
• Invalid values: negative quantities, birth dates in the future, email addresses without an "@".
• Orphaned records: order lines pointing at an order that no longer exists.

Step 1: Measure the problem

Cleansing should start with a baseline, so you can show progress and prioritise. A few SQL queries reveal a lot.

-- Exact duplicates on email
SELECT LOWER(TRIM(email)) AS norm_email, COUNT(*) AS copies
FROM customers
WHERE email IS NOT NULL AND email <> ''
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY copies DESC;

-- How many distinct spellings does the state column hold?
SELECT state, COUNT(*) FROM customers GROUP BY state ORDER BY state;

-- Orphaned order lines
SELECT oi.*
FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;

Record the counts. "We had 4,200 duplicate email groups and now have 30" is a much better report than "we cleaned the data".

Step 2: Back up and work on a copy

Cleansing changes and deletes records. Take a backup first, and do the work in a staging copy or inside transactions you can roll back. Keep a log of every change: which record was altered or merged, what the old value was, and why. That log is your safety net when someone asks in three months why a customer's address changed.

Step 3: Standardise formats

Many duplicates only become visible once values are normalised. Standardise first, then deduplicate.

-- Trim stray spaces and normalise email case
UPDATE customers SET email = LOWER(TRIM(email));

-- Map state variants to a standard code using a lookup table
UPDATE customers c
JOIN state_aliases a ON LOWER(TRIM(c.state)) = a.alias
SET c.state_code = a.code;

-- Keep only digits in phone numbers (MySQL 8+)
UPDATE customers
SET phone_digits = REGEXP_REPLACE(phone, '[^0-9]', '');

The state_aliases lookup table is worth highlighting. Rather than hard-coding every misspelling into queries, you maintain a small mapping table that business users can review and extend. The UPDATE ... JOIN syntax shown is MySQL's; PostgreSQL uses UPDATE ... FROM.

Store standardised values in new columns at first, alongside the originals, so you can compare before overwriting anything.

Step 4: Find near duplicates

Exact matching on a normalised email or phone number catches many duplicates. Names and addresses need fuzzy matching, which scores how similar two strings are rather than requiring them to be identical. Common techniques include:

• Edit distance (Levenshtein): how many single-character changes turn one string into another. PostgreSQL's fuzzystrmatch extension provides it.
• Trigram similarity: compares overlapping three-letter chunks; available in PostgreSQL's pg_trgm extension.
• Phonetic codes such as Soundex, which group names that sound alike. MySQL includes SOUNDEX(), though it was designed for English names and works poorly for many others.
• Token cleanup: removing words such as "Pvt", "Ltd", "and", "&" before comparing company names.

-- PostgreSQL: candidate duplicate companies in the same city
CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT a.id, a.name, b.id, b.name, similarity(a.name, b.name) AS score
FROM customers a
JOIN customers b ON a.id < b.id AND a.city = b.city
WHERE similarity(a.name, b.name) > 0.6
ORDER BY score DESC;

Comparing every record against every other is slow on large tables, which is why the query only compares customers in the same city. This is called blocking. Fuzzy matching produces candidates, not certainties. Review high-value or borderline matches manually; automatically merging two genuinely different businesses is worse than leaving a duplicate.

Step 5: Merge with clear survivorship rules

When two records are confirmed as the same entity, you must decide which values survive. Write the rules down, for example:

Re-pointing related records is the step most often forgotten. Every foreign key referencing the duplicate must move to the survivor inside the same transaction.

Step 6: Stop it happening again

A one-off cleanup decays quickly unless the causes are fixed:

• Add constraints: UNIQUE on normalised email, NOT NULL on required fields, CHECK constraints on value ranges, and foreign keys.
• Validate in forms: dropdowns instead of free-text for states and countries, format checks on phone and email.
• Search before create: show possible matches when staff add a new customer.
• Validate imports, rejecting or quarantining bad rows instead of loading everything.
• Run the Step 1 queries on a schedule and track the numbers over time.

Data cleansing is also a common first step before migrations, reporting projects and AI work. Our database management team handles deduplication and data quality work, and if clean data is groundwork for automation, see our AI and machine learning services.

Key takeaways

• Measure dirty data with SQL before you change anything, and keep a change log.
• Standardise formats first; many duplicates only appear afterwards.
• Fuzzy matching finds candidates; merge with written survivorship rules and re-point related records.
• Constraints, validation and monitoring keep data cleansing from becoming a yearly chore.