Chapter 10 · 8 levels
Cleaning data in SQL
Make messy data trustworthy before you report on it: standardize text, remove duplicate records, handle NULLs, divide safely, cast and parse raw text, and flag outliers.
StartReal tables are messy. Harbor & Pine’s customer table has the same city spelled four ways, people who signed up twice, and hundreds of blank referral sources. Order lines include a few zero and negative quantities, and files from suppliers and carriers arrive as plain text with dollar signs and three date formats.
This chapter turns that mess into numbers you can defend. You will build clean grouping keys, keep
one record per person with ROW_NUMBER, label and count NULLs with COALESCE, avoid dividing by
zero with NULLIF, convert text with TRY_CAST and try_strptime, and flag outliers instead of
silently deleting them. Each lesson also shows the SQL Server spelling, since many courses teach
T-SQL. The boss level builds a clean customer view and reports from it.
Levels
- One key per location
from Ryan Cooper, Regional Sales Manager
data cleaning, trim, upper, lower, concat, grouping key
- Keep one record per person
from Tyler Morgan, Junior Data Engineer
deduplication, row number, window functions, data cleaning
- Label the blanks with COALESCE
from Jordan Reyes, Head of Marketing
coalesce, null, data cleaning, percent of total
- Cost per conversion without dividing by zero
from Jordan Reyes, Head of Marketing
nullif, division by zero, ratios, data cleaning
- Turn a pasted price sheet into numbers
from Jessica Turner, Merchandising Manager
try cast, cast, replace, type conversion, data cleaning
- Parse a carrier feed with mixed date formats
from Kevin Brooks, Operations Manager
date parsing, strptime, strftime, coalesce, data cleaning
- Flag outliers, don't delete them
from Brian Mitchell, Finance Analyst
outliers, iqr, quantile, data quality, cte, data cleaning
- Boss: a clean customer view
from Priya Shah, Head of Data
data cleaning, deduplication, row number, coalesce, nullif, trim, cte