ETL Testing Basic 3 — Questions and Answers
Question 1: Which ETL load strategy completely replaces all existing target table data with freshly extracted source data?
- Incremental load
- Delta load
- Full refresh (truncate and reload) (Correct answer)
- Upsert load
Correct answer: Full refresh (truncate and reload)
A full refresh truncates the target table first and then loads all source records, making it simple but costly for large datasets.
Question 2: In ETL, 'data latency' refers to:
- The time it takes to run data profiling on the source
- The delay between when data is created in the source system and when it becomes available in the target (Correct answer)
- The number of rejected records in a load run
- The difference in row counts between source and target
Correct answer: The delay between when data is created in the source system and when it becomes available in the target
Data latency measures the lag between a business event occurring in the source and that data being queryable in the data warehouse.
Question 3: What is the purpose of a 'reject file' in ETL processing?
- To store records that passed all validation rules
- To hold records that failed validation so they can be reviewed and corrected without stopping the job (Correct answer)
- To archive successfully loaded records for auditing
- To track the execution time of each transformation step
Correct answer: To hold records that failed validation so they can be reviewed and corrected without stopping the job
A reject file captures bad records (e.g., type mismatches, constraint violations) so the ETL job can continue while those records are separately investigated.
Question 4: Slowly Changing Dimension (SCD) Type 2 testing verifies that:
- Dimension records are overwritten when source attributes change
- A new row is inserted for each attribute change, preserving the full history with effective dates (Correct answer)
- Only the most recent record is kept and old values are deleted
- Dimension changes are ignored and the original row is never updated
Correct answer: A new row is inserted for each attribute change, preserving the full history with effective dates
SCD Type 2 inserts a new dimension row for each change and marks the old row as expired, enabling historical reporting.
Question 5: ETL throughput, a key performance metric, is typically measured as:
- Number of ETL jobs per day
- Rows or megabytes processed per second or per hour (Correct answer)
- Number of source tables connected
- Count of transformation rules applied
Correct answer: Rows or megabytes processed per second or per hour
Throughput quantifies how much data the ETL pipeline processes in a given time unit and is the primary metric in ETL performance testing.
Question 6: Which error-handling strategy continues processing remaining records after encountering a bad row, rather than aborting the entire job?
- Fail-fast strategy
- Abort-on-error strategy
- Skip-and-log strategy (Correct answer)
- Rollback-all strategy
Correct answer: Skip-and-log strategy
The skip-and-log strategy writes the invalid record to a reject file or error table, increments an error counter, and continues processing valid records.
Question 7: ETL lineage tracking primarily serves to:
- Monitor the disk space used by the staging area
- Record the origin, transformation steps, and destination of each data element for traceability (Correct answer)
- Optimize SQL queries generated by the ETL tool
- Schedule ETL jobs in dependency order
Correct answer: Record the origin, transformation steps, and destination of each data element for traceability
Data lineage creates an auditable trail showing where each field came from, what transformations it underwent, and where it landed in the target.
ETL Testing Basic 3
Which ETL load strategy completely replaces all existing target table data with freshly extracted source data?