ETL vs ELT: Pipeline Diagrams, Trade-offs and When to Use Each

Finance asks for revenue by discount code for the last two years. At one company, that is a small SQL change and a rebuild, because every order row ever loaded still sits in a raw table. At another, the nightly job never kept the discount column, so someone changes the job and re-extracts two years of history from production.

The difference is where the transform runs. ETL (extract, transform, load) shapes data on a separate engine before it reaches the warehouse. ELT (extract, load, transform) copies raw data into the warehouse and shapes it there with SQL. That choice sets your compute bill, how fresh data is, how easily logic changes and where personal data ends up.

This post walks through both pipelines, compares them, shows where ETL still wins and the common hybrid, and ends with a side-by-side diagram.

How an ETL pipeline works

In ETL, a transform engine sits between the sources and the warehouse. A nightly run:

1. Extract new and changed rows, with a query on an updated_at column or from a change data capture stream.
2. Transform them on the engine: clean values, remove duplicates, join reference data, mask personal data and reshape the result into fact and dimension tables.
3. Load the result into the warehouse with inserts or merges.

The engine can be Informatica, SQL Server Integration Services (SSIS), a Spark job or a Python script. Raw extracts often sit in a staging area that the job clears after a successful run, so the warehouse cannot answer a question the job did not anticipate.

How an ELT pipeline works

In ELT, the warehouse does the transform work:

1. Extract and load. A loader such as Fivetran or Airbyte copies source tables into a raw schema almost unchanged, adding metadata columns such as _fivetran_synced or _airbyte_extracted_at that record when each row was synced.
2. Transform. SQL models, usually managed with dbt, read the raw tables and build cleaned and joined tables in other schemas.
3. Serve. BI tools query the final tables, usually called marts.

ELT spread with cloud warehouses that separate storage from compute, such as BigQuery and Snowflake: raw copies are cheap to keep, and transform capacity scales with the warehouse. Because every loaded row stays, you can fix a model, add a column or define a new metric and rebuild from history without touching the source.

ETL vs ELT compared

Schedules matter more than the acronym: Fivetran's default sync runs every six hours, which is staler than an hourly ETL job.

When ETL still wins

• Personal data must not land raw. If plain email addresses or card numbers must never reach the warehouse, mask them before the load. Warehouse masking policies do not change what is stored: Snowflake's Dynamic Data Masking, an Enterprise Edition feature, masks plain-text values at query time.
• The target cannot transform. A legacy warehouse with fixed capacity, a search index or a partner's file drop expects finished data.
• Enrichment that SQL handles badly, such as per-row API calls, geocoding, PDF parsing or model scoring, often fits better in Python or Spark.
• Streaming with tight latency. Apache Flink or Kafka Streams transform events seconds after they happen; the Kafka architecture diagram shows where they read from.
• Volume you do not want to keep. 10,000 sensors at one reading per second produce 864 million rows a day (10,000 × 86,400). One-minute averages computed before the load cut that to 14.4 million (10,000 × 1,440), 60 times fewer.

The common hybrid

In practice, many ELT pipelines include a small transform before the load, sometimes written EtLT. Transform in flight only what must never land raw; do everything else in the warehouse, where it is versioned, tested and easy to rebuild.

For personal data, Fivetran's column hashing hashes a column during the sync with SHA-256 and a per-destination salt, so hashed emails still join; data blocking drops a column entirely. Airbyte's mappings can hash or encrypt fields per stream. Whatever the tool, prefer a salted hash: a plain SHA-256 of an email can be matched against a list of known addresses. Two more caveats: hashing covers future syncs, so history needs a re-sync, and a salted hash is pseudonymous, not anonymous. Under the GDPR, pseudonymized data is still personal data (Recital 26). This post is not legal advice; read the official text with your privacy team and keep a data map of where personal data flows.

ELT with layered SQL models

dbt's project structure guide describes three layers that work with any SQL tool:

Lakehouses use the same idea under the names bronze, silver and gold; see the medallion architecture diagram.

dbt ships four generic data tests, unique, not_null, accepted_values and relationships, and any query that returns failing rows can become a test. Freshness checks catch a loader that silently stopped. One source definition declares both:

sources:
  - name: shop
    schema: raw_shop
    config:
      loaded_at_field: _fivetran_synced
      freshness:
        warn_after: {count: 12, period: hour}
        error_after: {count: 24, period: hour}
    tables:
      - name: orders
        columns:
          - name: id
            data_tests:
              - unique
              - not_null

dbt source freshness (or dbt freshness in dbt v2) warns when the newest _fivetran_synced value is over 12 hours old and reports an error after 24.

Orchestration and data quality

An orchestrator runs the steps in order. In Airflow, a DAG (tasks with dependencies and a schedule) triggers the loader sync, waits for it, then runs dbt. Dagster's dbt integration turns each dbt model into an asset, a table that should exist, so a mart can depend directly on the table a Fivetran sync writes.

Either way, dbt build keeps bad data out of dashboards: it runs models, tests, snapshots and seeds in DAG order, and a failing test skips everything downstream. Add freshness checks and an alert to the owning team, and both wrong and old data surface before a stakeholder notices.

Reverse ETL, which Airbyte calls data activation, runs the other way: it copies modeled data from the warehouse into tools such as a CRM or a support desk. At the time of writing (November 2026), Hightouch offers it as a standalone product, and Fivetran (which acquired Census) and Airbyte bundle it with ingestion.

The ETL vs ELT diagram

How to read it:

• Two lanes, same ends: the same Postgres orders database and the same BI dashboards. Only the middle changes.
• Amber marks the transform: a separate engine before the warehouse in ETL, the dbt models box inside the warehouse in ELT.
• The warehouse frames show what is stored. ETL's holds only fct_orders; ELT's holds raw.orders as loaded, the models and marts.fct_orders.
• The purple edge is the hybrid: the loader hashes personal data before the rows land.
• The Markdown block lists the ELT run order, the freshness thresholds and which schema BI reads.

Drawing ETL and ELT diagrams clearly

• Put the transform where it runs. Drawing ELT's transform outside the warehouse hides its main cost and risk.
• Draw the warehouse as a boundary. Raw, staging and marts inside one frame are schemas in one system, not separate databases.
• Label edges with what moves (raw rows, hashed rows, built tables) and the schedule when it matters.
• Mark where personal data is masked and where it still exists in plain form.
• Show layers, not every model. One example table per layer is enough; lineage belongs in dbt's generated docs.
• Keep control flow apart. Draw orchestrator triggers as dashed edges so nobody mistakes them for data.

The data pipeline architecture diagram shows a full pipeline with CDC, Kafka and a warehouse.

Build it in Archboard

1. Sign in with Google or GitHub and create a diagram.
2. Add two Frame nodes from the Core pack, one above the other, for the ETL and ELT lanes, and a nested Frame for the warehouse in each lane at the same horizontal position.
3. In the ETL lane, add a Postgres node from the Data pack, a Core Box for the transform engine, a Data Warehouse node from the Data pack for fct_orders inside the warehouse frame, and a Core Box for BI. Label the edges: raw rows, cleaned and conformed rows, SQL queries.
4. In the ELT lane, add a Postgres node and a Core Box for the loader. Inside the warehouse frame, add Data Warehouse nodes for raw.orders and marts.fct_orders with a Core Box for the dbt models between them. End with a Core Box for BI and label the edges the same way.
5. Color both transform boxes amber and the loader's outgoing edge purple. Add Core Text notes under the nodes that need explaining and a short color legend.
6. Add a Markdown block from the Core pack with the run order and freshness thresholds.

If the pipeline already exists as a Mermaid flowchart, paste it into the Mermaid to editable diagram tool for a first draft. The example uses 15 diagram nodes, within the Free plan.

FAQ

What is the difference between ETL and ELT? Where the transform runs. ETL transforms data on a separate engine and loads only the result. ELT loads raw data into the warehouse first and transforms it there with SQL, so the raw copy stays available for rebuilds.

Is ETL outdated? No. ELT is the usual default on cloud warehouses, but ETL still fits when personal data must be masked before it lands, when the target cannot transform, and for heavy enrichment or low-latency streaming.

Is dbt an ETL tool? dbt covers the T in ELT. It runs SQL models and tests inside your warehouse but does not extract or load data; a loader such as Fivetran or Airbyte does that.

Is ELT cheaper than ETL? It depends on volume and habits. ELT drops the separate engine but bills every model run as warehouse compute and stores raw copies. Incremental models keep costs down; hourly full rebuilds of large tables do not.

What is reverse ETL? Copying modeled data from the warehouse into operational tools such as a CRM. It should read from marts, run on a schedule and get the same tests and ownership as any other pipeline.

Start free — one seat, no credit card.