Most data integration explainers are written for analytics teams moving data into warehouses. ERP-driven businesses have a different problem: the data is operational, it moves in two directions, and a duplicate record is not a reporting error, it is a duplicate shipment.
This is a field guide to how data integration works when an ERP like SAP Business One, Microsoft Dynamics 365 Business Central or NetSuite is the system of record, and a storefront, CRM and warehouse system all need to agree with it.
*How data integration works *
Data integration is the movement and reconciliation of data between systems so each application works from the same facts. In an ERP-centric stack, three patterns cover almost everything.
Batch (classic ETL): extract on a schedule, transform, load. Fine for reporting and initial loads. The failure mode is staleness: an hourly batch means your storefront sells from inventory that is up to an hour old.
Event-driven sync: a business event (order created, stock adjusted, price changed) triggers propagation to the systems that care. This is the pattern operational data wants, because latency drops from schedule-time to seconds. The cost is that event systems deliver at-least-once, so every consumer must tolerate duplicates.
API-level integration: request/response calls for lookups and writes, usually combined with events. Good for βcheck credit before order confirmβ logic that cannot wait for a sync.
Real stacks mix all three: batch for backfills, events for operational objects, APIs for synchronous checks.
*Choosing between batch and real-time *
The decision rule is simple: match the sync pattern to the cost of staleness per object. Inventory and orders are real-time objects; a stale value costs money within minutes (oversells, delayed fulfilment). Products and pricing tolerate minutes to hours. Historical transactions for reporting tolerate a nightly batch.
Running everything real-time is as much a design failure as running everything batch. Event volume has a cost, and most objects do not need it.
*Why point-to-point integration collapses *
With two systems you write one mapping. With five systems, point-to-point means up to ten pairwise mappings, each with its own field translations, retry logic and failure modes. Every new system multiplies the work, and every schema change in one system ripples through every mapping that touches it.
The alternative is a canonical data model: a hub-and-spoke design where each system maps once to a shared internal representation (order, product, customer), and the hub maps to everyone else. New system? One new mapping, not five. Schema change? One translation to update. The trade-off is upfront design work on the canonical objects, which is why small two-system integrations rightly skip it and multi-system stacks rightly do not. A longer discussion of when a canonical model earns its complexity is here: https://www.appseconnect.com/what-are-canonical-data-models/
*Implementing automated data integration without duplicates
*
Automation is where integrations earn trust or lose it, and duplicates are the usual failure. Three practices, all standard in well-run event systems, prevent most of them.
Deterministic identifiers. Derive record keys from the data (source system + document number), never generate random IDs on the write path. Retries then produce the same key, and the target can reject the duplicate safely.
Idempotent consumers. Treat redelivery as expected, not exceptional. The common implementation is an inbox pattern: record the processed message ID and apply the business write in one atomic transaction, with a unique constraint on the message ID.
Reconciliation as a habit. Even with idempotency, drift happens: a mapping bug, a manual edit in one system. Scheduled reconciliation jobs that compare object counts and checksums between systems catch what event flows miss.
Error handling completes the picture: failed records go to a queue with the failure reason, retries are bounded and logged, and a human sees a daily exception summary rather than discovering problems in month-end numbers.
*ETL tools vs integration platforms for ERP data *
They solve different problems and are bad substitutes for each other. ETL and ELT tools move data one way, on schedules, into warehouses, and they are excellent at it. Integration platforms (iPaaS) move operational data both ways between live applications, with connectors that understand ERP object semantics, event triggers, retry and reconciliation machinery built in.
The practical test: if the data's destination is a dashboard, you want ETL. If the data's destination is another operational system that will act on it, you want an integration platform, or you will end up rebuilding one inside your ETL tool. A deeper comparison of the canonical-model and platform approaches: https://www.appseconnect.com/canonical-data-model-vs-ipaas
*FAQ *
What is the difference between data integration and data migration?
Migration is a one-time move with a cutover date. Integration is a permanent, running capability that keeps systems consistent. Confusing the two is why βwe integrated last yearβ projects decay.
Do I need a canonical data model for two systems?
Usually not. Direct mapping is simpler and faster for a single pair. The canonical model pays off from roughly the third system onward, when pairwise mappings start multiplying.
What should be monitored in a production integration?
Per-flow success and failure counts, end-to-end latency per object type, dead-letter queue depth, and scheduled reconciliation diffs. If those four are green, the integration is healthy.
Top comments (0)