Data Integration Patterns for Enterprise Systems
The CRM does not know what the ERP shipped; the warehouse system does not know what the web store sold. Making systems talk is data integration, and the single biggest decision is the pattern you choose per connection. There is no universal best — only a best fit for each pair of systems, each latency requirement, each failure budget.
The three fundamental patterns
Every integration is a variation of three primitives. ETL (extract-transform-load) moves data in scheduled batches: a job pulls, reshapes and writes, nightly or hourly. API calls are synchronous request-response: one system asks another and waits. Events are asynchronous: a system publishes a fact, and interested systems react whenever they can. ETL is simplest but stale; APIs are immediate but couple caller to callee; events decouple but add infrastructure.
Match the pattern to the need
- Reference data that changes daily — overnight ETL into the data warehouse. Fresh enough, trivially simple.
- "Does this customer exist?" during order entry — synchronous API call; the answer is needed before the user moves on.
- "An order was placed" — an event. Inventory, shipping and analytics all react, at their own pace, without the order system knowing who listens.
- A regulator's monthly report — batch ETL plus validation; nobody needs it faster, and correctness beats speed.
Choosing is not a one-time architecture diagram. Different data in the same company legitimately uses different patterns.
Idempotency is not optional
Networks fail and messages redeliver. If processing "order created" twice creates two shipments, the integration will eventually corrupt data — not often, but always at the worst moment. Make every consumer idempotent: track processed message IDs, or design handlers so repeating the same event is a no-op ("set status to shipped", not "increment shipment count"). This one discipline eliminates the most feared class of integration bugs.
Retries, dead letters and reconciliation
Retry failed calls with exponential backoff and a cap — hammering a struggling system multiplies the outage. After the cap, park the message in a dead-letter queue with full context, and alert a human; silent loss is the unforgivable failure. Independently of all that, run periodic reconciliation: compare counts and checksums between source and target, and surface drift before users find it in a report.
Events fit master data distribution
Recall the master data rule that downstream systems hold read-only copies. Events are the natural carrier: the owning system publishes "customer changed", and every subscriber updates its copy within seconds. New systems join by subscribing — no schema exports, no point-to-point plumbing. The pattern that governs data and the pattern that moves it turn out to be the same decision.
Own each connection end to end
Integrations rot fastest at the seams — the handoff where "their system" becomes "your problem". Assign every connection a single owner with end-to-end responsibility: the runbook, the alerts, the reconciliation results and the vendor conversation. When a feed breaks at 2 a.m., the owner answers, not a round-robin of teams each pointing at the other. Shared-nothing ownership is how integrations silently fail for weeks while every team assumes someone else is watching.
Resist the urge to unify on one pattern for elegance. A company that declares events everywhere eventually discovers that a nightly batch, rebuilt in three days, would have served a dozen reports perfectly well. Fit the pattern to the data, and let different connections look different.
Conclusion
Pick ETL for tolerance, APIs for immediacy, events for decoupling — per connection, not per company. Then do the unglamorous work that decides whether any of it survives production: idempotent consumers, bounded retries, dead-letter queues and reconciliation. Reliable pipes finally make it possible to answer the hardest question of all — who is allowed to see this data — with confidence.