Top 50 ETL Testing Interview Questions & Answers

You've learned ETL testing concepts, practiced SQL queries, and now there's an interview on your calendar. The difference between candidates who get offers and those who don't usually comes down to one thing: can you explain how you'd actually test a data pipeline, not just what ETL testing is?

This guide covers 50 real ETL testing interview questions organized by difficulty. Each answer explains the concept and, where relevant, includes the SQL or approach an interviewer expects to hear.

Beginner Questions (1-15)

1. What is ETL testing?

ETL testing validates data as it moves through Extract, Transform, and Load stages. You verify that data extracted from source systems is correctly transformed according to business rules and loaded into the target data warehouse without loss, duplication, or corruption.

2. How is ETL testing different from database testing?

Database testing validates a single database (schema, triggers, constraints, CRUD operations). ETL testing validates data movement between systems — checking that transformations, mappings, and business rules are applied correctly across the entire pipeline.

3. What are the different types of ETL testing?

• Data completeness testing — all records made it from source to target
• Data transformation testing — business rules applied correctly
• Data quality testing — no NULLs, duplicates, or invalid formats where they shouldn't be
• Regression testing — changes didn't break existing functionality
• Performance testing — pipeline handles production data volumes
• Incremental load testing — delta loads process only changed records

4. What is a mapping document?

A mapping document (or mapping sheet) defines how source fields map to target fields. It specifies which source column goes to which target column, what transformations apply, data type conversions, default values for NULLs, and any filtering or aggregation rules. This document is your primary test oracle.

5. What is the difference between a full load and an incremental load?

A full load drops and reloads the entire target table every time — simple but slow for large datasets. An incremental load processes only new or changed records since the last run, using timestamps, change data capture (CDC), or comparison logic. Most production systems use incremental loads.

6. What is source-to-target testing?

Source-to-target testing compares data in the source system against data in the target system to verify accuracy. You check row counts, specific field values, data types, and transformation results. It's the most fundamental type of ETL validation.

-- Basic source-to-target row count comparison
SELECT 'Source' AS system, COUNT(*) AS row_count
FROM source_db.orders
UNION ALL
SELECT 'Target', COUNT(*)
FROM warehouse.fact_orders;

7. What is data profiling?

Data profiling is analyzing source data to understand its structure, content, and quality before ETL testing begins. You examine data types, value distributions, NULL percentages, uniqueness, and patterns. Profiling helps you design better test cases because you know what "normal" data looks like.

8. What is a slowly changing dimension (SCD)?

An SCD is a dimension table that handles changes to records over time:

• Type 1 — Overwrite the old value. No history kept.
• Type 2 — Add a new row with version tracking (start/end dates, current flag). Full history preserved.
• Type 3 — Add a column for the previous value. Limited history (usually just one prior value).

9. What is a fact table vs. a dimension table?

A fact table stores measurable business events (sales, orders, transactions) with numeric values you aggregate (SUM, COUNT, AVG). A dimension table stores descriptive context (customer names, product categories, dates) that you use to filter and group facts.

10. What is a surrogate key?

A surrogate key is a system-generated unique identifier (usually an auto-incrementing integer) assigned to each row in a dimension table. Unlike natural keys (like customer_id from the source), surrogate keys are independent of source systems, handle SCD versioning, and avoid issues when source keys overlap across systems.

11. How do you test for duplicate records?

-- Find duplicate records by business key
SELECT customer_id, COUNT(*) AS cnt
FROM warehouse.dim_customer
GROUP BY customer_id
HAVING COUNT(*) > 1;

12. What is a NULL and why does it matter in ETL testing?

NULL represents a missing or unknown value. It matters because NULLs propagate silently: any arithmetic with NULL produces NULL, comparisons with NULL return UNKNOWN (not TRUE or FALSE), and aggregate functions like SUM skip NULLs. An untested NULL in a revenue column can make an entire financial report return NULL instead of $10 million.

13. What is referential integrity testing?

Referential integrity testing verifies that foreign key relationships hold in the target. Every foreign key in a fact table should have a matching primary key in the corresponding dimension table. Orphan records (facts pointing to non-existent dimensions) indicate a load ordering issue or missing data.

-- Find orphan records (orders with no matching customer)
SELECT f.order_id, f.customer_key
FROM warehouse.fact_orders f
LEFT JOIN warehouse.dim_customer d
  ON f.customer_key = d.customer_key
WHERE d.customer_key IS NULL;

14. What is the difference between ETL and ELT?

ETL transforms data before loading it into the target (using a middle-tier tool like Informatica or SSIS). ELT loads raw data into the target first, then transforms it inside the target database (common with cloud warehouses like Snowflake and BigQuery that have powerful compute). Testing ELT means your transformation validation queries run against the target system rather than a staging area.

15. What tools are commonly used for ETL testing?

SQL (universal), Informatica Data Validation, QuerySurge, SSIS (SQL Server Integration Services), Talend, Apache NiFi, and increasingly AI-powered testing tools. The tool choice depends on your organization's ETL stack, but SQL is the constant across all of them.

Intermediate Questions (16-35)

16. How do you validate data transformations?

Compare source values against target values using the mapping document as your oracle. For each transformation rule, write a SQL query that applies the same logic to source data and compares the result against the target. For example, if the rule is "full_name = first_name + ' ' + last_name":

SELECT s.customer_id,
       s.first_name + ' ' + s.last_name AS expected,
       t.full_name AS actual
FROM source_db.customers s
JOIN warehouse.dim_customer t
  ON s.customer_id = t.customer_id
WHERE s.first_name + ' ' + s.last_name != t.full_name;

17. How do you test an incremental load?

1. Run the initial full load and validate
2. Insert, update, and delete records in the source
3. Run the incremental load
4. Verify: new records appeared, updated records changed, deleted records handled correctly (soft delete, hard delete, or SCD Type 2 closure)
5. Verify no duplicate records were created
6. Verify records that didn't change were not touched

18. What is a data quality check you'd run after every ETL load?

• Row count comparison (source vs. target)
• NULL check on NOT NULL columns
• Duplicate check on business keys
• Referential integrity check (foreign keys match primary keys)
• Date range validation (no future dates where they shouldn't exist)
• Aggregate comparison (SUM of revenue in source vs. target)

19. How do you handle rejected records during ETL?

Rejected records should be logged to a reject/error table with the rejection reason, source record data, timestamp, and ETL job ID. You test this by intentionally feeding bad data (NULLs in required fields, invalid data types, duplicate keys) and verifying that: (a) bad records land in the reject table, (b) good records still load successfully, (c) reject counts match expectations.

20. What is a star schema?

A star schema has a central fact table connected to multiple dimension tables via foreign keys. It's called "star" because the diagram looks like a star with the fact table in the center. It's the most common data warehouse design because it's simple to query and performs well for analytical workloads.

21. What is a snowflake schema and how does it differ from star schema?

A snowflake schema normalizes dimension tables into sub-dimensions. For example, instead of one dim_product with a category column, you'd have dim_product linked to a separate dim_category table. It reduces data redundancy but makes queries more complex (more JOINs). Star schemas are more common in practice.

22. How do you test SCD Type 2 implementation?

1. Load initial data and note the surrogate keys, start dates, and current flags
2. Change a tracked attribute in the source (e.g., customer address)
3. Run the ETL and verify: old row's end_date is set, old row's current_flag = 'N', new row inserted with new values, new start_date, current_flag = 'Y', and a new surrogate key
4. Verify the business key count is still correct (same customer, two versions)

23. What is boundary value testing in ETL?

Test data at the edges of valid ranges: maximum string lengths, minimum/maximum numeric values, earliest/latest dates, empty strings vs. NULLs, zero values, and negative numbers. Boundary conditions are where ETL bugs hide — a VARCHAR(50) mapped to VARCHAR(30) silently truncates data past the boundary.

24. How do you test data type conversions?

Verify that source data types convert correctly to target data types. Common issues: decimal precision loss (FLOAT to DECIMAL), date format changes (MM/DD/YYYY to YYYY-MM-DD), string truncation (longer source to shorter target), and implicit conversions that change values (string "007" becoming integer 7).

25. What is negative testing in ETL?

Feeding invalid or unexpected data into the ETL pipeline to verify it handles errors correctly. Examples: NULL values in required fields, strings in numeric fields, dates in invalid formats, records that violate foreign key constraints, and extremely large files. The pipeline should either reject these gracefully or transform them according to error-handling rules — never load them silently as bad data.

26. How do you validate aggregate transformations?

-- Verify SUM aggregation: daily revenue summary
SELECT order_date,
       SUM(amount) AS expected_total
FROM source_db.orders
GROUP BY order_date

EXCEPT

SELECT order_date, daily_total
FROM warehouse.daily_revenue_summary;

If the EXCEPT returns no rows, the aggregations match.

27. What is metadata testing?

Metadata testing validates that table structures, column names, data types, constraints, and indexes in the target match the design specification. You check that column names match the mapping document, data types are correct, NOT NULL constraints are in place, and primary/foreign keys are defined.

28. How do you test ETL performance?

Measure execution time against production-scale data volumes. Track: total runtime, rows processed per second, memory and CPU usage, and whether the job completes within the batch window. Compare against the SLA (e.g., "must complete in under 2 hours"). If it fails, identify bottlenecks — typically slow queries, missing indexes, or network latency between source and target.

29. What is a staging area and why is it used?

A staging area is an intermediate database where source data lands before transformation. It serves as a snapshot of source data at extraction time, enables comparison between source and transformed data, allows re-processing without re-extracting, and isolates the source system from transformation load.

30. How do you test character encoding?

Insert source records with special characters (accents: Renee, umlauts: Muller, CJK characters, emojis) and verify they survive the ETL process intact. Common failures: UTF-8 to Latin-1 conversion drops non-ASCII characters, or special characters become question marks or garbled text.

31. What is a lookup transformation and how do you test it?

A lookup transformation enriches data by fetching related values from a reference table (e.g., replacing a country code with a country name). Test by verifying: correct values are returned for matching keys, unmatched keys are handled (NULL, default value, or rejection), and performance is acceptable with large lookup tables.

32. How do you test date/timezone conversions?

Insert records with timestamps in different timezones and verify the target stores them in the expected timezone (usually UTC). Pay attention to daylight saving time transitions, dates near midnight (which might shift to a different day), and the difference between date-only and datetime fields.

33. What is regression testing in ETL and when do you do it?

Regression testing re-runs existing test cases after any ETL change (new column, modified transformation, performance optimization) to confirm nothing broke. You do it after every code change, schema change, or environment change. This is where maintaining a library of reusable SQL validation queries pays off.

34. How do you test ETL error handling and recovery?

Simulate failures at each stage: source connection failure, transformation error (divide by zero, invalid cast), target constraint violation, and mid-job crash. Verify: the error is logged with actionable detail, partial loads are rolled back or clearly identified, the job can be restarted without duplicating data, and alerts are triggered.

35. What is the difference between verification and validation in ETL?

Verification checks that the ETL process was built correctly — does the code match the mapping document? Validation checks that the output is correct — does the target data match what the business expects? Both are necessary. A pipeline can be built exactly to spec (verified) but produce wrong results because the spec itself was wrong (not validated).

Advanced Questions (36-50)

36. How would you design an ETL test strategy for a data migration project?

1. Pre-migration: Profile source data, document baseline metrics (row counts, checksums, aggregates)
2. Mapping validation: Verify every source-to-target mapping rule with sample data
3. Data completeness: Reconcile row counts across all tables
4. Data accuracy: Sample validation on critical fields (financials, dates, keys)
5. Business rule validation: Verify transformations, defaults, and derivations
6. Referential integrity: All foreign keys resolve
7. UAT support: Help business users validate reports against legacy system
8. Cutover testing: Simulate the production migration window end-to-end

37. How do you handle testing when source data changes frequently?

Use a snapshot approach: capture source data at a known point in time, run ETL against that snapshot, and validate against the same snapshot. This eliminates "moving target" problems. For continuous/streaming ETL, use watermarks or sequence numbers to define a test window and validate data within that window.

38. How do you test ETL pipelines that merge data from multiple sources?

Test each source independently first (completeness, quality), then test the merge logic: correct join type, deduplication rules, conflict resolution (which source wins when values disagree), and that the combined record count is correct. Pay special attention to records that exist in one source but not others.

39. What is data reconciliation and how do you automate it?

Data reconciliation compares aggregate metrics between source and target: row counts, sums, min/max values, and distinct counts. Automate it by storing expected metrics in a control table, running validation queries after each ETL load, and comparing actual vs. expected with tolerance thresholds. Alert when discrepancies exceed the threshold.

40. How do you test a real-time or near-real-time ETL pipeline?

Verify latency (data appears in target within the SLA window), completeness (no messages dropped), ordering (events processed in correct sequence), idempotency (re-processing a message doesn't create duplicates), and exactly-once semantics if required. Use controlled test events with known timestamps to measure end-to-end latency.

41. Scenario: A daily ETL job loaded 1 million rows yesterday, but today it loaded only 10 rows. How do you investigate?

Check: (1) Was the source data actually low today, or is the extraction filter wrong? (2) Did the incremental load logic use the wrong watermark/timestamp? (3) Were most rows rejected — check the reject/error table. (4) Did the source connection fail partially? (5) Check ETL job logs for errors or warnings. The key is distinguishing between "the source genuinely had fewer records" and "the pipeline missed records."

42. Scenario: Business users report that a revenue dashboard shows $0 for last month. Where do you start?

Work backwards: (1) Check the target fact table — are there rows for last month? (2) If rows exist, check if the revenue column is NULL or zero. (3) If no rows, check the staging area — did data arrive? (4) If staging is empty, check the source system. (5) Check the ETL job log — did the job run? Did it complete? (6) Check the date filter logic — is "last month" calculated correctly? This systematic approach isolates whether the problem is in the source, ETL, or reporting layer.

43. How do you test ETL in a cloud data warehouse (Snowflake, BigQuery, Redshift)?

The testing principles are the same, but the tooling differs. Use the warehouse's native SQL for validation queries. Leverage features like TIME TRAVEL (Snowflake) to compare data before and after loads. Test warehouse-specific behaviors: clustering key effectiveness, materialized view refresh, cross-region replication latency, and cost implications of full-table scans during testing.

44. How do you test data masking or anonymization in ETL?

Verify: (1) Sensitive fields are masked/anonymized in the target (no PII visible). (2) Masking is consistent — the same source value always produces the same masked value (for referential integrity). (3) Masked data is still realistic enough for downstream use (correct format, valid ranges). (4) Non-sensitive fields are not affected. (5) Masking cannot be reversed.

45. What is a checksum and how is it used in ETL testing?

A checksum is a hash value computed from data content. Generate checksums on source and target datasets — if they match, the data is identical. Useful for validating large datasets quickly without comparing every row. You can compute checksums at the table level, partition level, or row level depending on the granularity you need.

46. How do you test ETL with very large datasets (billions of rows)?

Full row-by-row comparison is impractical. Use: (1) Aggregate validation (row counts, sums, checksums). (2) Statistical sampling — validate a random sample of N rows in detail. (3) Automated reconciliation queries that run in parallel. (4) Partitioned testing — validate one partition/date range at a time. (5) Data quality rules that scan for anomalies rather than comparing every value.

47. How do you create a reusable ETL test framework?

Build a library of parameterized validation queries: row count comparison (pass source table, target table), duplicate check (pass table, key columns), NULL check (pass table, column list), and referential integrity check (pass fact table, dimension table, key columns). Store these in version control. Run them as a test suite after every ETL deployment. Add new tests as you find new bugs.

48. What metrics do you track for ETL testing?

• Defect density — defects found per table or per mapping rule
• Test coverage — percentage of mapping rules with test cases
• Data accuracy rate — percentage of records matching source-to-target
• ETL execution time — trending over time to catch performance degradation
• Reject rate — percentage of records landing in error tables
• Regression pass rate — percentage of regression tests passing after changes

49. How is AI changing ETL testing?

AI agents can auto-generate validation queries from mapping documents, profile data and flag anomalies automatically, detect data drift over time, and suggest test cases for edge conditions humans might miss. The tester's role shifts from writing repetitive SQL to designing test strategy, interpreting results, and making judgment calls on whether anomalies are bugs or valid edge cases.

50. What advice would you give someone starting their ETL testing career?

Master SQL first — it's 90% of the job. Learn JOINs, GROUP BY, window functions, and subqueries until they're second nature. Understand data warehouse concepts — star schema, slowly changing dimensions, fact vs. dimension tables. Think like a data detective — always ask "what could go wrong?" and "how would I prove the data is correct?" Build a query library — save every validation query you write. Learn one ETL tool — SSIS, Informatica, or Talend, depending on your target market.

Quick Reference Cheat Sheet

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