Data Integration and ETL
Domain 5 - Data Integration and ETL
Expect questions on batch versus streaming, change data capture, idempotency, and how a pipeline behaves when the source misbehaves.
Patterns
ETL transforms before loading; ELT loads raw data first and transforms inside the warehouse, which suits columnar engines and keeps raw data replayable. Batch fits periodic reporting; streaming fits event-level latency requirements and brings out-of-order events, watermarks for late data and exactly-once concerns. CDC captures only changes - log-based CDC reads the transaction log (low source impact, order-preserving, captures deletes) while timestamp polling misses hard deletes and can miss rows updated during the poll window.
Pipeline reliability
Make every load idempotent: rerunning the same batch must not duplicate rows. Use MERGE on the natural key, delete-insert of a partition, or a unique batch_id. Track high-water marks, handle late-arriving facts with a lookback window, and apply slowly changing dimension logic (Type 1 overwrite, Type 2 versioned rows). Orchestrate with explicit dependency DAGs, retries with backoff, and a dead-letter path for poison records. Reconcile every run: source versus target row counts, control totals, checksums. Agree data contracts and alert on schema drift rather than letting a renamed column silently null a report.
Worked example
A nightly orders load duplicated rows after a failed run was retried. Diagnosis: the job used plain INSERT with no key check. Fix: stage the extract, then MERGE INTO fact_orders USING staging ON fact_orders.order_id = staging.order_id, updating changed attributes and inserting new ones inside a transaction keyed by batch_id, so a second run of the same batch is a no-op. Add a post-load check that counts by order_date match staging exactly.
Traps
- Non-idempotent loads - the most common production incident in this domain.
- Relying on updated_at alone; clock skew and bulk updates break it.
- Ignoring late-arriving data, so yesterday's report changes silently today.
- Assuming schemas never change: no drift detection, no alerting.
- Loading without reconciliation, so failures are discovered by the business.
- Full refresh every night when the volume no longer fits the window.