ETL Testing Interview Questions and Answers

ETL testing checks that data is pulled from the sources correctly. It also checks that the business rules are applied and that all the data reaches the target. The target is usually a data warehouse or a data lake.

It is needed because:

• Silent data errors (missing rows, wrong joins, cut-off text) do not crash anything. They still give wrong reports and wrong decisions.
• The transformation logic is complex and changes often.
• Data volumes and sources keep changing, so manual spot checks miss problems.

Main checks: record counts, data completeness, transformation correctness, data type and format, duplicates, referential integrity (keys match between tables), load speed, and error handling. The test basis is the source-to-target mapping document and the business rules.

What they are checking: A clear definition linked to business impact.

A typical flow:

1. Understand requirements: mapping document, business rules, source and target schemas, and how often the load runs.
2. Plan and design tests: scope, test cases for each rule, test data, environments, and entry/exit criteria.
3. Prepare test data: normal, boundary, null, duplicate and invalid records.
4. Source checks: profile the source data. Confirm counts and quality before the run.
5. Run the ETL and check the logs, rejected rows and job status.
6. Validate: counts, source-to-target comparison, transformation logic, duplicates, integrity, data types and totals.
7. Report defects with the query, sample rows, and expected vs actual result.
8. Retest and regression: after each fix and each release. Include incremental runs and re-runs.
9. Sign-off: a summary of coverage and open risks.

What they are checking: Can you explain a process that can be repeated?

Group by customer. Then filter the groups with HAVING. Do not use WHERE, because it filters rows before grouping.

SELECT c.customer_id,
       c.name,
       COUNT(o.order_id)   AS order_count,
       SUM(o.amount)       AS total_amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
HAVING COUNT(o.order_id) > 1
ORDER BY total_amount DESC;

Points to say aloud:

• JOIN (inner) drops customers with no orders. Use LEFT JOIN if you also need them with a zero count.
• Every selected column that is not aggregated must be in GROUP BY.
• COUNT(o.order_id) ignores NULLs. COUNT(*) counts all rows.

What they are checking: This is the basic live SQL task that many candidates fail.

Six common dimensions:

Automate them as SQL checks or rules in a tool (Great Expectations, dbt tests). Run them on every load, with thresholds and alerts.

What they are checking: Do you know the terms, and can you turn them into tests?

Run count queries on both sides. Use the same filters that the ETL applies.

-- source
SELECT COUNT(*) FROM src.orders WHERE order_date >= DATE '2026-01-01';
-- target
SELECT COUNT(*) FROM dw.fact_orders WHERE order_date >= DATE '2026-01-01';

If the counts differ, investigate:

• Missing rows: find them with EXCEPT / LEFT JOIN ... WHERE t.key IS NULL.
• Extra rows: duplicates from a bad join, or rows not removed after a delete in the source.
• Legitimate filtering: rejects, deduplication, status filters. Check the reject table and the mapping rules.
• Timing: the source changed after the extract. Compare at the same cut-off time.

A matching count is needed but not enough. It can hide one missing row and one duplicated row.

What they are checking: Do you understand the limits of count checks?

Group by the columns that define a duplicate. Keep the groups with more than one row.

SELECT email, COUNT(*) AS cnt
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

To see the full duplicate rows, use a window function:

SELECT *
FROM (
  SELECT c.*,
         COUNT(*) OVER (PARTITION BY email) AS cnt
  FROM customers c
) t
WHERE cnt > 1
ORDER BY email;

Say what counts as a duplicate: the primary key, or a business key (email, or customer_id + order_date). A table can have no duplicate primary keys and still have duplicate business records with different surrogate ids (system-made ids).

What they are checking: The live query that candidates fail most often.

Test for NULLs with IS NULL / IS NOT NULL. = NULL never matches.

SELECT COUNT(*) FROM dw.dim_customer WHERE email IS NULL;

Behaviour to know:

• Any comparison with NULL gives unknown. So col <> 'X' leaves out NULL rows too.
• COUNT(col) skips NULLs. COUNT(*) counts all rows.
• SUM and AVG ignore NULLs. AVG divides by the count of non-NULL values.
• NULL = NULL is not true. Use IS NOT DISTINCT FROM or COALESCE when you compare source and target.
• NOT IN with a subquery that returns a NULL returns no rows. Prefer NOT EXISTS.

Check against the mapping. Mandatory columns must have zero NULLs. Optional columns should show NULL (not an empty string or 'N/A') exactly as the rule says.

What they are checking: Do you know the classic SQL traps?

Two standard SQL approaches. Both use the business key.

-- Anti-join
SELECT s.order_id
FROM src.orders s
LEFT JOIN dw.fact_orders t ON t.order_id = s.order_id
WHERE t.order_id IS NULL;

-- Set difference (EXCEPT in PostgreSQL/SQL Server, MINUS in Oracle)
SELECT order_id FROM src.orders
EXCEPT
SELECT order_id FROM dw.fact_orders;

Swap the tables to find extra rows in the target. If the databases are on different servers, load the keys into a staging table. Or use a comparison tool or script (pandas merge with indicator=True). Check the reject table before you raise a defect. The row may have been rejected on purpose.

What they are checking: A basic reconciliation query.

SCDs describe how a dimension handles changes in its attributes over time.

Check that what users see is right, from the data to the display.

• INNER JOIN: only rows that match on both sides.
• LEFT JOIN: all rows from the left table, and the matched rows from the right (NULLs where there is no match).
• FULL OUTER JOIN: all rows from both tables, with NULLs where there is no match.

Both move data into an analytics store. They differ in where the transformation happens.

Common categories:

• Source-to-target (data completeness) testing: all expected records arrive.
• Data transformation testing: business rules are applied correctly.
• Data quality testing: duplicates, nulls, formats, ranges.
• Data integrity / referential testing: keys and relationships stay correct. -...

Boundary testing applies boundary value analysis to data. Test the minimum, the maximum, and values just inside and just outside the allowed range.

• Full load: wipe and reload the whole dataset each run. It is simple and consistent. It gets slow and heavy as data grows.
• Incremental (delta) load: load only the records inserted or changed since the last run. It uses timestamps, version numbers, or change data capture.

Common data defects:

• Missing or extra records, and duplicates
• Wrong transformation (calculation, mapping, case, trimming)
• Cut-off text, rounding and data type conversion errors
• Wrong NULL/default handling
• Referential integrity breaks and orphan keys
• Date and time zone mistakes
• Wrong history handling (SCD) or gaps in incremental loads
• Rejected records not logged
• Slow performance and job failures

Two common live tasks.

Compare the metadata with the mapping document. Do not just look at it by eye.

• Database (OLTP): normalised, transactional, current data. Test CRUD, constraints and transactions.
• Data warehouse (OLAP): joined, historical data, modelled (facts and dimensions) for analytics. Test ETL correctness, history (SCD), totals, query speed and report accuracy.
• Data lake: raw files of any...

Compare the source and target at several levels, from cheap to deep.

1. Counts: row counts per table and per partition or date.
2. Totals: SUM, MIN, MAX, AVG on number columns. Distinct counts on keys.
3. Set comparison: find missing and extra rows with EXCEPT/MINUS or an anti-join, on the business key.
4. Column-level comparison: join on the key. Apply the documented transformation, then compare each mapped column.
5. Hash/checksum comparison for wide tables.
6. Sampling with business review for complex rules.

-- rows in source but not in target
SELECT order_id, customer_id, amount FROM src.orders
EXCEPT
SELECT order_id, customer_id, amount FROM dw.fact_orders;

Run it in both directions. Allow for known differences in the expected result, such as rejected rows, filtered statuses and type casts. Otherwise the comparison has too much noise.

What they are checking: A layered method, not just 'compare counts'.

Rule: 'customer_tier is GOLD when lifetime spend is at least 10000, SILVER at least 5000, otherwise BRONZE. NULL spend counts as 0.'

Approach: work out the expected value on your own from the source. Then compare it with the target.

WITH expected AS (
  SELECT customer_id,
         CASE WHEN COALESCE(SUM(amount), 0) >= 10000 THEN 'GOLD'
              WHEN COALESCE(SUM(amount), 0) >= 5000  THEN 'SILVER'
              ELSE 'BRONZE' END AS tier
  FROM src.orders GROUP BY customer_id
)
SELECT e.customer_id, e.tier AS expected, d.customer_tier AS actual
FROM expected e
JOIN dw.dim_customer d ON d.customer_id = e.customer_id
WHERE d.customer_tier <> e.tier;

Zero rows means the rule holds. Add test data at the boundaries (4999.99, 5000, 9999.99, 10000, NULL) and negative cases. Never copy the developer's SQL as the expected result. Write the expectation from the requirement.

What they are checking: An independent expected result, and boundary test design.

Prepare a changed record in the source. Run the load. Then check the old and new rows.

Use pandas or SQLAlchemy with pytest for repeatable checks. Run them in CI after each load.

Compute a hash of each row's relevant columns on both sides and compare them. This is faster than comparing every column by hand.

Number the rows within each key, newest first. Then delete everything except row 1.

Check that every foreign key in a fact table points to a dimension row that exists. Also check that unknown values are handled by design.

Data reconciliation means confirming that the data in two systems or layers agrees for the same business scope. You must also explain every difference.

Total both sides by day and join them. Flag any difference.

An idempotent pipeline gives the same final result whether a run executes once or many times for the same input. This makes retries and backfills (reloading old data) safe.

Feed in bad data on purpose. Check that the pipeline behaves as specified.

Great Expectations (GX) is an open-source Python framework. You write data quality checks, called Expectations, and run them. It gives readable validation reports.

dbt tests are SQL checks that run with dbt test. A test passes when its query returns zero rows.

Generic tests are declared in YAML:

You cannot assert an exact string. So test the properties of the output and measure quality with statistics.

Check that sensitive data is protected wherever it is stored, moved or shown.

Check the format with a pattern. Check validity against the official reference table.

Format check (ICD-10-CM codes look like E11.9: a letter, two characters, an optional dot and up to four more):

An incremental load processes only the records that are new or changed since the last run. It usually uses a watermark (a timestamp or id) or change data capture.

Trace the number backwards, layer by layer. Split the problem in half each time.

Matching counts only prove the number of rows. Move to row-level and column-level comparison.

Find what is duplicated, then find out why.

1. Define the duplicate. Is it the same business key, or a whole-row copy?

A green job does not prove the data is correct. Work outward from the symptom.

Migration testing checks that data moved from an old system to a new one is complete, correct and usable.

This is schema drift. Approach:

Do not pull everything into one place. Compare summaries first. Look closely only where they differ.

Timeliness: data must be available by the SLA time.

• Check the latest load time and compare it with now. Alert if the lag is above the limit.
• Test the job's start and end time against the time window with realistic volume.

You need a stable snapshot that you can compare against.