BA Prince the B.A. Subscribe

Home / SQL for Business Analysts

Data Quality Checks in SQL for Business Analysts

Data Quality Checks in SQL for Business Analysts

Watch on YouTube Subscribe

Four SQL checks every business analyst can run before trusting a data set: missing values, orphan records with no parent, values out of range, and values that disagree with another table. Shown on a sample online shop database.

Step by step

Trust the data?

  1. Can we trust it?. Bad data gives bad reports. Before you trust a data set, run a few quality checks in SQL. Each check is written to find the problem rows, so 0 rows means the check passed. Here are 4 of them.
  2. The shop database. The sample online shop database has 4 tables: customers, orders, products and order_items.

Missing values

  1. Missing values. Check 1 looks for missing values. This query lists customers with no email or no city. IS NULL finds empty values, and OR means 1 missing value is enough to list the row. It returns 3 customers: 2 with no email and 1 with no city. Write them down for the data owner.
    SELECT customer_id, first_name, email, city
FROM customers
WHERE email IS NULL OR city IS NULL;

Orphan records

  1. Orphans. Check 2 looks for orphans. An orphan is a row that points to a parent that doesn't exist. LEFT JOIN orders to customers, then keep only the orders where c.customer_id IS NULL, meaning no matching customer was found. It returns 1 orphan: order 1141 belongs to customer 99, and there is no customer 99. Any report grouped by customer will quietly leave this order out.
    SELECT o.order_id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

Out of range

  1. Out of range. Check 3 looks for values out of range. An order can't ship before it was placed, and an order total should never be 0 or less. The query looks for shipped_date < order_date OR order_total <= 0. It returns 0 rows. There are no problem rows, so this check passes. Always check the row count shown with the results. An empty result is a good answer here, not a broken query.
    SELECT order_id, order_date, shipped_date
FROM orders
WHERE shipped_date < order_date
  OR order_total <= 0;

Values that disagree

  1. Values disagree. Check 4 looks for values that disagree. The price paid on each order line should match the product's list price. JOIN order_items to products on product_id, then keep the lines where i.unit_price <> p.unit_price. It returns 1 line that paid $12 for a product listed at $6, and it's order 1141 again. Problems often show up in the same records, so ask about this order first.
    SELECT i.order_id, i.unit_price AS paid,
  p.unit_price AS list
FROM order_items AS i
JOIN products AS p
  ON p.product_id = i.product_id
WHERE i.unit_price <> p.unit_price;

Recap

  1. Four quality checks. So the 4 checks are: missing values, orphans, values out of range, and values that disagree with another table. Write each one to find the bad rows, so 0 rows means pass.

Common mistakes

  • Writing = NULL instead of IS NULL. WHERE email = NULL returns no rows, because NULL is never equal to anything, not even another NULL. Use WHERE email IS NULL.
  • Using an inner JOIN to find orphans. An inner JOIN drops orders that have no matching customer, so the orphans never appear. Use LEFT JOIN and keep the rows where c.customer_id IS NULL.
  • Thinking IS NULL catches blank text. An email saved as an empty string '' is not NULL, so the missing-values check skips it. Add OR email = '' to catch blanks too.
  • Calling every price difference an error. The list price may have changed since the order was placed, or a discount was applied. Ask the data owner before you report the line as wrong.

When a business analyst uses this

Before signing off a data migration, a business analyst runs these 4 checks on the source data. Each check that returns rows, like orphan order 1141, goes into the RAID log as an issue with the data owner's name, so it gets fixed before go-live.

Questions people ask

How do I find NULL values in SQL?

Use IS NULL in the WHERE clause, for example WHERE email IS NULL OR city IS NULL. Writing = NULL doesn't work, because NULL is never equal to anything.

How do I find orphan records in SQL?

LEFT JOIN the child table to the parent table, then keep the rows where the parent's key IS NULL. Those are child rows with no matching parent, like an order with no customer.

What does it mean when a SQL query returns 0 rows?

It means no rows matched the WHERE clause. If the query was written to find bad rows, 0 rows means the data passed that check.

More in SQL for Business Analysts

COUNT vs COUNT DISTINCT in SQL SUM, AVG, MIN, MAX in SQL: Summary Stats for a Report SQL CASE WHEN: If-Then Logic in a Query SQL CTE (WITH): Make Long Queries Readable, Step by Step