---
title: "Data cleansing"
description: "Data cleansing (or data cleaning) is the process of detecting and correcting inaccurate, incomplete, inconsistent, or malformed data so it's reliable for analysis and downstream systems."
canonical: "https://motherduck.com/glossary/data-cleansing/"
related:
  - title: "Analyze JSON Data Using SQL and DuckDB"
    url: "https://motherduck.com/blog/analyze-json-data-using-sql/"
  - title: "Data loading patterns | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-patterns/"
  - title: "Text-to-SQL, Data Modeling for AI, and MotherDuck MCP | MotherDuck"
    url: "https://motherduck.com/videos/text-to-sql-data-modeling-llms-mcp-dives/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# Data cleansing

> Data cleansing (or data cleaning) is the process of detecting and correcting inaccurate, incomplete, inconsistent, or malformed data so it's reliable for analysis and downstream systems.

## 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`:

```sql
SELECT
  customer_id,
  COALESCE(country, 'UNKNOWN') AS country,
  COALESCE(discount_pct, 0) AS discount_pct
FROM raw_customers;
```

**Trimming and standardizing text**:

```sql
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:

```sql
-- 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`:

```sql
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**:

```sql
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.