Resource library

QA Interview

ETL and Data Warehouse Testing Interview Questions

ETL testing interview questions with sample answers on source-to-target mapping, reconciliation, transformation logic, SCD types, and data validation SQL.

2,196 words | Article schema | FAQ schema | Breadcrumb schema

Overview

ETL testing interviews live and die on precision. There is no UI to click through and no green checkmark to trust. You are proving that millions of rows moved from source systems into a warehouse without loss, corruption, or silent transformation errors, and interviewers ask questions that reveal whether you can actually catch a mismatch buried in a nightly load. This guide gives you answers backed by real validation SQL.

It is written for ETL testers and data quality engineers who validate pipelines feeding a data warehouse or lakehouse. Each question comes with a sample answer plus the specific check you would run, because in data testing the credible signal is a concrete query, not a description of one.

Focus your preparation on source-to-target mapping, transformation validation, and slowly changing dimensions. Those three areas produce the majority of real ETL defects, and they are where an interviewer can tell in one follow-up question whether you have validated a production pipeline or only read about it.

What ETL Testing Really Verifies

Open by framing the job. ETL testing verifies that data is Extracted from source systems, Transformed according to business rules, and Loaded into target tables completely and correctly. Unlike application testing, the product is the data itself, so you validate completeness (nothing lost), correctness (rules applied right), integrity (relationships intact), and quality (no duplicates, nulls, or bad formats), typically by comparing source and target with SQL.

A strong framing answer: 'I treat the mapping document as my requirements. Every column has a source, a transformation rule, and a target, and my job is to prove each one at scale. I validate counts, values, transformations, and referential integrity, and I care as much about the rows that should not have moved as the ones that should.' That signals you test against a spec, not vibes.

Source-To-Target Mapping Validation

The source-to-target mapping (STTM) document is the contract, and interviewers expect you to test against it column by column. It defines each target column, its source column, the transformation applied, and rules for data type, length, nullability, and defaults. Your job is to confirm the pipeline honors every row of that document.

The canonical completeness check is a minus or except comparison in both directions plus a count check. If `SELECT ... FROM source EXCEPT SELECT ... FROM target` returns rows, data was lost or altered, and the reverse direction catches extra or duplicated rows. A record count reconciliation confirms volume: source count adjusted for documented filters should equal target count. Mentioning that you run the minus both ways is a small detail that marks real experience.

Validate every column against the STTM: source, transformation, type, length, nullability, default.
Completeness: `source EXCEPT target` and `target EXCEPT source` should both return zero rows.
Count reconciliation: source count minus documented filters equals target count.
Confirm rows that should be filtered out did not load, not only that valid rows did.

Transformation Logic Testing

Straight column moves are easy. The bugs live in transformations: concatenations, lookups, derived columns, aggregations, currency or unit conversions, and conditional logic. You cannot trust the ETL tool to have implemented the rule correctly, so you independently recompute the expected value from source data and compare it to the target.

Give a concrete method: 'For a derived column I write a query that reproduces the business rule against the source and compares row by row to the target, flagging any mismatch. For an aggregate like daily revenue per store I recompute the group-by from raw source and diff it against the loaded fact. I pay special attention to rounding, null handling in sums, and the order of operations, since a discount applied before tax versus after tax produces different totals.'

Recompute derived and aggregated values from source; do not trust the tool's output.
Test null handling: nulls in sums, coalesced defaults, and null joins that drop rows.
Check rounding and precision on money and unit conversions explicitly.
Validate conditional logic branches, including the else and edge cases.

Data Quality Dimensions To Check

Interviewers often ask what data quality means concretely, and a vague answer costs you. Name the dimensions and the check for each: completeness (no unexpected nulls in required fields), uniqueness (no duplicate keys), validity (values conform to format and domain, like a valid date or an allowed status code), consistency (the same fact agrees across tables), accuracy (values match the source of truth), and timeliness (data landed within the load window).

The must-know query is duplicate detection: `SELECT key_column, COUNT(*) FROM target GROUP BY key_column HAVING COUNT(*) > 1`. Follow it with a null profile on required columns and a domain check that flags values outside the allowed set. A good answer ties these to gates: 'I run a data-quality profile every load and fail the run if duplicate business keys appear or required fields are null, because those corrupt every downstream report.'

Slowly Changing Dimensions: The Deep-Dive Question

Slowly changing dimensions (SCD) are the classic senior ETL question because they are easy to describe and hard to test. Type 1 overwrites the old value, keeping no history. Type 2 preserves history by inserting a new row with an effective-date range and a current-record flag, keeping the old row closed. Type 3 keeps limited history in an extra column, such as previous value alongside current.

The testing detail is what impresses: 'For a Type 2 dimension I verify that when a source attribute changes, the ETL closes the prior row by setting its end date and current flag to false and inserts a new active row with the correct start date, so there is exactly one current row per business key and no gaps or overlaps in the date ranges. I test the no-change case too, where an unchanged record must not create a spurious new version.' That level of detail is exactly what SCD questions are fishing for.

Type 1: overwrite in place, no history retained.
Type 2: new versioned row with effective dates and a current flag; old row closed.
Type 3: limited history in extra columns (previous and current value).
Type 2 checks: exactly one current row per key, no date gaps or overlaps, unchanged records create no new version.

Referential Integrity And The Star Schema

Warehouse structure drives a set of predictable questions. A star schema has a central fact table (measurable events like sales) surrounded by dimension tables (descriptive context like product, customer, date), joined by surrogate keys. A snowflake schema normalizes those dimensions into sub-tables. You test that every fact row points to valid dimension rows and that no fact references a missing key.

The orphan-key check is the query to have ready: a fact foreign key with no matching dimension row is a defect, found with `SELECT f.product_key FROM fact_sales f LEFT JOIN dim_product d ON f.product_key = d.product_key WHERE d.product_key IS NULL`. Extend the answer: 'I validate surrogate key generation, that late-arriving dimensions are handled with an inferred or unknown member rather than dropping the fact, and that the date dimension is complete so no event falls outside it.'

Incremental Loads, CDC, And Reconciliation

Most real pipelines do not reload everything nightly, so incremental load testing comes up fast. Change Data Capture (CDC) or a high-water-mark column moves only new and changed rows since the last run. This is fertile ground for bugs: missed updates, double-counted rows on a rerun, and records that arrive out of order.

Show you test the hard parts: 'I verify that inserts, updates, and deletes in the source all propagate correctly, that rerunning a failed load is idempotent and does not duplicate rows, and that a record updated twice between loads reflects the final state. I also test the boundary of the high-water mark, since records landing exactly at the cutoff time are where duplicates and misses appear.' Daily reconciliation between source and warehouse totals is the safety net that catches drift the row checks miss.

Verify inserts, updates, and deletes all propagate through the incremental load.
Rerunning a load must be idempotent: no duplicated rows on retry.
Test the high-water-mark boundary where cutoff-time records slip or double.
Run daily total reconciliation between source and warehouse as a drift alarm.

ETL Versus ELT And Modern Data Stacks

You may be asked how modern platforms change your testing. In classic ETL, data is transformed before loading, often in a dedicated tool. In ELT, raw data lands in a cloud warehouse or lakehouse first and transformations run inside it, frequently through SQL models. The validation goals are identical, but in ELT you often test transformation models directly in the warehouse and can add tests as code alongside the models.

A current answer acknowledges the shift: 'The principles do not change: completeness, correctness, integrity, and quality. What changes is where I run the checks. In an ELT stack I write data tests next to the transformation models so they run on every build, assert uniqueness and not-null on keys, and add referential and custom business-rule tests. I still reconcile against the source, because in-warehouse tests do not prove the raw extract was complete.'

Scenario And Behavioral Questions

A frequent scenario: 'A business report shows a number that looks wrong. How do you trace it?' The disciplined answer works backward through the pipeline: confirm the report query, then the warehouse table, then the transformation, then the raw source, isolating at which layer the value first diverges. You reproduce with a specific slice of data rather than arguing about the whole report, and you check for a join dropping rows, a filter, or a transformation as the usual culprits.

Another: 'You have limited time and a huge dataset. How do you validate without comparing every row?' A mature answer balances rigor and speed: 'I run full counts and aggregate reconciliations, which are cheap and catch gross errors, then sample rows for detailed value comparison, weighting the sample toward high-risk transformations and boundary values. I automate the recurring checks so every load is validated, and reserve deep manual investigation for anomalies the automated gates flag.'

Frequently Asked Questions

What is ETL testing?

ETL testing verifies that data is extracted from source systems, transformed by business rules, and loaded into target tables completely and correctly. Because the product is the data itself, you validate completeness, correctness, integrity, and quality, usually by comparing source and target with SQL against a source-to-target mapping document.

How do you validate data completeness in ETL testing?

Compare source and target both ways with a minus or EXCEPT query: source EXCEPT target catches lost or altered rows, and target EXCEPT source catches extra or duplicated rows. Add a record count reconciliation where the source count, adjusted for documented filters, equals the target count. Both must agree for the load to be complete.

What is a Slowly Changing Dimension and how do you test it?

An SCD handles changes to dimension attributes over time. Type 1 overwrites, Type 2 inserts a new versioned row with effective dates and a current flag, and Type 3 keeps limited history in extra columns. For Type 2, verify exactly one current row per business key, correct effective dates with no gaps or overlaps, and that unchanged records create no new version.

How do you check for duplicate records in a data warehouse?

Group by the business or primary key and filter for counts above one: SELECT key_column, COUNT(*) FROM target GROUP BY key_column HAVING COUNT(*) > 1. Any returned rows are duplicates. This should be a standard gate on every load because duplicate keys corrupt downstream aggregations and reports.

What is the difference between ETL and ELT testing?

In ETL, data is transformed before loading, often in a dedicated tool. In ELT, raw data lands in a cloud warehouse first and transformations run inside it with SQL. The validation goals are identical, but in ELT you often test transformation models directly in the warehouse and add tests as code, while still reconciling against the source.

How do you test a star schema data warehouse?

Validate that every fact row references valid dimension rows using a LEFT JOIN that flags foreign keys with no matching dimension. Check surrogate key generation, ensure late-arriving dimensions map to an unknown member rather than dropping the fact, and confirm the date dimension is complete so no event falls outside it.

How do you validate a huge dataset when you cannot compare every row?

Combine cheap full checks with targeted sampling. Run complete row counts and aggregate reconciliations to catch gross errors, then sample rows for detailed value comparison, weighting the sample toward high-risk transformations and boundary values. Automate the recurring checks so every load is gated and reserve manual investigation for flagged anomalies.