Data validation and reconciliation in SQL

Why validate?

An analysis can be logically perfect and still wrong because the data was incomplete, duplicated or loaded twice. Validation is the analyst's insurance. Senior analysts are trusted precisely because their numbers reconcile.

1. Profile the table

SELECT COUNT(*)                                  AS rows,
       COUNT(DISTINCT order_id)                  AS distinct_orders,
       MIN(order_date)                           AS first_date,
       MAX(order_date)                           AS last_date,
       SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customers,
       SUM(CASE WHEN discount_pct < 0 OR discount_pct > 0.9 THEN 1 ELSE 0 END) AS suspicious_discounts
FROM orders;

Rows = distinct orders (no duplicate keys), dates cover the expected range, no missing customers, no impossible discounts.

2. Duplicates

SELECT customer_id, COUNT(*) AS copies
FROM customers_raw
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY copies DESC
LIMIT 5;

Distinguish exact duplicates (every column identical — usually a loading error) from entity duplicates (same customer, different spelling — needs matching rules).

3. Referential integrity (orphans)

SELECT
  (SELECT COUNT(*) FROM order_items oi LEFT JOIN orders o   ON o.order_id = oi.order_id     WHERE o.order_id IS NULL)   AS lines_without_order,
  (SELECT COUNT(*) FROM order_items oi LEFT JOIN products p ON p.product_id = oi.product_id WHERE p.product_id IS NULL) AS lines_without_product,
  (SELECT COUNT(*) FROM orders o LEFT JOIN order_items oi    ON oi.order_id = o.order_id     WHERE oi.order_id IS NULL)  AS orders_without_lines;

All zeros: every line has an order and product, and every order has at least one line.

4. Business-rule checks

Rules that must hold regardless of schema constraints: quantities positive, prices above cost (or flagged), close dates after open dates, a balance sheet that balances.

SELECT year_month,
       ROUND(SUM(CASE WHEN section = 'Assets' THEN amount ELSE 0 END), 2) AS assets,
       ROUND(SUM(CASE WHEN section IN ('Liabilities', 'Equity') THEN amount ELSE 0 END), 2) AS liabilities_and_equity,
       ROUND(SUM(CASE WHEN section = 'Assets' THEN amount ELSE -amount END), 2) AS difference
FROM balance_sheet
GROUP BY year_month
ORDER BY ABS(difference) DESC
LIMIT 3;

The largest absolute difference across all months is effectively zero — the balance sheet balances.

5. Reconciliation

Reconciliation compares a figure computed two independent ways, or from two systems, and explains every difference. Example: revenue computed from order lines vs monthly plan file loaded by Finance — or here, order-line revenue split by channel must sum to the total:

WITH lines AS (
  SELECT o.channel, oi.quantity * oi.unit_price * (1 - oi.discount_pct) AS rev
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed' AND o.order_date >= '2025-01-01'
)
SELECT ROUND((SELECT SUM(rev) FROM lines), 2) AS total,
       ROUND((SELECT SUM(rev) FROM lines WHERE channel = 'store')
           + (SELECT SUM(rev) FROM lines WHERE channel = 'online'), 2) AS sum_of_channels;

When two systems disagree (CRM vs ERP), build a reconciliation table: matched records, records only in A, only in B, and matched records with different amounts (FULL OUTER JOIN from the joins lesson), then explain each bucket — timing, status definitions, currency, cancellations.

Practice

Summary

• Profile first: counts, keys, NULLs, ranges.
• Check duplicates, orphans and business rules explicitly.
• Reconcile to independent totals and explain every difference.
• Automate recurring checks in the ETL/ELT pipeline.