Can You Answer These ETL Testing Interview Questions?

ETL testing interviews test more than definitions — they test your ability to think through real data problems, write SQL on the spot, and connect technical work to business impact. This guide walks you through the six most commonly asked questions, with answers that go beyond the textbook.

Pause after each question and try answering before reading. That's how you actually prepare.

Q1. What is ETL testing and why does it matter?

ETL testing validates that data moves correctly from source systems through transformation logic and into the target data warehouse — accurately, completely, and within performance SLAs.

The reason it matters: every downstream dashboard, financial report, and machine learning model depends on this data being correct. A broken transformation doesn't announce itself — it silently delivers wrong numbers to decision-makers. ETL testing is the safety net between raw data and trusted insights.

A strong answer covers three dimensions: data integrity (every record moved correctly), business rule validation (transformations match requirements), and performance (the pipeline completes within its SLA window).

Q2. Full Load vs Incremental Load — how does your testing change?

A Full Load truncates the target table and reloads all data from scratch. Testing focuses on bulk validation: total record count, aggregate comparisons (SUM of revenue), and a MINUS/EXCEPT query to find missing records.

An Incremental Load processes only records that are new or changed since the last run. Testing shifts to the delta: isolate the exact records that should have been processed, verify new inserts appeared, and — if SCD Type 2 is involved — confirm the old record was expired and a new version was created.

-- Find records in source missing from target (EXCEPT query)
SELECT order_id, customer_id, amount
FROM source_orders
EXCEPT
SELECT order_id, customer_id, amount
FROM target_orders;

Q3. Write SQL to find records in source missing from target.

Two approaches — know both:

-- Option 1: EXCEPT (SQL Server/PostgreSQL) or MINUS (Oracle)
SELECT order_id FROM source_orders
EXCEPT
SELECT order_id FROM target_orders;

-- Option 2: LEFT JOIN with NULL filter (works everywhere)
SELECT s.*
FROM source_orders s
LEFT JOIN target_orders t ON s.order_id = t.order_id
WHERE t.order_id IS NULL;

Use EXCEPT for full row comparison. Use LEFT JOIN when you only want to match on a key column or when your database doesn't support EXCEPT.

Q4. Star Schema vs Snowflake Schema — when do you use which?

A Star Schema has a central fact table connected directly to denormalized dimension tables. Fewer joins, faster queries. Best for BI and reporting workloads.

A Snowflake Schema normalizes dimension tables into sub-tables, reducing data redundancy. More joins required, but easier maintenance when dimension data changes. Best when storage efficiency and data integrity outweigh query speed.

In practice: star schema wins for most analytical use cases. Use snowflake when you have deep dimension hierarchies or need to update shared dimension attributes in one place.

Q5. How do you test SCD Type 2?

SCD Type 2 keeps history by adding a new row when a tracked attribute changes. Here's the test sequence:

1. Load initial data, note the surrogate key, start date, and is_active = 1
2. Change a tracked attribute in the source (e.g., customer moves to a new city)
3. Run the ETL job
4. Verify the old row: is_active = 0, end_date is set
5. Verify the new row: new surrogate key, new city, is_active = 1, end_date = NULL

-- Verify only one active record per customer
SELECT customer_id, COUNT(*) AS active_count
FROM dim_customer
WHERE is_active = 1
GROUP BY customer_id
HAVING COUNT(*) > 1;
-- Should return 0 rows

Q6. An ETL job is running 2x over its 5-hour SLA. How do you debug it?

Work systematically — isolate each phase before drawing conclusions:

1. Check ETL logs — most tools log start/end time per stage. Find which step is slow.
2. Profile the Extract — run the source query directly. Is it slow due to a missing index or a full table scan?
3. Profile the Transform — are there expensive joins on large unindexed columns? Row-by-row operations?
4. Profile the Load — does the target table have too many indexes slowing bulk inserts?
5. Collaborate with DBAs — ask them to review query execution plans and identify bottlenecks.

Quick Interview Tips

AN

Written by

Asim Noaman Lodhi

Certified Google Partner · QA Consultant · 12+ Years IT

QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.

4.5 Rating 913+ Students Google Partner 79 Lectures