The table below summarizes the pros and cons of each approach to integration.
Extract, Transform, Load
|Handles Simple Mappings||✓||✓||✓|
|Handles Complex Mappings
Complex mappings are master-detail mappings between systems where data elements don't naturally line up
|Handles Simple Customs Mappings
Ability to handle mappings other than those that are pre-configured
|Handles Complex Custom Mappings||✓|
|Bi-Directional Sync Capability||✓||sometimes||seldom|
Can be modified to meet changing business requirements
|Access Live Data||✓||usually|
Makes all data from all systems appear as though it is in single database
|SQL Queries Across Non-Database Sources||✓|
|Uses SQL For Mapping, Transformations, Sync||✓|
|Uses Proprietary Language For Mapping, Transformations, Sync||✓|
|Setup & Configuration||Easy||Very Easy||Complex|
A Federated Database is the newest approach to solving long standing integration challenges. Essentially you have a single database (in the cloud) that has federated links to some or all of your other systems. The existing systems in this case can be on-prem applications with underlying databases (e.g. on-prem ERP) or newer cloud based SaaS offerings accessible only via APIs. federated drivers link their respective systems into your federated database. This makes it appear as though all of your data from all of your systems is in one big database. You can issue normal SQL queries to select data and read/write/update data across systems. API programming is eliminated. Synchronization between systems can be live. "Integration" becomes just like moving data around in a single database.
Federated-based integrations have unlimited configurability, live access to the freshest data, and full support for bi-directional integration.
Most integrations today still rely on some form of Extract, Transform, Load (ETL). Essentially the data is extracted from one system, transformed in some way, and loaded into another system. This if often performed nightly. There are two primary problems with this approach: synchronization and stale data. The largest challenge is synchronization. If the data is flowing only from system A to system B this is generally not an issue. However, if system B needs to change that data in some way and send those changes back to system A, things can quickly get complicated when you need to prevent one system from overwriting changes made by another system. Bi-directional sync is often avoided in ETL for this reason. The benefit of the ETL approach is that the data mappings are typically customizable and therefore can meet changing business requirements.
ETL-based integrations make you wait for your data and lack support for effective bi-directional integration.
Although there are many types of plugins used for integration, plugins typically try to provide semi-live integration and therefore can sometimes overcome the bi-directional sync issues and data freshness issues that plague ETL-based integrations. However, plugin-based integrations typically have very limited configuration options and are often only viable for the simplest of integrations. Businesses looking for an enterprise-class solution generally need more flexibility than a plugin can provide.
Plugin-based integrations have limited configurability and seldom work for anything beyond simple use cases.