SQL Data Cleaning Methods

Data cleaning is an essential step in the process of solving a Data Science problem to ensure the data’s quality and reliability. If you are learning Data Science and want to understand how to clean your data using SQL, this article is for you. In this article, I’ll take you through a detailed practical guide on SQL data cleaning methods you should know for Data Science.

SQL Data Cleaning Methods

Below are some essential SQL data cleaning methods you should know for Data Science:

1. Identifying Missing Data
2. Removing Duplicate Records
3. Handling Missing Data
4. Standardizing Data
5. Correcting Data Entry Errors

Let’s understand all these SQL data cleaning methods in detail one by one.

Identifying Missing Data

Before cleaning, you must identify missing or NULL values. You can use the IS NULL condition to find missing data. For example, look at the table given below:

Here’s how to identify missing data from the above table:

SELECT *
FROM Sales
WHERE ProductName IS NULL OR SaleAmount IS NULL OR SaleDate IS NULL;

Removing Duplicate Records

Duplicate records can skew analysis. Use DISTINCT or ROW_NUMBER() with PARTITION BY to identify and remove duplicates. For example, look at the table given below:

Here’s how to identify duplicate records from the table:

SELECT OrderID, CustomerID, OrderDate,
       COUNT(*) OVER (PARTITION BY CustomerID, OrderDate) AS DuplicateCount
FROM Orders;

And, here’s how to remove the duplicate records:

WITH CTE AS (
    SELECT OrderID, CustomerID, OrderDate,
           ROW_NUMBER() OVER (PARTITION BY CustomerID, OrderDate ORDER BY OrderID) AS RowNum
    FROM Orders
)
DELETE FROM Orders
WHERE OrderID IN (SELECT OrderID FROM CTE WHERE RowNum > 1);

Handling Missing Data

There are several strategies for handling missing data, including deletion, imputation, or replacement with default values. Look at the table given below, it has missing values in all the rows except the first row:

Here’s how to delete rows with missing data:

DELETE FROM Sales
WHERE ProductName IS NULL OR SaleAmount IS NULL OR SaleDate IS NULL;

And if you want to replace missing values with a default value, here’s an example:

UPDATE Sales
SET ProductName = 'Unknown'
WHERE ProductName IS NULL;

UPDATE Sales
SET SaleAmount = 0
WHERE SaleAmount IS NULL;

UPDATE Sales
SET SaleDate = '2024-01-01'
WHERE SaleDate IS NULL;

Standardizing Data

Standardizing data involves converting data to a consistent format. For example, converting all text to lowercase or uppercase. Look at the example table below, the format of the country column is inconsistent:

Here’s how to correct the inconsistencies in our data:

UPDATE Customers
SET Country = UPPER(Country);

Correcting Data Entry Errors

Data entry errors, like misspelt words or incorrect values, can be corrected using SQL. For example, look at the table given below, the spelling of Phone is incorrect in the table:

Here’s how you can correct it:

UPDATE Products
SET ProductName = 'Phone'
WHERE ProductName = 'Phonne';

Summary

Below are some essential SQL data cleaning methods you should know for Data Science:

• Identifying Missing Data: Use IS NULL to find missing data.
• Removing Duplicate Records: Use DISTINCT or ROW_NUMBER() to identify and remove duplicates.
• Handling Missing Data: Delete, impute, or replace missing data with default values.
• Standardizing Data: Convert data to a consistent format.
• Correcting Data Entry Errors: Use UPDATE to correct misspelled words or incorrect values.