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 mapping specifies. The expected result is not a screen or API response — it is the source data plus the mapping rules, so almost every test is a SQL query comparing two datasets. Three differences stand out: the test oracle is the source-to-target mapping document, not a UI spec; volume is a first-class concern, since a check that works on 1,000 rows may be useless on 100 million; and many defects are not code defects at all — bad source data, an ambiguous mapping rule, or a mis-sequenced job each produce wrong output with correct code. Deciding which of those you are looking at is part of the tester's job.

A source-to-target mapping (STM) 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. Derive tests rule by rule: direct moves get value comparisons; derived columns get an independent recomputation; lookups get a join to the reference table plus an orphan check; default rules get cases where the source is NULL or invalid. Then add table-level cases no single mapping row implies — counts, duplicates on the declared grain, referential integrity, and truncation checks where 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 counts — after the filters and joins the spec defines — to target counts, overall and per load date. Duplicate checks: group by the business key and flag keys appearing more than once. Null checks: mandatory columns hold no NULLs and defaulting rules fired. Referential integrity: every fact foreign key resolves to a dimension row, since most warehouses do not enforce constraints. Transformation checks: recompute derived columns independently and compare. Datatype and truncation checks: verify precision, length, and encoding survive the move — VARCHAR(100) into VARCHAR(50) silently truncates in some configurations and errors in others. Each category maps to a reusable SQL pattern interviewers often ask you to write live.

A matching count proves the same number of rows arrived — not that they are the right rows or hold the right values. Failure modes that still pass: 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 altering 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 aggregates (SUM, MIN/MAX, distinct and null counts) compared between source and target, MINUS/EXCEPT on manageable volumes, and independent recomputation of transformed columns. The summary interviewers want: counts prove completeness of volume, not correctness of content.

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, per load rather than once. Then test the stated failure policy: many pipelines route unresolvable keys to a default "unknown" member (often key -1) instead of dropping the row, so verify orphans actually land there and rows are not silently discarded. Late-arriving dimensions are the subtle case: a fact can legitimately arrive before its dimension row, so find out 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, instead of 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 your own CASE expression over source columns and compare it to the target column. For rules too complex to inline, build a small handcrafted dataset with a worked expected output covering every branch and boundary, run it through the pipeline in a test environment, and diff. Prioritize by risk: amounts, keys, and status fields get full-volume checks; descriptive text is 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 exactly once. Updates: the changed row is updated (or versioned under SCD2) and nothing else is touched. Deletes: know whether the spec says hard delete, soft-delete flag, or ignore, and test that behavior explicitly. Unchanged rows: confirm they are not rewritten — counts will not reveal this; compare audit timestamps. Boundaries: a record timestamped exactly at the watermark, where > versus >= either drops or double-processes it. Rerun: execute the same window twice and verify the target is unchanged. Late data: an old event timestamp arriving after its window closed. Seed each case 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: assert each business key has exactly one current-flagged row, catching duplicated and missing current rows alike. No overlaps or gaps: with LAG, compare each row's effective start to the prior row's effective end per key; overlaps double-count facts at join time, gaps drop them. Change detection: update a tracked attribute in the source, run the load, and verify a new version appears, the old one closes with the correct end date, and the current flag moves. Then change a non-tracked attribute and verify no new version is created — over-triggering silently explodes the dimension. Finally, rerun the same load and confirm version counts are stable — all of it reusable SQL that belongs in every regression run of a dimension load.

Classify by 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 — the pipeline question is whether the row should have been rejected or quarantined. Mapping 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 defects: wrong connection, partial file pickup, mis-sequenced jobs. Performance defects: correct output, unacceptable load window. The taxonomy routes fixes to the right owner, stops developers "fixing" code to match bad data, and trending it shows where effort belongs — many spec defects signal a requirements problem, not a coding one.

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; scope is those plus everything downstream. Then a baseline diff: run old and new logic against the same frozen input 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 — versioned and run on every release, so the checks that caught last year's defects keep running. 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 profiles compared source versus target — SUM and MIN/MAX on numerics, COUNT(DISTINCT) on keys, null counts per column. These are single-pass aggregations even at this scale, and a mismatch names the column and partition to chase. Tier three: hashed buckets — hash each row, bucket by key (MOD of a key hash into 1,024 buckets), aggregate hashes per bucket on both sides, and run row-level EXCEPT only on mismatched buckets. Two cautions: hashing must be deterministic across engines, so cast and format consistently first, and reconcile against a consistent snapshot, or in-flight loads create false diffs.

Two sources, two jobs. Masked production subsets give realistic distributions and the messiness synthetic generators miss — but subsetting must be referentially consistent: pull the parent rows for every sampled child, or you manufacture fake orphan defects. Masking must happen before data reaches the test environment and must preserve formats, key uniqueness, and joinability, or half the suite breaks. Synthetic data covers what production lacks: NULLs in every nullable column, max-length strings, invalid values for the reject path, duplicate keys, a record timestamped exactly at the load watermark. Keep synthetic cases versioned with worked expected outputs so they double as regression fixtures. 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, recomputed transformations, SCD structure — is a versioned SQL query with a machine-readable pass condition, not a spreadsheet run by hand. An execution harness: tools in the QuerySurge mould do scheduled cross-database comparison; a pytest- or dbt-test-style runner querying both systems is the lightweight equivalent. Tolerances and severity: some checks hard-fail the pipeline (duplicate keys), others warn within a threshold — encoding that judgment is most of the design work. Integration: run in CI on code changes and post-load in production, so regression and monitoring share one suite. History: persist results per run — a metric drifting toward its threshold is a signal no single run shows. Tools vary; these requirements do not.