Raw transactional data is rarely ready for analysis. In practice, datasets arrive with placeholder values masquerading as data, columns assigned the wrong data types, logically derivable fields left blank, and records so incomplete they cannot be meaningfully imputed. This project addressed all of these problems on a cafe sales dataset, transforming a corrupted table into a clean, analysis-ready one using a structured, step-by-step SQL cleaning pipeline.
The goal was to produce a fully cleaned version of the dirty_cafe_sales table by systematically identifying and resolving every category of data quality issue; placeholder values, incorrect data types, missing fields, ambiguous records, and inconsistent formatting, while preserving as much data as possible through intelligent imputation before resorting to deletion.
The dataset was a cafe sales transaction table containing columns for Item, Quantity, Price Per Unit, Total Spent, Payment Method, Location, and Transaction Date. The data contained multiple corruption types: string placeholders (‘ERROR’, ‘UNKNOWN’), empty strings, an invalid date value, missing numeric fields recoverable through arithmetic, and rows with no recoverable identifying information.
The cleaning pipeline was executed entirely in MySQL and followed a deliberate sequence: placeholder removal → type correction → arithmetic imputation → domain-knowledge imputation → standardisation → selective row deletion. Each step was verified with a confirmation query before proceeding, ensuring that no cleaning action introduced new errors.
select *
from dirty_cafe_sales;
## Temporary Modification of data type in order to remove placeholders (Error,Unknown)
ALTER TABLE dirty_cafe_sales
MODIFY Quantity text,
MODIFY `Price Per Unit` text;
## Replacement of placeholders with null
UPDATE dirty_cafe_sales
SET
`Item` = CASE
WHEN `Item` IN ('ERROR','UNKNOWN') THEN null
ELSE `Item`
END,
`Quantity` = CASE
WHEN `Quantity` IN ('ERROR','UNKNOWN') THEN null
ELSE `Quantity`
END,
`Price Per Unit` = CASE
WHEN `Price Per Unit` IN ('ERROR','UNKNOWN') THEN null
ELSE `Price Per Unit`
END,
`Total Spent` = CASE
WHEN `Total Spent` IN ('ERROR','UNKNOWN') THEN null
ELSE `Total Spent`
END,
`Payment Method` = CASE
WHEN `Payment Method` IN ('ERROR','UNKNOWN') THEN null
ELSE `Payment Method`
END,
`Location` = CASE
WHEN `Location` IN ('ERROR','UNKNOWN') THEN null
ELSE `Location`
END,
`Transaction Date` = CASE
WHEN `Transaction Date` IN ('ERROR','UNKNOWN') THEN null
ELSE `Transaction Date`
END;
## Confirming if it worked. Yes, it did!
SELECT count(*)
FROM dirty_cafe_sales
WHERE Item IN ('ERROR','UNKNOWN')
OR Quantity IN ('ERROR','UNKNOWN')
OR `Price Per Unit` IN ('ERROR','UNKNOWN')
OR `Total Spent` IN ('ERROR','UNKNOWN')
OR `Payment Method` IN ('ERROR','UNKNOWN')
OR Location IN ('ERROR','UNKNOWN')
OR `Transaction Date` IN ('ERROR','UNKNOWN');
## Convert columns to normal data type
UPDATE dirty_cafe_sales
SET `Transaction Date` = null
WHERE `Transaction Date` = '' OR `Transaction Date` = 1998;
ALTER TABLE dirty_cafe_sales
MODIFY Quantity int,
MODIFY `Price Per Unit` int,
MODIFY `Total Spent` int,
MODIFY `Transaction Date` datetime;
## Filling the nulls in Total spent column
SELECT *
FROM dirty_cafe_sales;
UPDATE dirty_cafe_sales
SET `Total Spent` = Quantity * `Price Per Unit`
WHERE `Total Spent` is null OR `Total Spent` = '' ;
SELECT *
FROM dirty_cafe_sales
WHERE `Total Spent` = '' OR `Total Spent` is null;
## Filling the nulls in Quantity Column
UPDATE dirty_cafe_sales
SET Quantity = `Total Spent`/`Price Per Unit`
WHERE Quantity is null OR Quantity = '';
SELECT *
FROM dirty_cafe_sales
WHERE `Total Spent` = '' OR `Total Spent` is null;
## Filling the nulls in Price Per Unit
UPDATE dirty_cafe_sales
SET `Price Per Unit` = `Total Spent`/Quantity
WHERE `Price Per Unit` is null OR `Price Per Unit` = '';
SELECT *
FROM dirty_cafe_sales
WHERE `Price Per Unit` is null OR `Price Per Unit`= '';
/* Filling Item column. In the dataset, items have it own prices, with that, i could solve the problem of null in item column.
In cases where two items shared the same price, i just left it as null. */
SELECT *
FROM dirty_cafe_sales;
UPDATE dirty_cafe_sales
SET Item = CASE WHEN Item = '' and `Price Per Unit` = 1 THEN 'Cookie'
WHEN Item = '' OR Item is null and `Price Per Unit` = 1.5 THEN 'Tea'
WHEN Item = '' OR Item is null and `Price Per Unit` = 2 THEN 'Coffee'
WHEN Item = '' OR Item is null and `Price Per Unit` = 3 THEN null
WHEN Item = '' OR Item is null and `Price Per Unit` = 4 THEN null
WHEN Item = '' OR Item is null and `Price Per Unit` = 5 THEN 'Salad'
ELSE `Item`
END
WHERE Item = '';
## Confirming if it worked. Yes, It did!
SELECT *
FROM dirty_cafe_sales
WHERE item is null;
## Standardize Payment Method and Location
UPDATE dirty_cafe_sales
SET `Payment Method` = replace(`Payment Method`,'',null),
Location = replace(Location,'',null)
WHERE `Payment Method` = '' OR Location = '';
SELECT *
FROM dirty_cafe_sales
WHERE Item is null AND `Transaction Date` is null;
/* Drop rows where Item and Transaction date is null. Reason being that the only way to firgure the Item item is by transaction date,
but if there is no transaction date, then that might not be possible except in a case where we have anoher table */
DELETE FROM dirty_cafe_sales WHERE Item is null AND `Transaction Date` is null;
SELECT *
FROM dirty_cafe_sales
WHERE Item is null AND `Transaction Date` is null;
SELECT *
FROM dirty_cafe_sales;
This cleaning pipeline demonstrates that data cleaning is not a mechanical process of deleting bad rows, it is a series of reasoned decisions that balance data preservation against analytical integrity. Every step in this pipeline followed that principle: placeholder values were replaced rather than rows deleted, numeric nulls were arithmetically recovered, item nulls were resolved through domain knowledge where possible and left intentionally ambiguous where not, and deletion was reserved only for records with no viable recovery path.
The techniques applied here includes; temporary schema modification, multi-column batch updates, quality gate verification, sequential arithmetic imputation, domain-knowledge inference, and conditional deletion, collectively reflect the kind of structured, defensible cleaning methodology that production data pipelines require. The result is a dataset that is not just cleaner, but trustworthy where every remaining value is either original or demonstrably derived, and every deletion was justified.