How would you use SQL to validate data integrity after a migration or ETL job?

I validate a migration or ETL load in layers, from cheap and coarse to precise: reconcile volumes and totals, diff the actual rows, then run rule-based integrity checks (referential integrity, uniqueness, required fields, valid values, business rules). Each check is a query that should return zero rows or matching numbers, which makes them easy to automate and run after every load.

1. Reconcile counts and aggregates

SELECT 'source' AS side, COUNT(*) AS row_count, SUM(amount) AS total_amount,
       COUNT(DISTINCT customer_id) AS customers
FROM legacy.orders
WHERE order_date >= DATE '2026-01-01'
UNION ALL
SELECT 'target', COUNT(*), SUM(amount), COUNT(DISTINCT customer_id)
FROM sales.orders
WHERE order_date >= DATE '2026-01-01';

Counts catch dropped or duplicated rows; sums catch truncation, rounding and currency conversion bugs. Break them down by a dimension (per day, per status) so a mismatch points somewhere specific.

2. Row-level diff

When both datasets are reachable from one database (same server, foreign data wrapper, or a staged copy), EXCEPT finds rows that differ in any compared column, in either direction:

-- In source but missing or different in target
SELECT order_id, customer_id, amount, status FROM legacy.orders
EXCEPT
SELECT order_id, customer_id, amount, status FROM sales.orders;

-- In target but not in source
SELECT order_id, customer_id, amount, status FROM sales.orders
EXCEPT
SELECT order_id, customer_id, amount, status FROM legacy.orders;

Apply the same transformations the ETL is meant to apply (for example UPPER(status)), otherwise every row differs. Oracle calls EXCEPT MINUS. For very large tables, compare per-partition hashes first (md5(string_agg(... ORDER BY id)) per day) and only diff partitions whose hashes differ.

3. Referential integrity: orphans

SELECT o.order_id, o.customer_id
FROM sales.orders o
WHERE NOT EXISTS (
  SELECT 1 FROM sales.customers c WHERE c.customer_id = o.customer_id
);

The NOT IN trap

-- Looks equivalent, but returns NO rows if any customers.customer_id is NULL
SELECT * FROM sales.orders
WHERE customer_id NOT IN (SELECT customer_id FROM sales.customers);

x NOT IN (1, 2, NULL) evaluates to NULL (unknown), never true, so the whole query silently returns nothing and the check falsely “passes”. Prefer NOT EXISTS for anti-joins.

4. Rule checks

-- Duplicates on the business key
SELECT order_number, COUNT(*) FROM sales.orders
GROUP BY order_number HAVING COUNT(*) > 1;

-- Required fields
SELECT order_id FROM sales.orders WHERE customer_id IS NULL OR order_date IS NULL;

-- Domain and range
SELECT order_id, status FROM sales.orders
WHERE status NOT IN ('PENDING', 'PAID', 'SHIPPED', 'CANCELLED') OR amount < 0;

-- Business rule: header total equals sum of lines
SELECT o.order_id, o.amount, SUM(l.qty * l.unit_price) AS line_total
FROM sales.orders o
JOIN sales.order_lines l ON l.order_id = o.order_id
GROUP BY o.order_id, o.amount
HAVING o.amount <> SUM(l.qty * l.unit_price);

(The NOT IN with a literal list is safe here because the list has no NULLs; but rows where status IS NULL are not returned by it, so check NULLs separately.)

5. Also check

• Encoding and special characters (accents, emoji) survived.
• Timestamps: time zone conversions and daylight saving boundaries.
• Precision: NUMERIC to FLOAT conversions, trailing spaces in CHAR columns.
• Idempotency: running the job twice does not duplicate data.

Automate it

Each check returns zero rows on success, so a test runner (pytest, JUnit) or a data-quality tool (dbt tests, Great Expectations, Soda) can run them after every load and fail the pipeline with the offending rows attached.

What interviewers look for

• A layered approach from counts to row-level diffs to rules.
• EXCEPT or full outer join diffs in both directions.
• NOT EXISTS for orphans and awareness of the NOT IN / NULL trap.
• Automation and clear, actionable failure output.