Row count reconciliation

The simplest and often most effective check: does the number of rows loaded match the number of rows expected from the source?

row-count-check.sql
SELECT
  (SELECT COUNT(*) FROM source_orders WHERE load_date = '2026-08-10') AS source_count,
  (SELECT COUNT(*) FROM target_orders WHERE load_date = '2026-08-10') AS target_count;
-- A mismatch signals rows were dropped, duplicated, or filtered unexpectedly
Advertisement

Null-rate checks

null-rate-check.sql
SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN customer_email IS NULL THEN 1 ELSE 0 END) AS null_emails,
  ROUND(100.0 * SUM(CASE WHEN customer_email IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS null_pct
FROM orders
WHERE load_date = '2026-08-10';
-- A column normally at 2% NULL jumping to 40% usually means an upstream break

Referential integrity checks

orphaned-foreign-keys.sql
-- Finds order rows whose customer_id has no matching row in customers --
-- orphaned records, whether or not a formal FK constraint exists
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Duplicate detection

duplicate-detection.sql
-- Which key combinations appear more than once
SELECT order_id, COUNT(*) AS occurrences
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Row-level view to decide which specific duplicate to keep
SELECT *,
  ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) AS rn
FROM orders;
-- rn = 1 is the most recently loaded copy of each duplicated order_id
Advertisement

Anomaly detection queries

anomaly-detection.sql
WITH daily_counts AS (
  SELECT load_date, COUNT(*) AS row_count
  FROM orders
  WHERE load_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY load_date
),
stats AS (
  SELECT AVG(row_count) AS avg_count, STDDEV(row_count) AS std_count
  FROM daily_counts
  WHERE load_date < CURRENT_DATE  -- baseline excludes today
)
SELECT d.load_date, d.row_count, s.avg_count, s.std_count
FROM daily_counts d, stats s
WHERE d.load_date = CURRENT_DATE
  AND ABS(d.row_count - s.avg_count) > 2 * s.std_count;
-- Flags today only if it's more than 2 standard deviations from the 30-day average

Building a lightweight testing framework

These checks become genuinely useful once they run automatically after every load and write their results to a dedicated log table — a simple test_name, run_date, passed, details structure is enough to start alerting on failures rather than manually re-running checks after something already looks wrong.

Common mistakes

  • Only checking data quality after a stakeholder reports a problem, instead of running checks automatically on every load.
  • Setting anomaly thresholds too tight, generating so many false-positive alerts that real issues get ignored along with the noise.
  • Checking referential integrity only at the database constraint level, missing violations in systems where foreign keys aren't formally enforced.
  • Not excluding today's incomplete data from the historical baseline used in anomaly detection, skewing the comparison.

Key takeaways

  • Row count reconciliation is the cheapest check with the highest signal — always compare source vs target counts.
  • Null-rate monitoring catches upstream breaks before they surface as a wrong-looking report.
  • A LEFT JOIN finds orphaned foreign keys even without a formal constraint enforcing referential integrity.
  • GROUP BY + HAVING and ROW_NUMBER() cover both duplicate detection and duplicate resolution.
  • Anomaly detection needs a sensible historical baseline — exclude today's incomplete data from it.