Data quality checks in SQL

01The problem

You've just been given access to Harbourline's database, and finance wants a receivables report by Friday. Before you write a single report query, ask the question every experienced analyst asks first: can I trust this data?

A report built on data nobody checked can be wrong in ways no query will warn you about: customers counted twice, shipments marked Delivered with no delivery date, payments that belong to no shipment. Twenty minutes of checks can save the week you'd otherwise spend explaining a wrong number to finance.

02The concept

Data quality checks are short queries that test what you're assuming. Run them on any new table, and again whenever the data is refreshed.

A failed check is a question, not a verdict. When a check finds rows, look at them before doing anything. Some are errors. Some are perfectly legitimate and tell you something about the business. Write down what you found and what you decided, so the next person doesn't repeat the investigation.

03Example

A business-rule check: each status should come with the right dates. Booked and Cancelled shipments haven't shipped, In transit ones have a ship date only, and Delivered ones have both.

SELECT
  status,
  COUNT(*) AS shipments,
  COUNT(ship_date) AS with_ship_date,
  COUNT(delivery_date) AS with_delivery_date
FROM shipments
GROUP BY status;

Every status passes. Now a duplicate check on the natural key: the same customer booking the same route on the same day with the same number of containers.

SELECT s.*
FROM shipments AS s
JOIN (
  SELECT customer_id, route_id, booking_date, containers
  FROM shipments
  GROUP BY customer_id, route_id, booking_date, containers
  HAVING COUNT(*) > 1
) AS d
  ON  d.customer_id = s.customer_id
  AND d.route_id = s.route_id
  AND d.booking_date = s.booking_date
  AND d.containers = s.containers;

Two shipments match: 101206 and 101207, both booked by customer 27 on 2 October 2025. They aren't duplicates. They shipped on different days with different weights and different charges, so they're two real containers that happened to be booked together. Deleting one would have understated that customer's revenue by ₦3.7 million.

04Walkthrough

1. Profile the customers table: SELECT COUNT(*), COUNT(DISTINCT customer_id), COUNT(account_manager_id), MIN(signup_date), MAX(signup_date) FROM customers;. 120 customers, unique IDs, 8 with no account manager.
2. Check a relationship from the other side: customers with no shipments at all. There are 15. They signed up but never booked. Is that a data problem, or a sales opportunity?
3. Check a timing rule: does any customer's first booking come before their signup date? One does: Horizon Foods Plc (customer 79) signed up on 27 January 2025 but first booked on 12 January. That's probably a late account set-up, so it's worth noting but harmless.
4. Check payments against shipping: 59 payments are dated before the shipment even left. Are they prepayments, or payments recorded on the wrong date? That's a question for finance, not something to "fix" in SQL.
5. Write the results in a short data-quality note: check, result, decision.

05Practice

06Challenge

07More practice

08Check your understanding