SQL-Engineering-Handbook

Module 11 — NULL Handling & Data Cleaning

11 · NULL Handling & Data Cleaning

Part of the SQL Engineering Handbook Difficulty: Beginner → Advanced · Estimated study time: 3–3.5 hours

Table of Contents

Module Files

# Lesson Concept Lines (.md) SQL Lab
01 Introduction to NULLs Three-valued logic, IS NULL vs = NULL 130 .sql
02 NULL Handling Functions COALESCE(), IFNULL(), NULLIF() 125 .sql
03 Data Standardization TRIM(), REPLACE(), UPPER()/LOWER(), INITCAP() 130 .sql
04 Data Cleaning Techniques Blank vs NULL vs whitespace, duplicate detection & removal 151 .sql
05 Business Data Quality Case Studies Multi-technique diagnosis across retail, finance, healthcare 115 .sql
06 Data Validation Checks Orphaned FKs, invalid dates, out-of-range values 134 .sql
07 Production Data Cleaning Project Capstone: multi-table SaaS cleaning pipeline 112 .sql

Module Overview

Every analytics pipeline eventually collides with the same problem: the data is not clean. Customers leave fields blank. Systems migrate and drop values. Integrations write empty strings instead of nulls. Sales reps skip optional form fields. By the time data reaches an analyst, it is never as tidy as the schema diagram suggests.

This module teaches you how to reason about missing, inconsistent, and invalid data the way a production analytics engineer does — not as an annoyance to work around, but as a first-class part of the job. You will learn how SQL represents “unknown,” how that representation propagates silently through calculations, and how to build queries and pipelines that catch data quality problems before they reach a dashboard or an executive report.

By the end of this module, NULL will stop being a mysterious edge case and start being a tool you control deliberately.

Why Data Quality Matters

A query can be syntactically perfect and still produce a wrong answer if the underlying data is dirty. A SUM() that silently ignores NULLs, a COUNT(*) that overstates completeness, a customer name stored three different ways ("john smith", "John Smith ", "JOHN SMITH") that fragments a single customer into three rows in a GROUP BY — these are not rare occurrences. They are the default state of real-world data.

Companies do not lose money because their SQL syntax is wrong. They lose money because a report built on unvalidated data told leadership something that wasn’t true. Data quality is not a QA afterthought — it is a prerequisite for trustworthy analytics, and it is one of the most common things analytics engineers are actually hired to fix.

Learning Objectives

By completing this module, you will be able to:

Module Roadmap

flowchart TD
    A[01 Introduction to NULLs<br/>three-valued logic] --> B[02 NULL Handling Functions<br/>COALESCE / IFNULL / NULLIF]
    B --> C[03 Data Standardization<br/>TRIM / UPPER / REPLACE]
    C --> D[04 Data Cleaning Techniques<br/>blank vs NULL vs whitespace, dedup]
    D --> E[05 Business Data Quality<br/>Case Studies]
    E --> F[06 Data Validation Checks<br/>proactive quality gates]
    F --> G[07 Production Data Cleaning Project<br/>capstone pipeline]

Folder Structure

11_NULL_HANDLING_AND_DATA_CLEANING/
├── README.md                                  You are here
├── 01_INTRODUCTION_TO_NULLS.md / .sql          Three-valued logic
├── 02_NULL_HANDLING_FUNCTIONS.md / .sql        COALESCE, IFNULL, NULLIF
├── 03_DATA_STANDARDIZATION.md / .sql           TRIM, REPLACE, UPPER/LOWER
├── 04_DATA_CLEANING_TECHNIQUES.md / .sql       Blank/NULL/whitespace, dedup
├── 05_BUSINESS_DATA_QUALITY_CASE_STUDIES.md/.sql  Multi-technique case studies
├── 06_DATA_VALIDATION_CHECKS.md / .sql         Proactive validation queries
├── 07_PRODUCTION_DATA_CLEANING_PROJECT.md/.sql Capstone pipeline
└── assets/                                     Banner + per-lesson SVG diagrams
    ├── banner.svg
    ├── 01_three_valued_logic.svg
    ├── 02_null_functions_flow.svg
    ├── 03_standardization_pipeline.svg
    ├── 04_states_and_duplicates.svg
    ├── 05_investigation_flow.svg
    ├── 06_validation_gate.svg
    └── 07_capstone_pipeline.svg

Repository Footprint

Every file in this module, with size and length — useful for estimating study time or auditing content depth at a glance.

File Type Lines Size
01_INTRODUCTION_TO_NULLS.md Lesson 130 12 KB
01_INTRODUCTION_TO_NULLS.sql SQL Lab 181 8 KB
02_NULL_HANDLING_FUNCTIONS.md Lesson 125 12 KB
02_NULL_HANDLING_FUNCTIONS.sql SQL Lab 199 8 KB
03_DATA_STANDARDIZATION.md Lesson 130 12 KB
03_DATA_STANDARDIZATION.sql SQL Lab 202 12 KB
04_DATA_CLEANING_TECHNIQUES.md Lesson 151 12 KB
04_DATA_CLEANING_TECHNIQUES.sql SQL Lab 225 12 KB
05_BUSINESS_DATA_QUALITY_CASE_STUDIES.md Lesson 115 8 KB
05_BUSINESS_DATA_QUALITY_CASE_STUDIES.sql SQL Lab 205 12 KB
06_DATA_VALIDATION_CHECKS.md Lesson 134 8 KB
06_DATA_VALIDATION_CHECKS.sql SQL Lab 199 8 KB
07_PRODUCTION_DATA_CLEANING_PROJECT.md Lesson 112 12 KB
07_PRODUCTION_DATA_CLEANING_PROJECT.sql SQL Lab 233 12 KB
Total 7 lessons + 7 labs 2,341 ~148 KB

Visual Guide

Each lesson has a companion diagram in assets/ built to the same visual language as the rest of the handbook — muted slate/blue/teal tones, no neon, designed to read cleanly in both light and dark GitHub themes.

01 — Three-Valued Logic Three-valued logic: TRUE, FALSE, UNKNOWN Every comparison touching NULL evaluates to UNKNOWN, not TRUE or FALSE — the root cause of nearly every NULL-related bug.

02 — COALESCE · IFNULL · NULLIF COALESCE, IFNULL, and NULLIF function flow Three functions, three jobs: substitute a fallback, substitute a MySQL-only fallback, or deliberately convert a value into NULL.

03 — Standardization Pipeline Standardization pipeline from raw input to GROUP-BY-safe value " john smith ", "John Smith", and "JOHN SMITH" are three different strings to SQL until a standardization pipeline makes them one.

04 — States & Duplicates NULL vs empty string vs whitespace, and duplicate row collapse IS NULL only catches one of three “no meaningful value” states — and unresolved duplicates inflate every count built on top of them.

05 — Investigation Flow Data quality investigation flow from stakeholder report to fix A vague “this number looks wrong” ticket resolves through profiling, diagnosis, and a defensible, explainable fix.

06 — Validation Gate Validation gate pattern separating passing and failing rows Validation queries don’t fix anything — they flag, count, and report, so bad rows are caught before a dashboard is.

07 — Capstone Pipeline Raw layer to trusted layer capstone cleaning pipeline Every earlier lesson becomes one stage in a single production-shaped pipeline: validate → standardize → deduplicate → resolve NULLs → recompute the metric.

SQL Functions Covered

Category Functions
Null checks IS NULL, IS NOT NULL
Null substitution COALESCE(), IFNULL(), NULLIF()
Text cleaning TRIM(), LTRIM(), RTRIM(), REPLACE()
Text casing UPPER(), LOWER(), INITCAP() (Postgres)
Conditional logic CASE WHEN
Aggregation COUNT(), SUM(), AVG() (NULL-aware behavior)

Business Applications

Domain Where this module applies
Retail / E-commerce Reconciling customer records across online and in-store systems where fields are optional
Finance Ensuring transaction amounts are never silently excluded from totals due to NULL
HR Validating employee records for missing managers, invalid hire dates, or duplicate employee IDs
Healthcare Flagging incomplete patient records before they reach compliance reporting
SaaS Standardizing customer emails and company names for accurate account-level rollups
Marketing Deduplicating leads captured from multiple campaign sources

Production Use Cases

Analytics Engineering Perspective

In a modern analytics stack, data cleaning is not a one-time cleanup script — it is a layer. Raw data lands in a staging layer untouched; standardization and validation happen in an intermediate layer; only clean, tested data reaches the layer business users query. The patterns in this module are the SQL-level building blocks of that intermediate layer, regardless of whether your stack uses dbt, stored procedures, or plain scheduled SQL.

Common Data Quality Problems

Best Practices

Common Mistakes

Performance Notes

Difficulty & Prerequisites

If any prerequisite feels shaky, revisit the earlier modules before continuing — NULL handling assumes fluency with the fundamentals, since it touches nearly every clause in SQL.

Interview Preparation

Expect questions like:

This module is designed so that after completing it, these questions become straightforward rather than something to memorize answers for.

Career Relevance

Data quality work is unglamorous and extremely in-demand. Analytics Engineer and Data Analyst job postings routinely list “data validation,” “data cleaning,” and “ensuring data quality” as core responsibilities — not nice-to-haves. Demonstrating fluency here, especially in a portfolio project, signals production readiness in a way that a polished dashboard alone does not.

01_Fundamentals · 02_Aggregations · 03_Joins · 04_Subqueries · 05_CASE_WHEN · 06_CTEs · 07_Window_Functions · 08_WINDOW_BUSINESS_CASES · 09_Date_Functions · 10_STRING_FUNCTIONS · 12_ADVANCED_AGGREGATIONS · 13_SET_OPERATORS · 14_VIEWS · 15_INDEXES · 16_QUERY_OPTIMIZATION · 17_SQL_INTERVIEW_QUESTIONS · 18_SQL_BUSINESS_CASE_STUDIES · 19_SQL_PROJECTS · 20_SQL_CHEATSHEET

Contributor Guide

Contributions welcome — this module intentionally keeps every lesson to the same structure (Introduction → Concept Overview → Why This Exists → Business Context → Real Company Examples → Business Problems Solved → Visual Explanation → SQL reference → Common Mistakes → Interview Questions) so new lessons stay consistent.

To add a new lesson:

  1. Follow the existing NN_TOPIC_NAME.md / .sql naming pattern
  2. Reuse the schema and seed data already established in this module’s .sql files rather than introducing a new schema, unless the lesson genuinely needs new tables
  3. Add a matching SVG to assets/ in the same muted slate/blue/teal palette as the rest of the module — no neon, no oversaturated fills — and link it from the Visual Guide section
  4. Update the Module Files and Repository Footprint tables with the new file’s line count and size
  5. Prefer a real, demonstrable production bug over an invented one when illustrating a common mistake

Key Takeaway

NULL handling and data cleaning sit at the intersection of SQL syntax and engineering judgment. The functions themselves — COALESCE, IFNULL, NULLIF, TRIM, REPLACE — are simple. The skill is knowing which one applies to which business situation, and building the habit of validating data before trusting it. In production, dirty data doesn’t throw an error — it just quietly produces the wrong business decision.

Further Reading

Previous Module: 10 — String Functions Next Module: 12 — Advanced Aggregations

This site is open source. Improve this page.