Home › Blog › ETL Testing Interview Questions (2026): 30 Questions With SQL Checks and Answers

ETL Testing Interview Questions (2026): 30 Questions With SQL Checks and Answers

ETL testing interviews check that you can prove data arrived complete and correct: reading a source-to-target mapping, reconciling counts and values, validating transformations, catching duplicates and orphans, checking slowly changing dimensions and incremental loads, and automating the checks. SQL is the main tool, and candidates commonly report writing reconciliation queries live.

ETL testing interviews check that you can prove data arrived complete and correct: reading a source-to-target mapping, reconciling counts and values, validating transformations, catching duplicates and orphans, checking slowly changing dimensions and incremental loads, and automating the checks. SQL is the main tool, and candidates commonly report writing reconciliation queries live. Below are 30 questions by level, five defect scenarios and how interviews test ETL testers.

ETL checks and the SQL behind them

CheckWhat it provesTypical SQL
Count reconciliationNo rows lost or addedSELECT COUNT(*) ... WHERE load_date = :d on source and target
Full compareEvery row arrived with the same valuesEXCEPT (or Oracle's MINUS) in both directions
DuplicatesKeys are uniqueGROUP BY key HAVING COUNT(*) > 1
Mandatory fieldsRequired columns are populatedWHERE col IS NULL
OrphansFacts point at real dimension rowsLEFT JOIN dim ... WHERE dim.key IS NULL
TransformationRules were applied correctlyRecompute the rule from source and compare
SCD Type 2History is correctOne current row per key; no overlapping date ranges

Fundamentals

  1. What is ETL testing, and how is it different from database testing? ETL testing validates data as it is extracted, transformed and loaded into a warehouse or lake for analytics. Database testing checks an application database's CRUD behaviour, constraints and procedures.
  2. ETL or ELT: how does testing change? In ELT, raw data lands first and transformations run as SQL inside the warehouse, so tests also become SQL, often as dbt tests in the same project. See our dbt interview questions.
  3. What is a source-to-target mapping document? The spec listing each target column, its source columns, transformation rule, filters, keys, defaults and data types. Test cases come straight from it, so review it before writing any.
  4. What are the stages of ETL testing? Requirement and mapping review, source profiling, test data preparation, then validation of extraction, transformation and load, report checks, and regression on every change.
  5. What is data profiling, and why do it first? Measuring distinct counts, null rates, ranges, lengths and patterns in the source. It exposes surprises, such as free-text dates or duplicate keys, before you write expected results.
  6. Why isn't a count match enough? Counts can match while values are wrong, truncated or shifted. Add column aggregates, such as sums of amounts per day, and full compares on keys and values.
  7. Write a full compare query. SELECT order_id, amount FROM src.orders EXCEPT SELECT order_id, amount FROM dw.fact_orders; Run it both ways round, since EXCEPT only shows rows missing from the second set.
  8. How do you find duplicates? SELECT order_id, COUNT(*) FROM dw.fact_orders GROUP BY order_id HAVING COUNT(*) > 1; Then trace whether the source, a join or a double load created them.
  9. Which data quality dimensions do you check? Completeness, validity, accuracy, consistency, uniqueness and timeliness. Name which test covers each one.
  10. What are fact and dimension tables? Facts hold measurable events with foreign keys; dimensions describe who, what, where and when. Testing depends on the declared grain; our data warehouse interview questions cover modelling.
  11. What defects do you most often find? Truncated strings, lost decimal precision, time-zone shifts, untrimmed spaces breaking joins, inner joins silently dropping rows, joins multiplying rows, wrong null handling, encoding damage and double loads.

Intermediate

  1. How do you test incremental loads? Check the watermark or change-data-capture logic, late-arriving records, updates versus inserts, deletes or soft deletes, and that rerunning the same window creates no duplicates.
  2. How do you test an SCD Type 2 dimension? One current row per business key (WHERE is_current = 1 GROUP BY customer_id HAVING COUNT(*) > 1 returns nothing), no overlapping validity ranges, a new version when a tracked attribute changes, and untouched rows left alone.
  3. How do you validate a transformation rule? Recompute it from source in SQL, for example a currency conversion joined to the rate table for that date, and compare with the target, including nulls and rounding.
  4. How do you test referential integrity? SELECT f.customer_key FROM dw.fact_orders f LEFT JOIN dw.dim_customer d ON f.customer_key = d.customer_key WHERE d.customer_key IS NULL; Late-arriving dimensions should map to an agreed unknown member, not vanish.
  5. How do you test reports built on the warehouse? Reconcile report totals with warehouse queries for the same filters, then with source totals, and check rounding, currency and time-zone rules.
  6. How do you test rejected rows? Confirm rejects land in an error table with a reason, and that source rows equal loaded plus rejected plus intentionally filtered rows.
  7. What is restartability, and how do you test it? A failed load must resume or rerun without duplicates or gaps. Kill a job midway, rerun it and reconcile.
  8. How do you performance-test an ETL job? Run production-sized volumes, measure against the load window, watch for full scans and skewed partitions, and compare with a baseline after each change.
  9. What test data do you use? Masked production-like data for volume and variety, plus small synthetic sets with known expected outputs for edge cases such as nulls, extreme values, duplicates and Unicode.
  10. How do you validate target metadata? Compare column names, types, lengths and nullability with the mapping, for example from INFORMATION_SCHEMA.COLUMNS, and confirm constraints and indexes exist.

Experienced and senior

  1. How do you automate ETL tests? Store reconciliation and rule queries as versioned tests that run in CI on every pipeline change and after each scheduled load. dbt ships unique, not_null, accepted_values and relationships tests, and a test passes when its query returns zero failing rows.
  2. How do you compare very large tables? Compare aggregates and hashes per partition, such as row counts, sums and a hash over key columns per day, then drill into only the partitions that differ.
  3. What do you monitor in production? Freshness, volume anomalies, schema drift, null-rate jumps and reconciliation results, with alerts routed to the owning team.
  4. How do you test an ETL migration to a new tool or cloud? Run old and new pipelines in parallel for several cycles, reconcile counts, sums and hashes, agree sign-off criteria up front and track differences to closure. Tool-specific detail: our Informatica interview questions.
  5. How do you prioritize with little time? Risk first: money fields, keys, regulatory and executive reports, and the transformations that changed. Write down what you didn't test.
  6. A defect appears in a report. Is it the source, the mapping or the code? Pick one sample record and trace it through each layer with lineage, comparing values at every hop. The first hop where it changes unexpectedly is the culprit.
  7. How do you test time zones and dates? Confirm storage in UTC or a documented zone, test daylight-saving boundaries and month ends, and check the date dimension key matches the business calendar.
  8. How do you handle late-arriving facts? Verify they join to the dimension version valid at the event time, and that reprocessing windows are long enough to catch them.
  9. Which metrics show ETL QA is working? Defects found before release against after, coverage of mapping rules, reconciliation pass rate and freshness against agreed service levels.

Five defect scenarios interviewers give

How interviews test ETL testing

Employers don't publish question banks, but candidates commonly report live SQL (joins, GROUP BY, window functions and set operators), a walkthrough of a mapping document, defect scenarios like those above and questions on the ETL tool the team uses. Practice the SQL with our SQL interview questions.

Where CoPilot Interview fits

CoPilot Interview is an AI interview assistant whose free AI mock interview builds ETL testing and data QA questions from your résumé and the job description, reads them aloud and scores each spoken answer out of 10. The browser demo at /try has 145 practice questions in 10 tracks, scored out of 10, with no signup. The free plan gives 15 minutes a day of live help in sessions of up to 15 minutes, with AI mock interviews included; Standard costs $24.99 a month and Pro $39.99. For data roles, see our guide to practicing data engineer interviews with AI. Live coding tasks, take-homes and online assessments usually forbid outside help, so prepare for those; in any live interview, follow your employer's rules.

Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly.

Frequently asked questions

What SQL is asked in ETL testing interviews?

Joins, especially LEFT JOIN for orphans; GROUP BY with HAVING for duplicates; EXCEPT or MINUS for compares; window functions such as ROW_NUMBER for deduplication and SCD checks; and aggregates for reconciliation.

Is ETL testing manual or automated?

Both. Exploratory checks and mapping reviews are manual; reconciliation and rule checks should become automated SQL tests that run on every load.

What is the difference between ETL testing and data warehouse testing?

ETL testing focuses on the pipelines that move and transform data; data warehouse testing also covers the model, reports and performance of the warehouse itself.

Which tools do ETL testers use?

SQL clients first, then the ETL tool itself, dbt tests in ELT stacks, and dedicated data validation or observability tools where the team has them.

How do I prepare for an ETL testing interview?

Write the queries in the table above against a sample star schema, practice explaining an SCD Type 2 test, and prepare one story about a data defect you caught and traced.

Practice this interview before the real one

The AI mock interview builds questions from your resume and the job description, reads them aloud and scores each spoken answer out of 10. Included on the free plan.

Start a mock interview