Data Cleaning Techniques in SQL

The Art of Preparing Quality Data Using SQL

High-quality data is the cornerstone of meaningful insights. However, raw data often comes with imperfections like duplicates, missing values, and inconsistencies that hinder analysis. Data cleaning — the process of preparing data for analysis by rectifying these issues — is a crucial step in any data-driven workflow. SQL (Structured Query Language), being a versatile tool for managing and querying databases, offers powerful features to clean and transform data efficiently. This article explores common data cleaning techniques using SQL.

1. Removing Duplicate Records

Duplicate records can distort analysis results and inflate metrics. SQL provides straightforward methods to identify and remove duplicates.

Example:

-- Identify duplicate rows in a table
SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

-- Remove duplicates while keeping the first occurrence
DELETE FROM table_name
WHERE id NOT IN (
    SELECT MIN(id)
    FROM table_name
    GROUP BY column1, column2
);

In this example, duplicates are identified by grouping records with similar values in specified columns and removing all but the first occurrence.

2. Handling Missing Values

Missing values can disrupt analysis, especially in calculations and modeling. SQL allows you to identify and address these gaps.

Example:

-- Identify rows with missing values
SELECT *
FROM table_name
WHERE column_name IS NULL;

-- Replace missing values with a default value
UPDATE table_name
SET column_name = 'default_value'
WHERE column_name IS NULL;

-- Fill missing values using values from other columns
UPDATE table_name
SET column_name = other_column
WHERE column_name IS NULL;

This approach ensures no NULL values interfere with downstream processes.

3. Standardizing Data Formats

Inconsistent data formats, such as date formats or text case, can lead to inaccuracies. SQL functions help enforce consistency.

Example:

-- Standardize text to uppercase
UPDATE table_name
SET column_name = UPPER(column_name);

-- Standardize text to lowercase
UPDATE table_name
SET column_name = LOWER(column_name);

-- Convert date formats
SELECT TO_CHAR(date_column, 'YYYY-MM-DD') AS standardized_date
FROM table_name;

By standardizing formats, data becomes easier to query and interpret.

4. Validating Data

Data validation ensures that records conform to expected rules or ranges. SQL constraints and queries can enforce data validity.

Example:

-- Find invalid entries
SELECT *
FROM table_name
WHERE column_name NOT IN ('ValidValue1', 'ValidValue2');

-- Delete invalid entries
DELETE FROM table_name
WHERE column_name NOT IN ('ValidValue1', 'ValidValue2');

Adding constraints like CHECK during table creation can prevent invalid data from being inserted:

CREATE TABLE table_name (
    column_name VARCHAR(50) 
CHECK (column_name IN ('ValidValue1', 'ValidValue2'))
);

5. Parsing and Splitting Data

Complex fields, such as concatenated strings, can hinder analysis. SQL functions like SUBSTRING and SPLIT_PART help split or parse such data.

Example:

-- Extract the first name from a full name field
SELECT SUBSTRING(full_name, 1, POSITION(' ' IN full_name) - 1) 
AS first_name
FROM table_name;

-- Split a field using a delimiter
SELECT SPLIT_PART(concatenated_field, '-', 1) AS part1,
       SPLIT_PART(concatenated_field, '-', 2) AS part2
FROM table_name;

Breaking down complex fields improves the granularity of analysis.

6. Eliminating Outliers

Outliers can skew results, making them unreliable. SQL can help identify and remove these anomalies.

Example:

-- Identify outliers using a statistical threshold
SELECT *
FROM table_name
WHERE column_name > 
      (SELECT AVG(column_name) + 3 * STDDEV(column_name) FROM table_name)
   OR column_name < 
      (SELECT AVG(column_name) - 3 * STDDEV(column_name) FROM table_name);

-- Delete identified outliers
DELETE FROM table_name
WHERE column_name > 
      (SELECT AVG(column_name) + 3 * STDDEV(column_name) FROM table_name)
   OR column_name < 
      (SELECT AVG(column_name) - 3 * STDDEV(column_name) FROM table_name);

This approach ensures data remains within a reasonable range.

7. Merging and Deduplicating Data

When combining data from multiple sources, duplicates or mismatched records often arise. SQL provides tools for merging datasets and resolving conflicts.

Example:

-- Merge data from two tables
INSERT INTO table_name
SELECT *
FROM another_table
ON CONFLICT (unique_column) DO UPDATE
SET column_name = EXCLUDED.column_name;

This ensures seamless integration of multiple datasets.

Get Vishnu TR’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Effective data cleaning is foundational to any successful data project, and SQL is an indispensable tool for this task. By mastering these techniques, analysts can ensure their data is accurate, consistent, and ready for analysis. Clean data not only enhances the reliability of insights but also lays the groundwork for robust decision-making.