4.9: Cleaning Data with SQL

Cleaning Data with SQL

SQL (Structured Query Language) is the backbone of data manipulation in relational databases. It might not have fancy graphical interfaces, but it is extremely powerful for cleaning operations. Many data analysts and engineers rely on SQL scripts or queries as part of their Extract-Transform-Load (ETL) processes for data cleaning.

SQL for filtering and detection:

The first step in cleaning is often detection of issues, and SQL excels at querying data to find anomalies. We have already given many examples: using WHERE clauses to find NULLs or out-of-range values, using GROUP BY ... HAVING COUNT(*)>1 to find duplicates, joins to find referential breaks, etc. SQL allows you to slice and dice the data to isolate problematic records. For example:

-- Find products with negative prices (invalid business rule)
SELECT ProductID, Price FROM Products
WHERE Price < 0;

Or:

-- Find potential duplicate customers by same email
SELECT Email, COUNT(*) FROM Customers
GROUP BY Email
HAVING COUNT(*) > 1;

Such queries don’t fix data by themselves, but they produce result sets that highlight what needs fixing.

SQL for transformation:

Once issues are identified, SQL can also correct or transform data via UPDATE statements or creating new cleaned tables with INSERT SELECT statements. For instance, if we want to fix inconsistent text cases:

UPDATE Customers
SET State = UPPER(State);

This would standardize all state abbreviations to uppercase. Another example, replacing empty strings with NULL:

UPDATE Customers
SET Phone = NULL
WHERE Phone = '';

In some workflows, instead of updating the original data, you might create a cleaned version. For example:

CREATE TABLE Customers_Cleaned AS
SELECT
  CustomerID,
  UPPER(TRIM(State)) AS State,
  COALESCE(NULLIF(Phone, ''), 'Unknown') AS Phone,
  -- ... other fields with transformations ...
FROM Customers;

Here, we create a new table applying transformations: TRIM to remove whitespace, UPPER to normalize case, and using a combination of NULLIF and COALESCE to turn empty strings into "Unknown" (COALESCE returns the first non-null, so if Phone becomes NULL for empty string, it then gives "Unknown"). This approach is common in ETL: reading from a raw table and writing to a cleaned table.

Casting and converting in SQL: We already talked about CAST() for data types. SQL also provides functions like TO_DATE or PARSE functions in some dialects for complex conversion (e.g., parsing a string with a specific date format into a date). Ensuring all conversions succeed is part of cleaning. Often, you might use a staging table where all columns are text (just to load the raw data), and then a SQL step to convert those to their proper types, pushing any bad records into an “error” table for manual review. This can be done with case expressions:

INSERT INTO Errors_Table
SELECT *
FROM Staging
WHERE TRY_CAST(price AS DECIMAL(10,2)) IS NULL
  AND price IS NOT NULL;

(This captures rows where price is not null but couldn’t convert to decimal, meaning it contained non-numeric text.)

SQL for deduplication: Suppose we identify duplicate entries. How to remove them with SQL? One way is using window functions:

DELETE FROM Customers
WHERE CustomerID NOT IN (
    SELECT MIN(CustomerID)
    FROM Customers
    GROUP BY Email
);

This would keep the lowest ID for each email and delete others, under the assumption duplicates share email. Or using window functions:

WITH Ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerID) as rn
  FROM Customers
)
DELETE FROM Ranked WHERE rn > 1;

This marks duplicates (rn > 1) for deletion, leaving one record per email. Caution: You need to decide the criteria for which duplicate to keep (here we arbitrarily kept the lowest ID).

Validating with constraints in SQL: One neat trick during cleaning is to leverage the database’s constraint enforcement to check your work. For example, after cleaning, you might add a temporary UNIQUE constraint on a field that should be unique to see if any duplicates remain (the DB will throw an error if they do). Or use ALTER TABLE ... ADD CHECK to test a rule – if it fails, the DB will complain indicating some row violates it. If you use a transaction and roll it back, you can do this testing without permanently altering the schema, just to verify integrity.

Stored procedures and scripts:

In complex cleaning, you may wrap SQL steps in a stored procedure or script that does multiple passes: e.g., first fill default values, then fix formats, then remove duplicates, etc., logging how many rows were affected at each step. This becomes a repeatable cleaning pipeline that can be run whenever new raw data comes in. For example, a stored procedure might take all new daily records, apply a series of UPDATE/DELETE statements for cleaning, and then insert them into the main table.

Example of a cleaning workflow in SQL:

Imagine we have a table Sales_Staging with raw sales data loaded from CSVs. We want to clean it and append to Sales table. Steps might be: 1. Remove entirely blank rows (if any got in) – DELETE FROM Sales_Staging WHERE OrderID IS NULL AND CustomerID IS NULL ...;. 2. Fix data types: perhaps ALTER TABLE Sales_Staging ALTER COLUMN SaleAmount TYPE DECIMAL(10,2) if the staging was text – but more often, we’d do INSERT ... SELECT CAST(...) into the final. 3. Handle missing values: fill ShipDate with OrderDate if missing (assuming same-day shipping as a business rule) – UPDATE Sales_Staging SET ShipDate = OrderDate WHERE ShipDate IS NULL;. 4. Enforce constraints manually: ensure all CustomerIDs exist in Customers (remove or mark those that don’t, or fix IDs if possible). 5. Finally, insert into Sales (which might have constraints turned on).

Each of these can be done with plain SQL. It might be more code to write compared to using a specialized tool, but SQL’s advantage is that it is very transparent, auditable, and often efficient on large datasets since the work is pushed to the database engine.

In short, SQL remains one of the most important tools for data cleaning. It provides granular control and is the lingua franca for data in relational stores. Mastering a set of SQL queries for cleaning common issues is a valuable skill for any data analyst. It’s no surprise that you can explore and clean data through SQL or via UI tools – oftentimes, even the UI tools will generate SQL under the hood for the transformations you specify.