ETL Testing Trivia 4 — Questions and Answers
Question 1: What is the purpose of 'null handling' rules in ETL transformation testing?
- To delete all records containing null values from the source
- To verify that null values are replaced, defaulted, or flagged according to business specifications (Correct answer)
- To prevent null columns from being loaded into the staging area
- To assign null surrogate keys to unmatched dimension records
Correct answer: To verify that null values are replaced, defaulted, or flagged according to business specifications
Null handling rules define how the ETL should treat missing source values — whether to substitute a default, reject the record, or propagate the null — and testing verifies these rules work correctly.
Question 2: Which ETL testing activity verifies that the target table's column data types, lengths, and constraints match the design specification?
- Data completeness testing
- Metadata testing (Correct answer)
- Performance benchmarking
- Boundary value analysis
Correct answer: Metadata testing
Metadata testing confirms that the target schema — including column names, data types, lengths, nullability, and constraints — matches the agreed design specification.
Question 3: What does 'data masking' accomplish in an ETL testing environment?
- It accelerates ETL job performance by filtering irrelevant columns
- It replaces sensitive production data with realistic but fictitious values for safe use in testing (Correct answer)
- It hides ETL transformation logic from source system administrators
- It encrypts the ETL job logs to prevent unauthorized access
Correct answer: It replaces sensitive production data with realistic but fictitious values for safe use in testing
Data masking substitutes sensitive values (like SSNs or credit card numbers) with realistic fake data so testers can work with production-like datasets without exposing real personal information.
Question 4: In ETL testing, what is a 'reject file' or 'error file' used for?
- Storing ETL job execution logs and performance metrics
- Capturing records that failed validation rules so they can be reviewed and reprocessed (Correct answer)
- Holding duplicate records before deduplication runs
- Archiving historical data replaced by newer source records
Correct answer: Capturing records that failed validation rules so they can be reviewed and reprocessed
A reject or error file captures records that could not be loaded due to validation failures, data type mismatches, or constraint violations, allowing investigation and potential reprocessing.
Question 5: What ETL testing challenge is unique to systems that pull data from multiple heterogeneous source systems?
- Verifying that all source systems use the same database vendor
- Reconciling conflicting data formats, encodings, and business key definitions across sources (Correct answer)
- Ensuring all sources share a single ETL scheduling window
- Confirming that all sources write to the same staging table
Correct answer: Reconciling conflicting data formats, encodings, and business key definitions across sources
When sources are heterogeneous, testers must validate that conflicting formats, date representations, character encodings, and overlapping business keys are correctly reconciled during integration.
Question 6: Which strategy is most appropriate for testing an ETL pipeline's behavior under maximum expected data volume?
- Unit testing individual SQL transformation statements
- Volume or stress testing using a dataset sized to production scale or beyond (Correct answer)
- Smoke testing with a handful of sample records
- Regression testing with the previous release's data subset
Correct answer: Volume or stress testing using a dataset sized to production scale or beyond
Volume or stress testing loads the ETL pipeline with production-scale or larger datasets to uncover performance bottlenecks, memory issues, and timeout failures that only appear at scale.
Question 7: What is a 'fact table' in a data warehouse, and how does ETL testing relate to it?
- A table storing descriptive attributes about business entities; ETL testing verifies dimension updates
- A central table storing measurable business events and metrics; ETL testing verifies correct foreign key references and aggregated measures (Correct answer)
- A table holding rejected ETL records; ETL testing clears it after each run
- A configuration table listing ETL job parameters; ETL testing ensures parameters are current
Correct answer: A central table storing measurable business events and metrics; ETL testing verifies correct foreign key references and aggregated measures
A fact table contains quantitative measures (like sales amounts or quantities) and foreign keys to dimension tables; ETL testing verifies that measures are accurately calculated and dimension keys are correctly resolved.
ETL Testing Trivia 4
What is the purpose of 'null handling' rules in ETL transformation testing?