Data Integration and ETL
What this domain tests
Can you design a load that is still correct on day 100? Scenarios target incremental logic, idempotency, late-arriving data, delete handling and reconciliation.
Core rules
- ETL transforms before loading into the warehouse; ELT loads raw then transforms in-warehouse. ELT suits elastic columnar platforms and preserves raw data for lineage and replay.
- Extraction: full (simple, expensive), incremental by high-water mark, or log-based CDC, which reads the transaction log and captures inserts, updates and deletes with low source impact.
- Loading: full refresh of a partition, append, or upsert/merge on the natural key. Merges must handle deletes explicitly; a common failure is a record deleted in the source but still present in the warehouse.
- Idempotency: rerunning a batch must produce the same result. Use partition overwrite, delete-then-insert for the batch window, or a deterministic merge.
- Late-arriving data: use an overlap window such as reprocessing the last N days rather than a strict greater-than-max-timestamp watermark, because sources backdate records.
- Do not assume exactly-once end to end; design at-least-once delivery plus deduplication on a deterministic key.
- Every pipeline needs control totals or row-count reconciliation, a dead-letter path for rejects, restartability from a checkpoint, and UTC timestamps.
Worked example
A nightly load pulls orders where updated_at is greater than the last run. On Monday, 300 Saturday orders appear with an older updated_at because the source backfilled them. Reasoning: a strict watermark drops them permanently. The fix: (1) pull a rolling window, for example last run minus three days; (2) merge on order_id with the latest updated_at winning; (3) deduplicate identical keys; (4) reconcile source and target counts for the window and alert on variance; (5) record the batch watermark in a control table. Rerunning the same window must not duplicate rows - that is the idempotency test.
Traps that cost marks
- Watermarks that ignore backdated updates and hard deletes.
- Non-idempotent append loads that duplicate on retry.
- Assuming streaming guarantees exactly-once across every sink.
- Ignoring time zones and daylight-saving shifts in daily partitions.
- No reconciliation, so silent row loss goes unnoticed for weeks.