What a pipeline is really doing

A data pipeline moves data from a place where it was produced to a place where it can be used, and changes its shape on the way. That is the whole idea. Everything difficult about the job is in the details of when, how often, what happens on failure, and how you know it worked.

ETL became ELT, and the reason matters#

The old shape was extract, transform, load: pull from the source, reshape it on some intermediate machine, then write the finished result into the warehouse. This existed because warehouse storage and compute were expensive and coupled — you did not want to store anything you were not going to use.

The modern shape is extract, load, transform: pull from the source, dump it into the warehouse almost unchanged, then transform inside the warehouse using SQL.

The flip happened because cloud warehouses made storage cheap and decoupled it from compute. Once storage is nearly free, keeping the raw data is obviously correct, and the consequences are large:

• You can re-transform history. When you find a bug in your transformation logic — and you will — you re-run it against raw data you still have. Under ETL, that data was gone.
• Transformations become SQL, which means analysts can own them, review them, and test them.
• The extract step gets boring, which is the highest compliment you can pay a piece of infrastructure.

The three layers everyone converges on#

Independently, most teams end up with the same three-layer shape, whatever they call it:

Raw (or bronze, or landing). Source data as it arrived, plus ingestion metadata: when it was loaded, from where, by which run. Append-only. Ugly on purpose — it mirrors the source, including the source's bad decisions.

Staging (or silver, or cleaned). One model per source table. Rename columns to your conventions, cast types, parse timestamps, drop the columns nobody will use. No business logic and no joins yet. The job here is purely: make it consistent.

Marts (or gold, or presentation). Business concepts. orders, customers, daily_revenue. Joined, aggregated, documented, tested. This is what analysts and dashboards query, and the only layer most of the company should ever see.

The temptation is always to skip the middle. Do not — staging is where the source's weirdness gets quarantined, so that exactly one model knows the vendor spells country codes in lower case.

What a pipeline owes you beyond the data#

A pipeline that only moves rows is half a pipeline. A real one also gives you:

Observability. How many rows arrived, at what time, from which source, and how that compares with normal. A run that silently loads zero rows must be an incident, not a success.

Idempotency. Running it twice produces the same state as running it once. The next lesson is entirely about this, because it is where most pipelines are quietly broken.

Lineage. For any table, which upstream tables feed it and which downstream things break if it is wrong. You need this at 2am, and 2am is not when you want to be reading code.

Replayability. The ability to say "reprocess the last fourteen days" and have that be a normal, safe operation rather than a heroic one.

A concrete minimal ingestion#

Nothing exotic — pull an API page by page, land it as-is with metadata, and let SQL do the rest.

import json, time
from datetime import datetime, timezone
import requests

def extract_orders(since: str, page_size: int = 500):
    """Yield raw records from a paginated source, oldest first."""
    cursor = None
    while True:
        response = requests.get(
            "https://api.example.com/v2/orders",
            params={"updated_since": since, "cursor": cursor, "limit": page_size},
            timeout=30,
        )
        response.raise_for_status()
        payload = response.json()
        yield from payload["data"]
        cursor = payload.get("next_cursor")
        if not cursor:
            return
        time.sleep(0.2)          # be a good citizen; sources have rate limits

def land(records, run_id: str, path: str):
    """Write raw records untouched, with ingestion metadata attached."""
    loaded_at = datetime.now(timezone.utc).isoformat()
    with open(path, "w") as handle:
        for record in records:
            handle.write(json.dumps({
                "_loaded_at": loaded_at,
                "_run_id": run_id,
                "_source": "orders_api_v2",
                "payload": record,          # untouched, on purpose
            }) + "\n")

Note what is not happening: no renaming, no filtering, no type coercion, no business logic. The extract step's only job is to get bytes into the warehouse without losing or corrupting any. Every decision you make here is a decision you cannot revisit later without re-fetching from a source that may not let you.

How the data gets out of the source at all#

Four mechanisms, in rough order of preference:

1. Change data capture (CDC) — read the database's replication log. Gives you every change including deletes, with minimal load on the source. Best option; most operational effort.
2. Incremental pull on an updated-at column — query for rows changed since your last watermark. Simple and widely available. Silently misses hard deletes, and breaks if the source's clock or updated_at is unreliable.
3. Full snapshot — pull the whole table every time. Wasteful, but honest and impossible to get subtly wrong. Perfectly correct for small dimension tables, and underrated.
4. Event stream — the source emits events as they happen. Excellent when it exists; you rarely get to choose.

Most real platforms use three of these at once, and that is normal.