ETL Testing ETL Data Loading Strategies 2

Question 1

In ETL loading, what is a 'reject file' or 'error file'?

Accepted Answer

A file where the ETL engine writes records that failed to load due to constraint violations or errors

Answer

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'?

Accepted Answer

Idempotent incremental load with MERGE/upsert

Answer

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?

Accepted Answer

Metadata/schema validation test

Answer

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?

Accepted Answer

Loading data into a staging partition and then instantly swapping it with the target partition using metadata only, with no data movement

Answer

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?

Accepted Answer

Surrogate key join validation

Answer

Surrogate key join validation queries the fact table joined to dimension tables and verifies that metrics are attributed to the correct dimensional attributes.

ETL Testing - extract, transform, load Practice Test

ETL Testing - extract, transform, load Practice Test

ETL Testing ETL Data Loading Strategies 2