Cleaning Messy Data with SQL
Profile, standardise, deduplicate, validate and reconcile a messy sign-up table, and learn when to set rows aside instead of guessing.
In this lesson you will
- Profile a table before fixing it
- Standardise text and NULLs
- Remove duplicates with ROW_NUMBER
- Reconcile counts after cleaning
About 30 minutes
The export that arrived on Friday
A new customer sends Maya a list of sign-ups. It has capital letters in odd places, spaces at the ends of words, a date written the other way round, an amount that says ‘n/a’, a negative amount and the same person twice. Dev’s advice is the oldest rule of data work: never trust a file you did not make. Cleaning data takes more of most analysts’ time than analysing it. SQL is a good place to do it, because the steps are repeatable, visible and testable. This lesson walks through a method that you can reuse on any messy table.
A method in five steps
- Profile. Count the rows, the missing values and the odd-looking ones, before changing anything.
- Standardise. Trim spaces, fix capitals and turn empty text into NULL, so equal things look equal.
- Deduplicate. Decide what makes two rows the same person, then keep one of them.
- Validate. Apply your rules, and set aside rows that break them, with a reason.
- Reconcile. Check that kept plus set aside equals the rows you started with.
Profile the table
The messy table has eight sign-ups. Query 1 counts the problems with CASE inside SUM, the conditional aggregation from lesson 13. The test for text in the amount column uses GLOB, a pattern matcher in SQLite. Other databases use regular expressions or a safe-conversion function for the same job, so look up your own.
-- 1
SELECT COUNT(*) AS rows_in,
SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS no_email,
SUM(CASE WHEN TRIM(city) = '' THEN 1 ELSE 0 END) AS blank_city,
SUM(CASE WHEN joined NOT LIKE '____-__-__' THEN 1 ELSE 0 END) AS odd_date,
SUM(CASE WHEN amount GLOB '*[^0-9.-]*' THEN 1 ELSE 0 END) AS text_amount,
SUM(CASE WHEN CAST(amount AS REAL) < 0 THEN 1 ELSE 0 END) AS negative
FROM signups
-- 2
SELECT id, UPPER(SUBSTR(TRIM(name), 1, 1)) || LOWER(SUBSTR(TRIM(name), 2)) AS name,
LOWER(TRIM(email)) AS email,
NULLIF(UPPER(SUBSTR(TRIM(city), 1, 1)) || LOWER(SUBSTR(TRIM(city), 2)), '') AS city
FROM signups WHERE id IN (1, 2, 4, 6)
-- 3
WITH cleaned AS (
SELECT id, UPPER(SUBSTR(TRIM(name), 1, 1)) || LOWER(SUBSTR(TRIM(name), 2)) AS name, LOWER(TRIM(email)) AS email,
NULLIF(UPPER(SUBSTR(TRIM(city), 1, 1)) || LOWER(SUBSTR(TRIM(city), 2)), '') AS city, joined, amount,
CASE WHEN email IS NULL THEN 'no email'
WHEN joined NOT LIKE '____-__-__' THEN 'bad date'
WHEN amount GLOB '*[^0-9.-]*' THEN 'bad amount'
WHEN CAST(amount AS REAL) < 0 THEN 'negative amount' END AS problem
FROM signups),
numbered AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY COALESCE(email, 'row-' || id), joined ORDER BY id) AS copy_no FROM cleaned)
SELECT id, email, joined, copy_no FROM numbered WHERE copy_no > 1
-- 4
WITH cleaned AS (
SELECT id, UPPER(SUBSTR(TRIM(name), 1, 1)) || LOWER(SUBSTR(TRIM(name), 2)) AS name, LOWER(TRIM(email)) AS email,
NULLIF(UPPER(SUBSTR(TRIM(city), 1, 1)) || LOWER(SUBSTR(TRIM(city), 2)), '') AS city, joined, amount,
CASE WHEN email IS NULL THEN 'no email'
WHEN joined NOT LIKE '____-__-__' THEN 'bad date'
WHEN amount GLOB '*[^0-9.-]*' THEN 'bad amount'
WHEN CAST(amount AS REAL) < 0 THEN 'negative amount' END AS problem
FROM signups),
numbered AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY COALESCE(email, 'row-' || id), joined ORDER BY id) AS copy_no FROM cleaned)
SELECT id, problem FROM numbered WHERE copy_no = 1 AND problem IS NOT NULL ORDER BY id
-- 5
WITH cleaned AS (
SELECT id, UPPER(SUBSTR(TRIM(name), 1, 1)) || LOWER(SUBSTR(TRIM(name), 2)) AS name, LOWER(TRIM(email)) AS email,
NULLIF(UPPER(SUBSTR(TRIM(city), 1, 1)) || LOWER(SUBSTR(TRIM(city), 2)), '') AS city, joined, amount,
CASE WHEN email IS NULL THEN 'no email'
WHEN joined NOT LIKE '____-__-__' THEN 'bad date'
WHEN amount GLOB '*[^0-9.-]*' THEN 'bad amount'
WHEN CAST(amount AS REAL) < 0 THEN 'negative amount' END AS problem
FROM signups),
numbered AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY COALESCE(email, 'row-' || id), joined ORDER BY id) AS copy_no FROM cleaned)
SELECT id, name, email, COALESCE(city, 'unknown') AS city, CAST(amount AS REAL) AS amount
FROM numbered WHERE copy_no = 1 AND problem IS NULL ORDER BY id
-- 1
rows_in | no_email | blank_city | odd_date | text_amount | negative
8 | 1 | 1 | 1 | 1 | 1
-- 2
id | name | email | city
1 | Asha | asha@example.com | Leeds
2 | Ben | ben@example.com | York
4 | Chloe | NULL | NULL
6 | Ben | ben@example.com | York
-- 3
id | email | joined | copy_no
3 | asha@example.com | 2026-09-01 | 2
6 | ben@example.com | 2026-09-03 | 2
-- 4
id | problem
4 | no email
5 | bad date
7 | negative amount
-- 5
id | name | email | city | amount
1 | Asha | asha@example.com | Leeds | 12.50
2 | Ben | ben@example.com | York | 8.00
8 | Femi | femi@example.com | unknown | 20.00
Query 1 found 8 rows, with 1 missing email, 1 blank city, 1 odd date, 1 amount that is text and 1 negative amount. Query 2 shows the standardising step on four rows: names get a capital first letter, emails are lower-cased and trimmed, and blank cities become NULL, which means ‘unknown’ and cannot be mistaken for a real value. After this step, rows 2 and 6 are both Ben, ben@example.com and York. Query 3 uses ROW_NUMBER, from lesson 16, to number rows that share the same email and join date. The rows numbered 2 are the extra copies: ids 3, 6. The kept row for each person is the first, the one with the lowest id. A row with no email cannot be matched to anyone, so the partition uses the row id for it, which keeps it as its own group.
Query 4 lists the rows that break a rule, among the rows we kept: id 4 (no email), id 5 (bad date), id 7 (negative amount). Each row gets exactly one reason, the first rule it breaks, in a deliberate order. Query 5 is the clean result: 3 rows, ids 1, 2, 8. Reconcile the counts: eight rows came in, 2 were duplicates, 3 were set aside, and 3 are clean, and 2 plus 3 plus 3 makes eight. If a count does not add up, find the missing rows before going on.
Choices that are yours to make
- What counts as a duplicate. The same email and date here. A name alone would merge different people.
- Which copy to keep. The first, the latest or the most complete. Write the rule down.
- Fix or reject. The date 09/10/2026 could mean the ninth of October or the tenth of September. Guessing risks silent errors, so it was set aside, not fixed.
- NULL or a default. A missing city became the word unknown in the final report, but stayed NULL in the stored data.
- Keep the originals. Do the cleaning in a new table or a query, and leave the raw table untouched.
Fixing data by guessing, for example turning every ambiguous date into one reading. A guess that looks tidy hides an error. Set the row aside with a reason, and let the owner of the data decide.
The sign-ups are made up. Functions such as GLOB, TRIM and the case tricks used here are SQLite’s, and other databases offer different or extra ones, such as regular expressions and safe casts. Check your documentation before copying a cleaning query.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
Why profile the data before changing it?
- To make the table smaller
- To know the size and kinds of problems first
- To sort it
- To speed up queries
Query 1 counted the problems before any fix.
What does ROW_NUMBER do in the duplicate step?
- Deletes rows
- Joins tables
- Sorts the result
- Numbers rows sharing a key, so extra copies get 2 or more
Copies numbered 2 or more were the extras.
Why was the date 09/10/2026 set aside rather than fixed?
- It was too long
- It could be read two ways, so a guess might be wrong
- It was a future date
- It had no year
Guessing hides errors.
What should kept plus set aside plus removed equal?
- Zero
- The number of columns
- Half the rows
- The number of rows you started with
Two duplicates, three set aside and three clean make eight.
Why leave the raw table untouched?
- You can repeat or change the cleaning later
- It saves space
- Databases forbid changes
- It speeds up joins
Keep the originals so the process can be rerun.
Summary
- Profile first, then standardise, deduplicate, validate and reconcile.
- Set aside rows with a reason instead of guessing.
- Keep the raw data and clean in a new step.
- Counts must add up.
Questions
Should I clean data in SQL or in a spreadsheet?
SQL is good when the steps must be repeatable and the data is large. Spreadsheets are fine for one-off small jobs, but they hide the steps.
How do I choose the duplicate rule?
Ask what uniquely identifies a real thing in this data, such as an email and a date, and check a few examples by hand.
Are the sign-ups real?
No. They are a made-up messy sample.
