Data integration, ETL and ELT: a practical guide

It usually starts with a spreadsheet. Someone exports sales from the ERP, orders from the e-shop and campaigns from the CRM, pastes them together and builds a report. It works until the export takes half a day, the numbers don’t match last week’s, and management asks for the same report every morning.

Data integration is how you replace that spreadsheet with something reliable: pipelines that copy data from operational systems into one place built for analysis, keep it up to date, and make it trustworthy. This guide covers the main ideas, ETL versus ELT, how to keep loads incremental, the problems that appear in production, and how to compare tools.

Data integration vs application integration

Both move data between systems, but they answer different questions.

	Application integration	Data integration
Goal	Make a business process work across systems	Make data available for analysis
Example	E-shop order creates a sales order in the ERP	All orders of the last 5 years in a warehouse
Unit of work	One record or event, often in real time	Tables or large batches, on a schedule
Direction	Often both ways	Mostly one way: sources to warehouse or lake
Main concern	Correctness of each transaction	Completeness, history, query performance

Many companies need both, and the tools overlap. If your question is “how do I keep the ERP and the e-shop in sync?”, read our application integration guide instead.

The building blocks

Sources and destinations

Sources are the systems that hold the data: ERP and CRM databases, SaaS applications through their APIs, files on SFTP, spreadsheets. Destinations are where analysis happens:

A cloud data warehouse (Snowflake, Google BigQuery, Amazon Redshift, Azure Synapse or Fabric, Databricks SQL): structured tables, SQL, fast analytical queries.
A data lake (files in object storage such as Amazon S3 or Azure Data Lake Storage): cheap storage for raw and semi-structured data, queried with engines on top.
A lakehouse, which combines both ideas using open table formats.

For most SMEs, a cloud warehouse is the simplest starting point.

ETL and ELT

The difference is where the transformation happens.

ETL (extract, transform, load): data is transformed in the integration tool, then loaded into the destination in its final shape. This was the default when warehouse storage and compute were expensive.
ELT (extract, load, transform): raw data is loaded first, then transformed inside the warehouse with SQL. Modern cloud warehouses make this cheap and fast, and keeping the raw data means you can rebuild any model later.

ETL:  ERP ──► transform ──► warehouse (final tables)
ELT:  ERP ──► warehouse (raw tables) ──► transform in SQL ──► final tables

ELT is the common default today. ETL still makes sense when data must be masked or filtered before it leaves your network, for example personal data you are not allowed to store in the cloud.

Batch, incremental and CDC

Full load: copy the whole table every time. Simple and correct, but slow and expensive as tables grow.
Incremental load: copy only rows changed since the last run, usually using a “last modified” column as a watermark.
Change data capture (CDC): read the database’s transaction log to capture every insert, update and delete as it happens, with minimal load on the source.

Incremental loads need care with deletes and late-arriving changes. We cover that in detail in Incremental loads and CDC with Boomi Data Integration.

Orchestration

Pipelines depend on each other: load customers and orders, then build the sales model, then refresh the dashboard. Orchestration runs the steps in the right order, on a schedule or on a trigger, retries failures and alerts someone when things break.

Data quality

Loading data is the easy part; trusting it is harder. Typical checks: row counts compared with the source, no duplicate keys, no unexpected nulls in required columns, totals that reconcile with the ERP (for example monthly revenue). Run them automatically after each load, not when a manager spots a strange number.

Problems that appear in production

Schema drift. Someone adds a column in the ERP, renames a field in the e-shop, or changes a type. A good pipeline detects this and either adapts (adds the column) or stops loudly; a bad one silently drops data.
API limits. SaaS sources throttle requests. A full load of three years of e-shop orders may take hours or hit the daily limit.
Time zones and timestamps. Mixing local Greek time and UTC in watermarks is a classic cause of missed rows twice a year.
Deletes. Incremental loads based on “last modified” don’t see deleted rows. Decide whether you need them.
Backfills. When logic changes or a load failed for a week, you need a safe way to reload a period without duplicates.

Do you need a dedicated tool?

You probably don’t yet if you have one or two sources, a few reports, and someone comfortable with scheduled SQL scripts. A nightly export plus a well-written script can carry you a long way.

You probably do if several of these are true: more than a handful of sources, SaaS applications with APIs among them, reports needed daily or more often, repeated “the numbers don’t match” discussions, or a person whose job has quietly become running exports.

Common mistakes

Transforming everything before loading. You lose the raw data and with it the ability to fix logic later.
Full loads forever. Fine at 10,000 rows, painful at 10 million. Plan incremental loads for large tables early.
No monitoring. A pipeline that failed silently for three days is worse than no pipeline.
Business logic in five places. Keep calculations such as “net revenue” in one transformation layer, version controlled, not repeated in every report.
Ignoring personal data. Copying CRM and e-shop customers into a warehouse is processing personal data under GDPR. Load only what you need and control access.

How to compare tools

Connectors for your sources. Check your actual ERP (often a SQL Server or Oracle database underneath), CRM and e-shop, and what happens when there is no ready-made connector.
Incremental and CDC support. Which sources support CDC, and how deletes are handled.
ETL, ELT or both. Does the tool transform itself, push SQL down to the warehouse, or leave transformation to a separate tool?
Orchestration and monitoring. Dependencies, retries, alerts, logs you can actually read.
Deployment model. Fully managed SaaS, self-hosted, or hybrid with an agent close to on-premises databases.
Pricing model. By rows, by volume, by connectors, by compute credits. Model it with your real data volumes and change rates.

Tools in this space

In alphabetical order. Many teams combine a loading tool with a separate transformation tool.

Airbyte. An open-source data movement platform with a large catalogue of connectors, available self-hosted or as a cloud service. Popular with data teams that want control and the ability to build their own connectors.
Azure Data Factory. Microsoft’s cloud data integration service for orchestrating copy and transformation pipelines, with a self-hosted runtime for on-premises sources. A natural fit for organisations on Azure and Microsoft Fabric.
Boomi Data Integration. The ELT service that came to Boomi through its 2024 acquisition of Rivery. It provides managed source-to-target pipelines, CDC for common databases and orchestration of SQL and Python logic in the warehouse. It fits best for teams that already use Boomi for application integration and want analytics pipelines on the same platform.
dbt. Not a loading tool but the de facto standard for the “T” in ELT: SQL-based transformation models with testing, documentation and version control, run inside your warehouse. Available as open-source dbt Core and as dbt Cloud.
Fivetran. A fully managed ELT service known for low-maintenance connectors that adapt to schema changes automatically. Teams value that it “just runs”; pricing is usage-based, so model your volumes.
Informatica. A long-standing enterprise leader in data integration and data quality, now centred on its cloud platform (IDMC). Strong in complex, governed enterprise environments.

Where to go next

Two practical articles build these patterns with Boomi Data Integration:

Loading ERP and e-shop data into a cloud data warehouse with Boomi Data Integration: source-to-target pipelines, scheduling and transformation in the warehouse.
Incremental loads and CDC with Boomi Data Integration: watermarks, log-based CDC, deletes and backfills.