Data Cleaning using SQL

Data cleaning is very crucial step in data analysis process, which involves identifying errors, inconsistencies, inaccuracies in your dataset and correcting them according to our need. It is a first step of knowing your data. While data cleaning is often done using python, it’s necessary to know how to perform the same using SQL. In this article we will explore how to clean your data using SQL queries.

Before diving into SQL commands, lets see common quality issues that we face in our dataset:

1. Duplicate records: Multiple identical records which can effect accuracy.
2. Missing Data: Gaps in the dataset where values are absent, leading to incomplete information.
3. Outliers: Unusually high or low values that don’t match the rest of the data.
4. Incorrect Data: Data that contains errors or inaccuracies, leading to false conclusions.

Step-by-Step Data Cleansing using SQL

1. Removing Duplicates:

Imagine you have an employees table with the following columns: employee_id, first_name, last_name, department, salary.

Suppose the table contains the following data:

In this table, Arjun Patel and Priya Sharma appear twice, indicating potential duplicate records.

Identifying Duplicates: To identify duplicates, you can group the records based on the employee’s first name, last name, department, and salary:

SELECT first_name, last_name, department, salary, 
COUNT(*) as duplicate_count
FROM employees
GROUP BY first_name, last_name, department, salary
HAVING COUNT(*) > 1;

Removing Duplicates: To remove the duplicate records, use the ROW_NUMBER function, which assigns a unique row number to each record within the group, and then delete the duplicates:

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY first_name, 
           last_name, department, salary ORDER BY employee_id) AS rn
    FROM employees
)
DELETE FROM CTE WHERE rn > 1;

This query keeps the first occurrence of each duplicate (based on the employee_id) and deletes the rest. After running this query, the table will only contain unique employee records, ensuring accurate employee data:

2. Handling Missing Data:

Handling missing data effectively is crucial for maintaining data quality. Depending on the situation, you can replace missing values with zeros, the mean or median, or even delete the rows entirely. Choosing the right method depends on the context and how you intend to use the data in your analysis.

Imagine you have a transactions table, and some rows are missing values in the total_amount column:

• Replacing with a Default Value (e.g., 0): You can replace the missing values with a default value such as 0, which may be appropriate if a missing value represents a situation where no transaction occurred.

UPDATE transactions
SET total_amount = 0
WHERE total_amount IS NULL;

• Replacing with the Mean Value: Another approach is to replace the missing values with the mean of the existing data, providing a more balanced estimate.

SELECT AVG(total_amount) AS mean_value
FROM transactions
WHERE total_amount IS NOT NULL;  # Finding Mean Val

UPDATE transactions
SET total_amount = 4425
WHERE total_amount IS NULL;

• Replacing with the Median Value: Using the median can be more robust, especially when the data has outliers. To replace with the median, we need to find the median value. But let’s assume you find that the median is 4500

UPDATE transactions
SET total_amount = 4500
WHERE total_amount IS NULL;

• Forward Fill or Backward Fill: Another method is to fill the missing data by carrying forward or backward the previous or next available value. This is often used in time series data.

UPDATE transactions t1
SET total_amount = (
    SELECT total_amount 
    FROM transactions t2 
    WHERE t2.transaction_id = t1.transaction_id - 1
)
WHERE total_amount IS NULL;  # Backward Fill

• Deleting Rows with Missing Values: In some cases, it might be appropriate to delete rows with missing values, especially if the missing data is sparse and might not significantly affect the overall analysis.

DELETE FROM transactions
WHERE total_amount IS NULL;

3. Outliers:

Get Prajwala B R’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Identifying Outliers Using Standard Deviation: Outliers are values that deviate significantly from the mean. A common method to identify outliers is by using standard deviation. Let’s consider a simple employee table:

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) + 2 * STDDEV(salary) FROM employees)
   OR salary < (SELECT AVG(salary) - 2 * STDDEV(salary) FROM employees);

By adding ‘2 * STDDEV(salary)’ to the average salary AVG(salary), the query is setting a threshold that is two standard deviations above the average salary. Statistically, approximately 95% of the data points in a normal distribution lie within two standard deviations of the mean. Therefore, salaries that are greater than this threshold can be considered outliers or unusually high compared to the rest of the data.

Handling Outliers:

• Deleting Outliers: You can remove outliers by deleting the identified records from the dataset. Note: Removing outliers can be useful in cleaning data for further analysis but may lead to loss of important data if not done carefully.

DELETE FROM employees
WHERE salary > (SELECT AVG(salary) + 2 * STDDEV(salary) FROM employees)
   OR salary < (SELECT AVG(salary) - 2 * STDDEV(salary) FROM employees);

• Cap Outliers: Instead of removing outliers, you can cap them by setting a maximum or minimum threshold:

# Capping High Outliers

UPDATE employees
SET salary = (SELECT AVG(salary) + 2 * STDDEV(salary) FROM employees)
WHERE salary > (SELECT AVG(salary) + 2 * STDDEV(salary) FROM employees); 

# Capping Low Outliers

UPDATE employees
SET salary = (SELECT AVG(salary) - 2 * STDDEV(salary) FROM employees)
WHERE salary < (SELECT AVG(salary) - 2 * STDDEV(salary) FROM employees);

• Replace Outliers: Outliers can be replaced with other values, such as the mean, median, or a fixed value:

# Replacing Outliers with Mean (Average)

UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees)
WHERE salary > (SELECT AVG(salary) + 2 * STDDEV(salary) FROM employees)
   OR salary < (SELECT AVG(salary) - 2 * STDDEV(salary) FROM employees);

4. Correcting Data Entry Errors

Data entry errors, such as misspellings or inconsistent values, can lead to inaccuracies in your analysis. These errors can occur due to manual entry, faulty data migration, or inconsistent data sources. SQL allows you to correct these errors efficiently, ensuring that your data remains clean and reliable.

• Standardizing Inconsistent Data: Inconsistent data entries can cause problems, especially when you’re aggregating or filtering data. For example, variations in the way a state is recorded, such as “CA”, “C.A.”, or “California”, can lead to fragmented results in your reports.

Suppose your ‘addresses’ table has inconsistent entries in the ‘state’ column. To standardize all entries to "California":

UPDATE addresses
SET state = 'California'
WHERE state IN ('CA', 'C.A.', 'California');

• Correcting Misspelled Values: Misspellings in data entries can skew results and mislead analyses. It’s important to correct these errors to maintain data integrity. To correct misspelled customer names in the ‘customers’ table:

UPDATE customers
SET last_name = 'Smith'
WHERE last_name = 'Smit';

• Handling Case Sensitivity Issues: Sometimes, data entries may have inconsistent use of upper and lower case letters, which can affect case-sensitive queries. SQL can help normalize this. To correct case inconsistencies in the ‘product_name’ column:

UPDATE products
SET product_name = INITCAP(product_name);

• Using Regular Expressions for Complex Corrections: For more complex corrections, regular expressions (RegEx) can be very effective. They allow you to find and replace patterns in your data. To correct phone numbers with inconsistent formatting in the ‘contacts’ table:

UPDATE contacts
SET phone = REGEXP_REPLACE(phone, '[^0-9]', '')
WHERE phone LIKE '%-%';

• Correcting Logical Errors: Logical errors, such as incorrect dates or values that don’t make sense within the context of your data, can also be corrected using SQL. To correct future birth dates in the ‘employees’ table:

UPDATE employees
SET birth_date = '2000-01-01'
WHERE birth_date > CURRENT_DATE;

In a world where data-driven decisions are crucial, maintaining clean data helps you avoid misleading results and ensures that your analyses are based on the most accurate information available.

However, these are just a few of the many methods available for ensuring your data is clean and ready for analysis.

Thank you for reading.

Keep Learning, Keep Growing!