Data Reconciliation and Data Quality Checks

After moving data between systems (source → warehouse, staging → production), you need to prove the data landed correctly. These are the standard SQL checks for that.

Row Count Reconciliation

The simplest check — do both sides agree on how many rows exist?

SELECT
  (SELECT COUNT(*) FROM source_orders) AS source_count,
  (SELECT COUNT(*) FROM warehouse.orders) AS warehouse_count;

Checksum / Aggregate Comparison

Comparing every single row is expensive — comparing a summary of both sides is fast and still catches most problems.

SELECT SUM(total), COUNT(*), MIN(created_at), MAX(created_at)
FROM source_orders;

SELECT SUM(total), COUNT(*), MIN(created_at), MAX(created_at)
FROM warehouse.orders;
-- if these don't match, something was lost, duplicated, or corrupted in transit

Finding Rows That Exist on One Side Only

An anti-join (see the Anti-Joins lesson) pinpoints exactly which rows are missing.

SELECT s.id FROM source_orders s
LEFT JOIN warehouse.orders w ON s.id = w.id
WHERE w.id IS NULL;
-- rows present in the source but missing from the warehouse

Finding Duplicates

SELECT order_id, COUNT(*)
FROM warehouse.orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Finding Orphaned Foreign Keys

Rows that reference a parent that doesn't exist — a sign of a broken load order or a source-side deletion that didn't cascade.

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE o.customer_id IS NOT NULL AND c.id IS NULL;

Detecting Stale Data

SELECT MAX(loaded_at) AS last_load_time
FROM warehouse.orders;
-- if this is older than expected, the pipeline may have silently stopped running

A Simple Data Quality Report

SELECT
  'orders' AS table_name,
  COUNT(*) AS total_rows,
  COUNT(*) FILTER (WHERE customer_id IS NULL) AS missing_customer,
  COUNT(*) FILTER (WHERE total < 0) AS negative_totals,
  COUNT(DISTINCT id) AS distinct_ids,
  COUNT(*) - COUNT(DISTINCT id) AS duplicate_count
FROM orders;

Common Errors & Troubleshooting

• Row counts matching but data still wrong — a matching count doesn't guarantee matching content; pair it with the aggregate/checksum comparison (SUM, MIN, MAX) shown above to catch value-level discrepancies a count alone would miss.
• Anti-join reconciliation query timing out on huge tables — make sure both sides of the join have an index on the compared column; an unindexed anti-join over millions of rows on both sides can be very slow.
• False positives from timezone differences — comparing timestamps across a source and a warehouse in different timezones can make identical data look mismatched; normalize both sides to the same timezone before comparing.

Key Takeaways

• Compare row counts and aggregate summaries (SUM, MIN, MAX) as a cheap first-pass reconciliation check before comparing individual rows.
• Anti-joins are the standard way to find rows present on one side of a migration/sync but missing on the other.
• COUNT(*) FILTER (WHERE ...) (from the Advanced Aggregates lesson) makes it easy to build a single-query data quality report.