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.
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.
| Cadence | How it operates | Latency | Typical use case |
|---|---|---|---|
| Batch | Collects data over a period and runs on a schedule | Hours or overnight | Daily finance summaries and reporting tables |
| Streaming | Processes each event as it occurs | Sub-second to seconds | Fraud 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.
| Word | What it means |
|---|---|
| Pipeline | A written-down chain of steps that moves data from a source to a place people can query |
| Extract | Pull data out of its source |
| Transform | Clean and reshape the data |
| Load | Write the result into the lake or warehouse |
| Batch | Process a chunk of data on a schedule |
| Idempotent | Running 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_id | city | amount_inr |
|---|---|---|
| 101 | Bengaluru | 40 |
| 102 | Mumbai | 50 |
| 103 | Delhi | 30 |
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.
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.
- Vendor updateQuiet format change
- Source driftAmounts flip from rupees to paise
- Unchecked loadRevenue inflated 100x
- 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.
- Station 1: ExtractRaw parts unloaded from delivery trucks
- Station 2: TransformShaping, polishing, and quality testing
- 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.
| Pattern | Processing engine | Raw history kept? | Rerun flexibility |
|---|---|---|---|
| ETL | Separate compute server before loading | Often discarded to save storage | Difficult: requires re-fetching from source |
| ELT | Cloud warehouse engines via SQL | Preserved permanently in cheap object storage | Flexible: 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.
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.
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_id | city | status | amount |
|---|---|---|---|
| 101 | Bengaluru | delivered | 249.00 |
| 102 | Mumbai | cancelled | 180.00 |
| 103 | Delhi | delivered | 99.50 |
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_id | city | amount |
|---|---|---|
| 101 | Bengaluru | 249.0 |
| 103 | Delhi | 99.5 |
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.
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:
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.
Extract data from APIs, databases, or files and land raw records into object storage.
- extract
- raw bronze
Clean formats, cast types, and remove invalid rows in transform without modifying sources.
- transform
- clean silver
Load curated rows into analytical destination tables using idempotent writes.
- load
- gold tables
Surface exceptions loudly so schedulers record failures instead of masking empty runs.
- fail loud
- alerting
- Isolate extract, transform, and load into distinct functions or tasks.
- Store raw copies of extracted data before running transformations.
- Write data using idempotent operations to make reruns safe.
- 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.
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.
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 as | Backfill behaviour |
|---|---|
| run(date): replace that date's partition | Safe: run any day, any number of times |
| run(): append whatever is new since last time | Unsafe: reruns duplicate, and a past day cannot be targeted |
| run(): uses today's date inside the code | Cannot 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.
- Handle Missing ValuesInterview-style drill: Show dropna, mean fill, and ffill strategies labeled by method.Studiobeginnerpandas12 min
- Forward-fill per deviceInterview-style drill: Fill missing readings from the same device only, never across devices.Studiointermediatepandas14 min
- Fill with the group median and flag itInterview-style drill: Fill each missing temperature with the median of its site and mark which values were filled.Studiointermediatepandas14 minLocked