ETL Testing MCQ 2

Question 1

Which ETL testing type verifies that data is not lost or duplicated during the extraction phase?

Accepted Answer

Reconciliation testing

Answer

Reconciliation testing compares row counts and key metrics between source and target to confirm no data loss or duplication during extraction.

Question 2

What is a 'slowly changing dimension' (SCD) Type 2 strategy in ETL?

Accepted Answer

Add a new row with effective date ranges to preserve history

Answer

SCD Type 2 inserts a new row for each change with start/end effective dates, preserving the full history of dimension records.

Question 3

During ETL testing, a tester finds the target table has 5% more rows than the source. The most likely root cause is:

Accepted Answer

Duplicate key handling inserting extras

Answer

When duplicate key logic is misconfigured, the ETL may insert multiple rows for a single source record instead of updating or ignoring duplicates.

Question 4

Which SQL clause is most commonly used to validate referential integrity between a fact table and a dimension table in ETL testing?

Accepted Answer

LEFT OUTER JOIN with NULL check

Answer

A LEFT OUTER JOIN followed by filtering for NULLs in the dimension key column reveals fact records that have no matching dimension entry.

Question 5

What does 'data profiling' accomplish in the ETL testing process?

Accepted Answer

Analyzes source data for patterns, nulls, and anomalies before transformation

Answer

Data profiling examines source data statistics—null rates, value distributions, and outliers—to inform transformation rules and anticipate quality issues.

ETL Testing - extract, transform, load Practice Test

ETL Testing - extract, transform, load Practice Test

ETL Testing MCQ 2