Data integration methods: ETL, ELT and event-driven sync

When two systems in a business need the same data, there are three common ways to move it: ETL, ELT and event-driven sync. Picking between them is less about fashion and more about four questions: how fresh the data has to be, how clean it has to be, who is allowed to see it, and what happens when a run fails. This guide explains each method in plain terms and shows the parts that usually break: retries, duplicates and schema changes.

Integration tools are not warehouses

Older articles on this topic, including our previous version, mixed up two kinds of product.

• Integration tools move data. Examples are connector platforms such as Airbyte, workflow tools such as n8n, Make or Zapier, and change data capture tools such as Debezium.
• Warehouses and databases store data and let you query it. Examples are a Postgres database, BigQuery or Snowflake.

You usually need one of each if you want reporting across systems. If you only need one app to react to another, for example creating an invoice when a deal closes, you may not need a warehouse at all.

The three methods in plain terms

ETL: extract, transform, load

Data is pulled from the source, cleaned and reshaped on the way, then loaded into the destination. The destination only ever sees the finished version.

Choose it when the destination should not hold raw data, for example when you must drop personal fields before anything reaches a shared reporting database, or when the destination is another business app that expects a fixed format.

ELT: extract, load, transform

Data is loaded raw into the warehouse first, then transformed there with SQL or a transformation tool.

Choose it when you want to keep the raw history and change your reporting logic later without re-extracting. A common misunderstanding is that ELT means real time. It does not. ELT describes where the transformation happens, not how often data moves. Most ELT pipelines run on a schedule, every hour or every night, and the data is only as fresh as the last run.

Event-driven sync

Instead of pulling on a schedule, the source sends a message when something happens: a webhook when an order is paid, or a change event when a database row is updated. Change data capture tools such as Debezium describe their job as capturing row-level changes in your databases and publishing them as a stream other applications can read.

Choose it when a downstream system needs to react within seconds or minutes, such as updating stock, notifying a customer or starting a fulfillment step.

Decision table

Many businesses end up using two methods: events for operational actions and ELT for reporting.

Retries and idempotency

Every integration will see failures. The question is whether a retry causes damage.

Stripe's webhook documentation is a good model of what to expect from any event source. It says Stripe retries undelivered events with exponential backoff for up to three days in live mode, that endpoints occasionally receive the same event more than once, and that it does not guarantee events arrive in the order they were generated. Its advice: log the IDs of events you have processed and skip ones you have seen, return a success response quickly before doing slow work, and process events through a queue.

Turn that into rules for any integration you build:

1. Give every record a stable key from the source system, and write with "insert or update" on that key instead of plain inserts.
2. Keep a table of processed event or batch IDs. Check it before acting.
3. Do not rely on arrival order. If the order matters, fetch the current state from the source API before writing.
4. Acknowledge fast, then do the work in a queue, so a slow downstream system does not cause the sender to retry.
5. Send failures that keep failing to a separate list a person reviews, rather than retrying forever.

An idempotent step is one you can run twice with the same result as running it once. If every step is idempotent, retries stop being scary.

Schema changes

Sources change. A field is renamed, a column is added, a type changes from number to text. Decide in advance what your pipeline should do.

Airbyte's documentation shows the choices a connector platform typically offers per connection: propagate field changes only, propagate all field and stream changes, detect changes but wait for a person to approve them, or stop future syncs when any change is detected. It also treats some changes as breaking, such as removing a primary key or cursor field, and pauses the connection for manual review whatever the setting.

For a small team, a sensible default is to approve changes manually on anything that feeds finance or customer-facing data, and to alert a named person when a change is detected. If your workflow tool has no such setting, add a check that compares incoming fields to an expected list and stops on a mismatch.

Check licensing and availability before you choose

Tool roundups age badly. Products change licenses, get acquired, or drop their free tier. Before committing:

• Read the license in the official docs. For example, Airbyte's license FAQ says the platform uses the Elastic License 2.0, which among other things prohibits providing the product to others as a managed service. That matters if you plan to host it for clients, not if you run it for your own company.
• Confirm the connector you need exists and is maintained, and who maintains it.
• Check where the service stores data and whether that matches your customers' expectations and your obligations.
• Check the current pricing page yourself. Do not rely on numbers in articles.

Recovery checklist

• Can you rerun yesterday's job without creating duplicates?
• Can you replay events from a given time if the receiver was down?
• Do you compare record counts between source and destination on a schedule?
• Is there one named person who gets the failure alert?
• Is there a written note on how to backfill after a long outage?

Where automotif fits

This is automation builds work: we design and build the sync between your systems in your own accounts, using n8n, Make or Zapier where they fit and code where they do not, with retries, deduplication and failure alerts in place from the start. To map your systems and data flows with us, book a free call for 30 to 60 minutes.

Sources

• Receive Stripe events in your webhook endpoint
• Schema change management (Airbyte Docs)
• License FAQ (Airbyte Docs)
• Debezium Documentation