ETL Testing Basic 5 — Questions and Answers
Question 1: In ETL testing, 'data completeness' testing ensures that:
- All ETL jobs complete within the scheduled time window
- Every expected record from the source is present in the target with no missing rows or fields (Correct answer)
- Target table indexes are rebuilt after each load
- Transformation rules cover every possible input data type
Correct answer: Every expected record from the source is present in the target with no missing rows or fields
Completeness testing checks that no records were silently dropped during extraction, transformation, or loading by comparing source and target counts and key values.
Question 2: Checksum testing in ETL is used to:
- Measure the time taken by each transformation step
- Detect unauthorized changes to source data between extraction cycles
- Verify data integrity by computing hash or sum values on source columns and comparing them to the target (Correct answer)
- Validate that ETL tool license keys are current
Correct answer: Verify data integrity by computing hash or sum values on source columns and comparing them to the target
A checksum (e.g., MD5 hash or column SUM) computed on source data and re-computed on the target catches any corruption or alteration introduced during ETL.
Question 3: Which ETL testing type verifies that a newly deployed ETL version produces results identical to the previous version for the same input data?
- User acceptance testing (UAT)
- Regression testing (Correct answer)
- Integration testing
- Volume testing
Correct answer: Regression testing
ETL regression testing reruns old input through the new ETL version and diffs the output against the previously validated baseline to catch unintended changes.
Question 4: In ETL, 'idempotency' means that:
- The ETL job runs faster on each successive execution
- Running the same ETL job multiple times produces the same target state as running it once (Correct answer)
- The source data is always identical to the target data
- ETL jobs are scheduled to run simultaneously in parallel
Correct answer: Running the same ETL job multiple times produces the same target state as running it once
An idempotent ETL can be safely re-run (e.g., after a failure) without duplicating data or corrupting the target.
Question 5: Lookup transformation testing in ETL focuses on verifying that:
- The ETL job can read from more than one source simultaneously
- Reference data lookups return the correct mapped values and handle unmatched keys according to the specified default or reject rule (Correct answer)
- The target table primary key is auto-incremented correctly
- Aggregate functions produce non-null results
Correct answer: Reference data lookups return the correct mapped values and handle unmatched keys according to the specified default or reject rule
Lookup tests supply known input keys and confirm that the correct reference value is returned, and that missing keys are handled as specified (default, NULL, or reject).
Question 6: What does 'parallel testing' refer to in ETL project delivery?
- Running multiple ETL jobs at the same time to improve throughput
- Running the legacy system and the new ETL-fed system simultaneously and comparing their outputs to validate the new system before cutover (Correct answer)
- Testing ETL performance by splitting data across multiple servers
- Executing unit tests and integration tests at the same time
Correct answer: Running the legacy system and the new ETL-fed system simultaneously and comparing their outputs to validate the new system before cutover
Parallel (or parallel-run) testing operates both the old and new pipelines on live data simultaneously to confirm the new system matches the old before the old is decommissioned.
Question 7: When testing an ETL pipeline that uses a SQL MERGE statement for loading, which scenario is most critical to include in the test plan?
- A source record that has never existed in the target (INSERT path)
- A source record whose key already exists in the target with changed attributes (UPDATE path)
- A source record whose key already exists in the target with no attribute changes (no-op path)
- All of the above scenarios must be tested to fully validate MERGE behavior (Correct answer)
Correct answer: All of the above scenarios must be tested to fully validate MERGE behavior
A MERGE has at least three branches — INSERT, UPDATE, and matched-but-unchanged — and each must be tested independently to ensure the correct action is taken.
ETL Testing Basic 5
In ETL testing, 'data completeness' testing ensures that: