ETL Testing Basic 2 — Questions and Answers
Question 1: What is the primary purpose of a staging area in an ETL pipeline?
- To permanently store transformed data
- To serve as an intermediate holding zone for raw extracted data before transformation (Correct answer)
- To generate reports for end users
- To replace the data warehouse entirely
Correct answer: To serve as an intermediate holding zone for raw extracted data before transformation
A staging area is a temporary storage zone where raw source data lands before transformations are applied, allowing validation and reprocessing without re-extracting.
Question 2: In ETL testing, what does 'data profiling' primarily help you determine?
- How fast the ETL job runs
- The structure, quality, and statistical characteristics of source data (Correct answer)
- Whether the target schema matches the source schema exactly
- The number of ETL jobs scheduled per day
Correct answer: The structure, quality, and statistical characteristics of source data
Data profiling analyzes source data to reveal patterns, distributions, null rates, and anomalies before ETL design begins.
Question 3: A surrogate key in a data warehouse fact table is best described as:
- The natural key copied directly from the source system
- A system-generated integer key that uniquely identifies a dimension record (Correct answer)
- A composite key made from multiple source columns
- A foreign key referencing another fact table
Correct answer: A system-generated integer key that uniquely identifies a dimension record
Surrogate keys are system-generated (usually sequential integers) and are used instead of natural business keys to ensure uniqueness and support SCD handling.
Question 4: Which ETL test specifically validates that NULL values from the source are handled correctly by transformation logic?
- Boundary value testing
- Referential integrity testing
- Null handling / negative testing (Correct answer)
- Performance stress testing
Correct answer: Null handling / negative testing
Null handling tests confirm that NULLs are either substituted with defaults, propagated, or flagged as errors according to the transformation specification.
Question 5: What does 'referential integrity' testing in ETL verify?
- That every record has a non-null primary key
- That foreign key values in the target exist in the corresponding dimension tables (Correct answer)
- That transformation rules execute in the correct order
- That the ETL job does not exceed its scheduled run window
Correct answer: That foreign key values in the target exist in the corresponding dimension tables
Referential integrity testing ensures that fact table foreign keys reference valid dimension records, preventing orphan rows in the data warehouse.
Question 6: A transformation converts a date from MM/DD/YYYY to YYYY-MM-DD format. Which test type directly validates this rule?
- Load testing
- Transformation / mapping validation testing (Correct answer)
- Regression testing
- Volume testing
Correct answer: Transformation / mapping validation testing
Transformation or mapping validation testing checks that each business rule — including format conversions — is implemented correctly in the ETL code.
Question 7: Aggregate validation testing in ETL is used to confirm that:
- Index creation on the target table succeeds
- Summary calculations like SUM, COUNT, and AVG in the target match equivalent calculations on the source (Correct answer)
- The ETL job can handle the maximum expected data volume
- All lookup tables are populated before the fact load runs
Correct answer: Summary calculations like SUM, COUNT, and AVG in the target match equivalent calculations on the source
Aggregate validation runs parallel computations on source and target data to verify that rolled-up metrics are mathematically consistent.
ETL Testing Basic 2
What is the primary purpose of a staging area in an ETL pipeline?