ETL Testing ETL Data Quality and Validation 2 — Questions and Answers
Question 1: What is the primary purpose of performing a metadata validation check in ETL testing?
- To confirm row counts between source and target
- To verify column names, data types, lengths, and constraints match the design specification (Correct answer)
- To test the performance of the ETL pipeline under load
- To check for duplicate rows in the target table
Correct answer: To verify column names, data types, lengths, and constraints match the design specification
Metadata validation ensures that target table structure—column names, data types, lengths, nullability, and constraints—matches the ETL design specifications.
Question 2: In an ETL pipeline, a surrogate key is typically validated to ensure which of the following properties?
- It matches the natural key from the source system
- It is unique and not NULL for every row in the dimension table (Correct answer)
- It is a composite key made of multiple source columns
- It is populated only for slowly changing dimension Type 2 records
Correct answer: It is unique and not NULL for every row in the dimension table
Surrogate keys must be unique and non-null for every dimension row since they are the primary identifiers used for joins in the data warehouse.
Question 3: Which data quality test would detect a situation where the ETL process loaded the same transaction record twice into the target table?
- Null check
- Duplicate record check (Correct answer)
- Referential integrity check
- Range check
Correct answer: Duplicate record check
A duplicate record check identifies rows that have been inserted more than once, which can occur due to ETL restarts, retry logic, or missing deduplication steps.
Question 4: When testing a Type 2 Slowly Changing Dimension (SCD), which validation confirms that historical records are correctly preserved with start and end date columns?
- Checksum validation
- SCD version history validation (Correct answer)
- Boundary check
- Orphan record check
Correct answer: SCD version history validation
SCD version history validation verifies that old records are expired with an end date and new records are inserted with a new start date, maintaining full change history.
Question 5: An orphan record in ETL testing refers to which of the following scenarios?
- A fact table row whose foreign key has no matching primary key in the dimension table (Correct answer)
- A dimension record that has never been updated since initial load
- A source record that was rejected by the ETL error handler
- A target record with all NULL attribute columns
Correct answer: A fact table row whose foreign key has no matching primary key in the dimension table
An orphan record is a fact table row whose foreign key value does not match any primary key in the related dimension, breaking referential integrity.
Question 6: Which ETL quality metric measures the percentage of records that successfully passed all validation rules out of the total records processed?
- Data latency rate
- Error rejection rate
- Data acceptance rate (Correct answer)
- Transformation ratio
Correct answer: Data acceptance rate
The data acceptance rate indicates the proportion of records that met all quality rules and were successfully loaded, reflecting overall pipeline health.
Question 7: In ETL testing, what does a 'completeness check on a NOT NULL column' specifically verify?
- That the column length does not exceed the defined maximum
- That no rows exist where that column contains a NULL value (Correct answer)
- That the column values fall within an acceptable numeric range
- That the column contains only unique values across all rows
Correct answer: That no rows exist where that column contains a NULL value
A NOT NULL completeness check confirms that every row has a value in that column, since the database constraint or business rule prohibits NULL entries there.
ETL Testing ETL Data Quality and Validation 2
What is the primary purpose of performing a metadata validation check in ETL testing?