Data Cleaning – Guided Study Workspace

Introduction: The Dirty Customer Dataset

Consider this raw table extracted from a production CRM:

id	name	email	city	age	status
101	`" Rahul "`	rahul@email.com	Mumbai	25	Active
102	`"PRIYA"`	priya@email.com	mumbai	27	active
103	`"Amit"`	NULL	Delhi	NULL	Active
104	`" Neha"`	`" "` (Blank)	Delhi	24	ACTIVE
105	`"Ravi "`	ravi@email.com	MUMBAI	-1	Inactive

Would you trust this dataset for business revenue forecasting or customer segmentation? No. Whitespace, inconsistent capitalization, empty strings masquerading as emails, and impossible negative ages will corrupt any analysis.

What Is Data Cleaning? (The 5-Step Pipeline)

Core Definition

Data Cleaning is the disciplined process of detecting, standardizing, and appropriately handling inaccurate, incomplete, inconsistent, or invalid data before analysis.

The 5-Step Data Cleaning Pipeline

Step 1
Inspect Raw Data

──▶

Step 2
Detect Defects

──▶

Step 3
Standardize & Clean

──▶

Step 4
Validate Rules

──▶

Step 5
Analysis Ready

Common Data-Quality Problems

1. Surrounding Whitespace

" Rahul " creates false distinct values.

2. Mixed Letter Casing

Mumbai vs mumbai vs MUMBAI.

3. Blanks vs NULLs

Empty strings '' hiding missing values.

4. Impossible Values

Age -1 or 999 violating logic.

Stripping Whitespace with TRIM()

SELECT
name AS raw_name,
TRIM(name) AS cleaned_name
FROM customers;

Text Standardization (UPPER / LOWER / INITCAP)

Combining TRIM() with LOWER() or UPPER() unifies mixed casing:

SELECT
LOWER(TRIM(city)) AS standardized_city
FROM customers;

Handling NULLs & COALESCE()

Provide explicit fallback defaults for reporting presentation using COALESCE:

SELECT
COALESCE(email, 'Not Provided') AS display_email
FROM customers;

Empty Strings vs. NULL: The NULLIF() Technique

Empty strings ('') or whitespace strings (' ') fail IS NULL checks. Convert them to true SQL NULL using NULLIF(TRIM(col), ''):

SELECT
COALESCE(NULLIF(TRIM(email), ''), 'Not Provided') AS robust_email
FROM customers;

Standardizing Inconsistent Categories with CASE WHEN

SELECT
    CASE
        WHEN LOWER(TRIM(status)) = 'active' THEN 'Active'
        WHEN LOWER(TRIM(status)) = 'inactive' THEN 'Inactive'
        ELSE 'Unknown'
    END AS cleaned_status
FROM customers;

Invalid Business Values & Outliers

SELECT
    CASE
        WHEN age BETWEEN 0 AND 120 THEN age
        ELSE NULL
    END AS validated_age
FROM customers;

Data Type Cleaning & Explicit Casting

Numbers stored as text strings (e.g. '150.50') cannot be summed or averaged accurately until explicitly cast:

SELECT
CAST(amount_text AS DECIMAL(10, 2)) AS amount_numeric
FROM transactions;

Duplicate Record Detection (GROUP BY + HAVING)

SELECT
email,
COUNT(*) AS duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Before vs. After Comparison

❌ BEFORE (Dirty Raw Data):

• " Rahul " (Whitespace)
• mumbai (Messy casing)
• ' ' (Hidden blank string)
• -1 (Impossible age)
• ACTIVE (Inconsistent category)

✅ AFTER (Cleaned Analysis Data):

• Rahul (Stripped)
• Mumbai (Standardized)
• Not Provided (Properly handled)
• NULL (Excluded from stats)
• Active (Standardized category)

⚡ Live Interactive SQL Data Cleaning Practice Lab

Execute live deterministic cleaning SQL, toggle pipeline transformations, inspect data defects, and score your Data Quality

Inspect the dirty raw dataset below. Notice highlighted defects in red and amber:

ID	Raw Name	Raw City	Raw Email	Raw Age	Raw Status
101	" Rahul " (Spaces)	Mumbai	rahul@email.com	25	Active
102	PRIYA	mumbai (Case Mismatch)	priya@email.com	27	active (Inconsistent)
103	Amit	Delhi	NULL	NULL	Active
104	" Neha" (Spaces)	Delhi	" " (Blank string!)	24	ACTIVE (Inconsistent)
105	"Ravi " (Spaces)	MUMBAI (Case Mismatch)	ravi@email.com	-1 (Invalid!)	Inactive
106	Rahul	Delhi	rahul@email.com	29	inactive (Inconsistent)

What You Should Know Now: Checklist

✓TRIM(name): Strips invisible leading/trailing whitespace.
✓NULLIF(TRIM(email), ''): Converts blank whitespace strings into true SQL NULLs.
✓CASE WHEN: Normalizes categories and excludes out-of-bounds outliers.
✓GROUP BY ... HAVING COUNT(*) > 1: Exposes duplicate keys without blindly deleting data.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL Data Cleaning

Test your understanding of whitespace trimming, NULL vs blank strings, CASE standardization, outlier handling, and duplicate detection.

Question 1 of 8Current Score: 0 / 0

What is the primary role of TRIM() during SQL data cleaning?