ETL Testing ETL Data Loading Strategies 2 — Questions and Answers
Question 1: In ETL loading, what is a 'reject file' or 'error file'?
- A file containing records rejected by the transformation layer
- A file where the ETL engine writes records that failed to load due to constraint violations or errors (Correct answer)
- A database table tracking rejected vendor invoices
- A log of all successfully loaded records
Correct answer: A file where the ETL engine writes records that failed to load due to constraint violations or errors
A reject file captures records that could not be loaded into the target due to data quality issues, constraint violations, or type mismatches, enabling investigation and reprocessing.
Question 2: Which loading technique is BEST to test when the business requirement is 'no duplicate orders in the target regardless of how many times the ETL re-runs'?
- Full load with truncate
- Idempotent incremental load with MERGE/upsert (Correct answer)
- Append-only incremental load
- Manual load via CSV import
Correct answer: Idempotent incremental load with MERGE/upsert
An idempotent MERGE-based load ensures that rerunning the ETL multiple times produces the same result without creating duplicates, satisfying the no-duplicate requirement.
Question 3: What ETL loading test verifies that a target table's column data types match the specification after loading?
- Metadata/schema validation test (Correct answer)
- Row count test
- Business rule test
- Performance test
Correct answer: Metadata/schema validation test
Schema or metadata validation checks the data dictionary or INFORMATION_SCHEMA to confirm that loaded columns have the correct data types, lengths, and nullability.
Question 4: What is 'partition switching' as an ETL loading optimization in SQL Server or similar RDBMS?
- Switching database instances between primary and secondary
- Loading data into a staging partition and then instantly swapping it with the target partition using metadata only, with no data movement (Correct answer)
- Partitioning ETL jobs across multiple servers
- Switching between full and incremental load modes
Correct answer: Loading data into a staging partition and then instantly swapping it with the target partition using metadata only, with no data movement
Partition switching is a near-instantaneous metadata operation that swaps a loaded staging partition into a target table partition without physically moving data.
Question 5: In a data warehouse ETL load, which test confirms that fact table metrics are correctly associated with the right dimension keys?
- Surrogate key join validation (Correct answer)
- Row count test
- File format test
- Network latency test
Correct answer: Surrogate key join validation
Surrogate key join validation queries the fact table joined to dimension tables and verifies that metrics are attributed to the correct dimensional attributes.
Question 6: What is the purpose of disabling and re-enabling indexes during a bulk ETL load?
- To prevent users from querying the target during load
- To dramatically improve load speed by avoiding index maintenance overhead during the insert, then rebuilding indexes afterward (Correct answer)
- To avoid foreign key constraint errors
- To reset auto-increment identity columns
Correct answer: To dramatically improve load speed by avoiding index maintenance overhead during the insert, then rebuilding indexes afterward
Dropping or disabling indexes before bulk loading removes the overhead of updating index structures row-by-row, then a single post-load index rebuild is faster overall.
ETL Testing ETL Data Loading Strategies 2
In ETL loading, what is a 'reject file' or 'error file'?