Data cleansing

Overview

Data cleansing covers the concrete fixes applied once data profiling has revealed problems: filling or removing nulls, standardizing formats (dates, phone numbers, casing), trimming whitespace, fixing encoding issues, correcting invalid values, and reconciling inconsistent representations of the same thing (e.g. "NY", "N.Y.", and "New York" all meaning the same state).

It sits between raw ingestion and modeling in most pipelines. Cleansing logic is typically expressed as SQL transformations, dbt models, or scripts that run as part of a scheduled job, so the same fixes are applied consistently every time new data arrives rather than being patched ad hoc.

Common cleansing operations in SQL

Handling nulls and defaults with COALESCE:

Copy code
SELECT
  customer_id,
  COALESCE(country, 'UNKNOWN') AS country,
  COALESCE(discount_pct, 0) AS discount_pct
FROM raw_customers;

Trimming and standardizing text:

Copy code
SELECT
  TRIM(email) AS email,
  UPPER(TRIM(state_code)) AS state_code
FROM raw_customers;

Pattern-based cleanup with regular expressions. DuckDB's regexp_replace takes a string, a pattern, a replacement, and optional flags:

Copy code
-- strip non-digit characters from a phone number
SELECT regexp_replace(phone, '[^0-9]', '', 'g') AS phone_digits
FROM raw_customers;

Standardizing categorical values with CASE:

Copy code
SELECT
  CASE
    WHEN LOWER(status) IN ('active', 'a') THEN 'ACTIVE'
    WHEN LOWER(status) IN ('cancelled', 'canceled', 'c') THEN 'CANCELLED'
    ELSE 'UNKNOWN'
  END AS status_clean
FROM raw_subscriptions;

Type casting and validation:

Copy code
SELECT
  TRY_CAST(order_date AS DATE) AS order_date,
  TRY_CAST(amount AS DECIMAL(10,2)) AS amount
FROM raw_orders;

TRY_CAST returns NULL instead of erroring on a value that can't be converted, which is useful for isolating bad rows rather than failing an entire load.

Why it matters

Uncleaned data quietly breaks joins, skews aggregates, and undermines trust in dashboards and models. Cleansing rules, once written, are usually one of the first transformation steps in a pipeline (often the first dbt model layer, sometimes called "staging") so that every downstream model works from a consistent, validated base rather than re-deriving the same fixes repeatedly.