ERP Integration Architecture: API vs iPaaS vs ETL
ERP integration architecture API vs iPaaS vs ETL which should we use
Also searched as
- should we use iPaaS or point to point ERP integration
- ERP API vs ETL vs middleware decision
- how to choose ERP integration approach CTO
- iPaaS vs custom integration for ERP
Short answer
Choose by what the data does, not by tool preference. Use the ERP's own API (or its native bus) for transactional writes that must be validated by ERP business logic, ETL or change data capture for bulk analytical reads, and an iPaaS when you have many endpoints, need monitoring and retries, and lack the staff to run your own middleware. Most real estates need two of the three.
Applies to: Any ERP: NetSuite, Infor SyteLine, M3, LN, SAP S/4HANA and ECC, Dynamics 365 F&O, Oracle Fusion, Epicor. Applies to cloud, hybrid and on-prem estates.
How to decide the integration pattern for each flow
- 1List every integration as a flow with four attributes: direction (read or write), latency need (seconds, minutes, nightly), volume (records per run) and owner of the business rule.
- 2Route every write that creates or changes ERP transactions (orders, receipts, journals) through the ERP's supported API so its validation and security run. Never write directly to ERP tables.
- 3Route bulk reads for reporting and analytics through ETL, a vendor replication feature or change data capture into a separate store, so reporting load stays off the transactional database.
- 4Count your distinct endpoints. If you connect fewer than about five systems to the ERP, point to point with a thin shared library is usually cheaper than an iPaaS licence.
- 5If you connect many SaaS tools (CRM, e-commerce, tax, carriers, WMS), shortlist an iPaaS and check that it has a maintained connector for your ERP version, not just a generic HTTP one.
- 6Define the failure contract per flow: retry policy, idempotency key, dead-letter handling, and who gets alerted. Reject any option that cannot meet it.
- 7Check the ERP's rate and concurrency limits (for example NetSuite governance and concurrency limits per account) against peak load before committing.
- 8Document the decision per flow in one page and review it when the ERP version or volume changes.
What each pattern is actually good at
Direct API integration means a service you control calls the ERP's published interface: NetSuite SuiteTalk REST or SOAP and RESTlets, SAP OData services and BAPIs, Infor ION API and BODs, Dynamics 365 OData and Dataverse, Oracle Fusion REST. Its strength is that ERP business logic validates every write. Its weakness is that you own error handling, pagination, throttling and version drift.
ETL and change data capture are built for volume. A scheduled extract or a log-based CDC tool copies data out of the ERP's database or a vendor replica into a warehouse or lakehouse. This is the right default for reporting, planning models and AI retrieval, because large reads never compete with order entry. It is the wrong tool for pushing data back in.
An iPaaS (Boomi, Workato, MuleSoft, Celigo, Oracle Integration Cloud and similar) sits in the middle and sells connectors, mapping, scheduling, monitoring and retries as a managed service. It pays off when the number of endpoints and the pace of change is high. It costs more than it appears to once you add per-connection or per-message pricing and the mapping logic that now lives in a vendor console.
A decision rule that survives contact with reality
Start from the write path. Anything that creates an order, a purchase receipt, a journal or a work order must go through the ERP's own interface, because the ERP enforces periods, credit holds, item status, tax and approvals. Direct SQL inserts skip all of that and tend to corrupt sequence tables, which is a common cause of upgrade pain.
Then look at the read path. If a dashboard, a planning tool or an AI assistant needs the data, ask whether it can tolerate minutes of lag. If yes, replicate to a separate store and read there. If it needs the current state of one record at the moment of a transaction, such as available-to-promise at order entry, call the ERP API for that single lookup.
Finally count endpoints and staff. Three systems and a capable developer favour direct APIs with a small shared client. Fifteen SaaS systems and a two-person integration team favour an iPaaS. A large manufacturer with SAP or Infor already usually has a native bus (SAP Integration Suite, Infor ION) that should be evaluated before buying another layer.
Flow: Sales order from e-commerce -> ERP Direction: write | Latency: seconds | Owner of rules: ERP Pattern: API (queue in front, idempotency key = web order id) Flow: ERP -> data warehouse Direction: read | Latency: 15 min | Volume: millions of rows Pattern: CDC or vendor replication, not API polling Flow: 12 SaaS tools <-> ERP master data Direction: both | Many endpoints | Small team Pattern: iPaaS with maintained ERP connector
How each approach fails in production
API integrations fail on limits and drift. Throttling (NetSuite concurrency, SAP gateway timeouts, Infor ION API quotas) turns a nightly batch that worked at 5,000 records into an outage at 50,000. Version upgrades change field names and required fields. Mitigate with a queue in front of the ERP, bounded concurrency, and contract tests that run against a sandbox after every ERP update.
ETL fails silently. A schema change in the ERP, a custom field added by a consultant, or a changed primary key breaks a nightly job, and the warehouse serves stale data that looks plausible. Add row count and freshness checks, and alert when a source table's last update is older than its expected cadence.
iPaaS fails through lock-in and hidden logic. Mapping rules, transformations and error routing end up in a vendor's visual designer where they are hard to version-control, test or move. Insist on exportable definitions, source control integration and a documented exit path before signing a multi-year contract.
Common pitfalls
- !Writing straight into ERP tables to save time. It bypasses validation and security and is a frequent source of failed upgrades and support disputes with the vendor.
- !Polling the ERP API every minute for bulk data. It consumes API quota and degrades interactive users. Use replication or CDC for volume.
- !Buying an iPaaS before counting endpoints. With a handful of systems the licence and mapping lock-in can exceed the cost of a small maintained codebase.
- !No idempotency key on writes. A retry after a timeout creates duplicate orders or receipts, which are expensive to reverse in an ERP.
- !Treating the integration as finished at go-live. Without contract tests and monitoring, the first ERP update silently breaks field mappings.
- !Mixing business rules into the middleware. Pricing, approvals and costing logic belong in the ERP, or you end up with two sources of truth.
How an ERP-grounded AI assistant handles this
An AI assistant grounded in your ERP is a read-heavy consumer, so it belongs on the replicated or read-only side of this architecture, not on the transactional write path. ERPray connects through read-only connectors, shows the query it ran and respects ERP roles; any proposed write-back is routed to a human approval step and then through the ERP's own API rather than direct SQL.
Frequently asked questions
Is an iPaaS always better than custom integration code?
No. An iPaaS earns its cost when you have many endpoints, frequent change and a small team that needs managed monitoring and retries. With a few stable integrations and an engineer who can maintain code, a small shared client library with a queue is often cheaper and easier to test and version.
Can I use the ERP API for reporting data extracts?
For small, bounded lookups yes. For bulk reporting it is the wrong tool: pagination, throttling and API quotas make it slow, and it competes with transactional users. Use vendor replication, a database replica or change data capture into a separate analytical store instead.
When is direct database access acceptable?
Read-only access to a replica or a vendor-supported reporting view is generally acceptable and common, for example SuiteAnalytics Connect for NetSuite or a SQL Server read replica for on-prem ERPs. Writing to production ERP tables is not acceptable unless the vendor explicitly documents that interface.
Do I need event streaming such as Kafka for ERP integration?
Only if you need many consumers reacting to the same changes with low latency or need replayable history. Most estates start with queues and scheduled replication. Add a streaming backbone when the number of consumers grows and point to point feeds start to multiply.
Related
Event-Driven ERP Integration: Architecture That Holds Up
Use events when several systems must react to ERP changes quickly and you want to stop polling. Get events from the ERP's native mechanism where one exists, otherwise from change data capture, and design every consumer to be idempotent and tolerant of reordering. Keep the ERP as the system of record and treat events as notifications, not as the ledger.
CIO briefingData Platform for ERP Data: Replica, Warehouse or Lakehouse
Start with the smallest thing that removes reporting load from the ERP: a read replica or vendor replication feed. Add a warehouse or lakehouse when you must join ERP data with other sources, keep history, or serve many consumers. Put a governed semantic layer on top, because raw ERP tables are not a usable analytics or AI interface on their own.
CIO briefingBuild vs Buy for ERP Extensions: A CTO Decision Guide
Work down a ladder: configure the standard product, then buy a supported ISV add-on, then build outside the ERP core against its APIs, and only then modify in the core. Each step down raises upgrade risk and lifetime cost. Build only where the capability is a genuine differentiator or no product fits, and keep the code upgrade-safe and owned.
How-toHow to Run an ERP User Access Review (Quarterly Checklist)
An ERP user access review is a recurring control where system owners confirm that every active user, role and privileged permission is still justified. Extract users and roles from the ERP itself, join them to HR status, send each business owner a scoped list to certify or revoke, record the decisions, and execute the removals with ticket evidence.
How-toHow to Test ERP Backup and Disaster Recovery
A backup is only proven when you have restored it and a user has transacted in the restored system. Define RTO and RPO with the business, restore the full ERP stack (database, application, integrations, attachments, secrets) to an isolated environment, run a scripted smoke test, time every step, and record gaps as action items.
How-toERP Patching Cadence: How Often Should You Patch?
Patch the ERP stack in layers on different clocks: operating system and database monthly or quarterly, ERP vendor cumulative updates quarterly or semi-annually, and security fixes on an emergency path measured in days. Test every patch in a sandbox refreshed from production, with a scripted regression and a tested rollback.
AI for ERPAI Agents Over Infor ION API, BODs, and the Infor Data Lake
Build AI agents on Infor ION API Gateway, BODs, and Data Lake, alongside or instead of Infor's own GenAI features, with your choice of model and hosting.
AI for ERPAI for SAP S/4HANA, Running On-Prem or in Your Private Cloud
Run AI on SAP S/4HANA without sending ERP data to a public API. On-prem and private-cloud architecture, CDS views, OData, and honest deployment trade-offs.
Stuck on ERP integration (vendor-neutral)?
Talk to engineers who work inside ERP integration (vendor-neutral) every week, and who build private AI that answers these questions from your own ERP data.