ETL Testing ETL Data Extraction Techniques 2 — Questions and Answers
Question 1: In ETL testing, what is the purpose of a 'source-to-staging' validation?
- Verify transformations were applied correctly
- Confirm that raw extracted data landed intact in the staging area (Correct answer)
- Check target table indexes
- Validate final report output
Correct answer: Confirm that raw extracted data landed intact in the staging area
Source-to-staging validation ensures that data extracted from the source arrived in the staging zone without corruption or loss.
Question 2: A trigger-based extraction fires a database trigger on DML events. What is a key risk of this approach?
- It cannot capture DELETE operations
- It adds performance overhead to the source system (Correct answer)
- It requires a data warehouse license
- It only works on NoSQL databases
Correct answer: It adds performance overhead to the source system
Trigger-based extraction executes additional logic on every insert, update, or delete, which can slow down the source system under heavy transactional load.
Question 3: What is 'push extraction' in ETL architecture?
- The ETL engine queries the source on a schedule
- The source system sends data to the ETL layer when changes occur (Correct answer)
- Data is manually uploaded by end users
- The target database requests data from staging
Correct answer: The source system sends data to the ETL layer when changes occur
In push extraction, the source system proactively delivers changed data to the ETL pipeline, often via messaging queues or event streams.
Question 4: During ETL extraction testing, you notice the extracted file has fewer rows than the source query returns. What is the FIRST diagnostic step?
- Rebuild the target warehouse schema
- Check for extraction filters or WHERE clause conditions applied during pull (Correct answer)
- Delete and re-run the full load
- Increase database connection pool size
Correct answer: Check for extraction filters or WHERE clause conditions applied during pull
Extraction filters in the ETL configuration can silently limit rows, so verifying the extraction query or filter logic is the first step when row counts don't match.
Question 5: Which data extraction method is BEST for near-real-time ETL pipelines that need sub-minute latency?
- Nightly full table scan
- Weekly incremental batch
- Streaming CDC via Kafka or similar messaging (Correct answer)
- Manual file drop
Correct answer: Streaming CDC via Kafka or similar messaging
Streaming CDC publishes change events to a message broker in real time, enabling near-instant ingestion into downstream systems.
Question 6: What is a 'watermark' in incremental ETL extraction?
- A data encryption marker
- A stored timestamp or ID used to identify where the last extraction ended (Correct answer)
- A schema version number
- A checksum of extracted records
Correct answer: A stored timestamp or ID used to identify where the last extraction ended
A watermark records the high-water mark of the last successful extraction (e.g., max timestamp or max ID) so the next run knows where to start.
ETL Testing ETL Data Extraction Techniques 2
In ETL testing, what is the purpose of a 'source-to-staging' validation?