Cloud Parts

ETL & ELT pipelines

Copy data from app databases, SaaS APIs, files and streams into a warehouse or lake, then reshape it into tables people can query.

13 technologies on this page ↓
Live model
What you’re watchingOrder changes come from the database’s log and charges from an API cursor. The loader merges both into raw tables, SQL models rebuild clean tables from them, and a failed test stops a bad batch before it reaches the marts.

How it works

A pipeline has three stages and a scheduler. Extract reads the source. A full extract copies everything each time, which is simple but slow; an incremental extract keeps a cursor, such as the highest updated_at seen or an API’s page token, and asks only for what changed since. For databases, change data capture (CDC) reads the database’s own transaction log (PostgreSQL’s write-ahead log, MySQL’s binlog), so it sees every insert, update and delete, including deletes a cursor query would miss, without scanning the tables. Load writes the batch into the destination: append for events that never change, or merge (upsert) on the primary key so a changed row replaces its old version. Sources also change shape, which is called schema drift: a new column appears in the API, and the loader either adds it to the table or blocks it. Transform turns raw tables into clean ones: SQL models build on each other (stg_orders feeds fct_revenue), tests check that keys are unique and not null, and the dependency graph gives you lineage, so you can see what breaks if a column changes. In classic ETL the transform runs on a separate server before loading, so only finished tables reach the warehouse. In ELT the raw data is loaded first and transformed inside the warehouse with SQL, which keeps a raw copy to rebuild from when the logic changes. An orchestrator such as Airflow or Dagster runs it all on a schedule, in dependency order, retries failed steps and reruns past days (backfills). Reverse ETL runs the other way: it pushes modelled tables from the warehouse back into tools like a CRM.

Think of it as

A restaurant’s supply chain. ETL is a supplier who washes, peels and portions everything before delivery because the kitchen is tiny. ELT is a big walk-in fridge: everything arrives raw and the cooks prep it in-house, however today’s menu needs it. The orchestrator is the prep schedule on the wall.

Reach for it when

  • Analytics needs data from many systems (app database, billing, CRM, ad platforms) in one place
  • Reports can be minutes or hours behind the source
  • You want raw history in the warehouse so models can be rebuilt when business logic changes
  • Mirroring a production database into analytics without running heavy queries on it (CDC)

Think twice when

  • The app needs the answer in milliseconds; read from the service, a replica or a cache
  • You must react to each event within seconds (fraud checks, alerts); use stream processing
  • Source and warehouse are both in one cloud and a managed zero-ETL integration already replicates what you need

A real example

Every 15 minutes a connector reads new changes to the orders table from Postgres’s write-ahead log and pulls new charges from the billing API using the cursor it saved after the last run. Both land as raw tables in the warehouse: changed orders are merged on order_id, and when the billing API adds a tax_code field a new column appears. At 06:00 the orchestrator runs the SQL models: stg_orders cleans the raw table and fct_revenue joins it with charges. One night a bug duplicates orders; the unique test on order_id fails, the models downstream are skipped, and the finance dashboard shows yesterday’s numbers instead of wrong ones.

Technologies

Specific products, and what each does differently.

Managed connectors and change data capture

Hosted services that copy data in for you.

FivetranCommercialManaged connectors that copy data from SaaS apps, databases and files into your warehouse or lake, billed by the rows that change each month.AirbyteCommercialAn ELT platform you can host yourself: hundreds of connectors, each a Docker image speaking one JSON protocol, scheduled by services on Kubernetes.ArtieCommercialA managed change data capture service that copies every insert, update and delete from production databases into a warehouse, aiming for under a minute of delay.EstuaryCommercialA managed data-movement platform that captures changes from databases and SaaS sources into collections kept as JSON files in your own bucket, then keeps warehouses and other systems in sync, in real time or on a schedule.Tiger LakeBetaCommercialTiger Cloud’s built-in Iceberg connector, in public beta: it copies a Postgres table or TimescaleDB hypertable into Apache Iceberg, then keeps it current from the write-ahead log.Mainly Lakehouses

Also used for etl & elt pipelines

Products of other kinds that are often used this way, grouped by what they are.

What each provider calls it

AWS
AWS Glue (serverless Spark ETL jobs, crawlers, Data Catalog); DMS for full load plus CDC from database logs; zero-ETL integrations that replicate Aurora, RDS, DynamoDB and apps such as Salesforce into Redshift
Google Cloud
Datastream (serverless CDC into BigQuery, Cloud Storage or Iceberg); BigQuery Data Transfer Service for SaaS, ads and storage sources; Dataflow (Apache Beam) for code-heavy pipelines; Dataform for SQL transformations in BigQuery
Azure
Data Factory in Microsoft Fabric (copy job with CDC, pipelines, Dataflow Gen2, mirroring, dbt jobs), the successor to Azure Data Factory (pipelines, Copy activity, mapping data flows on Spark)
Open source & others
Extract and load: Airbyte (Elastic License 2.0, source-available), Meltano (Singer taps and targets), Apache NiFi, Debezium for log-based CDC, OLake (Apache 2.0, databases to Iceberg). Transform: dbt, SQLMesh. Both in one tool: Bruin. Orchestration: Apache Airflow, Dagster. Managed services: Fivetran, Estuary, Artie

In code · sql

-- Load: apply the latest extracted batch (one row per id, newest change)
MERGE INTO raw.orders AS t
USING raw.orders_changes AS s
  ON t.id = s.id
WHEN MATCHED AND s.op = 'delete' THEN DELETE
WHEN MATCHED THEN UPDATE SET status = s.status, amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED AND s.op <> 'delete' THEN
  INSERT (id, status, amount, created_at, updated_at)
  VALUES (s.id, s.status, s.amount, s.created_at, s.updated_at);

-- Transform: a model rebuilt from the raw table on every run
CREATE OR REPLACE TABLE analytics.daily_revenue AS
SELECT CAST(created_at AS DATE) AS day, SUM(amount) AS revenue
FROM raw.orders
WHERE status = 'paid'
GROUP BY 1;

-- Test: passes when it returns zero rows
SELECT id, COUNT(*) AS copies FROM raw.orders GROUP BY id HAVING COUNT(*) > 1;

Often confused with

Stream processingData warehousesLakehousesWorkflow orchestration
Where it came from1988–2026

Data warehouses needed filling. From the late 1980s, batch jobs pulled records out of operational systems, cleaned and reshaped them on a separate server, and loaded only the finished tables. Transforming first made sense because, as a Fivetran history puts it, “storage, computation and bandwidth were extremely scarce”. Companies such as Informatica, founded in 1993, sold tools for building these jobs.

Cloud warehouses changed the arithmetic. Amazon Redshift (2012), BigQuery and Snowflake made storage cheap and compute elastic, so it became simpler to load raw copies and transform them later with SQL inside the warehouse. Managed connectors such as Fivetran took over extraction, and dbt (2016), “the T in ELT” in Tristan Handy’s words, turned that SQL into versioned, tested code. Airflow, started at Airbnb in 2014, gave these jobs an open-source scheduler, and Debezium (2016) streamed changes straight from database logs.

The market is now consolidating. Salesforce bought Informatica in 2025, and Fivetran, which added reverse ETL by buying Census, completed a merger with dbt Labs in June 2026.

Why it’s built this way

Transform before loading (ETL)
Warehouse storage and compute were scarce and expensive, so jobs cut data down and conformed it on the way in. Only clean, finished tables took up warehouse space.
Load raw, transform inside the warehouse (ELT)
Once storage cost cents per gigabyte and compute scaled on demand, keeping a raw copy became cheap. Models can then be rebuilt from it whenever the business logic changes, without extracting again.
Read the database log instead of querying tables (CDC)
A cursor query on updated_at misses deletes and loads the source with scans. The transaction log already records every change in order, so tools such as Debezium and DMS read it instead.
Transformations as code
The dbt viewpoint argued analytics had “a workflow problem”: SQL scattered across scripts and BI tools. Keeping models in version control with tests brought the review and testing habits of software teams.

Timeline

  1. 1988

    Devlin and Murphy at IBM describe a warehouse loaded from operational systems

  2. 1993

    Informatica is founded; graphical ETL tools spread through the 1990s

  3. 2012

    Amazon Redshift launches; cheap warehouse compute makes ELT practical

  4. 2015

    Airbnb announces Airflow, its open-source workflow scheduler, in June

  5. 2016

    Debezium 0.1 reads MySQL’s binlog; dbt is first released

  6. 2025

    Fivetran agrees to buy Census (reverse ETL); Salesforce completes its Informatica purchase

  7. 2026

    Fivetran and dbt Labs complete their merger on 1 June

Sources27

Improve this page on GitHub