SQL Data Validation Interview Questions: Reconcile a Migration Before Sign-Off

The source and target each contain six rows. Their amount totals match. Is the migration correct? Not necessarily: one key can disappear while another appears, two incorrect amounts can cancel, and a NULL can become an empty string without changing either summary.

A defensible SQL validation answer establishes a comparable snapshot, checks keys, compares canonical rows, and reconciles business invariants. Matching counts and totals are useful screening evidence, not sufficient sign-off evidence. This guide builds that argument with an original migration fixture and executable SQL.

Official AWS and PostgreSQL documentation supports tool and language behavior. The validation sequence and acceptance examples are editorial recommendations, not a claim that a real migration was performed or that an employer uses this exercise. For related practice, see Validate order book data across multiple databases.

Define what should be equal

Before comparing tables, record the source snapshot, target snapshot, included population, key, column mapping, and transformation rules. Equality is meaningful only after these boundaries are aligned.

For this exercise, source_orders and target_orders have order_id, amount, and status. The migration should preserve all three fields exactly. Order IDs are non-NULL and unique. Amounts are exact decimals; status may be NULL. Both tables represent the same frozen business state, with no concurrent changes.

A real migration may intentionally rename statuses, change timestamp representation, or split a table. Then compare the target against a reviewed canonical projection of the source. Do not blindly demand byte equality where the contract requires a transformation, and do not normalize away differences merely to make a test pass.

Specify exclusions, too. If cancelled orders are omitted intentionally, count and document them separately. A filter copied incorrectly into both validation queries can make two incomplete populations appear consistent.

A fixture where summaries pass and rows fail

Source rows are (1, 10, paid), (2, 20, paid), (3, 30, NULL), (4, 40, paid), (5, 50, paid), and (6, 60, paid). Target rows preserve 1 and 6, change order 2 to amount 25, change order 3's status to an empty string, replace order 4 with order 7 at amount 40, and change order 5 to amount 45.

Check	Source	Target	Does it expose the problem?
Row count	6	6	No
Amount sum	210	210	No
Distinct key count	6	6	No
Key membership	Includes 4	Includes 7 instead	Yes
Amount for key 2	20	25	Yes
Amount for key 5	50	45	Yes
Status for key 3	NULL	Empty string	Yes

The expected detailed report has five findings: two amount mismatches, one status mismatch, one missing target key, and one unexpected target key. A full scan of aggregate totals still cannot identify those differences because the aggregates discard information.

This fixture is deliberately small enough to reason about by hand. It demonstrates why adding more decimal places to a checksum total is not the same as checking record identity.

Gate row comparisons on valid keys

Before a key-based join, verify non-NULL and unique keys on each side. Here is a duplicate check for the target:

SELECT order_id, COUNT(*) AS row_count
FROM target_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Run the equivalent check on the source, plus explicit NULL-key checks. A full outer join assumes one row on each side for each key. If one source row matches three target duplicates, the diff can multiply and obscure whether the problem is identity, values, or both.

Do not repair the evidence by adding DISTINCT indiscriminately. If duplicate rows are forbidden, their presence is itself a migration failure. If duplicates are legitimate, the comparison must use an appropriate business key or multiset semantics rather than pretend the table is uniquely keyed.

Official tool boundary: AWS DMS data validation requires a primary key or unique index, with documented limitations. That is a requirement of that validation feature, not proof that every table declaring a key is actually correct in every target engine. See AWS DMS data validation.

Produce a NULL-aware row difference report

Once key checks pass, classify missing rows before comparing fields:

SELECT COALESCE(s.order_id, t.order_id) AS order_id,
       CASE
           WHEN s.order_id IS NULL THEN 'unexpected_target'
           WHEN t.order_id IS NULL THEN 'missing_target'
           ELSE 'value_mismatch'
       END AS issue,
       s.amount AS source_amount,
       t.amount AS target_amount,
       s.status AS source_status,
       t.status AS target_status
FROM source_orders AS s
FULL OUTER JOIN target_orders AS t
  ON t.order_id = s.order_id
WHERE s.order_id IS NULL
   OR t.order_id IS NULL
   OR s.amount IS DISTINCT FROM t.amount
   OR s.status IS DISTINCT FROM t.status
ORDER BY order_id;

The non-NULL key contract lets s.order_id IS NULL indicate an absent source row rather than a real NULL key. Without that contract, use an explicit presence marker or resolve the invalid key problem first.

Official PostgreSQL semantics: IS DISTINCT FROM treats NULL as a comparable value for this predicate, so NULL versus empty string is reported while NULL versus NULL is not. Ordinary <> can produce unknown for NULL comparisons and miss the intended mismatch. See comparison functions and operators.

The query returns keys 2, 3, 4, 5, and 7 with the expected classifications. Both amounts are retained in the output, so a reviewer can inspect compensating errors rather than seeing only a generic failure count. These queries were executed in DuckDB 1.5.2; no production data or PostgreSQL runtime was used.

Use set comparisons with the right multiplicity

A second approach compares projected rows in both directions:

SELECT 'source_only' AS side, d.*
FROM (
    SELECT order_id, amount, status FROM source_orders
    EXCEPT ALL
    SELECT order_id, amount, status FROM target_orders
) AS d
UNION ALL
SELECT 'target_only' AS side, d.*
FROM (
    SELECT order_id, amount, status FROM target_orders
    EXCEPT ALL
    SELECT order_id, amount, status FROM source_orders
) AS d;

Official PostgreSQL semantics: ordinary EXCEPT removes duplicate result rows; EXCEPT ALL retains the relevant multiplicity difference. See combining queries. Choose deliberately when duplicate counts matter.

For the fixture, this report has eight rows: four source-side records and four target-side records differ. That is compatible with the five-key report above. Changed values create an old and new row representation; a key-based report combines them into one finding.

Do not compare only source minus target. That can find missing or changed source records while overlooking unexpected extra target records. Also do not compare SELECT * across evolving schemas without reviewing column order and compatible types.

Align the comparison clock before diagnosing mismatches

Our frozen fixture avoids concurrency. Real migration validation often compares a changing source with a target that is still applying changes. A mismatch may be a real transformation defect or a temporary difference in replication progress.

Record a common logical boundary, such as a source snapshot and the corresponding applied change position. Compare data known to be included on both sides. Merely running two SELECT statements at nearly the same wall-clock time does not guarantee the same business state.

Official PostgreSQL behavior: a Repeatable Read transaction provides a stable snapshot for successive reads within that transaction. See transaction isolation. Two independent databases do not acquire a shared cross-system snapshot simply because both use that isolation level.

For change-data capture, account for inserts, updates, and deletes through the agreed watermark. An updated-at filter alone may not represent deleted rows. Preserve deletion evidence or use a comparison strategy that can observe absence correctly.

Classify unresolved lag separately from confirmed content differences, but do not turn “probably lag” into a permanent waiver. Rerun after the target reaches the boundary and retain the evidence showing whether the mismatch disappeared.

Validate transformations without hiding defects

A canonical projection may cast a source integer number of cents into a target decimal amount, map an approved status vocabulary, or normalize timestamps to UTC. Each transformation needs its own expected behavior and boundary tests.

For example, if a source amount of 199 cents should become 1.99, test zero, negative adjustments if allowed, maximum values, and rounding rules. If a source timestamp loses fractional precision in the target, record whether that loss is intentional and what comparisons remain valid.

String normalization deserves similar care. Trimming spaces, changing case, or replacing NULL with an empty string can hide real identity or semantic differences. Apply only transformations in the migration contract, not whichever ones reduce the diff count.

Tolerance should also be specific. Approximate measurements may need an absolute and relative error policy; exact monetary decimals usually need exact agreement under their declared conversion. A global “within 1%” rule can conceal large errors or behave badly near zero.

Add business invariants and downstream checks

Row equivalence is powerful but bounded by the selected columns and population. Add invariants that reflect the actual model: child rows reference existing parents, quantities satisfy allowed ranges, ledger movements reconcile, or lifecycle states follow permitted transitions.

Segment totals by dimensions that can reveal compensating errors, such as date, currency, tenant, or status. In the fixture, the +5 and −5 amount errors cancel globally. Even segmented totals can still miss some swaps, so they complement rather than replace detailed comparisons.

Run representative downstream queries against both versions with the same inputs. A migrated timestamp type or collation can change grouping, ordering, or joins even when a superficial display looks similar. Use known reports and application access patterns to test the consumer contract.

Avoid presenting a successful sample as proof that all rows match. Sampling can guide investigation or supplement expensive checks, but it leaves an untested population. State the coverage and selection method clearly.

Scale validation while keeping failures inspectable

Partition large comparisons by stable key ranges or another complete, nonoverlapping scheme. Record the included boundaries and completion status for every partition so retries cannot silently skip a segment.

Hashes can reduce data movement or identify suspect partitions, but they need canonical serialization, column ordering, NULL representation, and type handling. Ambiguous concatenation can make different records produce the same input string. Hash collisions and aggregate cancellation also limit what a matching digest proves.

Retain a path from a failed digest to exact mismatched keys and values. A checksum that says “different” without a drill-down plan may be insufficient for remediation. Conversely, raw differences alone can be too expensive to inspect without summary counts and categories.

Official operational fact: AWS DMS validation issues additional source and target queries and consumes database and network resources. Its documentation makes clear that validation is real workload. Budget capacity and schedule checks accordingly; do not assume validation is free because migration tooling exposes a checkbox.

Make sign-off an evidence-backed decision

A useful validation record names the run, source and target boundaries, query or code version, table coverage, key checks, detailed mismatch counts, invariants, exclusions, and accepted exceptions. Assign an owner and reason to each exception instead of quietly dropping it from the denominator.

For this synthetic fixture, the decision is no sign-off: five keys differ despite matching counts and sums. After repairing the target to the declared source state, rerun the checks; both detailed reports should be empty and the key gates should still pass. The saved local test verifies that repair outcome as well as the failing fixture.

In an interview, make the recommendation conditional on observed evidence: “I would not approve this comparison until the missing and unexpected keys, NULL change, and amount differences are resolved at an aligned boundary.” That is more precise than saying the migration “looks good” because one total matches.

Practice reconciliation reasoning

Question	Relevant practice
Validate order book data across multiple databases	Define cross-system invariants and comparison boundaries
Prove SQL proficiency with concrete outcomes	Explain validation evidence and safe migration decisions
Transform messy transactions with pandas	Normalize types and deduplicate under explicit rules
Debug row loss after SQL joins	Identify missing populations and join assumptions
Reason About Composite Join Keys and Predicate Placement	Preserve full business-key scope

Start with Validate order book data across multiple databases, and write the sign-off conditions before proposing checksums or row-count queries.

Sources and Further Reading

• AWS DMS: Data validation behavior and limitations
• PostgreSQL: NULL-aware comparison predicates
• PostgreSQL: EXCEPT and EXCEPT ALL
• PostgreSQL: Transaction isolation and snapshots