ETL Testing ETL Data Quality and Validation 2

Question 1

What is the primary purpose of performing a metadata validation check in ETL testing?

Accepted Answer

To verify column names, data types, lengths, and constraints match the design specification

Answer

Metadata validation ensures that target table structure—column names, data types, lengths, nullability, and constraints—matches the ETL design specifications.

Question 2

In an ETL pipeline, a surrogate key is typically validated to ensure which of the following properties?

Accepted Answer

It is unique and not NULL for every row in the dimension table

Answer

Surrogate keys must be unique and non-null for every dimension row since they are the primary identifiers used for joins in the data warehouse.

Question 3

Which data quality test would detect a situation where the ETL process loaded the same transaction record twice into the target table?

Accepted Answer

Duplicate record check

Answer

A duplicate record check identifies rows that have been inserted more than once, which can occur due to ETL restarts, retry logic, or missing deduplication steps.

Question 4

When testing a Type 2 Slowly Changing Dimension (SCD), which validation confirms that historical records are correctly preserved with start and end date columns?

Accepted Answer

SCD version history validation

Answer

SCD version history validation verifies that old records are expired with an end date and new records are inserted with a new start date, maintaining full change history.

Question 5

An orphan record in ETL testing refers to which of the following scenarios?

Accepted Answer

A fact table row whose foreign key has no matching primary key in the dimension table

Answer

An orphan record is a fact table row whose foreign key value does not match any primary key in the related dimension, breaking referential integrity.

ETL Testing - extract, transform, load Practice Test

ETL Testing - extract, transform, load Practice Test

ETL Testing ETL Data Quality and Validation 2