# ETL & ELT Pipelines, Explained

> What a data pipeline actually is, the difference between transforming before or after you load, and what it takes to make a scheduled pipeline run reliably instead of just running.


---

# ETL & ELT Pipelines, Explained

Somewhere between "we have data in a few places" and "the dashboard updates itself every morning," a pipeline appeared. Maybe you inherited one - a tangle of scripts, a scheduler nobody fully understands, a Slack channel that lights up red at 6am. Maybe you're being asked to build one and the acronyms (ETL? ELT? DAG?) are flying past faster than you can pin them down.

Here's the reassuring part: underneath the jargon, a data pipeline is a small set of plain ideas. Data gets pulled out of where it lives, reshaped into something usable, and written where people can actually use it. Everything else - the tools, the schedulers, the "modern data stack" - is detail layered on top of those three moves.

This guide installs the mental model first, then shows you the one design choice that splits the whole field (transform *before* loading, or *after*), and finally what separates a pipeline that "ran" from one that ran *correctly*.

## How to read this
- **Need the ETL-vs-ELT answer right now?** Jump to [Phase 2: ETL vs ELT](02-etl-vs-elt.md) - it leads with the difference and the trade-off.
- **Want it to finally make sense?** Read in order. Each phase builds on the last: the three stages, then the order swap, then making it run reliably.

## The phases
1. **[Extract, Transform, Load](01-extract-transform-load.md)** - the three stages plainly, as an assembly line: pull data from sources, clean and reshape it, write it where it's used.
2. **[ETL vs ELT](02-etl-vs-elt.md)** - the order swap and why it matters: transform before loading (classic) vs. load raw then transform inside a powerful warehouse (modern), and the trade-off.
3. **[Orchestration: Making It Run Reliably](03-orchestration.md)** - pipelines are scheduled, multi-step jobs: dependencies as a DAG, scheduling, retries, idempotency, and backfills. Why "it ran" isn't "it ran correctly."

> This guide stops at the shape of pipelines and how to run them safely. Where the data lands - warehouse vs. lake vs. lakehouse - is its own topic, covered in [Warehouses vs. Lakes](/guides/warehouses-vs-lakes). How to *trust* what comes out the far end is in [Data Quality & Observability](/guides/data-quality-and-observability).

---

Related guides: [Spreadsheets to SQL to Pipelines](/guides/spreadsheets-to-sql-to-pipelines) · [Warehouses vs. Lakes](/guides/warehouses-vs-lakes) · [Data Quality & Observability](/guides/data-quality-and-observability)


---

# Extract, Transform, Load

ETL gets thrown around like one thing, but it's really three separate jobs wearing one name. They blur together because the data goes "into the pipeline" and "comes out clean," and the middle is fog.

The fix is one picture: an assembly line. Raw material comes in one end, gets worked on in the middle, a finished product comes out the other. That's the whole mental model - once it's in place, every tool and buzzword you meet later slots into one of the three stations.

## The whole thing, in one picture

**What a pipeline actually is.** A pipeline is a sequence of steps that moves data from where it's *produced* to where it's *consumed*, reshaping it along the way. Think of a factory line: each station does one job and hands its output to the next.

```mermaid
flowchart LR
  db[(database)] --> E
  api[API] --> E
  files[files] --> E
  E[Extract<br/>read it] --> T[Transform<br/>clean / reshape]
  T --> L[Load<br/>write it where used]
  L --> dest[(Analytics DB)]
```

Each station has exactly one responsibility. When something breaks, this picture is what lets you ask the right question: *which station failed?* That alone turns "the pipeline is broken" into a debuggable problem.

> 📝 **Terminology.** *Source* = where data comes from (an app's database, a third-party API, uploaded files). *Destination* (or *sink*, or *target*) = where the pipeline writes its output, usually an analytics database people query. We'll define the warehouse vs. lake distinction in [Warehouses vs. Lakes](/guides/warehouses-vs-lakes); for now, "the place reports read from."

## Stage 1 - Extract: pull the data out

**What it actually is.** Extract is the "read" step: copy data *out* of a source so the pipeline can work on it without disturbing the source. You are making a copy, not moving the original - the app's production database keeps serving the app.

**What it does in real life.** Extraction connects to a source and pulls records. Sometimes it's a full pull (grab everything); more often it's *incremental* (grab only what changed since last time), because re-reading millions of rows every run is wasteful.

**A real example.** Pulling new orders from a production database since the last run:

```console
$ python extract_orders.py --since 2026-06-18
Connecting to orders-db (read replica)...
Querying orders WHERE updated_at > '2026-06-18'...
Fetched 1,204 rows
Wrote raw/orders_2026-06-19.json
```

*What just happened:* The script connected to a *read replica* (a copy of the database meant for reads, so analytics queries don't slow down the live app), asked only for orders touched since yesterday, and dumped the raw result to a file untouched. No cleaning yet - extract's only job is to get the data out faithfully.

⚠️ **Gotcha - extract reads from production; treat it gently.** A naive "grab everything" query against the live database your app depends on can slow the app down for real users. This is why teams extract from read replicas, run heavy pulls off-hours, and prefer incremental pulls. The extract step is the one most likely to hurt something *outside* the pipeline.

**Why this saves you later.** When a report is missing yesterday's orders, the assembly-line picture sends you straight to station one: *did extract run, and did it pull the right window of data?* Most "missing data" mysteries are an extract that pulled the wrong range or silently fetched zero rows.

## Stage 2 - Transform: clean and reshape it

**What it actually is.** Transform is where raw, messy data - inconsistent formats, duplicate rows, codes instead of labels, three systems that each spell "USA" differently - becomes something a human or a dashboard can trust.

**What it does in real life.** Common transforms:
- **Clean** - fix types, trim whitespace, standardize values (`"usa"`, `"US"`, `"U.S.A."` → `"US"`).
- **Filter** - drop test records, drop rows you don't need.
- **Join / enrich** - stitch tables together (orders + customers + products into one row).
- **Aggregate** - roll detail up into summaries (orders → daily revenue per region).

**A real example.** A transform that standardizes country codes and joins in customer data:

```console
$ python transform_orders.py raw/orders_2026-06-19.json
Loaded 1,204 rows
Standardized 'country' field (47 variants -> 31 ISO codes)
Dropped 18 test orders (email ending @example.com)
Joined customer names from customers table
Wrote staged/orders_clean_2026-06-19.parquet
```

*What just happened:* The raw 1,204 rows went in; the script normalized the messy `country` field down to standard codes, removed obvious test data, and attached customer names so downstream reports don't have to. What comes out is the same orders, but *trustworthy and ready to use*. (Numbers are illustrative, not measured.)

💡 **Key point.** Transform is the stage that holds all your business logic - what "a valid order" means, how revenue is defined, which records count. That's why it's where most of the real engineering effort goes, and, as the next phase shows, *where* you run it is the choice that defines ETL vs. ELT.

**Why this saves you later.** When a number looks wrong - revenue double-counted, a region missing - the cause is almost always a transform rule, not a broken machine. Knowing the logic lives at station two tells you exactly which code to read.

## Stage 3 - Load: write it where it's used

**What it actually is.** Load is the "write" step: take the cleaned, reshaped data and put it in the destination where people and tools will actually read it - the analytics database behind your dashboards.

**What it does in real life.** Loading writes rows into target tables. The key decision is *how* you write:
- **Append** - add new rows to what's already there (good for event logs).
- **Overwrite** - replace a table or partition wholesale (good for "today's full snapshot").
- **Upsert / merge** - update rows that exist, insert ones that don't (good for records that change, like an order moving from "pending" to "shipped").

**A real example.** Loading the cleaned file into the warehouse table:

```console
$ python load_orders.py staged/orders_clean_2026-06-19.parquet
Connecting to warehouse...
MERGE into analytics.orders ON order_id
  1,186 rows updated or inserted
Done.
```

*What just happened:* The cleaned rows were merged into the analytics table on `order_id` - existing orders got updated, brand-new ones got inserted. Now the dashboards reading `analytics.orders` see fresh, clean data. The assembly line has produced its finished product.

⚠️ **Gotcha - the wrong write mode quietly corrupts your data.** Use *append* where you meant *merge* and re-running the pipeline doubles your rows. Use *overwrite* on the wrong scope and you wipe history you needed. The load step is where re-running a pipeline can do real damage - which is exactly the *idempotency* problem we tackle in [Phase 3](03-orchestration.md).

**Why this saves you later.** When numbers are inflated after a re-run, the load step's write mode is the first suspect. Recognizing append-vs-merge as a deliberate choice - not a detail - saves you from the classic "why is revenue suddenly 2x?" panic.

## Recap

1. A **pipeline is an assembly line**: raw material in, finished product out, one job per station.
2. **Extract** copies data out of sources faithfully - read gently, prefer incremental, don't hurt production.
3. **Transform** cleans, filters, joins, and aggregates - this is where all your business logic lives.
4. **Load** writes the result where people use it - and the *write mode* (append / overwrite / merge) matters more than it looks.
5. When something breaks, the three stations tell you *where* to look first.

You now have the shape of every pipeline. The next question is one of order: do you transform the data *before* you load it, or load it raw and transform it *after*? That single swap is the difference between ETL and ELT.

Watch it animated: [ETL pipelines](/explainers/ETLPipelines.dc.html)


---

# ETL vs ELT

You read the three stages in Phase 1 in the classic order: Extract, Transform, Load. Then someone on your team says "we do ELT now" and reorders two letters, and it feels like it should be a small thing. It isn't. Swapping the **T** and the **L** changes where your data gets cleaned, what hardware does the work, and whether you can ever recover the original raw data after a mistake.

The good news: there's only *one* difference between them, and once you see it the rest follows. Put the two side by side, then look at *why* the industry mostly moved from one to the other - a story about hardware getting cheaper, and one that tells you which fits *your* situation.

## The one difference, in a picture

Both pipelines do the same three jobs. The only question is whether **Transform** happens *before* or *after* the **Load**:

```mermaid
flowchart LR
  subgraph ETL [ETL - transform first]
    direction LR
    E1[Extract] --> T1[Transform<br/>separate box] --> L1[Load<br/>clean data only]
  end
  subgraph ELT [ELT - load first]
    direction LR
    E2[Extract] --> L2[Load<br/>raw lands] --> T2[Transform<br/>SQL, in warehouse]
  end
```

That's the whole distinction. **ETL transforms on the way in; ELT transforms after it's already in.** Everything else - the trade-offs, the tooling, the team debates - flows from that.

## Why ETL came first: compute used to be expensive

**The world that created ETL.** For decades, the destination was a traditional data warehouse - powerful, but expensive and capacity-limited. You did *not* want to waste its precious compute cleaning messy data; you wanted it reserved for serving queries. So teams stood up a *separate* machine (an ETL server) to do all the transforming, and loaded only the finished, cleaned result into the warehouse.

**The logic was sound for its time:** transform on cheap commodity hardware, load only what's needed, keep the costly warehouse lean. ETL is a design shaped by scarcity - when warehouse compute is the bottleneck, you protect it.

**The cost of that design.** Because only the *transformed* data lands in the warehouse, the **raw data is gone** (or sitting in files nobody queries). If you later discover your transform had a bug, or you need a field you'd dropped, you can't just re-derive it - you have to re-extract from the source, if the source even still has it.

## Why ELT took over: the cloud warehouse changed the math

**What changed.** Modern cloud warehouses (the BigQuery / Snowflake / Redshift generation) made warehouse compute cheap and elastic - you can throw a lot of processing power at a problem and pay only for what you use. The bottleneck that justified ETL largely went away.

So the order flipped. With ELT you **load the raw data straight in, then transform it in place using the warehouse's own SQL engine.** The expensive separate transform box disappears; the warehouse does the heavy lifting it was once protected from.

This unlocks two things people genuinely value:

- **The raw data is preserved.** Because you load *before* transforming, the original lands in the warehouse and stays there. Found a bug in your revenue logic six months in? Fix the SQL and re-run the transform over the raw data you already have - no re-extraction, no begging the source system for history it may have purged.
- **You transform with SQL.** Transforms become SQL queries (often managed by tools like dbt) running where the data already sits. Anyone who can write SQL can read and change the logic - you don't need a separate processing framework or language for the transform stage.

> 📝 **Terminology.** People often describe ELT transforms as turning *raw* tables into *staged* and then *modeled* tables - successive SQL layers inside the warehouse, each more refined than the last. Same Phase 1 transform work; it just runs as SQL, in stages, in place.

## The straight comparison

Neither is "better" in the abstract - they fit different constraints. Here's both sides straight:

| | **ETL** (transform first) | **ELT** (load first) |
|---|---|---|
| Where transform runs | A separate processing server | Inside the warehouse, in SQL |
| What lands in the warehouse | Only cleaned data | Raw data first, then derived tables |
| Raw data after loading | Usually discarded / hard to reach | Preserved and queryable |
| Best when | Warehouse compute is scarce/expensive; or you must clean/mask data *before* it ever lands (e.g. compliance) | You have an elastic cloud warehouse and want flexibility + raw history |
| Transform skills needed | A processing framework (Spark, custom code) | SQL (often + dbt) |
| Typical era | Traditional on-prem warehouses | Modern cloud warehouses |

⚠️ **Gotcha - ELT means raw, possibly sensitive data lands first.** Loading before transforming is exactly what preserves raw history - but it also means personal or regulated data hits the warehouse *before* any masking. If you're under rules that forbid storing certain fields, transforming (or masking) *before* the load - the ETL pattern - may be a hard requirement, not a preference. Choose the order with that in mind.

💡 **Key point.** The choice isn't fashion. ELT wins where warehouse compute is cheap and you value keeping raw data; ETL still wins where compute is constrained or where data must be cleaned/masked before it can be stored at all. The order of two letters encodes a real engineering trade-off - name the constraint, and the right order is usually obvious.

> Whether you load into a *warehouse* (structured, SQL-first) or a *lake* (raw files, any format) shapes this decision too - ELT in particular leans on a capable destination. That comparison has its own home: [Warehouses vs. Lakes](/guides/warehouses-vs-lakes).

## Recap

1. **The only difference is order:** ETL transforms *before* loading; ELT loads raw *then* transforms in place.
2. **ETL was born of scarcity** - protect expensive warehouse compute by cleaning on a separate box and loading only the result.
3. **ELT rose with cheap cloud warehouses** - load raw, transform with SQL inside the warehouse.
4. **ELT's payoffs:** raw data preserved (re-run transforms without re-extracting) and transforms written in plain SQL.
5. **ETL still wins** when compute is constrained or data must be masked before it can land at all.

You can now read the data-stage of any pipeline and know *why* it's ordered the way it is. But knowing the stages and their order isn't enough to trust a pipeline. A pipeline runs on a schedule, with steps that depend on each other, and it *will* fail at 3am. Making it run *reliably* is the final piece.


---

# Orchestration: Making It Run Reliably

A pipeline you run by hand, once, on your laptop is a script. A pipeline that runs every morning at 5am, in order, recovers when a step fails, and doesn't quietly corrupt your data when it re-runs - that's a *production* pipeline. The gap between those two is **orchestration**, and it's where most of the real pain (and the 6am pages) lives.

The hardest lesson in this whole field fits in one sentence: **"it ran" is not the same as "it ran correctly."** A pipeline can finish with a green checkmark and still have loaded yesterday's data twice, or skipped a source that returned nothing, or written garbage. This phase is about closing that gap - making runs both *happen* and *be right*.

## The orchestration cheat-card

> **Hit one of these? Find your situation, then read the section.**

| Situation | What's going on | Where |
|---|---|---|
| "Step B started before step A finished" | Dependencies aren't declared - orchestrator doesn't know the order | §1 |
| "The pipeline didn't run last night" | Scheduling / trigger problem, not a logic problem | §2 |
| "A step failed once then worked on retry" | Transient failure; retries are doing their job | §3 |
| "We re-ran it and now revenue is doubled" | A step isn't **idempotent** - re-running changed the result | §4 |
| "We need to reprocess the last 3 months" | That's a **backfill** - and it's only safe if steps are idempotent | §5 |
| "It's green but the numbers are wrong" | It ran ≠ it ran correctly | §6 |

---

## 1. Dependencies as a DAG - what runs after what

**What it actually is.** A real pipeline isn't one script; it's many steps, and some can't start until others finish - you can't transform orders before extracting them, or build daily-revenue before both orders and products are loaded. Those "must happen after" relationships form a shape called a **DAG**.

> 📝 **Terminology.** *DAG* = **Directed Acyclic Graph**. *Directed*: each arrow points one way (A then B). *Acyclic*: no loops - you can never end up depending on yourself, directly or in a circle. It's just a dependency map: "this step runs after that step."

```mermaid
flowchart LR
  EO[extract_orders] --> TO[transform_orders]
  EP[extract_products] --> TO
  TO --> DR[daily_revenue]
  EP --> DR
```

**What it does in real life.** You declare the dependencies, and the orchestrator (Airflow, Dagster, Prefect, and friends) figures out the order - running independent steps in parallel and waiting for prerequisites before starting dependents. You describe *what depends on what*; it handles *when*.

**Why this saves you later.** When `daily_revenue` is missing products, the DAG tells you instantly whether `extract_products` even ran before the transform tried to read it. The dependency map is also your debugging map.

## 2. Scheduling - making it run on its own

**What it actually is.** Orchestrators run pipelines on a *trigger* - most often a schedule ("every day at 5am"), sometimes an event ("when a new file lands"). The schedule is what turns a script you remember to run into infrastructure that runs itself.

**A real example.** A scheduler logging a triggered run:

```console
$ airflow dags list-runs -d orders_pipeline
dag_id          | run_id                          | state   | execution_date
================+=================================+=========+====================
orders_pipeline | scheduled__2026-06-19T05:00:00  | success | 2026-06-19T05:00:00
orders_pipeline | scheduled__2026-06-18T05:00:00  | success | 2026-06-18T05:00:00
```

*What just happened:* The scheduler fired the pipeline at 05:00 daily and logged each run against the date it was *processing data for* (`execution_date`). That date matters - it's what makes re-running a single day's run meaningful later.

⚠️ **Gotcha - a missed run is silent unless you watch for it.** If the scheduler is down or a trigger never fires, the pipeline doesn't fail loudly - it *doesn't run*, and your data quietly stops being fresh. "No news" is not "good news" here. You need an alert for *absence*, not only for errors.

## 3. Retries - surviving the flaky 3am failure

**What it actually is.** Networks blip, APIs time out, databases hiccup - many failures are *transient* and would succeed if you tried again. A retry policy tells the orchestrator: if a step fails, wait a bit and try again, up to N times, before giving up and alerting a human.

**What it does in real life.** A step that fails on a network timeout retries automatically; if the second attempt succeeds, nobody gets paged and the pipeline carries on. Only a step that exhausts its retries raises an alarm.

⚠️ **Gotcha - retries are only safe if the step is idempotent.** Here's the trap: a step might fail *after* it already wrote some data but *before* it reported success. The orchestrator sees a failure and retries - running the write *again*. If that step double-writes on a second run, your automatic retry just corrupted your data. Which brings us to the most important idea in this whole phase.

## 4. Idempotency - the one that bites everyone

**What it actually is.** A step is **idempotent** when running it twice produces the same result as running it once - no double-counting, no duplicate rows, no drift. It lands on the same final state every time.

> 📝 **Terminology.** *Idempotent* - from "same" + "power." A light switch set to "off" is idempotent: flip it off twice, it's still off. Appending "+1 order" is *not* idempotent: do it twice and you've counted the order twice.

**Why it matters.** Retries (§3), backfills (§5), and manual re-runs all do the same thing: **run a step again.** If a step isn't idempotent, every one of those safety mechanisms becomes a corruption mechanism. The classic disaster:

```text
  NOT idempotent (append):              IDEMPOTENT (overwrite/merge the window):

  run 1:  append today's 1,204 orders   run 1:  replace 2026-06-19 partition
          → table has 1,204                     → partition has 1,204
  run 2:  append today's 1,204 again     run 2:  replace 2026-06-19 partition again
          → table has 2,408  ✗ doubled          → partition still has 1,204  ✓
```

**The calm fix - make re-running a no-op-on-repeat.** Two common patterns:
- **Overwrite the window, don't append to it.** Have each daily run *replace* its own day's partition rather than blindly adding rows. Re-running the day's load just rewrites that day - same result every time.
- **Merge on a key (upsert).** Match on `order_id` and update-or-insert (as in Phase 1's load) so a row can't be inserted twice.

*What just happened:* Writing the *whole window* fresh instead of appending makes the step safe to run any number of times. Idempotency is what makes a pipeline *trustworthy under repetition* - and pipelines repeat constantly.

💡 **Key point.** Design every step so that re-running it is boring. If "what happens if this runs twice?" has a scary answer, that step is a latent outage waiting for the next retry.

## 5. Backfills - reprocessing the past

**What it actually is.** A **backfill** is running the pipeline over *historical* time windows - you built a new table and need last year's data populated, or fixed a transform bug and must reprocess three months with the corrected logic.

**What it does in real life.** Because each scheduled run is tied to a date window (§2), a backfill is "run the pipeline for 2026-03-01, then 2026-03-02, then …" across the range you need. Good orchestrators do this for you given a start and end date.

**A real example.** Backfilling a quarter after a transform fix:

```console
$ airflow dags backfill orders_pipeline \
    --start-date 2026-03-01 --end-date 2026-05-31
[backfill] 92 runs queued (one per day)
[backfill] 2026-03-01 ... success
[backfill] 2026-03-02 ... success
...
```

*What just happened:* The orchestrator queued one run per historical day and replayed the pipeline across the quarter with today's (fixed) code. Each day's run rewrote that day's data - safe only because the load step is idempotent (§4). Without that, this backfill would have stacked a second copy of three months on top of the first.

⚠️ **Gotcha - a backfill on a non-idempotent pipeline is a self-inflicted disaster.** It's the highest-volume way to trigger the double-count bug, because you're deliberately re-running hundreds of windows. Never backfill a pipeline whose steps aren't idempotent - fix the idempotency first.

## 6. "It ran" is not "it ran correctly"

You've now got dependencies, scheduling, retries, idempotency, and backfills. The pipeline runs itself and survives re-runs. And it can *still* be wrong.

A green checkmark only means *the code finished without throwing an error*. It does **not** mean:
- the source actually returned data (an empty pull "succeeds" and loads nothing);
- the numbers are right (a transform bug runs cleanly and produces wrong results);
- nothing upstream changed (a renamed source column can pass silently, then quietly null out a field).

```text
   "it ran"                          "it ran CORRECTLY"
   ─────────                         ──────────────────
   ✓ no errors thrown                ✓ no errors thrown
   ✓ all steps green             +   ✓ rows actually arrived (not zero)
                                  +   ✓ values pass sanity checks
                                  +   ✓ totals reconcile with the source

   orchestration gives you          data quality / observability
   the LEFT column                  gives you the RIGHT column
```

This is the boundary of orchestration. Making a pipeline *run reliably* is necessary but not sufficient; making sure what it produced is *trustworthy* - row-count checks, freshness alerts, schema monitoring, reconciliation - is a discipline of its own.

> That discipline has its own guide: [Data Quality & Observability](/guides/data-quality-and-observability). If this phase made you slightly paranoid about green checkmarks, that's the correct instinct - and that's where you take it next.

## Recap

1. **Dependencies form a DAG** - declare what runs after what; the orchestrator handles order and parallelism.
2. **Scheduling** runs the pipeline on its own - and a *missed* run is silent, so alert on absence.
3. **Retries** survive transient failures - but are only safe on idempotent steps.
4. **Idempotency** is the keystone: running a step twice must equal running it once (overwrite the window or merge on a key). It's what makes retries, backfills, and re-runs safe.
5. **Backfills** replay history over date windows - safe *only* when steps are idempotent.
6. **"It ran" ≠ "it ran correctly"** - a green run can still be empty or wrong; trusting the output is the job of [Data Quality & Observability](/guides/data-quality-and-observability).

You now have the full shape of moving and shaping data: the three stages, the order trade-off, and what it takes to run the whole thing reliably. From here, the two natural next steps are *where the data lands* ([Warehouses vs. Lakes](/guides/warehouses-vs-lakes)) and *how to trust it once it's there* ([Data Quality & Observability](/guides/data-quality-and-observability)).