ETL Testing ETL Data Transformation Rules 2 — Questions and Answers
Question 1: When testing ETL aggregation transformations, which validation is MOST critical?
- Checking that source file timestamps are preserved
- Verifying that SUM, COUNT, and AVG results match independently calculated totals (Correct answer)
- Confirming that all indexes were rebuilt
- Validating that column names match the source
Correct answer: Verifying that SUM, COUNT, and AVG results match independently calculated totals
Aggregation test results must be reconciled against independently computed totals to confirm the ETL grouping and calculation logic is accurate.
Question 2: What does 'data standardization' mean in the ETL transformation context?
- Compressing all data to a standard file size
- Converting data to a consistent format, such as making all date formats YYYY-MM-DD (Correct answer)
- Removing all special characters from text fields
- Limiting all numeric fields to two decimal places
Correct answer: Converting data to a consistent format, such as making all date formats YYYY-MM-DD
Data standardization ensures that data from diverse sources conforms to a uniform format, improving consistency and analytical accuracy in the target.
Question 3: In slowly changing dimension (SCD) Type 2 handling, what does the ETL transformation do when a source record changes?
- Overwrites the existing target record
- Inserts a new row with the changed values and sets an expiry date on the old row (Correct answer)
- Deletes the old row and inserts a new one
- Flags the old row as an error
Correct answer: Inserts a new row with the changed values and sets an expiry date on the old row
SCD Type 2 preserves history by expiring the current row and inserting a new version, allowing historical analysis of attribute changes over time.
Question 4: Which ETL transformation technique is used to combine first name and last name fields into a single full name column?
- Splitting
- Concatenation (Correct answer)
- Pivoting
- Aggregation
Correct answer: Concatenation
Concatenation combines multiple source fields into a single target field, such as merging first and last name into a full name.
Question 5: When testing a NULL-handling transformation rule that substitutes 0 for null numeric values, what test case is ESSENTIAL?
- Send a record with value 0 and verify it stays 0
- Send a record with a null numeric field and verify the target contains 0 (Correct answer)
- Send a record with a negative number and verify it becomes null
- Send a record with a string and verify it converts to null
Correct answer: Send a record with a null numeric field and verify the target contains 0
The core test must confirm that a null input is correctly replaced by the default value (0), validating the null substitution rule.
Question 6: What is 'data splitting' in ETL transformation?
- Dividing a large ETL job into parallel threads
- Separating a single source field into multiple target fields, such as splitting a full address into street, city, and zip (Correct answer)
- Partitioning a target table for performance
- Splitting source files into chunks for incremental loading
Correct answer: Separating a single source field into multiple target fields, such as splitting a full address into street, city, and zip
Data splitting parses a single composite source field and distributes its components into separate target columns for better structure and querying.
ETL Testing ETL Data Transformation Rules 2
When testing ETL aggregation transformations, which validation is MOST critical?