Image by Author | Canva

Introduction

Data cleaning is one of the most important steps in working with datasets. No matter how advanced your analysis or machine learning models are, their accuracy depends on the quality of the underlying data. Harvard Business Review reports an IBM estimate that bad data costs the U.S. economy about $3.1 trillion per year, which shows the scale of the problem.

Gartner also finds that poor data quality costs organizations at least $12.9 million per year on average, which is a practical way to think about impact at the company level.

Structured Query Language (SQL) has remained a fundamental tool for managing and preparing large datasets. Unlike spreadsheets or one-off scripts, SQL allows you to perform repeatable, scalable, and auditable data-cleaning operations directly inside the database. This makes it a good choice for analysts and data scientists who need trustworthy data pipelines.

In this article, we’ll walk through the essential steps of cleaning data with SQL, from handling missing values and duplicates to standardizing formats and detecting outliers. Each section includes practical examples and explanations you can adapt to your own projects.

Image by Author

Understanding Dirty Data

Before you can clean data with SQL, you need to recognize what “dirty data” looks like. Dirty data refers to inaccuracies, inconsistencies, or gaps in a dataset that reduce its quality and reliability. Analysts spend 60–80% of their time cleaning and preparing data before meaningful analysis can begin. Knowing the specific problems to look for is the first step to fixing them.

Missing Values

Missing values appear as NULL, empty strings, or incomplete records. If not handled, they can cause aggregations like AVG() or SUM() to return misleading results. For example, customer profiles without age or income details make segmentation unreliable.

In SQL:

• Detect with IS NULL or =”
• Decide whether to replace with defaults, impute based on other data, or remove

Duplicate Records

Duplicates happen when the same entity is recorded more than once, often due to faulty imports, user errors, or system glitches. They inflate counts and distort KPIs like customer churn or product sales.

In SQL:

• Use GROUP BY, COUNT(), or ROW_NUMBER() to identify duplicates
• Retain only the most relevant record (e.g., the earliest or most recent)

Inconsistent Formatting

Formatting issues are subtle but damaging. Common cases include: Dates stored in multiple formats (2025-09-09, 09/09/25, September 9th, 2025), text with mixed casing (LAGOS, lagos, Lagos), currency symbols stored with numbers, making columns unreadable as numeric data.

In SQL:

• Use functions like UPPER(), TRIM(), or TO_DATE() to enforce uniformity

Outliers and Anomalies

Outliers are extreme values that can heavily skew averages and forecasts. For instance, a transaction logged as “$1,000,000” instead of “$1000” will distort revenue insights. Anomalies might also indicate fraud, errors, or genuine but rare events.

In SQL:

• Compare values against statistical measures (AVG(), STDDEV())
• Flag outliers for further review instead of deleting blindly

Irrelevant Data

Not all data collected is useful. Some columns or rows do not contribute to the analysis and only add noise. For example, a “temporary_notes” field may be unnecessary for reporting but still consumes storage.

In SQL:

• Drop unused columns with ALTER TABLE … DROP COLUMN
• Filter rows that do not align with the scope of analysis

Mixed Data Types

When columns contain inconsistent data types, queries fail or return unreliable results. Examples include: numeric columns storing text (“1000USD” instead of 1000), Boolean fields captured as “yes/no” instead of TRUE/FALSE.

In SQL:

• Convert with CAST() or CONVERT()
• Apply constraints (CHECK, NOT NULL) to enforce data integrity

Preparing Your SQL Environment

A well-prepared environment makes data cleaning reproducible and safe. Instead of editing live data directly, it’s best to organize your workflow in stages.
Popular relational databases like PostgreSQL, MySQL, and SQL Server all support the SQL features required for cleaning. Your choice depends on your existing stack, performance needs, and licensing requirements. PostgreSQL is widely used for analytics because of its strong support for window functions and JSON data.

1. Importing Raw Datasets into Staging Tables

Raw data should be loaded into a staging area before cleaning. This keeps the original source intact and allows you to test transformations safely. For example, you might create a staging_customers table to hold imported CSV data.

-- Create staging table for raw customer data
CREATE TABLE staging_customers AS
SELECT * FROM raw_customers;

This query copies all records from raw_customers into staging_customers. The staging table can now be modified without touching the raw data.

2. Creating Backup Tables Before Transformations

Always create a backup before running updates or deletes. This ensures you can roll back if something goes wrong.

-- Backup staging table before making transformations
CREATE TABLE staging_customers_backup AS
SELECT * FROM staging_customers;

Here we duplicate the staging_customers table into a backup. If any cleaning operation fails, you can restore from this backup table instead of re-importing raw data.

Handling Missing Data in SQL

Missing values are among the most common issues in datasets. They can appear as NULL, blank, or incomplete fields. SQL provides several strategies to handle them, depending on the context.

1. Identifying Missing Values

Before fixing, locate the missing values.

-- Find customers with missing phone numbers
SELECT id, name
FROM staging_customers
WHERE phone_number IS NULL;

This query lists all customers whose phone_number field is NULL, helping you see the extent of the issue.

2. Replacing Missing Values with Defaults

When appropriate, substitute missing values with defaults or placeholders.

-- Replace NULL phone numbers with 'Unknown'
UPDATE staging_customers
SET phone_number = COALESCE(phone_number, 'Unknown')
WHERE phone_number IS NULL;

The COALESCE() function replaces NULL with the string “Unknown.” Adding the WHERE clause avoids unnecessary writes.

3. Using Derived Values

Sometimes you can fill in missing data based on related information.

-- Replace NULL city with customer's state capital (example logic)
UPDATE staging_customers
SET city = 'Lagos'
WHERE city IS NULL AND state = 'Lagos State';

This updates missing city values with a derived entry (here, Lagos for customers from Lagos State). This approach requires business knowledge and careful validation.

4. Dropping Rows with Excessive Missing Data

If a record has too many gaps to be useful, it’s better to remove it.

-- Delete rows where both phone and email are missing
DELETE FROM staging_customers
WHERE phone_number IS NULL AND email IS NULL;

This ensures only usable customer records remain in the dataset, preventing broken joins and incomplete insights.

Removing Duplicates

Duplicate records are a common problem in transactional systems and imported datasets. They inflate counts, distort aggregates, and can cause incorrect business insights. The goal is to detect duplicates and retain only one valid record.

1. Detecting Duplicates with COUNT()

You can quickly identify duplicate entries using GROUP BY and COUNT().

-- Find emails that appear more than once
SELECT email, COUNT(*) AS occurrences
FROM staging_customers
GROUP BY email
HAVING COUNT(*) > 1;

This query groups customers by email and counts occurrences. Any email with more than one record indicates a duplicate.

2. Using ROW_NUMBER() for Precision

The ROW_NUMBER() function helps label each duplicate row, allowing you to keep the first and flag the rest.

-- Identify duplicate customers by email
SELECT id, email,
       ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_num
FROM staging_customers;

This assigns a row number to each record grouped by email. The first occurrence gets row_num = 1, while duplicates get higher values.

3. Deleting Duplicates While Keeping One Record

After identifying duplicates, you can delete extra records while retaining one valid entry.

-- Remove duplicates, keeping the first entry for each email
DELETE FROM staging_customers
WHERE id NOT IN (
  SELECT MIN(id)
  FROM staging_customers
  GROUP BY email
);

Here, MIN(id) ensures only the lowest id (usually the earliest entry) remains per email. All other duplicate rows are removed.

Standardizing Data Formats

Even when data is complete and unique, inconsistent formatting can cause major problems. Queries may fail to match values correctly if case, whitespace, or date formats differ. Standardizing ensures consistency across the dataset.

1. Converting Text to a Consistent Case

Different casing leads to mismatched results in joins or groupings.

-- Convert all city names to lowercase
UPDATE staging_customers
SET city = LOWER(city);

This enforces lowercase city names, so “LAGOS”, “lagos”, and “Lagos” all become “lagos.”

2. Trimming Extra Whitespace

Extra spaces often go unnoticed but cause mismatches.

-- Remove leading and trailing spaces from names
UPDATE staging_customers
SET name = TRIM(name);

This removes unnecessary whitespace, ensuring “ John Doe ” is stored as “John Doe.”

3. Standardizing Date Formats

Inconsistent date formats make filtering and aggregation unreliable. You can enforce a single format using built-in functions.

PostgreSQL Example:

-- Convert inconsistent date strings into a uniform format
UPDATE staging_customers
SET signup_date = TO_DATE(signup_date, 'MM/DD/YYYY');

This converts date strings like 09/09/2025 into a standard date type for consistency.

MySQL Example:

-- Reformat date values
UPDATE staging_customers
SET signup_date = STR_TO_DATE(signup_date, '%m/%d/%Y');

This ensures all dates are parsed into the correct format recognized by MySQL.

Detecting and Handling Outliers

Outliers can distort aggregates and forecasts. Start by detecting them with basic statistics, then decide whether to exclude, cap, or review them in a separate table.

1. Spot Anomalies with Averages and Standard Deviations

We first compute summary statistics, then flag values far from the mean. See the aggregate docs for AVG and STDDEV.

-- Compute stats for the 'amount' column
SELECT
  AVG(amount)      AS mean_amount,
  STDDEV(amount)   AS sd_amount
FROM staging_orders;

This returns the mean and standard deviation of amount. Use these to define a threshold.

-- Flag potential outliers using a z-score rule (|z| >= 3)
WITH stats AS (
  SELECT AVG(amount) AS mean_amount, STDDEV(amount) AS sd_amount
  FROM staging_orders
)
SELECT o.id, o.customer_id, o.amount,
       (o.amount - s.mean_amount) / NULLIF(s.sd_amount, 0) AS zscore
FROM staging_orders o CROSS JOIN stats s
WHERE ABS((o.amount - s.mean_amount) / NULLIF(s.sd_amount, 0)) >= 3;

This query labels rows whose amount is at least three standard deviations from the mean. NULLIF prevents division by zero if the variance is zero.

2. Using the IQR Method (Robust to Skew)

percentile_cont helps you compute quartiles in PostgreSQL.

-- IQR-based outlier detection: values < Q1 - 1.5*IQR or > Q3 + 1.5*IQR
WITH q AS (
  SELECT
    percentile_cont(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
    percentile_cont(0.75) WITHIN GROUP (ORDER BY amount) AS q3
  FROM staging_orders
)
SELECT o.*
FROM staging_orders o, q
WHERE o.amount < q.q1 - 1.5 * (q.q3 - q.q1)
   OR o.amount > q.q3 + 1.5 * (q.q3 - q.q1);

This finds outliers using the interquartile range, which is less sensitive to extreme values.

3. Excluding or Reviewing Outliers Safely

Often, you should review outliers rather than delete them. Create a holding table for investigation.

-- Create a review table to store flagged outliers
CREATE TABLE IF NOT EXISTS orders_outliers_review AS
SELECT * FROM staging_orders WHERE false;  -- empty table with same structure

-- Insert z-score outliers for review
WITH stats AS (
  SELECT AVG(amount) AS mean_amount, STDDEV(amount) AS sd_amount
  FROM staging_orders
)
INSERT INTO orders_outliers_review
SELECT o.*
FROM staging_orders o CROSS JOIN stats s
WHERE ABS((o.amount - s.mean_amount) / NULLIF(s.sd_amount, 0)) >= 3;

The first statement creates an empty review table that matches the schema. The second inserts flagged rows for human checks.

-- Exclude flagged outliers when building a cleaned view
CREATE OR REPLACE VIEW orders_cleaned AS
SELECT o.*
FROM staging_orders o
LEFT JOIN orders_outliers_review r ON r.id = o.id
WHERE r.id IS NULL;

This view hides outliers from downstream queries without destroying the original data.

Ensuring Consistent Data Types

Wrong data types cause failed joins, broken indexes, and invalid math. Fix types, then enforce constraints so the problem does not return.

1. Cast Columns to Proper Types

See casts in PostgreSQL or MySQL CAST/CONVERT.

-- Safe numeric cast: strip non-digits and convert to numeric
-- Example assumes amounts may contain commas or spaces
UPDATE staging_orders
SET amount_clean = NULLIF(REGEXP_REPLACE(amount_raw, '[^0-9\.]', '', 'g'), '')::numeric;

This removes nonnumeric characters, converts the result to a numeric type, and stores it in amount_clean. NULLIF keeps empty strings as NULL.

-- Convert boolean-like strings to real booleans
UPDATE staging_customers
SET is_active = CASE
  WHEN LOWER(TRIM(is_active_raw)) IN ('true','t','1','yes','y')  THEN TRUE
  WHEN LOWER(TRIM(is_active_raw)) IN ('false','f','0','no','n')  THEN FALSE
  ELSE NULL
END;

This normalizes a variety of truthy and falsy strings into a proper Boolean column.

2. Enforce Integrity with Constraints

Constraints stop bad data at the door. See CHECK and NOT NULL and MySQL constraints.

-- Add NOT NULL after cleaning
ALTER TABLE staging_customers
  ALTER COLUMN email SET NOT NULL;

This prevents future rows with missing emails.

-- Add a CHECK constraint to keep amounts nonnegative
ALTER TABLE staging_orders
  ADD CONSTRAINT chk_amount_nonnegative CHECK (amount_clean >= 0);

This ensures amount_clean cannot be negative.

-- Validate uniqueness to stop duplicates by email
ALTER TABLE staging_customers
  ADD CONSTRAINT uq_customers_email UNIQUE (email);

This guarantees only one row per email going forward.
Pro Tip: In PostgreSQL, you can add constraints with NOT VALID then VALIDATE CONSTRAINT to avoid heavy table locks during busy hours.

Documenting and Automating Cleaning Processes

Cleaning should be repeatable and visible. To do so, put transformations in version-controlled SQL, expose a cleaned view, and schedule the job.

1. Store Procedures or Scripts in Version Control

Keep your Data Definition Language (DDL) and Data Manipulation Language (DML) in a repo. Tools like dbt, Apache Airflow, or plain SQL files plus CI help you track changes and run them reliably.

-- Example: wrap a cleaning step in a transactional function (PostgreSQL)
CREATE OR REPLACE FUNCTION run_customer_standardization()
RETURNS void AS $$
BEGIN
  -- Trim and lowercase emails for consistency
  UPDATE staging_customers
  SET email = LOWER(TRIM(email));

  -- Remove obvious email junk rows
  DELETE FROM staging_customers
  WHERE email NOT LIKE '%@%';
END;
$$ LANGUAGE plpgsql;

This function encapsulates a small cleaning routine so you can call it in jobs or tests.

2. Provide a Stable Interface with Views

Views expose a consistent shape to analysts while you keep iterating behind the scenes. See PostgreSQL views and MySQL views.

-- A canonical "clean" customer view
CREATE OR REPLACE VIEW customers_clean AS
SELECT
  id,
  LOWER(TRIM(email))          AS email,
  INITCAP(TRIM(name))         AS name,
  COALESCE(phone_number,'Unknown') AS phone_number,
  signup_date::date           AS signup_date
FROM staging_customers
WHERE email LIKE '%@%';

This view standardizes casing, trims whitespace, handles missing phones, casts dates, and filters bad emails. Downstream dashboards can query customers_clean directly.

3. Schedule Jobs in the Database or via an Orchestrator

Pick one scheduling approach and document it.

• PostgreSQL: use the pg_cron extension to schedule SQL inside the database
• MySQL: enable the Event Scheduler
• SQL Server: use SQL Server Agent
• External orchestration: Airflow or dbt jobs trigger your SQL on a cadence

Example:

-- PostgreSQL pg_cron: run cleaning every night at 02:00
SELECT cron.schedule('nightly_customer_clean',
                     '0 2 * * *',
                     $$SELECT run_customer_standardization();$$);

This schedules the run_customer_standardization() function to run daily at 02:00.

-- MySQL Event Scheduler: hourly cleanup example
CREATE EVENT IF NOT EXISTS hourly_customer_cleanup
ON SCHEDULE EVERY 1 HOUR
DO
  UPDATE staging_customers
  SET email = LOWER(TRIM(email));

This enables a simple recurring cleanup using MySQL’s event system.

Best Practices and Tips

Once you know the key cleaning techniques, it’s worth following a few general rules to keep your process safe and repeatable. These best practices help ensure that your cleaned datasets remain reliable over time.

• Always stage before overwriting raw data: Keep a staging table between the raw source and the cleaned output. This prevents accidental data loss and gives you a safe place to test transformations.
• Log transformations for auditability: Store notes in comments, commit SQL scripts to version control, and keep a record of what changed. This helps teams reproduce results and explain decisions later.
• Validate with small subsets before applying globally: Run cleaning queries on a slice of data before updating millions of rows. Use LIMIT or temporary tables to test first.
• Use constraints and defaults to prevent future errors: Once a column is clean, add NOT NULL, CHECK, or UNIQUE constraints so that new data cannot reintroduce the same problem.
• Combine SQL with Python or R for advanced cases: SQL handles structural cleaning very well, but complex statistical methods or natural language processing are better handled in Python (pandas) or R (dplyr). Many teams export from SQL, clean further in these tools, then load the results back.

Conclusion

Dirty datasets lead to wrong insights, wasted resources, and broken trust. By staging your data, handling missing values, removing duplicates, standardizing formats, catching outliers, and enforcing consistent types, you give your analysis a strong foundation.

SQL remains one of the most effective tools for this type of work. It scales to millions of rows, integrates directly with enterprise systems, and makes your cleaning steps transparent and auditable.

The takeaway is simple: clean data = better insights. Treat cleaning as a repeatable, documented pipeline, not a one-time fix. By doing so, you’ll spend less time debugging bad inputs and more time building models and dashboards that people can trust.