Data Integration and Interoperability
Data Integration and Interoperability tests how data moves and converges: batch or streaming, push or pull, ETL or ELT, and how conflicting sources are resolved into one trusted record. Expect scenarios on change data capture, idempotency, schema evolution, and master data survivorship.
Core rules and concepts
- ETL transforms before loading; ELT loads raw and transforms in the target, relying on warehouse compute. ELT preserves source detail and supports reprocessing; ETL suits strict targets and legacy constraints.
- Batch suits volume and reconciliation windows; streaming suits latency requirements and costs more in complexity and operations. Micro-batch is a middle path.
- Change data capture reads logs or timestamps to move only changes; design for deletes, schema changes, and out-of-order arrivals. Idempotent loads, keyed on a stable key with merge or upsert, allow safe retries.
- Interfaces need a contract: schema, semantics, quality thresholds, latency, and ownership. Schema evolution must be backward compatible or explicitly versioned.
- Master data management resolves entities across sources with match and merge rules, survivorship, and a golden record; the golden record must be written back or consistently referenced to be useful.
- Reconcile with control totals and row counts; silent partial loads are the most expensive failure.
Worked example
Two CRM systems are merged; the same customer exists in both with different addresses, and one has a closed account. Reasoning: match on deterministic keys first (tax ID, normalized email), then probabilistic scoring for the remainder, with a review queue for borderline pairs. Survivorship rules are agreed per attribute: the most recently verified address wins, the legal name comes from the finance system, and the most recent lifecycle status wins, so a closed account is not reopened. The golden record, plus the cross-reference table linking source IDs, is published. Deletion and consent flags are carried through, because the merged record is still personal data.
Traps that cost marks
- Promising exactly-once delivery end to end; guarantee idempotent effects instead.
- Ignoring late-arriving or deleted records, which quietly skew facts.
- Assuming schemas never change and breaking downstream consumers.
- Choosing streaming for a requirement that is satisfied daily.
- Merging without a cross-reference table, so lineage and re-resolution are impossible.