ETL Testing ETL Data Transformation Rules 1 — Questions and Answers
Question 1: What is 'data cleansing' in the ETL transformation phase?
- Encrypting data before storage
- Identifying and correcting inaccurate, incomplete, or duplicate records (Correct answer)
- Compressing data for faster loading
- Converting data types to strings
Correct answer: Identifying and correcting inaccurate, incomplete, or duplicate records
Data cleansing during transformation removes or corrects dirty data such as nulls, duplicates, and invalid formats before it reaches the target.
Question 2: In ETL testing, a 'mapping document' is primarily used to:
- Track server IP addresses
- Define how source fields are transformed and mapped to target columns (Correct answer)
- Document network topology
- List all staging table indexes
Correct answer: Define how source fields are transformed and mapped to target columns
The ETL mapping document specifies the source-to-target column mappings, transformation logic, and business rules that testers validate against.
Question 3: Which transformation type converts a value from one unit to another, such as converting temperatures from Celsius to Fahrenheit?
- Aggregation
- Lookup transformation
- Calculation/Derivation transformation (Correct answer)
- Filter transformation
Correct answer: Calculation/Derivation transformation
Calculation or derivation transformations apply mathematical formulas to source values to produce new derived target values.
Question 4: What is 'surrogate key generation' in ETL transformation?
- Creating a natural business key from source data
- Assigning a system-generated unique integer key to each record in the data warehouse (Correct answer)
- Encrypting the primary key field
- Generating a hash of all columns for deduplication
Correct answer: Assigning a system-generated unique integer key to each record in the data warehouse
Surrogate keys are artificial integer identifiers assigned by the ETL process to uniquely identify dimension records independent of source system keys.
Question 5: In ETL transformation testing, which test validates that a business rule such as 'sales amount cannot be negative' is enforced?
- Referential integrity test
- Business rule validation test (Correct answer)
- Load performance test
- Schema test
Correct answer: Business rule validation test
Business rule validation tests verify that transformation logic correctly enforces domain-specific constraints and business requirements.
Question 6: What is a 'lookup transformation' in ETL?
- A search across web APIs for reference data
- A transformation that matches a source value against a reference table to retrieve corresponding values (Correct answer)
- A full table scan to verify row counts
- A technique to locate duplicate records
Correct answer: A transformation that matches a source value against a reference table to retrieve corresponding values
Lookup transformations query a reference or dimension table to replace or enrich source values with corresponding target values, such as replacing a state code with a full state name.
ETL Testing ETL Data Transformation Rules 1
What is 'data cleansing' in the ETL transformation phase?