Why Clean Data in SQL Instead of Excel?
Let me be clear about when SQL cleaning wins, because it is not always. If you have a 2,000-row file you need to fix once, Excel is faster and you already know it. SQL cleaning earns its keep when the data is in a database, when it is too big for a spreadsheet, or when you will clean the same structure repeatedly. A cleaning query you write once and re-run on every weekly export is the difference between ten minutes a week and an hour by hand.
The other advantage is auditability. A cleaning query is written down — anyone can see exactly what you did to the data, and you can reproduce it. That matters when a number has to be defensible to a manager, an auditor, or a client. 'I cleaned it in a script you can read' beats 'I fixed it in a spreadsheet where I lost track of the steps.' This reproducibility is often the real reason teams move cleaning into SQL.
One important caution before we start: if you are cleaning data you will not need again, or data you should not modify in place, use SELECT queries (which build a cleaned view) rather than UPDATE or DELETE (which change the source). I will show both, but I default to SELECT-based cleaning in this guide because it is safer — you always have the original to fall back on. Modify in place only when you are sure.
Step 1: Find (and Remove) Duplicates
Duplicate rows are the most common data problem and the most dangerous, because they silently inflate counts and totals. A duplicated order in a revenue report makes the total wrong without any error message. The fix is to first see the duplicates, then decide whether to remove them or just exclude them from your analysis. Always look before you delete.
To see which values appear more than once in a key column, use: SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVING COUNT(*) > 1. This lists every order_id that appears more than once and how many times. Run this first — it tells you the scope of the problem before you touch anything. If nothing comes back, you have no duplicates on that column and can move on.
A 'duplicate' depends on your business rule. Sometimes two rows with the same order_id are always a problem. Sometimes two rows with the same customer and date are a duplicate even with different IDs. Define your uniqueness rule explicitly — 'an order is unique by order_id' or 'a record is unique by customer_id + order_date' — before you write any dedup logic. Getting this wrong deletes legitimate data.
To keep one row per duplicate and remove the rest, use a window function: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY created_at DESC) AS rn FROM orders) SELECT * FROM ranked WHERE rn = 1. The PARTITION BY sets the uniqueness rule, ORDER BY picks which copy to keep (here the newest). This is the standard, safe dedup pattern across SQL dialects.
Never delete duplicates before you know which copy is the 'right' one. Two rows with the same order_id might have different amounts because of a correction — deleting the wrong copy loses the fix. Look at both copies first (SELECT * FROM orders WHERE order_id = 'that-id'), decide the rule, then dedup. Five minutes of inspection prevents a data-loss mistake you cannot undo.
Step 2: Handle NULLs Deliberately
NULLs are not zeros, and treating them like zeros is one of the most dangerous cleaning mistakes. A NULL in a revenue column is 'unknown,' not 'zero revenue.' If you SUM a column with NULLs, SQL ignores them by default — which might be what you want or a silent undercount. The fix is to decide, column by column, what a NULL means and how to handle it.
To map missing values across a table, run: SELECT COUNT(*) AS total, SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) AS null_amount, SUM(CASE WHEN region IS NULL THEN 1 ELSE 0 END) AS null_region FROM orders. This one query shows how many NULLs each important column has. It is your cleaning radar — it tells you which columns need attention before you build anything else.
For each NULL column, choose a strategy. Exclude: WHERE amount IS NOT NULL if a missing amount means the row is unusable. Replace: COALESCE(amount, 0) if a missing amount genuinely means zero. Flag: add a column is_missing_amount if you want to keep the row but mark the gap. The choice depends on the business meaning. Do not apply one rule to all columns — a NULL customer_id is different from a NULL discount.
Aggregations ignore NULLs by default: AVG(amount) over a column with NULLs gives the average of the non-NULL values, not the average treating missing as zero. That is usually what you want, but be aware of it. If you need the average including the missing-as-zero rows, use AVG(COALESCE(amount, 0)). The subtlety is why NULL handling needs to be deliberate, not automatic.
Add a comment next to every NULL-handling decision: '-- missing discount treated as zero' or '-- rows with NULL amount excluded because amount is required'. Future-you, or a colleague auditing your query, needs to know why you made the call. A silent COALESCE without context is how a wrong assumption about NULLs survives for months.
Step 3: Standardize Text (Casing, Spaces, Values)
Text data arrives messy: mixed casing, leading and trailing spaces, and the same value written several ways — 'New York', 'new york', 'NY'. If you GROUP BY a messy text column, you get separate buckets for each variation and your counts splinter. Standardizing text before grouping is the fix, and SQL has the functions for it.
TRIM(LOWER(region)) trims spaces and lowercases in one step. To clean the column in a query: SELECT TRIM(LOWER(region)) AS region_clean, COUNT(*) FROM orders GROUP BY TRIM(LOWER(region)). This collapses 'New York', ' new york ', and 'NEW YORK' into one 'new york' bucket. For a SELECT-based clean, use it in the GROUP BY like this so the grouping sees the normalized value.
Run SELECT region, COUNT(*) FROM orders GROUP BY region to list every distinct spelling and its count. This shows you the variants you need to consolidate. Do not guess — look. Once you see 'NY', 'New York', 'newyork' as separate buckets, you know exactly what to map. This lookup is the difference between fixing the real mess and cleaning a mess you imagined.
To map variants to a canonical value in a query: SELECT CASE WHEN region IN ('NY','newyork','new york') THEN 'New York' WHEN region IN ('CA','california') THEN 'California' ELSE region END AS region_clean, COUNT(*) FROM orders GROUP BY region_clean. This collapses the variants into clean buckets. For a permanent fix you would use an UPDATE with the same CASE, but the SELECT version is safe for analysis.
For text columns with a known set of valid values (like regions or statuses), the CASE-based mapping is your friend. For free-text fields like names or notes, do not try to consolidate everything — just trim, lowercase, and leave the variety. Over-normalizing free text destroys real differences. Match the level of standardization to the column's purpose.
Step 4: Fix Date and Type Problems
Dates stored as text are a classic mess: '01/15/2024', '15-01-2024', '2024-01-15' all describe January 15 but sort and compare differently as text. If you GROUP BY a text date, every format becomes its own bucket and your time series breaks into meaningless fragments. Converting dates to a real date type is the fix, and it is essential before any time-based analysis.
In most databases, CAST or the dialect's date function handles it: CAST(order_date AS DATE) in PostgreSQL and SQL Server, STR_TO_DATE(order_date, '%m/%d/%Y') in MySQL, or CAST(order_date AS DATE) in BigQuery. The exact function differs by database, but the goal is the same: turn the text into a type that sorts and compares as a date. Look up the right function for your database once and keep it as a template.
Before converting, run SELECT order_date, COUNT(*) FROM orders GROUP BY order_date ORDER BY COUNT(*) DESC to see the distinct values. If some are '01/15/2024' and others are '2024-01-15', a single format conversion will fail or misread some of them. You may need to handle the different formats separately, or find the source and standardize it. A date column with mixed formats is a sign to fix it upstream, not just in your query.
If dates are timestamps from different regions, they may need timezone conversion before you group by day. A transaction at 11pm Pacific is the next day in UTC. Decide which timezone your reporting uses and convert consistently. This is subtle and easy to miss, but it shows up as a boundary day being off by one in your daily totals. If your data crosses timezones, handle it explicitly.
Always test a date conversion on a small sample before running it on the whole table. Convert a few rows, inspect them, and confirm they read as the correct dates. A single wrong format assumption converts an entire column to NULLs or to a shifted date, and once you aggregate, the error is baked in. Five minutes of sampling saves an hour of debugging a time series that is off by a month.
Step 5: Spot and Handle Weird Values
Beyond duplicates, NULLs, text, and dates, your data will contain values that are simply wrong — a negative revenue, a price of 9999999, a percentage above 100. These are often typos or unit errors, and they distort averages and totals if you do not catch them. The skill is finding them (distribution checks) and deciding whether to fix, exclude, or flag.
Run a distribution query: SELECT MIN(amount), MAX(amount), AVG(amount), COUNT(*) FROM orders. The MIN and MAX immediately reveal outliers — a minimum revenue of -5000 or a maximum of 99999999 jumps out. These are the values that would wreck your average if you left them. Profiling is the fastest way to find problems you did not know you had.
When MIN or MAX looks wrong, inspect the actual rows: SELECT * FROM orders WHERE amount > 1000000 OR amount < 0. This shows you the outliers in context. Sometimes a huge value is real (a bulk order); sometimes it is a typo (an extra digit). You cannot tell from the number alone — you have to look at the row and use business judgment. This step is why cleaning is still human work even with SQL.
For each outlier, choose a strategy. Fix: correct the typo if you can confirm it. Exclude: filter it out of the analysis with WHERE if it is an error. Flag: add a column marking it suspicious if you are not sure, so it can be reviewed. The worst choice is leaving a known-wrong value in silently. Whatever you decide, make it visible in your query and your output.
Record every outlier decision. Keep a small table or comment log of 'what I found and what I did' — 'removed 3 rows with negative revenue (data entry error), flagged 1 row with amount > $1M (awaiting confirmation)'. When someone asks about a number, you can show exactly what you adjusted. This audit trail is what makes SQL cleaning defensible and repeatable.
Putting It Together: A Repeatable Cleaning Workflow
The power of cleaning in SQL is that you can turn these individual steps into one repeatable workflow. Instead of re-cleaning every new export by hand, you build a sequence of queries (or a view) that transforms the raw table into a clean one, then run the whole thing on every new batch. Here is the order that works, and it maps to the steps above in the right sequence.
Run the discovery queries first: row count, duplicate check, NULL map, distinct text values, and min/max on numerics. This tells you what problems exist before you write any fixes. Cleaning blind — without profiling — means you fix imagined problems and miss the real ones. Ten minutes of profiling saves an hour of wrong cleaning.
Handle the foundational issues first: convert dates and types, then standardize text, then handle NULLs, then duplicates, then outliers. The order matters because each step can affect the next — converting a date first means later grouping sees real dates. Fix types and text before you dedupe, because duplicates defined on unnormalized text are unreliable.
After each cleaning step, check that your row count is what you expect. Removing duplicates should reduce rows by the number of duplicates you found. Converting types should not change the row count at all. A row count that changes unexpectedly is a signal a step is doing more (or less) than you intended. Track the count through the whole workflow.
Once you have a clean sequence, save it as a script or a view you can run on the next export. Write a comment at the top describing the source and the cleaning rules. Next week, next month, or next quarter, you run the same workflow on the new data and get a clean table in minutes. This is the moment SQL cleaning stops being a chore and starts paying you back.
Make your cleaning workflow idempotent — running it twice should give the same result as running it once. If a step is 'remove duplicates', running it twice removes no extra rows. This makes the workflow safe to re-run and debug, because you can test it repeatedly without corrupting the data. Idempotency is the difference between a cleaning script you trust and one you fear.
The Cleaning Mistakes That Corrupt Results
These are the errors I see most often, and they all share a theme: they look fine and are silently wrong. The good news is each one is preventable with one check. Watch for these and your cleaning output will be trustworthy — which, at the end of the day, is the whole point of cleaning.
SUM and AVG ignore NULLs, but if you convert NULL to 0 with COALESCE and then sum, you are adding zeros that were not there — fine for SUM, misleading for AVG and MIN/MAX. Know what your aggregation does with NULLs and choose your COALESCE strategy deliberately. A silent 'COALESCE(amount, 0)' can turn a clean average into a wrong one.
Deleting duplicate rows without looking at the copies first can remove the corrected version and keep the wrong one. Always inspect both copies, decide the keep-rule, then dedupe. The ROW_NUMBER pattern is safe because you control which copy survives. Blind DELETE of duplicates is how real data gets lost.
GROUP BY on a text column with mixed casing or extra spaces splits one logical value into several buckets. 'New York', 'new york', and 'newyork' become three groups, and your counts are wrong. Normalize the text (TRIM + LOWER, plus CASE for variants) before grouping. This is the most common silent error in text-heavy analysis.
If your date column mixes formats, a single conversion will fail or misread part of it. Always check the distinct date values before converting, and handle mixed formats separately. A time series built on misread dates looks plausible and is wrong. The distribution check is your defense.
The universal safety net: build your cleaned dataset as a new table or view, never by overwriting the original in place. Keep the raw data intact and untouchable. If a cleaning step is wrong, you re-run it from the clean source instead of reconstructing data you destroyed. One line of 'keep the original' saves you from the worst cleaning disasters.
