What a Data Pipeline Actually Is

ETL has three letters. Most people can explain two of them.

Extract and Load are obvious. Something comes from somewhere and goes somewhere else. Transform is where the debate starts, because it’s the step that everyone has an opinion about and nobody defines the same way.

That ambiguity is worth taking seriously. How you think about the T changes where your pipeline breaks, who owns it, and how much you can trust the data at the end.

The job is always the same

A data pipeline moves data from a source to a destination and changes its shape along the way. That’s the whole thing.

The source might be a production database, an API, a file, an event stream, or another data store. The destination might be a warehouse, a reporting database, a downstream service, or another pipeline. The transformation might be minimal (rename a field, cast a type) or substantial (aggregate across millions of rows, join multiple sources, apply business rules).

The shape of the pipeline depends on what the data needs to look like at the destination, not what it looks like at the source.

Extract and load are not where decisions get made

Getting data out of one system and into another is mostly a solved problem. Most tools handle it reliably. The complexity is in the edges: rate limits, authentication, pagination, schema drift when the source changes without warning.

The real decisions happen around transformation. When does the data get transformed? Who writes the transformation logic? What happens when the source data is wrong?

ETL: transform before you load

In traditional ETL, the data is transformed before it lands in the destination.

Extract the raw data. Clean it, reshape it, apply business rules, join it with other sources. Load the result.

Here’s what that looks like in Airflow for an orders pipeline that needs a cleaned version in the warehouse:

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime

def extract(**ctx):
    return query_production(
        "SELECT * FROM orders WHERE updated_at::date = %s",
        [ctx["ds"]]
    )

def transform(**ctx):
    raw = ctx["ti"].xcom_pull(task_ids="extract")
    return [
        {
            "order_id": r["id"],
            "revenue": r["amount"] if r["status"] != "refunded" else 0,
            "date": r["updated_at"][:10],
        }
        for r in raw
        if r["status"] not in ("test", "internal")
    ]

def load(**ctx):
    rows = ctx["ti"].xcom_pull(task_ids="transform")
    bulk_insert("warehouse.orders_daily", rows)

with DAG("orders_etl", start_date=datetime(2026, 1, 1), schedule="@daily") as dag:
    extract_task = PythonOperator(task_id="extract", python_callable=extract)
    transform_task = PythonOperator(task_id="transform", python_callable=transform)
    load_task = PythonOperator(task_id="load", python_callable=load)

    extract_task >> transform_task >> load_task

The business rules live in transform. The warehouse only ever receives what passed through it. If the transformation fails mid-run, nothing bad lands.

The disadvantage is that the transformation logic sits outside both the source and the destination. It belongs to whoever owns the pipeline. When business rules change, the pipeline needs to change too. That creates a dependency that can slow down the people who actually need the data.

ELT: load first, transform later

In ELT, the raw data lands first. The transformation happens inside the destination, usually with SQL or a tool like dbt.

The raw orders table arrives in the warehouse unchanged. Then a model runs on top of it:

-- models/orders_daily.sql (dbt)
select
    id          as order_id,
    customer_id,
    created_at::date as date,
    case
        when status = 'refunded' then 0
        else amount
    end         as revenue
from {{ source('production', 'orders') }}
where status not in ('test', 'internal')

This is a dbt model. It runs inside the warehouse, it’s version-controlled like code, and analysts can change the business logic without touching the pipeline that loaded the raw data.

The advantage is that the raw data is always available. If the transformation logic was wrong, you can run it again without re-extracting anything. The warehouse does the heavy lifting.

The disadvantage is that the destination now contains both raw and transformed data. The transformation layer needs its own testing, documentation, and governance. And the warehouse needs to be capable of running those transformations at the required scale and frequency.

The choice depends on who owns the rules

The choice depends on what the data is for, who owns it, and how much the transformation logic is likely to change.

If the transformation rules are stable and owned by a central team, ETL can work well. If the data needs to support many different analytical questions that evolve over time, ELT gives analysts more flexibility. If the raw data is sensitive and shouldn’t sit in a warehouse unprocessed, ETL is the right call.

Most real systems end up with both. Treating one as universally correct usually means the other gets introduced quietly anyway.

Pipelines break where the source doesn’t match the assumption

The most common failure mode isn’t in the pipeline itself. It’s in the assumptions about the source data.

The source schema changed. A field that used to contain an integer now sometimes contains a string. A nullable column started coming through as null. An API endpoint started returning an extra field that breaks a downstream parser.

What does the difference look like?

-- without validation: bad data lands silently
INSERT INTO warehouse.orders
SELECT * FROM staging.orders;

-- with validation: the pipeline fails loudly before anything bad gets in
INSERT INTO warehouse.orders
SELECT * FROM staging.orders
WHERE order_id IS NOT NULL
  AND amount >= 0
  AND status IN ('pending', 'completed', 'refunded');

The first query always succeeds. The second fails when the source sends something unexpected, which is exactly what you want. A failed pipeline run is recoverable. Wrong data in production isn’t.

Data lands in the warehouse, but it’s wrong. The dashboard shows numbers that look plausible. Nobody catches it until someone compares two reports that should match and do not.

This is the same underlying problem that makes unstructured data expensive to work with: data exists in the wrong shape for what downstream systems need. A well-designed pipeline solves it structurally. But it only solves it if the transformation logic explicitly handles the cases where the source doesn’t look like you expected.

The second most common failure mode is transformation logic that encodes business rules without making them visible.

A transformation that filters out records with a certain status sounds simple. Until the meaning of that status changes, the filter is still running, and nobody can find where the rule lives.

Most data problems don’t need a pipeline

If the data is in one place and the analysis is also in one place, a direct query is usually better. If the data volume is small and the transformation is simple, a scheduled script may be sufficient. If the destination only needs refreshed data occasionally, the overhead of building and operating a pipeline may not be worth it.

Pipelines earn their complexity when the source and destination are genuinely separate systems, when the volume makes direct queries impractical, or when multiple destinations need the same data in different shapes. If none of those apply, a scheduled script probably covers it.

The temptation is to build a pipeline as soon as data movement is involved. The better question is: what does the destination need, how often does it need it, and what’s the simplest way to get it there?

“The pipeline finished” isn’t the same as “the data is ready”

A pipeline that runs successfully isn’t the same as data that’s ready to use.

The data might be fresh but incorrectly shaped. It might be correctly shaped but missing rows. It might have rows that violate business rules nobody wrote down. It might have timestamps in the wrong timezone, IDs that don’t match across systems, or status values that changed meaning six months ago.

Ready to use means the destination can answer the questions it’s supposed to answer, with the accuracy those questions require.

That standard is harder to reach than “the pipeline finished without errors.” It requires tests on the output, not just on the pipeline itself.

What are the questions the destination is supposed to answer? That’s always the right place to start.