BBampaa

Lessons › SQL › Deep Dive: Data Cleaning

SQL lesson

Before-and-after validation comparing row counts and sums after cleaning

Before-and-after validation means capturing row counts and totals before a cleanup, then running the same checks afterward to confirm only the intended data changed.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Validating Retail Cleaning Results Before and After

WITH cleaned AS (SELECT DISTINCT order_id, customer_id, revenue, status, order_date FROM orders WHERE revenue IS NOT NULL) SELECT (SELECT COUNT(*) FROM orders) AS before_rows, (SELECT SUM(revenue) FROM orders) AS before_revenue_sum, (SELECT COUNT(*) FROM cleaned) AS after_rows, (SELECT SUM(revenue) FROM cleaned) AS after_revenue_sum

Result:

before_rowsbefore_revenue_sumafter_rowsafter_revenue_sum
5255.493255.49

Step by step

  1. WITH lets you name a sub-query so the main query can reference it.
    WITH cleaned AS (SELECT DISTINCT order_id, customer_id, revenue, status, order_date FROM orders WHERE revenue IS NOT NULL)
  2. AS gives a column a friendlier name in the result.
    WITH cleaned AS (SELECT DISTINCT order_id, customer_id, revenue, status, order_date FROM orders WHERE revenue IS NOT NULL)
SELECT (SELECT COUNT(*) FROM orders) AS before_rows, (SELECT SUM(revenue) FROM orders) AS before_revenue_sum, (SELECT COUNT(*) FROM cleaned) AS after_rows, (SELECT SUM(revenue) FROM cleaned) AS after_revenue_sum;

Practice

Try each one in your head first, then open the answer.

1. Write a query that compares the row count of orders_raw to orders_clean to validate the cleaning step.
SELECT (SELECT COUNT(*) FROM orders_raw) AS raw_count, (SELECT COUNT(*) FROM orders_clean) AS clean_count
2. Write a query that compares the row count of products_raw to products_clean to validate the cleaning step.
SELECT (SELECT COUNT(*) FROM products_raw) AS raw_count, (SELECT COUNT(*) FROM products_clean) AS clean_count
3. Write a query that compares the row count of customers_raw to customers_clean to validate the cleaning step.
SELECT (SELECT COUNT(*) FROM customers_raw) AS raw_count, (SELECT COUNT(*) FROM customers_clean) AS clean_count
4. Write a query that compares the row count of store_sales_raw to store_sales_clean to validate the cleaning step.
SELECT (SELECT COUNT(*) FROM store_sales_raw) AS raw_count, (SELECT COUNT(*) FROM store_sales_clean) AS clean_count
5. Write a query that compares the row count of returns_raw to returns_clean to validate the cleaning step.
SELECT (SELECT COUNT(*) FROM returns_raw) AS raw_count, (SELECT COUNT(*) FROM returns_clean) AS clean_count

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed