Step 0 — Profile before you clean

Cleaning without profiling means guessing at problems that might not exist, or missing ones that do. A few queries reveal most of what's wrong before writing a single fix:

profile.sql
-- Row count vs. distinct count on a column that should be unique
SELECT COUNT(*) AS total_rows, COUNT(DISTINCT customer_id) AS distinct_ids
FROM customers;

-- Every distinct value in a category column, to spot inconsistent labels
SELECT country, COUNT(*) AS n
FROM customers
GROUP BY country
ORDER BY n DESC;

-- NULL rate per column
SELECT
  SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_emails,
  SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS null_phones
FROM customers;

The second query alone usually surfaces the most common cleaning problem: the same real-world value spelled several different ways.

Advertisement

Trim and standardize text

trim-standardize.sql
SELECT
  TRIM(customer_name)               AS no_stray_whitespace,
  UPPER(TRIM(country_code))          AS standardized_code,
  INITCAP(TRIM(city))                AS title_case_city,  -- PostgreSQL/Oracle
  REPLACE(phone, ' ', '')             AS digits_only_phone
FROM customers;

INITCAP() isn't available in every engine — SQL Server and MySQL don't ship it natively, and title-casing there typically needs a small function or a CASE-based workaround. Collapsing repeated internal spaces (not just leading/trailing) needs a slightly different tool:

collapse-double-spaces.sql
-- PostgreSQL: collapse any run of whitespace down to a single space
SELECT REGEXP_REPLACE(TRIM(customer_name), '\s+', ' ', 'g') AS clean_name
FROM customers;

Fix inconsistent category labels

For a small, known set of variants, a CASE expression is the fastest fix:

standardize-categories.sql
SELECT
  CASE
    WHEN UPPER(TRIM(country)) IN ('USA', 'U.S.A.', 'UNITED STATES', 'US') THEN 'United States'
    WHEN UPPER(TRIM(country)) IN ('UK', 'U.K.', 'UNITED KINGDOM', 'GREAT BRITAIN') THEN 'United Kingdom'
    ELSE TRIM(country)
  END AS standardized_country
FROM customers;

Once the variant list grows past a handful of entries, a small mapping table is easier to maintain and update than an ever-longer CASE expression:

mapping-table.sql
CREATE TABLE country_aliases (
  raw_value    VARCHAR(50),
  clean_value  VARCHAR(50)
);
-- ('usa','United States'), ('U.S.A.','United States'), ('uk','United Kingdom'), ...

SELECT c.customer_id, COALESCE(a.clean_value, TRIM(c.country)) AS standardized_country
FROM customers c
LEFT JOIN country_aliases a ON UPPER(TRIM(c.country)) = UPPER(a.raw_value);

Blanks vs. NULLs

A blank string ('') and NULL both look empty in a query result, but they aren't the same thing — one was actively recorded as nothing, the other was never recorded at all — and mixing them up quietly skews aggregates:

blank-vs-null.sql
-- Convert a blank string to a true NULL, so COUNT() and AVG() treat it consistently
SELECT NULLIF(TRIM(middle_name), '') AS middle_name_or_null
FROM customers;

-- Convert a NULL numeric field to 0 only where that's the correct business meaning
SELECT COALESCE(discount_amount, 0) AS discount_amount_safe
FROM orders;
Coalescing every NULL to 0 by default is a common overcorrection — a NULL shipped_date means "hasn't shipped," not "shipped on day zero." Decide per column what NULL actually means before deciding how to fill it. See NULL vs blank vs zero for reporting for the full decision framework.
Advertisement

Deduplicate rows

Once duplicates are confirmed with the profiling query from Step 0, ROW_NUMBER() partitioned on the columns that define a duplicate is the standard fix:

find-duplicates.sql
SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

This article covers finding duplicates as part of the profiling pass — for the full removal technique when there's no unique ID to key off, see Remove Duplicates in SQL Without a Unique ID.

Validate types and ranges

validate.sql
-- SQL Server: find values that don't actually parse as a date, without erroring out
SELECT raw_order_date
FROM orders_staging
WHERE TRY_CAST(raw_order_date AS DATE) IS NULL
  AND raw_order_date IS NOT NULL;

-- Sanity-range check: negative quantities or absurd unit prices
SELECT * FROM order_lines
WHERE quantity < 0 OR unit_price > 100000;

TRY_CAST (SQL Server) and TRY_CONVERT return NULL instead of throwing an error on a bad value, which is what makes it possible to find every offending row in one pass instead of the query dying on the first one.

The staging-table workflow

Cleaning transformations written directly against the raw table are a one-way door — a wrong assumption overwrites data with no way back. The safer shape is three layers:

staging-workflow.sql
-- 1. Raw — loaded as-is, never modified
CREATE TABLE raw_customers AS SELECT * FROM source_extract;

-- 2. Cleaned — every transformation applied, kept as a view so logic stays visible
CREATE VIEW cleaned_customers AS
SELECT
  customer_id,
  TRIM(customer_name) AS customer_name,
  COALESCE(a.clean_value, UPPER(TRIM(c.country))) AS country,
  NULLIF(TRIM(c.middle_name), '') AS middle_name
FROM raw_customers c
LEFT JOIN country_aliases a ON UPPER(TRIM(c.country)) = UPPER(a.raw_value);

-- 3. Marts — built only on top of the cleaned layer, never on raw

Keeping the cleaning logic in a view (or a CTE, for smaller one-off jobs) instead of an in-place UPDATE means it's re-runnable, reviewable in a pull request, and never destroys the original values.

Common mistakes

  • Cleaning with UPDATE statements directly on raw source data instead of a staging table or view — unrecoverable if the logic is wrong.
  • Coalescing every NULL to 0 or an empty string by default, without checking whether NULL actually has a different business meaning for that column.
  • Standardizing casing without trimming first — UPPER(' usa') and UPPER('usa') still won't match in a comparison or GROUP BY.
  • Assuming a duplicate check on the whole row will catch duplicates that differ only by whitespace or casing in one column — normalize before comparing.

Key takeaways

  • Profile a table (row counts, distinct values, NULL rates) before writing any cleaning logic.
  • TRIM and standardize casing first — most other comparisons and mappings depend on it.
  • Use CASE WHEN for a handful of inconsistent labels, a mapping table once the list grows.
  • Blanks and NULLs are not interchangeable — decide per column what each one should mean before filling it.
  • Clean into a staging table or view, never by overwriting raw source data in place.