Data Pipelines

A data pipeline is an automated series of steps that moves data from where it is created to where it can be used, cleaning it on the way. Without one, every report starts with someone copying exports by hand, and no two reports quite agree.

Why raw data needs a pipeline

Most organisations collect data in many places at once. Sales sit in the shop's database, clicks arrive from the website's analytics, payments come from a provider's API and application logs pile up in files. Each source was built for its own job, not for analysis, so the data is scattered and inconsistent:

• Different formats. One system stores dates as 2026-03-01, another as 01/03/2026, and a third as a Unix timestamp.
• Different places. To answer 'which products sell best on mobile?' you need sales and clicks together, but they live in separate systems.
• Bad values. Missing prices, duplicate orders, test accounts and typos all creep in.

Querying the shop's live database directly for every report is also risky: a heavy analytical query can slow down the system customers are using. A pipeline solves both problems. It copies the data out on a schedule, fixes it once in one agreed way, and puts the result somewhere built for questions. The key word is automatically: the same steps run the same way every time, so the numbers are repeatable and nobody has to remember to do it.

ETL: extract, transform, load

The classic shape of a pipeline is ETL, named after its three steps in order.

Extract

Extracting means pulling data out of its sources: querying a database, calling an API, or reading files such as CSV or JSON exports. A good extract step changes nothing. It copies what is there, often only what is new since the last run (an incremental extract), so the pipeline doesn't re-read years of history every night.

Transform

Transforming is where messy data becomes useful data. Typical jobs are:

• Cleaning: trimming spaces, fixing letter case, turning every date into one format.
• Filtering: dropping test orders, bots and rows that are plainly broken.
• Fixing bad values: filling a missing currency with the shop's default, or setting aside a negative quantity for someone to check.
• Combining: joining orders to customers, or payments to orders, so related facts sit in one row.

The transform step is where the business rules live. Deciding what counts as a 'sale' (are refunds subtracted? is a cancelled order a sale?) happens here, once, instead of in every report separately.

Load

Loading writes the transformed data into its destination. That is usually a data warehouse: a database designed for analytical queries over large amounts of history, rather than for the quick single-row reads and writes an app makes. It could also be an analytics system or a data lake of files.

Once the data is loaded, it is ready for the people and programs that use it: dashboards that refresh themselves, reports for the finance team, and machine learning models that need clean, consistent training data.

One run, end to end

Here is one nightly run for an online shop, from three sources to a dashboard:

A worked example in Python

A real pipeline usually runs in a dedicated tool, but the three steps are easy to see in plain Python. This one reads an orders export, cleans it and loads it into a SQLite file standing in for the warehouse:

import csv
import sqlite3
 
def extract(path):
    with open(path, newline="") as f:
        return list(csv.DictReader(f))
 
def transform(rows):
    clean = []
    for row in rows:
        if not row["amount"]:
            continue  # no amount: skip it
        if row["email"].endswith("@test.shop"):
            continue  # a test order
        clean.append({
            "order_id": int(row["order_id"]),
            "day": row["created"][:10],
            "amount": round(float(row["amount"]), 2),
        })
    return clean
 
def load(rows, db):
    db.executemany(
        "INSERT OR REPLACE INTO sales "
        "VALUES (:order_id, :day, :amount)",
        rows,
    )
    db.commit()
 
db = sqlite3.connect("warehouse.db")
db.execute(
    "CREATE TABLE IF NOT EXISTS sales ("
    "order_id INTEGER PRIMARY KEY, "
    "day TEXT, amount REAL)"
)
load(transform(extract("orders.csv")), db)

Two details matter more than they look. transform never touches the source file, so a bug in the rules can be fixed and the run repeated. And INSERT OR REPLACE on a primary key makes the load idempotent: running it twice for the same day leaves one copy of each order, not two. Pipelines fail and get re-run all the time, so this property saves a lot of pain.

Once the data is in, the questions become simple queries:

SELECT day, SUM(amount) AS revenue
FROM sales
GROUP BY day
ORDER BY day;

Batch or real-time

Pipelines run in one of two rhythms.

Batch pipelines run on a schedule: every night, every hour, every fifteen minutes. Each run processes a chunk of data at once. Batch is simpler to build, to test and to re-run, and it suits most reporting, where yesterday's numbers are what people need.

Streaming (real-time) pipelines process each event moments after it happens, as a continuous flow rather than in chunks. They fit jobs where minutes matter: spotting a fraudulent payment, updating stock as orders arrive, or alerting when error logs spike.

Real-time sounds better, but it costs more. A streaming pipeline has to run all the time, cope with events arriving late or out of order, and is harder to fix when something goes wrong. A good rule is to start with batch and move a flow to streaming only when a real need for fresher data appears.

The tools: Airflow, Kafka and dbt

The video names three tools, and each does a different job.

• Apache Airflow is an orchestrator. You describe a pipeline in Python as a graph of tasks with dependencies (extract before transform before load), and Airflow runs it on a schedule, retries failed tasks and shows what ran and when. It coordinates the work rather than moving the data itself.
• Apache Kafka is an event streaming platform. Producers write events, such as 'order placed', to named topics, and consumers read them as they arrive. It is the backbone of many real-time pipelines.
• dbt handles the transform step inside the warehouse. Each transformation is a SQL SELECT statement, and dbt works out the order to build them in, turns them into tables or views, and runs tests on the results, such as 'order_id is never null'.

A common setup uses all three: Kafka carries events in, Airflow schedules the nightly jobs, and dbt shapes the loaded data into clean tables.

ETL or ELT

The video describes ETL, where data is transformed before it is loaded. You will also meet ELT: extract, load the raw data straight into the warehouse, then transform it there, often with dbt. Modern cloud warehouses can store and process large amounts of raw data cheaply, so ELT has become common.

ELT keeps an untouched copy of the raw data, so when a business rule changes you can rebuild the clean tables from scratch. ETL still fits when data must be cleaned or stripped of personal details before it is allowed into the warehouse at all. The steps are the same; only their order and where the transform runs differ.

Common mistakes

• Loads that aren't idempotent. If a re-run appends the same rows again, every failure doubles someone's revenue figures. Use keys, or replace a whole day's data at once.
• Silently dropping bad rows. Filtering is fine, but count what you drop and alert when the number jumps. A sudden rise usually means a source changed.
• Trusting the source's shape. A renamed column upstream can break a pipeline or, worse, fill a column with nulls. Check the schema and test the output.
• No monitoring. A pipeline that stopped running three days ago looks exactly like one with a quiet weekend. Alert on failures and on stale data.
• Streaming by default. Building real-time infrastructure for a report someone reads once a day adds cost and complexity for nothing.

Key takeaways

• A data pipeline moves data from its sources to where it is used, automatically and the same way every time.
• ETL means extract from the sources, transform (clean, filter, fix and combine), then load into a warehouse or analytics system.
• Batch pipelines run on a schedule and suit most reporting; streaming pipelines handle each event as it happens, at a higher cost.
• Airflow schedules and coordinates pipelines, Kafka carries streams of events, and dbt transforms data inside the warehouse with SQL.
• Make loads safe to re-run, and monitor what goes in and what gets dropped.