Clean data means one meaning, one spelling

The idea

Dirty data is rarely random garbage. It is the same real-world thing written in several ways: "New York", "NEW YORK" and "New York " (with a trailing space) are one city to you and three different values to the database. SQL compares text exactly, byte for byte, so it will happily make three groups, three customers or three join keys out of one.

Cleaning is the work of deciding what the one canonical form is, and converting every variant to it before you group, join or count. The second half is deciding what to do with rows that should not count at all (duplicates, impossible values) and values that are missing.

Why it exists

A report built on dirty data is wrong in a way that looks right. A city chart with four New Yorks still draws bars. A customer count that includes twenty people twice still prints a number. Nothing fails, so nobody notices until someone checks against a total they trust.

Fixing it in the source table is often impossible: you do not own the CRM, the supplier file arrives as text again next week, and you should not overwrite raw data you may need later. So analysts clean in the query: a CTE or view that turns raw rows into a trusted version, and every report reads from that. One place to fix, and a record of what was done.

How it works under the hood

Build a clean key

A key is the value you group or match on. Make it with functions that remove irrelevant differences: TRIM for stray spaces, LOWER or UPPER for case, REPLACE for punctuation. Apply the same expression everywhere the value is used. Group by the clean key and keep the original spellings alongside if you want to show them.

Note what you are not doing: you are not deciding that "NYC" means New York. That needs a mapping table, not a function, and it is a business decision.

Choose which duplicate wins

Duplicate people are different rows that should be one. Remove them with ROW_NUMBER(), not DISTINCT: DISTINCT only drops rows that are identical in every column, and these differ in id, signup date or the case of an email.

The ORDER BY inside the window is the business rule: earliest signup, most recent update, most complete row. Customers 2001 to 2018 in Harbor & Pine are such duplicates, some sharing an email exactly and some differing only in capitals or spaces. Remember to move orders onto the surviving id if you will count per customer.

Respect NULL

NULL means "unknown", and SQL carries the unknown through. 5 + NULL is NULL. NULL = NULL is not true. WHERE referral_source <> 'search' drops NULL rows, because NULL <> 'search' is unknown, not true. Aggregates skip NULLs, so AVG of a column with blanks averages only the filled rows.

COALESCE(a, b) returns the first value that is not NULL, so you can substitute a label or a default. Use it for display ('unknown'), not to invent numbers: replacing a missing cost with 0 makes margin look better than it is.

NULLIF(a, b) does the reverse: it returns NULL when a equals b. It is the standard guard against dividing by zero, because x / NULLIF(y, 0) gives NULL instead of an error when y is 0.

Convert text on purpose

Files pasted from suppliers and carriers arrive as text: $1,299.00, 2023-02-06, 06/02/2023. CAST fails the whole query on one bad value; TRY_CAST (and try_strptime for dates) returns NULL instead, so you can count and inspect the failures. Clean the text first (strip the $ and the comma), and parse each date format in its own branch.

Flag, don't delete

Some rows look wrong but may be real: order lines with quantity 0 or -1, or a price ten times the median. If you delete them quietly, the totals drop and nobody knows why. Add a flag column (is_outlier, is_valid_quantity) instead, report on the clean rows, and keep a count of what was excluded. A stated rule such as "outside 1.5 times the interquartile range" is something a colleague can argue with. A silent delete is not.

Order matters when you clean

Clean in layers, each in its own CTE: first text (trim, case, strip symbols), then types (cast and parse), then duplicates, then flags. A layer can only trust the one before it. Deduplicating on an email before lowercasing it misses BJONES76@GMAIL.COM, and casting a price before removing its $ turns a good value into a failure. When a number looks off, run the layers one at a time and see which one changed it.

Common misconceptions

"DISTINCT removes duplicate customers"

It removes rows that match in every selected column. Two records for one person differ in at least the id, so they are not duplicates to DISTINCT. Select only the columns that define a person and the case still matters, which is why you clean the key first.

"NULL is the same as zero or an empty string"

NULL is unknown. Zero is a number, and '' is text of length zero. They behave differently in counts, averages and comparisons. Before replacing NULLs, ask what the blank means: "no referral source recorded" is not a source called blank.

"Cleaning means fixing the table"

Cleaning in SQL means writing a query or view that returns the corrected version. The raw table stays untouched, so you can re-run the clean-up when the rules change and prove what you changed.