Data Cleansing in SQL: Transforming Raw Data into Actionable Insights

Real-world data always comes in a messy form. As a data practitioner, be it an analyst, a scientist, or even a developer, it is important that you be able to derive useful information and insights from these messy data. So, the question that we must answer as data practitioners is, how can we squeeze out insights from a messy data set, and why should SQL be my preferred choice of tool?

In this article, we will explore all of the techniques in SQL that can be used to clean a data set and make sense of it. So, lets learn how to carry out data cleansing in SQL!

What Is Data Cleansing?

Data cleaning can be defined as the process of removing incorrect, incomplete, duplicate, corrupted, incorrectly structured, and erroneous data in a data set. This also includes analysis of the quality of data in a data source, making it suitable for data analysis. Most of the time, these issues can come from human error during data entry or combining data from different data formats and structures. 

Why Is Data Cleansing Important?

Most business decisions, such as improving the quality of service, creating new products, and allocating funds, are often backed by data. When this data is questionable, the insights generated from it can severely affect organizations decision-making. Cleaning your data will improve the quality of the information obtained from these data ensuring organizations and businesses make the best of decisions.

Consequences of poor data quality:

• Faulty decision-making: Poor-quality data can lead to inaccurate insights.
• Resource waste: Organizations often spend alot of time and resources to clean up poor data.
• Customer dissatisfaction: wrong customer data can lead to errors in communication and also affect the customer service levels rendered by the organization
• Legal and compliance risks: Poor-quality data may lead to poor data governance by the organizations and this can result in breaches of regulations.

Learn more about data quality from our blog on Understanding Data Quality!

Importance of data cleansing:

• Enhances data credibility in the decision-making process.
• Improves accuracy of the predictions and statistical and data analysis.
• Helps to avoid mistakes made in reports and other working dashboards.
• Maintains compliance with data regulations
• Ensures better customer and user experience.

Good quality data can help organizations and businesses stay competitive and effective. Learn about the risks of poor data quality from our blog to understand better!

Why Use SQL for Data Cleaning?

Structured Query Language (SQL) can be used to manage, manipulate, and retrieve data from relational databases. SQL is very efficient, flexible, and easy to use. Since most organization’s data are stored in relational databases, SQL emerges as the most natural and preferred data manipulation tool.

Benefits of using SQL:

• Ease of use: SQL can be used to retrieve and manage data directly from databases.
• Scalability: SQL can be used to process big datasets efficiently.
• Automation: With SQL, you can write reusable scripts for repetitive tasks.
• Integration: SQL can work with most relational databases.
• Efficiency: Performs operations faster than manual methods.
• Standardization: With SQL we can be sure of consistent cleaning processes.

SQL is essential for managing data quality at scale.

Cleaning Data With SQL

Let us consider a table called ‘customers’

We will clean this table to show the effectiveness of SQL in cleaning poor data.

ID	name	email	phone	country	purchase_date	total_spent
101	katie doe	K_Doe@sql.com	null	USA	12/05/2022	300
102	Jane boys	j_boys@sql.com	null	GB	null	230
103	Jonh Jons	j_jons@sql.com	123456	NG	02/09/2023	nill
104	Alice Wonderly	a_wonderly	234566	null	09/04/2023	232
105	Katie doe	invalidemail	+234567	Nill	23/10/2022	248

1. Remove Irrelevant Data

Remove the rows where country is NULL or invalid, or where purchase_date is missing.

DELETE FROM customers  

WHERE country IS NULL OR country = 'Nill' OR purchase_date IS NULL;  

2. Remove Duplicate Data

We will keep only the first occurrence of duplicate rows based on the name and email combination.

DELETE FROM customers  

WHERE id NOT IN (

    SELECT MIN(id)  

    FROM customers  

    GROUP BY name, email  

);  

3. Fix Structured Errors

Correct invalid email addresses by applying a pattern validation.

UPDATE customers  

SET email = NULL  

WHERE email NOT LIKE '%@%.%';  

4. Do Type Conversion

• Ensure total_spent is stored as a DECIMAL and convert non-numeric values.

ALTER TABLE customers  

ALTER COLUMN total_spent TYPE DECIMAL(10, 2);  

• Convert purchase_date to a DATE type for consistency.

ALTER TABLE customers  

ALTER COLUMN purchase_date TYPE DATE  

USING TO_DATE(purchase_date, 'DD/MM/YYYY');  

5. Handle Missing Data

• Fill in missing phone numbers with a placeholder like “000000”.

UPDATE customers  

SET phone = '000000'  

WHERE phone IS NULL;  

• Assign a default value for the missing country.

UPDATE customers  

SET country = 'Unknown'  

WHERE country IS NULL;  

6. Deal With Outliers

• Remove rows where total_spent is abnormally high or low (e.g., below $50 or above $10,000).

DELETE FROM customers  

WHERE total_spent < 50 OR total_spent > 10000;  

7. Standardize/Normalize Data

• Format all name values to title case for consistency.

UPDATE customers  

SET name = INITCAP(name);  

• Convert email addresses to lowercase.

UPDATE customers  

SET email = LOWER(email);  

8. Validate Data

• Check for rows where email or phone values are invalid.

SELECT *  

FROM customers  

WHERE email NOT LIKE '%@%.%' OR phone NOT LIKE '+%';  

Final Cleaned Table

The steps and techniques above will adequately clean the ‘customer’s table and the table will be free of duplicates, structured errors and missing values 

Real-Life Examples

1. Customer Analytics 

Consider a scenario where you are working for a retail organization that uses customer information from multiple sources. The customer data contains a lot of duplicate records and null values that need to be addressed before carrying out the customer analytics.

To solve this problem, you will use SQL to merge duplicate entries and handle null values by assigning a value to them or replacing them entirely. 

Afterward, you will get a clean customer data set that is ready for analysis.

2. Healthcare Data Cleaning

Consider a second scenario where you are a data analyst in a healthcare system. You were presented with patient records that are filled with the wrong date of birth (DOB) format and outliers in the age column.

To address this issue, you will use SQL to correct the DOB. format and fill in the missing birthdays with default values. Also, remove the outliers.

By so doing, you will have a reliable and improved patient record to work with.

Want to know how important is data quality in healthcare? Read our blog to understand better.

Best Practices for Data Cleansing in SQL

To get the best out of your SQL transformation, we advise you to follow these best practices. 

1. Understanding of data: It is always advisable to understand the source of the data you are working with, including its structure, formats, and relationships.

2. Document the transformation process: Keep track of the steps taken and all the changes made during the data cleaning and transformation for future reference.

3. Test queries before execution: Always test SQL queries in a non-production environment to ensure they work before deploying them.

4. Backup your data: Always back up your data before carrying out any significant cleaning and transformation operation to avoid permanent data losses.

5. Use transactional processing and Control: Apply BEGIN and ROLLBACK to test changes safely. This ensures atomicity and consistency and also allows you to roll back changes if there are any errors. 

6. Query optimizations: Write efficient queries and consider indexing columns to enhance query performance.‍

7. Maintain quality data: Always practice good data governance and establish data quality measures to ensure data accuracy at all times.

Implementing these practices will enable data practitioners to clean data and provide high-quality data for analytics.

Explore data quality and its dimensions, importance, and metrics from our blogs so you can make sure your data is ready and perfect to be used!

Conclusion

With techniques like removing duplicates, correcting data formats, and handling outliers,  SQL is an effective way of cleaning and transforming structured data. To conclude, cleaning your data with SQL and following the best practices ensures that your analysts and engineers have high-quality data to work with

FAQs