ETL Testing MCQ 4 — Questions and Answers
Question 1: In ETL testing, what does 'data completeness' testing verify?
- That all columns have the correct data type
- That all expected records and fields are present in the target without omission (Correct answer)
- That the ETL finishes within an SLA window
- That surrogate keys are sequentially assigned
Correct answer: That all expected records and fields are present in the target without omission
Data completeness testing ensures every record from the source appears in the target and that no columns, rows, or values are missing after the pipeline runs.
Question 2: Which type of ETL bug would cause a sales amount to be loaded as 1000 instead of the correct 10.00?
- Truncation error
- Decimal precision or scaling error (Correct answer)
- Referential integrity violation
- Null propagation error
Correct answer: Decimal precision or scaling error
A decimal precision error—such as omitting a scale factor or mishandling decimal points—can shift values by orders of magnitude during transformation.
Question 3: What is the role of a 'surrogate key' in a data warehouse target table, and what ETL test validates it?
- It replaces business keys; tested by verifying no NULLs and uniqueness (Correct answer)
- It is a composite of natural keys; tested by duplicate checks only
- It stores encrypted natural keys; tested by decryption validation
- It timestamps each row; tested by date range overlap checks
Correct answer: It replaces business keys; tested by verifying no NULLs and uniqueness
Surrogate keys are system-generated unique identifiers replacing natural business keys; ETL tests confirm they are non-null, unique, and properly assigned to every loaded row.
Question 4: An ETL process aggregates daily sales into monthly totals. Which test would catch an off-by-one error in the aggregation date range?
- Schema validation test
- Boundary date range test (Correct answer)
- Column length test
- NULL constraint test
Correct answer: Boundary date range test
Boundary date range tests use records on month start/end dates (e.g., Jan 31 vs Feb 1) to confirm the aggregation window includes or excludes the correct days.
Question 5: What does 'CDC' stand for in the context of ETL pipelines, and what is its purpose?
- Central Data Catalog — stores metadata about tables
- Change Data Capture — tracks and extracts only changed source records (Correct answer)
- Consolidated Data Cleansing — removes duplicate records
- Computed Data Column — derives values during transformation
Correct answer: Change Data Capture — tracks and extracts only changed source records
Change Data Capture monitors source system logs or timestamps to identify and extract only the records that have been inserted, updated, or deleted since the last run.
Question 6: During ETL testing, a tester runs the same load twice and discovers row counts doubled. This most likely indicates:
- The transformation logic has a type mismatch
- The ETL lacks idempotency — no deduplication or upsert logic (Correct answer)
- The staging table was not indexed
- The source system returned extra columns
Correct answer: The ETL lacks idempotency — no deduplication or upsert logic
Without idempotency controls such as MERGE/upsert logic or pre-load truncation, re-running an ETL inserts all rows again, doubling the data.
Question 7: Which ETL testing approach validates that transformations applied to personally identifiable information (PII) comply with data masking requirements?
- Performance benchmarking
- Data security and masking validation testing (Correct answer)
- Schema drift testing
- Full-load reconciliation testing
Correct answer: Data security and masking validation testing
Data security and masking validation tests confirm that PII fields—SSNs, emails, phone numbers—are properly masked, hashed, or tokenized in the target per compliance rules.
ETL Testing MCQ 4
In ETL testing, what does 'data completeness' testing verify?