ETL Testing ETL Data Loading Strategies 1 — Questions and Answers
Question 1: What is a 'full load' in ETL data loading?
- Loading only changed records into the target
- Truncating the target table and reloading all records from scratch each run (Correct answer)
- Loading records only to a staging area
- Loading aggregated summary data only
Correct answer: Truncating the target table and reloading all records from scratch each run
A full load completely replaces the target table contents on each ETL run by truncating and reloading all records from the source or staging area.
Question 2: Which loading strategy appends only new or changed records to the target without removing existing data?
- Full load
- Incremental load (Correct answer)
- Bulk delete load
- Schema-only load
Correct answer: Incremental load
Incremental loading merges new and changed records into the existing target data, preserving historical records while keeping the target current.
Question 3: In ETL loading testing, which validation confirms that primary key uniqueness is maintained after loading?
- Referential integrity check
- Duplicate key / uniqueness constraint test (Correct answer)
- Row count reconciliation
- Column data type test
Correct answer: Duplicate key / uniqueness constraint test
A uniqueness constraint test queries the target for duplicate primary key values to ensure the load did not introduce any key violations.
Question 4: What is 'bulk loading' in ETL, and why is it preferred for large datasets?
- Inserting records one row at a time via standard INSERT statements
- Using database-native bulk utilities (e.g., SQL Server BCP, COPY in PostgreSQL) that bypass row-by-row processing for much faster throughput (Correct answer)
- Loading data in small batches of 10 records
- Loading only the largest tables first
Correct answer: Using database-native bulk utilities (e.g., SQL Server BCP, COPY in PostgreSQL) that bypass row-by-row processing for much faster throughput
Bulk loaders use optimized database utilities that write data in large blocks with minimal logging, achieving orders-of-magnitude faster throughput than row-by-row inserts.
Question 5: Which test verifies that foreign key relationships in the target remain valid after an ETL load?
- Referential integrity test (Correct answer)
- Data type test
- Null check
- Performance benchmark
Correct answer: Referential integrity test
Referential integrity testing queries the target to confirm that every foreign key value in child tables has a matching primary key in the parent table.
Question 6: What is an 'upsert' operation in ETL loading?
- Inserting a new record and immediately deleting the old one
- Combining INSERT and UPDATE logic — insert if the record is new, update if it already exists (Correct answer)
- Uploading data to an external SFTP server
- Upscaling a dimension table with new columns
Correct answer: Combining INSERT and UPDATE logic — insert if the record is new, update if it already exists
An upsert (MERGE statement or equivalent) checks for an existing record and updates it if found, or inserts a new row if not found, in a single atomic operation.
ETL Testing ETL Data Loading Strategies 1
What is a 'full load' in ETL data loading?