ETL Testing MCQ 3 ā Questions and Answers
Question 1: What is the primary purpose of a 'staging area' in an ETL pipeline?
- To serve end-user BI queries directly
- To temporarily hold raw extracted data before transformation (Correct answer)
- To archive historical data for compliance
- To cache transformed data for faster loads
Correct answer: To temporarily hold raw extracted data before transformation
The staging area acts as a temporary buffer where raw source data lands so transformations can be applied without affecting source systems.
Question 2: During ETL testing, which check specifically validates that currency values are converted correctly between EUR and USD?
- Null constraint check
- Data transformation accuracy check (Correct answer)
- Referential integrity check
- Schema compatibility check
Correct answer: Data transformation accuracy check
Data transformation accuracy checks verify that business rulesāincluding currency conversions, rounding, and exchange rate applicationāproduce correct target values.
Question 3: A tester notices that dates stored as '08/14/2026' in the source appear as '2026-08-14' in the target. This is an example of:
- Data loss
- Data type conversion failure
- Data format transformation (Correct answer)
- Referential integrity violation
Correct answer: Data format transformation
Converting date representations from MM/DD/YYYY to ISO 8601 format (YYYY-MM-DD) is a standard data format transformation applied during the ETL process.
Question 4: Which ETL testing scenario best tests the 'reject' or 'error' handling logic of a pipeline?
- Loading a full valid dataset
- Injecting records that violate business rules or constraints (Correct answer)
- Running the ETL with an empty source table
- Comparing row counts only
Correct answer: Injecting records that violate business rules or constraints
Injecting intentionally bad recordsānulls in required fields, out-of-range values, foreign key violationsāconfirms the ETL correctly routes them to an error or reject table.
Question 5: What is 'delta extraction' in ETL?
- Extracting all rows from the source every time
- Extracting only records that have changed since the last ETL run (Correct answer)
- Extracting data from multiple sources simultaneously
- Extracting a random sample for testing purposes
Correct answer: Extracting only records that have changed since the last ETL run
Delta (incremental) extraction captures only new or modified records since the previous run, using timestamps, change data capture, or watermarks to minimize load.
Question 6: When testing an ETL pipeline's lookup transformation, what scenario should a tester specifically validate?
- That the lookup runs faster than a JOIN
- Correct behavior when no matching lookup key is found (no-match handling) (Correct answer)
- That the staging table has an index
- Row count parity between source and target
Correct answer: Correct behavior when no matching lookup key is found (no-match handling)
A lookup with no matching record can silently drop rows, substitute NULL, or default-route them; testers must confirm the configured no-match behavior is correct.
Question 7: Which tool category is MOST commonly used for automated ETL regression testing?
- Load testing tools like JMeter
- Data comparison tools like QuerySurge or Informatica Data Validation (Correct answer)
- Browser automation tools like Selenium
- Network packet analyzers like Wireshark
Correct answer: Data comparison tools like QuerySurge or Informatica Data Validation
Data comparison tools automate SQL-level row-by-row comparison between source and target, making them the standard for ETL regression testing.
ETL Testing MCQ 3
What is the primary purpose of a 'staging area' in an ETL pipeline?