Lesson 1 of 3 in this module

1/14 in track

What is a data pipeline?

python12 min

Overview

Extract, transform and load as steps that run on their own. A script you start by hand is not something you can trust overnight.

On this page8 sections

The idea

The manual routine that breaks

Picture downloading yesterday's orders from an online shop API, cleaning the columns in a script, and pasting the result into a finance table every morning.

Do that by hand daily and it works until you fall sick, forget, or hit an API timeout. A data pipeline is that exact sequence written down so a machine executes it unattended.

A data pipeline turns a manual routine into an automated chain of machine steps.

The chain moves data from where it originates to where people query it. The core steps are extract, transform, and load.

When a machine runs the pipeline, every step executes in the exact same order with the exact same checks. The process no longer depends on human memory.

Extract, transform, and load

Extract pulls data out of source APIs, transactional databases, or raw CSV files. Transform cleans dates, drops duplicates, and standardizes columns. Load writes the clean result into a lake or warehouse.

Extract, transform, load
Source (API ordatabase)ExtractTransformLoadLake or warehouse

Data moves left to right. Each box starts only after the one before it finishes.

The order matters. If load runs before transform finishes, you write messy data. If transform runs before extract finishes, you process nothing.

Each stage has a clear boundary. Extract reads from the source, transform shapes data in memory, and load writes to the target destination.

Batch windows and real-time streams

Pipelines operate at two primary cadences: scheduled batches or real-time event streams.

CadenceHow it operatesLatencyTypical use case
BatchCollects data over a period and runs on a scheduleHours or overnightDaily finance summaries and reporting tables
StreamingProcesses each event as it occursSub-second to secondsFraud detection, live delivery tracking, urgent alerts

Batch is predictable and sufficient for daily dashboards. Streaming is reserved for workflows that require an immediate reaction. Most data platforms run both side by side.

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
PipelineA written-down chain of steps that moves data from a source to a place people can query
ExtractPull data out of its source
TransformClean and reshape the data
LoadWrite the result into the lake or warehouse
BatchProcess a chunk of data on a schedule
IdempotentRunning it twice leaves the same result as running it once

Why this exists

Silent failures in manual runs

Manual data processing fails in quiet, destructive ways. Someone forgets a morning run, an API times out unnoticed, or two people trigger the script and write duplicate rows.

Yesterday's orders as extracted. The true revenue is ₹120.

order_idcityamount_inr
101Bengaluru40
102Mumbai50
103Delhi30

Run the load step once and revenue reports 120. Run it a second time because someone was unsure, and three duplicate rows append. The dashboard shows 240 without throwing any database error.

This duplicate append happens because a simple script lacks idempotency: the property where running an operation twice leaves the same result. The database accepts whatever rows the script inserts.

Watch outA job you have to remember is not overnight-safe

If your routine requires opening a laptop and clicking Run, the first holiday becomes an incident. Write the steps down as code that runs unattended before reaching for complex tools.

Source drift as volume grows

At 20 orders a day, an engineer can spot anomalies by eye. At 20,000 orders, manual inspections become impractical.

  1. Vendor updateQuiet format change
  2. Source driftAmounts flip from rupees to paise
  3. Unchecked loadRevenue inflated 100x
  4. Pipeline testAutomated check halts the run

Scale hides subtle shifts like swapped date formats or unit changes. Automated pipeline checks verify data shapes on every run, catching errors before bad numbers reach business dashboards.

Picture this

The factory assembly line

Factories solved repeatable workflows with assembly lines. Parts slide down a fixed conveyor where each station performs one specific task before passing the piece forward.

  1. Station 1: ExtractRaw parts unloaded from delivery trucks
  2. Station 2: TransformShaping, polishing, and quality testing
  3. Station 3: LoadFinished product packaged into warehouse inventory

Swap two stations and the final product breaks. What makes an assembly line dependable is that the path stays identical on every shift, running whether watched or unattended.

If a station jams on an assembly line, the conveyor stops before half-assembled items reach shipping. A data pipeline does the same by halting before bad data writes to destination tables.

From ETL to ELT

In traditional on-premises architectures, storage and database compute were expensive. Teams transformed data on specialized ETL servers before writing only summarized numbers to the warehouse.

PatternProcessing engineRaw history kept?Rerun flexibility
ETLSeparate compute server before loadingOften discarded to save storageDifficult: requires re-fetching from source
ELTCloud warehouse engines via SQLPreserved permanently in cheap object storageFlexible: rerun transformations over raw bronze anytime

ELT gained adoption when cloud storage became inexpensive. Landing raw files into cloud object storage costs little. If business logic changes months later, engineers update the SQL and rerun across the preserved raw files.

In traditional ETL, fixing a transformation mistake required asking upstream application teams to replay lost historical events. ELT avoids that bottleneck.

The three medallion tiers

Modern data platforms organize ELT using the medallion architecture. Raw files land into bronze, get cleaned into silver, and aggregate into gold reporting marts.

If finance alters a tax calculation in March, you never have to ask source systems to replay January data. You adjust the transformation model and rerun across existing bronze tables.

A small example

Pipeline steps as a Python sequence

In Python, pipeline stages begin as ordered steps. The first milestone is declaring the execution sequence explicitly in code.

PythonName the steps in order
STEPS = ["extract", "transform", "load"]
print("a pipeline is")
for name in STEPS:
    print("-", name)
print("count", len(STEPS))

Guess how many lines the loop will print before revealing the output.

a pipeline is
- extract
- transform
- load
count 3

Representing stages as an ordered list establishes that downstream actions never start until upstream tasks finish successfully.

Passing records between isolated stages

Modular pipeline functions pass output from one step directly as input to the next, keeping each responsibility isolated.

PythonThree functions, one sequence
def extract():
    return {"orders": 3, "source": "api"}

def transform(raw):
    return {"orders": raw["orders"], "status": "clean"}

def load(clean):
    return {"written": clean["orders"], "target": "warehouse"}

raw = extract()
clean = transform(raw)
written = load(clean)
print(raw, clean, written)

Predict what load will return as the target key.

{'orders': 3, 'source': 'api'} {'orders': 3, 'status': 'clean'} {'written': 3, 'target': 'warehouse'}

Each function handles one task. This isolation enables workflow schedulers to manage retries, measure runtimes, and alert on individual failures independently.

Filtering bad records and casting numbers

A realistic data batch involves type cleanup and business rules, such as converting string amounts to numbers and filtering cancelled orders.

What extract returns. Amounts are strings.

order_idcitystatusamount
101Bengalurudelivered249.00
102Mumbaicancelled180.00
103Delhidelivered99.50
PythonSame three steps on real-looking rows
raw_orders = [
    {"order_id": 101, "city": "Bengaluru", "status": "delivered", "amount": "249.00"},
    {"order_id": 102, "city": "Mumbai", "status": "cancelled", "amount": "180.00"},
    {"order_id": 103, "city": "Delhi", "status": "delivered", "amount": "99.50"},
]

def extract():
    return raw_orders

def transform(rows):
    return [
        {"order_id": r["order_id"], "city": r["city"], "amount": float(r["amount"])}
        for r in rows
        if r["status"] == "delivered"
    ]

def load(rows):
    total = sum(r["amount"] for r in rows)
    print("loaded", len(rows), "orders, total ₹" + str(total))

clean_rows = transform(extract())
load(clean_rows)

Predict how many orders survive the status filter before checking the total.

loaded 2 orders, total ₹348.5

What load received after transform: cancelled order 102 is dropped and amounts are numbers.

order_idcityamount
101Bengaluru249.0
103Delhi99.5
Watch outSwapping steps breaks the pipeline

If you run load before transform, it tries summing string text like "249.00", which fails outright. If you omit the status filter, the cancelled order sneaks into revenue, printing 528.5 instead of 348.5. That silent calculation error is the kind that reaches business dashboards.

Crashes versus swallowed errors

A pipeline that crashes loudly stops immediately and points to the exact bug. A pipeline that swallows exceptions creates invisible corruption.

PythonLet the error happen and catch it cleanly
def extract(force_failure=False):
    if force_failure:
        raise ConnectionError("timed out talking to orders API")
    return [{"order_id": 101, "amount": 249.0}]

try:
    extract(force_failure=True)
except ConnectionError as err:
    print("caught error:", err)
caught error: timed out talking to orders API

That is the honest behavior. The run halts, logs the network timeout, and triggers an alert. Compare that with catching everything blindly:

PythonCatch everything and say nothing
def safe_extract(force_failure=False):
    try:
        return extract(force_failure)
    except Exception:
        return None

rows = safe_extract(force_failure=True)
print("swallowed rows:", rows)
print("pipeline status: success")
swallowed rows: None
pipeline status: success

The job prints success with zero rows, leaving dashboards completely blank. Always catch specific exceptions, log diagnostic context, and let unplanned errors fail loudly.

Pipeline rules to remember

Building reliable data pipelines requires discipline around step ordering, data preservation, and honest error handling.

The lifecycle of a production data pipeline
  1. Extract data from APIs, databases, or files and land raw records into object storage.

    1. extract
    2. raw bronze
  2. Clean formats, cast types, and remove invalid rows in transform without modifying sources.

    1. transform
    2. clean silver
  3. Load curated rows into analytical destination tables using idempotent writes.

    1. load
    2. gold tables
  4. Surface exceptions loudly so schedulers record failures instead of masking empty runs.

    1. fail loud
    2. alerting
  1. Isolate extract, transform, and load into distinct functions or tasks.
  2. Store raw copies of extracted data before running transformations.
  3. Write data using idempotent operations to make reruns safe.
  4. Allow unexpected exceptions to halt the pipeline loudly rather than swallowing errors.

Common beginner questions

Is a Jupyter notebook a pipeline?

A notebook is a useful place to explore. A pipeline is code that runs unattended, with a clear start, a clear output, and a record of whether it worked. Move notebook logic into scripts or orchestrator tasks before trusting it on a schedule.

Do I always transform before I load?

In ETL, yes. In ELT you load raw data first and transform it inside the warehouse. Both are pipelines. What you must never do is publish reporting tables nobody transformed, or transform a table that this run never loaded.

Why bother with a scheduler if cron can start one script?

For one independent script, cron can work. Once extract needs retries, load must wait for it, and you need to inspect last Tuesday's run, you need an orchestrator. That is what later lessons build toward.

What comes next

A pipeline that only runs when a person clicks a button is not finished. The next lesson covers scheduling: how a cron expression specifies execution times like 2 AM daily, so your steps start without manual intervention.

RememberBefore you start

This track assumes you completed Core Python. The exercises use lists, dicts, and small functions in the Python editor.

Practice

The exercise lists three pipeline tasks. Decide whether each task is extract, transform, or load, and assign the 3 words to a list called result, in the same order as TASKS. Print it, then compare with the pipeline figure above.

Going further: run the same pipeline for a past day

A pipeline that only knows 'today' cannot repair yesterday. A pipeline that takes the date as an input can be run for any day, which is what a backfill is: running the same job for a range of past dates, for example after a bug fix.

The trick is that each run must replace its own day, not add to it.

Predict the table after a normal run for 3 days, then after a backfill of day 2.

PythonA date-parameterised load, then a backfill
source = {
    "2026-09-01": [100, 150],
    "2026-09-02": [200],
    "2026-09-03": [50, 25, 25],
}
warehouse = {}                                    # one entry per day

def run(day):
    warehouse[day] = sum(source[day])             # replaces that day, never appends

for day in source:
    run(day)
print("after normal runs:", warehouse)

source["2026-09-02"] = [200, 75]                  # a late order arrives for day 2
run("2026-09-02")                                 # backfill just that day
print("after backfill   :", warehouse)
after normal runs: {'2026-09-01': 250, '2026-09-02': 200, '2026-09-03': 100}
after backfill   : {'2026-09-01': 250, '2026-09-02': 275, '2026-09-03': 100}

Day 2 changed from 200 to 275 and the other days were untouched. Because run(day) replaces that day's entry, running it twice gives the same answer. If it appended instead, the backfill would have counted day 2 twice. A scheduler such as Airflow gives each run its date for exactly this reason.

Pipeline written asBackfill behaviour
run(date): replace that date's partitionSafe: run any day, any number of times
run(): append whatever is new since last timeUnsafe: reruns duplicate, and a past day cannot be targeted
run(): uses today's date inside the codeCannot backfill at all without editing the code

Try a new scenario

A bug in a transform has produced wrong values for the last 10 days. Describe the safe repair, and what you check afterwards.

Work it out, then open the answer

Fix the code, then run the pipeline for each of the 10 dates, replacing each day's output. Check that row counts and totals for those days now reconcile with the source, and that days outside the range did not change. This only works because each run is keyed by its date and replaces its own output.

Interview: what makes a pipeline reliable, in one sentence?

It gives the same result when run twice (idempotent), declares what it depends on, retries transient failures, and checks its own output, so a bad day is detected and repaired instead of silently shipped.

Close the lesson and explain it

Without scrolling up, explain what a backfill is and why the run needs the date as an input.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Practice this

Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.

Rate:
Was this useful?