The tidy five-row orders table you queried in Part 8 does not exist in the wild. What lands in your inbox is a spreadsheet where the same customer appears three times under three spellings, half the phone numbers are blank, one date reads 04/01/2026 and the next reads 2026-04-01, and somewhere a spend column holds a value of minus 50. Before any of the grouping and joining you just learned pays off, the data has to be made trustworthy. That work is data cleaning, and most analysts spend more time on it than on the analysis itself.

Cleaning is not glamorous, but it is where you earn trust. A single duplicated customer inflates every count you report; one stray text value in a number column breaks an average with no warning. This part walks through the problems real datasets carry, then gives you a repeatable order for fixing them that you can run the same way every time. You already own the tools: the SQL from Parts 6 to 8 and the spreadsheet skills from Part 4 handle most of what follows.

What dirty data actually looks like

Every messy dataset you will ever open is a mix of the same handful of problems. Once you can name them, cleaning stops feeling like a bottomless chore and becomes a checklist. The table below lists the five that account for almost all of it, with what each looks like and how often it shows up. The counts come from profiling a typical 1,000-row customer export, the kind of file a sales system spits out, so you get a feel for the scale rather than a vague warning.

The five recurring problems, with how many rows each touched in one real 1,000-row export. Inconsistent text is almost always the largest bucket.

Notice that these overlap. A single bad row can carry three of them at once: a duplicate customer whose city is misspelled and whose spend is blank. That is why cleaning has an order, which we come to at the end. First, look at what one dirty row and its cleaned version actually contain, so the rest of the article has something concrete to point at.

Look before you clean

The single most common beginner mistake is to start fixing things immediately. You cannot fix what you have not measured. Profiling means asking the data a few blunt questions before you touch it: how many rows, how many are blank in each column, how many distinct values a column holds, and what its smallest and largest values are. Every one of those questions is a query you already know. COUNT(*) gives the row count. COUNT(column) against COUNT(*) reveals how many are missing. COUNT(DISTINCT column) tells you whether a city column has the 30 cities you expect or 47 because of typos. MIN and MAX flag impossible values, a spend of minus 50 or a birth year of 1017.

Run those checks first and you get a profile like the chart below, a plain count of how many rows each problem touched. Now your cleaning has a plan and a size. You know inconsistent text is the big job here and outliers are a small one, so you spend your effort where the rows are.

Missing values and what to do with them

A missing value is a cell with no real data: an empty string, a NULL, or sometimes a placeholder such as NA, unknown, or a sneaky 0 that actually means nobody filled it in. Your first job is to tell a true zero from a missing one, because treating a missing spend as zero drags every average down and quietly lies to whoever reads the report. Here is a small messy sample to make the choices concrete.

Five rows, every problem present: row 2 duplicates row 1 with different case and a trailing space, row 3 has no spend, row 4 has no email, row 5 has a city typo and an impossible spend.

You have three honest choices for a missing value, and the right one depends on why it is missing and how many there are. You can remove the row, which is fine when the gaps are few and scattered and the row is not worth much without the value. You can fill it in, called imputation, using a sensible substitute: the median for a skewed number like spend, the most common value for a category like city, or a value looked up from another system. Or you can flag it, adding a column that marks the value as estimated or missing so nobody downstream mistakes your guess for a fact.

The one rule that matters more than the choice: never fill a missing value silently and never overwrite the original. If you impute Ravi’s spend with the median, keep his real value, blank, in the raw file and do the fill in a clean copy, with a note. An analyst who quietly invents numbers loses trust the first time someone traces one back. When you are unsure whether a gap is random or meaningful, leave it as NULL and let your aggregates skip it, exactly the behaviour you learned in Part 8.

Duplicates that hide in plain sight

A duplicate is the same real thing recorded more than once. The trap is that duplicates rarely match exactly. In the sample, rows 1 and 2 are both Ana Rao, but one email is ana@shop.com and the other is Ana@shop.com with a trailing space. To a person they are one customer; to a database they are two different strings. So you never deduplicate on the raw text. You deduplicate on a normalised key, a version of the value with the case and spacing stripped out, so the two spellings collapse into one.

The window functions from Part 8 do this cleanly. Number the copies of each normalised email with ROW_NUMBER, and every row where the copy number is above 1 is a duplicate to inspect. This finds duplicates without deleting anything, which is the safe way to start.

SELECT customer_id,
       name,
       email,
       ROW_NUMBER() OVER (
         PARTITION BY lower(trim(email))
         ORDER BY customer_id
       ) AS copy_number
FROM customers
ORDER BY lower(trim(email)), copy_number;

Result: Ana’s two rows come out together, the first with copy_number 1 and the second with copy_number 2. Every other customer shows copy_number 1. Filtering to copy_number greater than 1 lists exactly the rows to review or remove.

Common failure: partitioning by the raw email instead of lower(trim(email)). Then Ana@shop.com and ana@shop.com land in different partitions, both get copy_number 1, and the duplicate slips through looking clean. The normalising step inside PARTITION BY is the whole point; without it the query runs fine and finds nothing.

Inconsistent labels and text

Mumbai, mumbai, and Mumbai with a trailing space are one city stored three ways. Every GROUP BY splits them into three groups, and your city report claims three cities where there is one. This is the largest bucket in most real files, and it is the easiest to fix once you see it. Standardise text before you group: strip the surrounding spaces, settle on one case, and map known variants and typos to a single canonical spelling.

Three tools cover almost all of it. TRIM removes leading and trailing spaces. LOWER, or UPPER, forces a single case so Delhi and delhi stop being rivals. And a small lookup table maps the typos you cannot fix by rule, Dellhi to Delhi, Bombay to Mumbai, to one agreed value. Do this once, near the start of your cleaning, so every query downstream sees tidy labels and your counts stop lying.

A cleaning order you can repeat

The steps have an order, and the order matters. Standardise text before you remove duplicates, or Ana@shop.com and ana@shop.com will not match and the duplicate survives. Fix types before you check ranges, or a spend stored as text will not compare against a number. Validate at the very end, then write down what you did. The flow below is the sequence I run on every new file, and it rarely changes.

flowchart TD
  A[Profile the raw data] --> B[Fix types and formats]
  B --> C[Standardise text, trim and case]
  C --> D[Handle missing values]
  D --> E[Remove duplicates on a clean key]
  E --> F[Check ranges and outliers]
  F --> G[Validate and profile again]
  G --> H[Document every change]

Outliers get their own step near the end for a reason: an odd value is not automatically wrong. A spend of minus 50 is impossible and should be corrected or removed, but a spend of 40,000 might be your best customer, not an error. The rule is to investigate before you act, flag the strange value, find out its story, and document the decision either way. Deleting a real outlier because it looked ugly throws away the exact rows a manager most wants to hear about.

Clean in a script, not by hand

Here is the recommendation this whole part builds to: never clean by editing cells in the raw file. Write every fix as a query, a spreadsheet formula, or later a pandas script, leave the original export untouched, and produce a separate clean copy. A hand-edited file cannot be re-run when next month’s export lands with the same problems, cannot be reviewed by a teammate, and hides what you changed. A script does all three: it re-runs in seconds, anyone can read it, and it is a record of exactly what you did and why. The extra ten minutes to write the query instead of clicking pays for itself the second time the file arrives.

You can now name the five problems in any dataset, profile a file before touching it, and fix missing values, duplicates and messy text with the SQL you already have. Part 10 picks up the clean table and starts the fun part, exploratory data analysis, where you poke at the data to find the story worth reporting. Keep the Data Analyst guide open as your map, and glance back at Part 8 when the dedup query’s window function needs a refresher.

This week, take any messy export you can find, profile it first with COUNT and COUNT DISTINCT, then write your five fixes as queries against a copy while the raw file stays read only. Do it once as a script and you will never dread a dirty file again.

References

• DataFrame.drop_duplicates, pandas Documentation
• String Functions, including trim and lower, PostgreSQL Documentation
• Data Cleaning and Preprocessing, Principles of Data Science, OpenStax

About The Author

Dr. Pranay Jha

Dr. Pranay Jha is a Cloud and AI Consultant with 18+ years of experience in hybrid cloud, virtualization, and enterprise infrastructure transformation. He specializes in VMware technologies, multi-cloud strategy, and Generative AI solutions. He holds a PhD in Computer Applications with research focused on Cloud and AI, has published multiple research papers, and has been a VMware vExpert since 2016 and a VMUG Community Leader.

See author's posts