Cleaning data means making it consistent with agreed rules, not deleting anything that looks unusual. Duplicate detection also needs a definition: two equal names may be different people, while two differently cased email strings may represent one account under your policy. This reference builds a reviewable cleaning workflow using staging, normalized keys, grouping, and ROW_NUMBER(). You will select deterministic survivors and preserve relationships before deletion. The examples use modern window-function syntax supported by PostgreSQL, SQL Server, MySQL 8+, and contemporary SQLite, while avoiding a destructive cross-dialect DELETE shortcut.
Define the meaning of a duplicate
Choose the business identity before writing GROUP BY. A customer email may be a candidate only if the application's policy treats it as unique and addresses shared mailboxes. A supplier product key can be more reliable than a description. Define case, whitespace, accent, and null treatment explicitly. Lowercasing and trimming can merge values that were intentionally distinct in some domains. Do not remove punctuation from account identifiers or phone numbers without a documented rule. Keep both original and normalized representations during review so you can explain how a match was found. A duplicate group is evidence for investigation, not automatic proof every row but one should be discarded.
Stage raw data and profile its quality
Import into a staging area with source row IDs and job identifiers before changing the production table. Preserve original fields and add computed or separate normalized fields. Profile missing values, invalid ranges, unexpected formats, and duplicate keys. Use counts to understand the scope before issuing updates. Empty strings and NULL require different checks; a trim operation may turn a whitespace-only string into empty text but not necessarily into NULL. Set a clear encoding and date-format policy. A staging table lets you reject or review questionable records without mixing them with accepted data. Protect sensitive raw rows and remove them under the appropriate retention policy after the job completes.
Normalize with explicit reversible review
TRIM can remove permitted surrounding whitespace, and LOWER can build a case-normalized comparison key where the business allows it. Date parsing and numeric conversion are dialect-specific and can fail or silently coerce invalid text, so use the selected engine's safe conversion strategy. Store a reason or transformation version when auditability matters. Preview the before-and-after values and affected count. Do not overwrite every original field just to make reports look tidy. For example, keep a person's chosen display capitalization while storing a separate normalized lookup key. Test characters outside basic ASCII because collation and case conversion may not match naive assumptions. A cleanup rule should be understandable to the data owner.
Rank duplicate rows deterministically
ROW_NUMBER() OVER (PARTITION BY normalized_key ORDER BY updated_at DESC, id DESC) assigns a survivor rank under a chosen preference rule. The unique id tie-breaker makes the result deterministic when timestamps match. Rank one is not inherently the best record; it is best only under the stated policy. You might prefer a verified account, a complete address, or a trusted source before recency. Handle null normalized keys separately so every unknown identifier does not become one giant duplicate group. Compare conflicting fields before merging records. A window query can show candidates without deleting anything, making it suitable for review and a later controlled migration.
Preserve references before removing rows
If orders reference duplicate customer IDs, deleting losing customer rows can fail or cascade valuable data. Create a reviewed old-ID-to-survivor-ID mapping, migrate child references under constraints, and resolve any new uniqueness conflicts in child tables. Some records should be merged rather than simply deleted because each contains useful facts. Keep an audit trail of the mapping and counts. Perform supported changes in a transaction or staged migration with a tested recovery plan. Large updates may need bounded batches and careful locking. Avoid a single clever DELETE statement copied from another dialect; join-delete syntax and concurrency behavior differ. Verify the intended final relationship counts before removing data.
Prevent the same problem from returning
After cleanup, add the appropriate unique constraint or ingestion idempotency rule, subject to the chosen null and collation semantics. Improve input validation and supplier contracts rather than repeatedly cleaning identical errors. Check the final count against accepted source records and reconcile rejected rows. Monitor duplicate rates and transformation failures across imports. Test the workflow with duplicate timestamps, missing keys, conflicting values, and referenced losing rows. A successful query execution does not prove the cleaning was correct; inspect representative results and totals. Keep the raw snapshot or backup only as long as the recovery and privacy policy requires. Document the survivor rule so future operators can reproduce the decision.
Practice task and worked solution
Clean a product import with supplier_id, supplier_code, name, and price_text. The solution defines identity as the supplier-code pair, preserves raw values, validates decimal price format, and ranks duplicates using trusted-source priority then source row ID. Exclude missing identity keys from automatic deduplication and return them for review. Build a mapping for losing rows and inspect related inventory references before any deletion. Preview counts and representative conflicting names. After acceptance, add a suitable uniqueness rule and a rerun-safe import key. Explain why equal product names across different suppliers should not automatically merge.
Continue learning
Review data cleaning, window functions, and transactions.
The PostgreSQL window-function reference documents ROW_NUMBER and its ordered partition behavior.
SQL Data Cleaning and Deduplication Examples
Each example shows the code, the output it produces and what each part does.
Review duplicates without deleting
This self-contained CTE fixture uses window functions and common text operations. It defines lowercase trimmed email as the review key solely for this example.
Row 2 ranks first in the Ada group because it has the later timestamp. Row 1 is a candidate loser, and row 3 is unique. No data is updated or deleted by this query.
WITH incoming (id, email, updated_at) AS (
SELECT 1, 'Ada@example.com', '2026-01-01'
UNION ALL SELECT 2, ' ada@example.com ', '2026-02-01'
UNION ALL SELECT 3, 'sam@example.com', '2026-01-10'
),
ranked AS (
SELECT id, email,
LOWER(TRIM(email)) AS normalized_email,
ROW_NUMBER() OVER (
PARTITION BY LOWER(TRIM(email))
ORDER BY updated_at DESC, id DESC
) AS duplicate_rank
FROM incoming
)
SELECT id, normalized_email, duplicate_rank
FROM ranked
ORDER BY normalized_email, duplicate_rank;id | normalized_email | duplicate_rank
2 | ada@example.com | 1
1 | ada@example.com | 2
3 | sam@example.com | 1SQL Data Cleaning and Deduplication FAQ
Does SELECT DISTINCT delete duplicates?
No. It changes the result set, showing each row once, but the stored rows are untouched. Deleting duplicates needs a DELETE that keeps one chosen survivor per group, after you've reviewed which rows will go.
Is the newest row always best?
Only under an agreed survivor rule. The newest row may have an empty phone number that an older row filled in. Decide the rule with the data owners, such as "most complete, then newest", and sometimes merge fields from several rows.
Can I group every null key together?
Usually not. Grouping all NULL emails together would treat every customer without an email as the same person, which conflates unknown identities. Review those rows separately, using other attributes, or leave them out of automatic deduplication.
Should cleanup overwrite original values immediately?
No. Stage and review before irreversible changes: copy the raw table, write cleaned values to new columns or a staging table, check counts and samples, then apply. Keep the original values until the result has been verified.
How do I delete duplicates but keep one row?
Number the rows in each duplicate group and delete all but the first: WITH ranked AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY created_on DESC, id) AS rn FROM customers) DELETE FROM customers WHERE id IN (SELECT id FROM ranked WHERE rn > 1);. Run the SELECT part first and review it.
SQL Data Cleaning and Deduplication Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Two products have the same name but different supplier keys. What is correct?
SQL Data Cleaning and Deduplication Quiz
2 questions to check what you learned.
What makes a survivor ranking deterministic when timestamps tie?
- ORDER BY updated_at alone when several timestamps are equal
- A GROUP BY without any survivor preference
- A unique tie-breaker such as id
- Selecting an arbitrary MIN(name) as the surviving record ID
What should happen before deleting a referenced duplicate customer?
- Delete the parent first and assume all child references repair themselves
- Disable foreign keys permanently before deletion
- Leave orders attached to losing IDs and hide them from reports
- Review and migrate child references through a survivor mapping