Data operations — a pipeline you can trust

You will be able to

• ✓ Name extract, transform, load as three jobs
• ✓ Clean before you aggregate
• ✓ Write a small pipeline as functions with tests

Extract is not “hope the file is there”

Check the path exists. Check the header row matches what you documented. Log how many rows arrived. If yesterday’s file had 1,200 rows and today’s has 12, stop — do not silently average 12 students and email the principal.

APIs (next lesson) are also extract: GET a JSON list, turn it into rows. The rest of the pipeline should not care whether the rows came from csv.DictReader or response.json().

Transform is where bugs hide

Strip spaces from names. Normalise house to Title Case so "blue" and "Blue" group together. Parse dates once, into one timezone. Convert marks to numbers — CSV gives you strings. Filter last. Aggregate last of all. If you average before dropping absentees flagged as -1, you invent a fake mean.

Idempotence: running the transform twice on the same extract should give the same load. Hidden “+= 1” counters in the middle of a notebook are the opposite.

Load is a contract with a human

The output table should have stable column names the next chart can bind to. Include units in the column name (celsius, not value). Include the run date. Write to a new file or a new SQLite table named like marks_clean_2026_08_24 rather than overwriting marks.csv — you will need the dirty original.

A pipeline without a test is a rumour. Test the transform with a three-row fixture: one normal, one missing, one impossible (marks = 400).

Scheduling and failure

Real pipelines run at 06:00 from Task Scheduler or cron. Failures must be loud: an email, a log file, a non-zero exit code. Swallowing exceptions so “the script never crashes” is how a school runs on last week’s attendance for a month.

Words that matter

ETL

Extract, Transform, Load — the three stages of a data pipeline.

Idempotent

Running again with the same input does not double-count.

Fixture

A tiny known dataset used to test a transform.

Contract

Documented columns, types and meaning of a table.

Common mistakes

On a full Python install — pandas pipeline

A real transform is a function: DataFrame in, DataFrame out. The notebook cells become that function when you ship.

pip install pandas

Clean house names, drop impossible marks, write a derived table.

import pandas as pd

def transform(df: pd.DataFrame) -> pd.DataFrame:
    out = df.copy()
    out["house"] = out["house"].str.strip().str.title()
    out["marks"] = pd.to_numeric(out["marks"], errors="coerce")
    out = out[out["marks"].between(0, 100)]
    return out

raw = pd.read_csv("data/marks_raw.csv")
clean = transform(raw)
clean.to_csv("data/marks_clean.csv", index=False)
print("rows in", len(raw), "rows out", len(clean))

Example program — Clean then average

Drop marks above 100 before the mean. Pipelines fail when you skip this.

Your turn — Drop the impossible score

raw = [70, 250, 80]. Keep only values <= 100. Print so 70 and 80 appear and the mean is 75.

Self-assessment

Check your understanding before you mark the lesson complete.