ETL Testing MCQ 5 — Questions and Answers
Question 1: What is 'schema drift' in ETL testing, and why is it a concern?
- When ETL jobs run slower over time due to data volume growth
- When source schema changes (added/removed columns) break the ETL pipeline unexpectedly (Correct answer)
- When the target warehouse schema is redesigned without reloading history
- When data types change in the target but not the source
Correct answer: When source schema changes (added/removed columns) break the ETL pipeline unexpectedly
Schema drift occurs when source systems silently alter their structure, causing ETL jobs to fail or load incorrect data because the pipeline was built against an older schema.
Question 2: A lookup transformation in an ETL returns the wrong country code for 2% of records. What is the FIRST step a tester should take?
- Restart the ETL job and recheck counts
- Isolate the affected records and compare lookup table values against source expectations (Correct answer)
- Rebuild the index on the lookup table
- Increase the connection pool size
Correct answer: Isolate the affected records and compare lookup table values against source expectations
Isolating the 2% of mismatched records and comparing them against the lookup reference table reveals whether the issue is stale lookup data, missing entries, or a transformation bug.
Question 3: Which ETL testing phase checks that the data warehouse meets query performance SLAs after a full data load?
- Unit testing
- System integration testing
- Performance and load testing (Correct answer)
- Smoke testing
Correct answer: Performance and load testing
Performance and load testing measures query response times, ETL throughput, and resource utilization after loading realistic data volumes to validate SLA compliance.
Question 4: What does testing for 'data consistency' across multiple subject areas in a data warehouse mean?
- Ensuring the ETL runs at a consistent schedule
- Verifying that shared dimensions and metrics report identical values across fact tables (Correct answer)
- Confirming that all tables use the same primary key type
- Checking that ETL logs are consistent with job execution history
Correct answer: Verifying that shared dimensions and metrics report identical values across fact tables
Data consistency testing confirms that a dimension like 'Customer' or a metric like 'Revenue' returns the same result whether queried from the Sales or Finance fact table.
Question 5: In an ETL pipeline that loads data from a flat file, which test specifically catches issues with incorrect delimiter handling?
- Referential integrity test
- File format and delimiter parsing test (Correct answer)
- Surrogate key uniqueness test
- Date format transformation test
Correct answer: File format and delimiter parsing test
File format and delimiter parsing tests verify that the parser correctly splits columns using the expected delimiter, catching cases where commas in data values break CSV parsing.
Question 6: Which of the following best describes 'end-to-end ETL testing'?
- Testing only the final SQL query against the data warehouse
- Validating data flow from the original source system through all ETL stages to the final reporting layer (Correct answer)
- Testing the network latency between source and target servers
- Verifying only the last transformation step before loading
Correct answer: Validating data flow from the original source system through all ETL stages to the final reporting layer
End-to-end ETL testing traces a record from its origin in the source system through extraction, staging, transformation, and loading, verifying correctness at every stage.
Question 7: A data warehouse table shows sales figures for Q1 are missing after an ETL run. Logs show the job completed successfully. The MOST likely cause is:
- Network packet loss during the load phase
- A WHERE clause filter in the extraction query excluding Q1 date range records (Correct answer)
- An incorrect surrogate key sequence reset
- A column alias mismatch in the SELECT list
Correct answer: A WHERE clause filter in the extraction query excluding Q1 date range records
A misconfigured date filter in the extraction SQL—such as an incorrect range or off-by-one boundary—silently excludes valid records while the job reports success.
ETL Testing MCQ 5
What is 'schema drift' in ETL testing, and why is it a concern?