Data Integration and Engineering: Pipelines, Patterns, and Reliability
What to master
Integration covers extract patterns (full, incremental, CDC), load strategies (batch, micro-batch, streaming), and transformation placement (ELT vs ETL). Understand idempotency-rerunning a job must not duplicate data-and exactly-once semantics versus at-least-once with deduplication. Know orchestration concepts (DAGs, dependencies, retries, backfills) and data contracts between producers and consumers. Study change data capture via logs or timestamps, watermarking for late-arriving data, and schema evolution handling. Reliability topics include observability (freshness, volume, schema drift alerts), lineage, and reconciliation controls between source and target.
Traps in questions
- Assuming streaming is always superior; it adds complexity and late-data handling costs.
- Confusing idempotency with exactly-once delivery-they are related but distinct guarantees.
- Forgetting that backfills must preserve idempotency or they corrupt history.
- Treating schema drift as an exception rather than an expected event requiring policy.
Worked example
A daily load reruns after a failure. If the job appends without a key, rows duplicate. Fix: merge on a natural or surrogate key with an upsert, making the job idempotent so reruns converge to the same state.
Rule of thumb: design every pipeline so that running it twice produces the same result as running it once.