ETL Testing Trivia 5 — Questions and Answers
Question 1: What is 'end-to-end ETL testing' and when is it most valuable?
- Testing only the Extract phase in isolation to catch source connection errors
- Testing the full pipeline from source extraction through transformation to target load to validate the complete data flow (Correct answer)
- Testing only the final Load phase after all transformations are assumed correct
- Testing the ETL scheduler independently of the transformation logic
Correct answer: Testing the full pipeline from source extraction through transformation to target load to validate the complete data flow
End-to-end ETL testing validates the entire data flow from source system through all transformations to the final target, catching integration defects that unit tests of individual phases would miss.
Question 2: Which ETL concept describes replacing an entire target table with freshly extracted and transformed source data on each run?
- Incremental load
- Full load (truncate and reload) (Correct answer)
- Delta load
- Upsert load
Correct answer: Full load (truncate and reload)
A full load — also called truncate-and-reload — deletes all existing target data before each run and reloads everything from the source, ensuring the target always mirrors the source completely.
Question 3: When testing ETL, what does 'referential integrity' checking confirm?
- That all source records have non-null primary keys
- That foreign key values in the target fact table correctly match existing keys in the related dimension tables (Correct answer)
- That the ETL job completes within the defined SLA time window
- That source and target databases use the same character set encoding
Correct answer: That foreign key values in the target fact table correctly match existing keys in the related dimension tables
Referential integrity testing verifies that every foreign key in the fact or child table corresponds to a valid primary key in the related dimension or parent table, ensuring no orphaned records exist.
Question 4: In ETL performance testing, what is a common metric used to measure throughput?
- Number of SQL joins per transformation
- Rows processed per second (or per minute) through the pipeline (Correct answer)
- Number of source tables queried during extraction
- Count of validation rules evaluated per record
Correct answer: Rows processed per second (or per minute) through the pipeline
ETL throughput is commonly measured in rows per second or rows per minute, indicating how quickly the pipeline can process data volume during the extraction, transformation, and load phases.
Question 5: What is the role of an ETL 'audit table' or 'control table' in a data pipeline?
- Storing the raw source data before any transformation is applied
- Recording job run metadata such as start time, end time, record counts, and status for each ETL execution (Correct answer)
- Holding the mapping specifications between source and target columns
- Caching frequently used lookup reference data for reuse across jobs
Correct answer: Recording job run metadata such as start time, end time, record counts, and status for each ETL execution
An audit or control table logs execution metadata for each ETL run — including timestamps, record counts loaded, and pass/fail status — enabling monitoring, reconciliation, and troubleshooting.
Question 6: Which ETL testing defect category would be raised if a VARCHAR(50) source column's data is being silently truncated to 20 characters in the target?
- Null handling defect
- Data truncation defect (Correct answer)
- Column mapping defect
- Duplicate record defect
Correct answer: Data truncation defect
A data truncation defect occurs when the target column's defined length is shorter than the source, causing values to be silently cut off, which can corrupt meaningful data.
Question 7: What does an ETL tester verify when performing 'date dimension' testing in a data warehouse?
- That ETL jobs execute on the correct calendar schedule
- That date values are correctly parsed, formatted, and mapped to the appropriate date dimension surrogate keys (Correct answer)
- That the data warehouse retains only the most recent two years of date records
- That ETL jobs adjust automatically for daylight saving time cutover
Correct answer: That date values are correctly parsed, formatted, and mapped to the appropriate date dimension surrogate keys
Date dimension testing verifies that source date fields are correctly parsed into the target format, map to the right surrogate key in the date dimension, and handle edge cases like fiscal year boundaries or null dates.
ETL Testing Trivia 5
What is 'end-to-end ETL testing' and when is it most valuable?