ETL Testing MCQ 2 — Questions and Answers
Question 1: Which ETL testing type verifies that data is not lost or duplicated during the extraction phase?
- Reconciliation testing (Correct answer)
- Schema validation testing
- Regression testing
- Performance testing
Correct answer: Reconciliation testing
Reconciliation testing compares row counts and key metrics between source and target to confirm no data loss or duplication during extraction.
Question 2: What is a 'slowly changing dimension' (SCD) Type 2 strategy in ETL?
- Overwrite old data with new values
- Add a new row with effective date ranges to preserve history (Correct answer)
- Delete old records and insert new ones
- Store only the latest five versions of a record
Correct answer: Add a new row with effective date ranges to preserve history
SCD Type 2 inserts a new row for each change with start/end effective dates, preserving the full history of dimension records.
Question 3: During ETL testing, a tester finds the target table has 5% more rows than the source. The most likely root cause is:
- Source system truncation
- Duplicate key handling inserting extras (Correct answer)
- Missing NOT NULL constraint
- Index fragmentation
Correct answer: Duplicate key handling inserting extras
When duplicate key logic is misconfigured, the ETL may insert multiple rows for a single source record instead of updating or ignoring duplicates.
Question 4: Which SQL clause is most commonly used to validate referential integrity between a fact table and a dimension table in ETL testing?
- GROUP BY
- LEFT OUTER JOIN with NULL check (Correct answer)
- UNION ALL
- HAVING
Correct answer: LEFT OUTER JOIN with NULL check
A LEFT OUTER JOIN followed by filtering for NULLs in the dimension key column reveals fact records that have no matching dimension entry.
Question 5: What does 'data profiling' accomplish in the ETL testing process?
- Measures ETL job execution speed
- Analyzes source data for patterns, nulls, and anomalies before transformation (Correct answer)
- Validates target schema column lengths
- Generates test data for staging tables
Correct answer: Analyzes source data for patterns, nulls, and anomalies before transformation
Data profiling examines source data statistics—null rates, value distributions, and outliers—to inform transformation rules and anticipate quality issues.
Question 6: An ETL job fails with 'ORA-01722: invalid number' during a load. What transformation issue does this indicate?
- Target column is too narrow for the string length
- A non-numeric string is being cast to a numeric column (Correct answer)
- Date format mismatch between source and target
- Sequence generator overflow in surrogate key
Correct answer: A non-numeric string is being cast to a numeric column
ORA-01722 occurs when Oracle tries to convert a non-numeric character string into a NUMBER type, indicating a transformation or data quality issue.
Question 7: Which testing technique involves re-running the full ETL process with the same source data to confirm results are identical?
- Boundary testing
- Idempotency testing (Correct answer)
- Smoke testing
- Mutation testing
Correct answer: Idempotency testing
Idempotency testing verifies that running the ETL pipeline multiple times with the same input produces the same output every time, preventing duplicate or inconsistent data.
ETL Testing MCQ 2
Which ETL testing type verifies that data is not lost or duplicated during the extraction phase?