ETL Testing Interview Questions for 2026: Test Design, Validation SQL, and Answers That Hold Up

ETL testing verifies that data extracted from source systems lands in the target complete, accurate, and transformed exactly as the specification says. The expected result is not a screen or an API response — it is the source data plus the mapping rules, so almost every test is a SQL query comparing two datasets. Three differences from application testing stand out. First, the test oracle is the source-to-target mapping document, not a UI spec. Second, volume is a first-class concern: a check that works on 1,000 rows may be useless on 100 million. Third, many defects are not code defects at all — bad data in the source, an ambiguous mapping rule, or a mis-sequenced job can each produce wrong output with correct code. Part of the tester's job is deciding which of those it is.

A source-to-target mapping (STM) document lists, for every target column: the source table and column, the data type on both sides, and the transformation rule — direct move, derived expression, lookup, default, or aggregation. It is the tester's primary requirement document. To derive test cases, work rule by rule: direct-move columns get a value comparison; derived columns get an independent recomputation of the expression; lookup columns get a join against the reference table plus an orphan check for keys that fail the lookup; default rules get cases where the source is NULL or invalid. Then add table-level cases no single mapping row implies: row counts, duplicate checks on the declared grain, referential integrity, and datatype or truncation checks where source and target types differ. If the STM is ambiguous, that is itself a defect — raise it rather than coding tests around a guess.

Six categories. Count checks: compare source row counts — after the filters and joins the spec defines — to target counts, overall and per partition or load date. Duplicate checks: group by the declared business key and flag keys appearing more than once. Null checks: mandatory columns should contain no NULLs, and defaulting rules should have fired. Referential integrity checks: every foreign key in a fact should resolve to a dimension row, since most warehouses do not enforce constraints. Transformation checks: recompute derived columns independently and compare against the target. Datatype and truncation checks: verify precision, length, and encoding survive the move — a VARCHAR(100) to VARCHAR(50) mapping silently truncates in some configurations and errors in others. Each category maps to one or two reusable SQL patterns, which is exactly why interviewers ask you to write them live.

A matching count only proves the same number of rows arrived — not that they are the right rows or hold the right values. Failure modes that pass a count check: values truncated or defaulted during load; a wrong join that drops some rows and duplicates others so the totals cancel out; updates applied to the wrong rows; character-set corruption; a transformation bug that alters a derived column on every row. Counts are the cheapest check, so run them first — but sign-off needs content validation: duplicate checks on the key, column-level aggregates (SUM, MIN/MAX, distinct counts, null counts) compared between source and target, MINUS/EXCEPT comparisons on manageable volumes, and independent recomputation of transformed columns. A useful summary for the interview: counts prove completeness of volume, not correctness of content.

Five families cover most of the job. Joins — especially LEFT JOIN with WHERE right_key IS NULL, the standard orphan and missing-row pattern. Aggregation with GROUP BY and HAVING — duplicate detection via HAVING COUNT(*) > 1, and column profiling with SUM, MIN, MAX, COUNT(DISTINCT) and null counts. Set operators — MINUS or EXCEPT, run in both directions, to find rows present on only one side. Window functions — ROW_NUMBER to inspect duplicate groups or pick the latest version of a record, LAG and LEAD to test SCD date-range continuity. CASE expressions — to independently recompute mapping rules instead of re-running the developer's logic. Underpinning all of it: NULL semantics. NULL = NULL is not true in a WHERE clause, but set operators and GROUP BY treat NULLs as matching, and COUNT(column) skips them — three behaviors that cause false passes if forgotten.

Start by pinning down the grain — "duplicate" is meaningless until you know what one row is supposed to represent. For business-key duplicates: SELECT key_cols, COUNT(*) FROM target GROUP BY key_cols HAVING COUNT(*) > 1. For full-row duplicates, group by all columns, or compare COUNT(*) with a distinct count over a row hash. To inspect offenders, use ROW_NUMBER() OVER (PARTITION BY key ORDER BY load_ts DESC) and pull groups larger than one — this also shows which copy the dedup logic should have kept. Then run the same check on the source: if the duplicate exists upstream, it is a source-data defect, and the question becomes whether the spec says dedupe, reject, or pass through. If the source is clean and the target is not, suspect the load — a rerun without idempotency or a join that fans out are the usual causes.

The pattern: (SELECT cols FROM source_query) EXCEPT (SELECT cols FROM target_query), then the reverse direction, because EXCEPT is asymmetric — one direction finds rows missing from the target, the other finds rows the target invented. The traps are the real interview material. EXCEPT is set-based: it collapses duplicates, so a row appearing three times in source and once in target produces no difference — always pair it with count and duplicate checks. NULLs are treated as equal to each other in set operations, unlike in join predicates — usually what you want, but it should be a conscious choice. Formatting differences — trailing spaces, decimal precision, timestamp truncation — produce noisy false diffs, so cast and trim deliberately. It is expensive at volume, and it cannot run natively across two different engines; there you extract both sides into one engine or use a cross-database comparison tool. Use EXCEPT for drill-down, not as the primary check on huge tables.

Most analytical databases accept foreign key declarations but do not enforce them, so referential integrity is the tester's job. The core query: SELECT f.* FROM fact f LEFT JOIN dim d ON f.dim_key = d.dim_key WHERE d.dim_key IS NULL — any rows returned are orphans. Run it for every fact-to-dimension relationship, ideally per load rather than once. Then test the design's stated policy for failures: many pipelines route unresolvable keys to a default "unknown" member (often key -1) instead of dropping the row, so verify orphans actually land there, that the unknown member exists, and that rows are not silently discarded. Late-arriving dimensions are the subtle case: a fact can legitimately arrive before its dimension row, so check whether the spec says hold, default-and-reprocess, or reject — and write a test for that behavior, not just the happy path.

The principle is independent recomputation: derive the expected value from the source yourself, using the mapping document, rather than re-running the developer's transformation — otherwise you are testing the code against itself. For a rule like "status = A if balance is positive and the account is open, else I", write a CASE expression over source columns and compare it to the target column — row by row on critical columns, sampled on the rest. For rules too complex to inline — multi-step derivations, aggregations, lookups with precedence — build a small handcrafted input dataset with a worked expected output covering every branch and boundary of the rule, push it through the pipeline in a test environment, and diff. Prioritize by risk: money amounts, keys, and status fields get full-volume verification; descriptive text can be sampled. Record which STM rule each test covers, so coverage stays auditable against the spec.

Design cases around every way a delta can behave, not just new rows. Inserts: a new source row appears in the target exactly once. Updates: the changed row is updated — or versioned, if SCD2 — and no other row is touched. Deletes: know the spec — hard delete, soft-delete flag, or ignored — and test the chosen behavior explicitly. Unchanged rows: confirm they are not rewritten, which count checks will not reveal; compare audit or update timestamps. Boundary conditions: a record whose timestamp equals the watermark exactly — an off-by-one comparison (> versus >=) either drops it or double-processes it. Rerun: execute the same window twice and verify the target is unchanged — idempotency tested from the QA seat. Late-arriving data: a record with an old event timestamp arriving after its window closed. For each case, seed the source deliberately in a test environment — waiting for production data to cover these paths is not a strategy.

Use structural queries, not eyeballing. One current version per key: group by business key and assert the count of current-flagged rows is exactly one — this catches both duplicated and missing current rows. No overlapping or gapped history: with LAG, compare each row's effective start against the previous row's effective end per key; overlaps double-count when facts join the dimension, gaps drop rows. Change detection: update a tracked attribute in the source, run the load, and verify a new version was created, the old one was closed with the correct end date, and the current flag moved. Then update a non-tracked attribute and verify no new version appears — over-triggering silently explodes the dimension. Finally, rerun the same load and confirm version counts are stable. All five checks fit in reusable SQL and belong in every regression run of a dimension load.

Classify along the axis of what has to change to fix it. Source data defects: the pipeline behaved correctly on bad input — duplicates, invalid dates, broken encodings upstream; the fix sits with the data owner, and the pipeline question is whether it should have rejected or quarantined the row. Mapping or spec defects: the STM is wrong, ambiguous, or contradicts source reality — the code faithfully implements a wrong rule. Code defects: output disagrees with an unambiguous STM. Environment or configuration defects: wrong connection, partial file pickup, jobs run out of sequence, permissions. Plus performance defects: correct output, unacceptable load window. The classification matters practically: it routes the fix to the right owner, prevents developers "fixing" code to match bad data, and trend analysis over categories shows where quality effort should go — a project drowning in spec defects has a requirements problem, not a coding problem.

The goal is proving the change altered only what it was meant to alter. Impact analysis first: from the changed mapping or code, list affected target tables and columns; regression scope is those plus everything downstream of them. Then a baseline diff: run the old and new logic against the same frozen input in a test environment and compare outputs — full EXCEPT on modest volumes, column aggregates and row-hash comparisons at scale. Every difference must be explainable by the change request; unexplained diffs are defects. Alongside that, keep a standing suite of assertion queries — counts, duplicates, referential integrity, SCD structure, key business aggregates — versioned and executed on every release, so the checks that caught last year's defects keep running. Snapshot key aggregates before the production deployment as a rollback reference. The trap to name explicitly: regression-testing only the changed table and missing fan-out effects downstream.

Tier the comparison so expensive checks run only where cheap ones fail. Tier one: row counts per partition — load date, region, source system — which localizes any discrepancy immediately. Tier two: column-level profiles compared source versus target — SUM and MIN/MAX on numerics, COUNT(DISTINCT) on keys, null counts per column, length profiles on strings. These are single-pass aggregations even at 500 million rows, and a mismatch tells you which column and partition to chase. Tier three: hashed buckets — compute a row hash, bucket rows by key (for example MOD of a key hash into 1,024 buckets), aggregate hashes per bucket on each side, and run row-level EXCEPT only on mismatched buckets, shrinking the row-by-row work by orders of magnitude. Optionally add stratified samples with full-column comparison for content confidence. Two cautions: make hashing deterministic across engines by casting and formatting consistently first, and reconcile against a consistent snapshot, or in-flight loads generate false diffs.

Two sources, for different jobs. Masked production subsets give realistic distributions, volumes, and the messiness synthetic generators miss — but subsetting must be referentially consistent: pull the parent rows for every child you sample, or you manufacture fake orphan defects. Masking must happen before data reaches the test environment and must preserve the properties tests depend on — formats, key uniqueness, joinability — or half the suite breaks. Synthetic data covers what production does not have yet: NULLs in every nullable column, max-length strings, boundary dates, invalid values the reject path should catch, duplicate keys, a record timestamped exactly at the load watermark. Keep synthetic cases as versioned files with worked expected outputs so they double as regression fixtures. Refresh cadence matters: stale test data drifts from production schema and distributions, and the suite quietly stops testing reality. The interview-worthy line: production copies find unknown defects; synthetic data proves known risks are handled.

Five things. Assertions as code: every check — counts, duplicates, referential integrity, transformation recomputes, SCD structure — is a versioned SQL query with a machine-readable pass condition, not a spreadsheet someone runs by hand. An execution harness: commercial tools in the QuerySurge mould specialize in scheduled cross-database comparison; a lightweight equivalent is a pytest- or dbt-test-style runner that executes queries against both systems and asserts on results. Tolerances and severity: some checks hard-fail the pipeline (duplicate keys), others warn within a threshold (a row count within a small tolerance for a snapshot source) — encoding that judgment is most of the design work. Integration: run in CI on every code change and post-load in the scheduled pipeline, so regression and production monitoring share one suite. History: persist results per run, because a metric drifting toward its threshold is a defect signal no single run shows. Tools vary; these requirements do not.