ETL Testing Basic 4 — Questions and Answers
Question 1: Change Data Capture (CDC) in ETL primarily captures:
- A full snapshot of every source table on each run
- Only the rows that were inserted, updated, or deleted since the last extraction (Correct answer)
- Schema changes made to the source database
- Performance metrics for each ETL execution
Correct answer: Only the rows that were inserted, updated, or deleted since the last extraction
CDC identifies and extracts only changed records, reducing extraction volume and enabling near-real-time data warehouse updates.
Question 2: Data reconciliation testing in ETL is best described as:
- Verifying that ETL jobs finish before their SLA deadline
- Comparing source and target record counts and key metrics to confirm no data was lost or duplicated during the ETL process (Correct answer)
- Testing that the ETL tool can connect to the source database
- Validating that index structures exist on the target table
Correct answer: Comparing source and target record counts and key metrics to confirm no data was lost or duplicated during the ETL process
Reconciliation compares totals, counts, and checksums between source and target to certify that every expected record landed correctly.
Question 3: Which approach best prevents duplicate records when running incremental ETL loads?
- Truncating the target table before every incremental load
- Using a high-watermark or last-run timestamp combined with an UPSERT (merge) operation (Correct answer)
- Extracting all source data and filtering duplicates in the staging area only
- Disabling primary key constraints on the target during the load
Correct answer: Using a high-watermark or last-run timestamp combined with an UPSERT (merge) operation
A high-watermark limits extraction to new/changed rows, and an UPSERT ensures existing target rows are updated rather than re-inserted.
Question 4: In ETL testing, a 'boundary value' test would check:
- Whether the ETL job can connect to all source systems simultaneously
- Data at the edges of allowed ranges, such as the minimum, maximum, and just-outside-maximum values for a numeric field (Correct answer)
- Whether all dimension tables are loaded before fact tables
- How the ETL handles a completely empty source table
Correct answer: Data at the edges of allowed ranges, such as the minimum, maximum, and just-outside-maximum values for a numeric field
Boundary value testing targets the extreme valid and just-invalid values of a field to confirm that validation logic accepts or rejects them correctly.
Question 5: What is 'metadata testing' in the context of ETL?
- Testing that transformation logic runs faster over time
- Validating that column names, data types, lengths, and constraints in the target match the agreed-upon design specifications (Correct answer)
- Verifying that ETL audit log tables record job start and end times
- Confirming that the ETL scheduler triggers jobs at the correct time
Correct answer: Validating that column names, data types, lengths, and constraints in the target match the agreed-upon design specifications
Metadata testing checks structural correctness — ensuring the target schema (column names, types, nullable flags, etc.) matches the mapping specification.
Question 6: During ETL performance testing, a 'scalability' test is designed to verify that:
- The ETL job produces correct results with a 10-row sample dataset
- The pipeline maintains acceptable throughput and run times as data volumes grow toward peak production levels (Correct answer)
- All source system credentials are valid
- Transformation rules are documented in the mapping sheet
Correct answer: The pipeline maintains acceptable throughput and run times as data volumes grow toward peak production levels
Scalability testing ramps up data volumes to confirm the ETL does not degrade unacceptably and will meet SLAs at maximum expected load.
Question 7: A 'full refresh' ETL load is most appropriate when:
- The source data volume is very large and changes frequently
- The target table is small, has no reliable change-tracking mechanism, or requires a clean slate on each run (Correct answer)
- The ETL window is very short and time is critical
- The target table has complex SCD Type 2 history requirements
Correct answer: The target table is small, has no reliable change-tracking mechanism, or requires a clean slate on each run
Full refreshes are practical for small or medium reference tables where incremental tracking would be more complex than simply reloading everything.
ETL Testing Basic 4
Change Data Capture (CDC) in ETL primarily captures: