ETL Testing Trivia 3 — Questions and Answers
Question 1: What is a 'surrogate key' commonly used for in ETL data warehousing?
- To encrypt sensitive source data
- To provide a system-generated unique identifier independent of source business keys (Correct answer)
- To store the original source primary key unchanged
- To link fact tables directly to source operational tables
Correct answer: To provide a system-generated unique identifier independent of source business keys
A surrogate key is a system-generated integer assigned in the data warehouse to uniquely identify dimension records, independent of the source system's natural key.
Question 2: In ETL testing, what does 'data profiling' help testers accomplish?
- Measuring ETL job execution speed
- Analyzing source data to understand its structure, quality, and anomalies before designing transformations (Correct answer)
- Configuring the target schema indexes
- Scheduling ETL job execution windows
Correct answer: Analyzing source data to understand its structure, quality, and anomalies before designing transformations
Data profiling examines source data's content, structure, and quality — such as null rates, value distributions, and format patterns — to inform ETL design and testing.
Question 3: What kind of ETL defect would cause a customer's city field to appear in the state column of the target table?
- Data truncation error
- Column mapping defect (Correct answer)
- Referential integrity violation
- Data type mismatch
Correct answer: Column mapping defect
A column mapping defect occurs when source columns are incorrectly mapped to target columns, causing data to land in the wrong field.
Question 4: Which ETL testing type checks that business rules encoded in transformations produce the correct output values?
- Load testing
- Transformation testing (Correct answer)
- Metadata testing
- Constraint testing
Correct answer: Transformation testing
Transformation testing validates that the logic applied during the Transform phase correctly converts, aggregates, or filters source data according to business rules.
Question 5: What is 'CDC' (Change Data Capture) used for in ETL pipelines?
- Compressing data before loading
- Identifying and capturing only the rows that changed in the source since the last extract (Correct answer)
- Converting character encodings during extraction
- Calculating data checksums for validation
Correct answer: Identifying and capturing only the rows that changed in the source since the last extract
Change Data Capture (CDC) tracks and captures only the rows that were inserted, updated, or deleted in the source system since the previous ETL run.
Question 6: When would a tester use a 'checksum' during ETL validation?
- To measure how fast the ETL job runs
- To verify data integrity by comparing computed hash values between source and target (Correct answer)
- To enforce foreign key constraints in the target
- To detect missing indexes in the staging area
Correct answer: To verify data integrity by comparing computed hash values between source and target
Checksums (hash values computed on column data) are compared between source and target to detect any unintended data modifications during ETL processing.
Question 7: In a slowly changing dimension (SCD) Type 2 implementation, what happens when a source record's attribute value changes?
- The existing row is updated in place with the new value
- A new row is inserted with the updated value and the old row is expired (Correct answer)
- The old row is deleted and replaced with the new row
- The change is ignored and the original value is preserved
Correct answer: A new row is inserted with the updated value and the old row is expired
SCD Type 2 preserves history by inserting a new dimension row for the changed record and marking the old row as expired with an end date or active flag.
ETL Testing Trivia 3
What is a 'surrogate key' commonly used for in ETL data warehousing?