Pipelines & reproducibility

The horror of the unrepeatable analysis

It is Friday at 4 p.m. Your manager walks over and says: “Great number on revenue last month — can you rerun it with this week's data added?” Your stomach drops. Three weeks ago you opened a spreadsheet, deleted a few obviously-broken rows by hand, copied a column, sorted it, built a pivot table, and typed the headline figure into a slide. You did not write any of that down. Now you cannot get the same number twice, let alone an updated one. That cold feeling is what this guide exists to prevent.

Let's define the goal in plain words. An analysis is reproducible when someone else — or you, six months from now — can take the same raw data and the same steps and arrive at exactly the same result. Note the word “exactly”: same inputs in, same number out, every time, with nothing left to memory. This is different from replicability, which asks whether a fresh, new dataset would lead to a similar conclusion. Replicability is about whether a finding is real; reproducibility is the more basic promise that your own work is even rerunnable. This guide is about reproducibility — the foundation everything else stands on.

Why care so much? Four reasons. First, trust: a number nobody can reproduce is a rumour, not a result. Second, debugging: when a figure looks wrong, you can only find the mistake if you can step through how it was made. Third, collaboration: a teammate should be able to build on your work without a one-hour walkthrough from you. Fourth, updates: business questions never get asked once — “rerun it with this month's data” is the rule, not the exception.

Here is the uncomfortable arithmetic of doing things by hand. Suppose your analysis has 20 manual steps, and on any given rerun you reproduce each step exactly — same filter, same sort, same cell selected — with 95% reliability. The chance that the whole chain comes out identical is 0.95 multiplied by itself 20 times. Introduce that idea before the formula: each careful step is almost always right, yet the errors compound.

Even at 95% per step, 20 hand steps reproduce only about 36% of the time — you would fail to get the same answer roughly two times out of three. Automation is not perfectionism; it is the only way the math works out in your favour.

Scripts beat clicks

The single most important habit in data work is this: every transformation should be code in a file, run from start to finish, not a sequence of mouse clicks you have to remember. A click leaves no trace. A line of code is a permanent, exact, shareable record of what you did. When the steps live in a script — a plain text file of instructions a computer runs top to bottom — rerunning the whole analysis becomes one command instead of an afternoon of careful re-clicking.

Compare the two worlds. Manual: “I sorted by date, deleted the rows with blank order IDs, removed duplicates, then made a pivot.” That sentence is not runnable, not checkable, and not exactly repeatable. Scripted: the same logic written as code that anyone can read, run, and diff. Below is the manual story turned into a tiny, honest reproducible cleaning script. It reads the raw file, never edits it, and writes a separate clean file.

import pandas as pd

# raw data is read-only: we read it, we never overwrite it
raw = pd.read_csv("data/raw/orders.csv")

clean = (
    raw
    .drop_duplicates(subset="order_id")        # remove exact duplicate orders
    .dropna(subset=["order_id", "amount"])      # drop rows we cannot use
    .assign(amount=lambda d: d["amount"].astype(float))
)

# write a NEW derived file; the raw file is left untouched
clean.to_csv("data/clean/orders.csv", index=False)

One script is good; a chain of scripts that rebuild each other is better. A small Makefile (a recipe file that says “to build this output, run that command, but only if its inputs changed”) lets a single command rebuild everything in the right order, from raw data all the way to the final report. The dependency lines below mean: the clean file depends on the raw file and the cleaning script, and the report depends on the clean file.

# `make all` rebuilds every output from raw data, in dependency order
all: reports/revenue.csv

data/clean/orders.csv: data/raw/orders.csv clean_orders.py
	python clean_orders.py

reports/revenue.csv: data/clean/orders.csv revenue.py
	python revenue.py

1. Write down, in order, every step you did by hand to get the number.
2. Translate each step into one or a few lines of code in a script.
3. Read raw data, write derived data — never overwrite the source.
4. Delete the spreadsheet. If a step isn't in the script, it didn't happen.
5. Run the script from a clean state and confirm you get the same number.

ETL vs ELT: when do you clean?

Once your steps are scripts, a design question appears: where in the journey do you clean and reshape the data? Two classic answers have names. ETL stands for Extract, Transform, Load: you pull data out of its sources, transform it (clean, join, reshape) on the way, and then load the finished result into a central store. ELT flips the last two letters — Extract, Load, Transform: you pull the data, load it raw into a central store first, and only then transform it there. See ETL and ELT for the term itself; the rest of this section is about why the order matters.

Make it concrete with an online shop. You have orders sitting in the app's database and ad spend sitting behind a marketing API. In the ETL world, a Python job reaches out to both, cleans and joins them in memory, and writes one tidy “daily marketing ROI” table into the store. In the ELT world, you dump both sources — raw, untouched — into a data warehouse (a central database built for analysis), and then write SQL that transforms the raw tables into the clean ROI table inside the warehouse.

Why did the industry largely flip from ETL to ELT? Cloud warehouses (think BigQuery, Snowflake) made storage cheap and made columnar computation enormously fast. Once storing the raw data costs almost nothing, loading it first and transforming later has two big wins for reproducibility. You keep the raw data forever, so when a transformation turns out to be wrong you can re-derive the clean table from scratch; and the transformation logic lives as readable SQL that the whole team can inspect, version, and rerun. Here is a tiny ELT transform.

-- ELT: the raw table is already loaded; we transform it with SQL
create or replace table analytics.clean_orders as
select
    order_id,
    cast(amount as float64) as amount,
    date(created_at)        as order_date
from raw.orders
where order_id is not null;

A close cousin of the warehouse is the data lake — a cheaper store that holds raw files of any shape (logs, images, JSON) before they are structured. Many teams use a lake for raw landing and a warehouse for clean analytics, and tools like dbt organize the SQL transformations into a tidy, tested, dependency-aware project. You don't need to master these tools today; you need the idea: keep the raw, transform with versioned code, never with a one-off click.

Thinking in pipelines (and DAGs of steps)

Once you have several scripts that feed each other, you have the beginnings of a data pipeline — a set of automated steps that take raw input and produce a trusted output, run on a schedule or triggered by an event. The mental model that makes pipelines manageable is the DAG: a directed acyclic graph. That sounds intimidating; it just means “a picture of steps connected by arrows, where the arrows never loop back.” Each step is a node; an arrow from step A to step B means B needs A's output.

Picture the shop's pipeline. Raw orders flow into a “clean orders” step, which flows into a “daily revenue” step. Separately, raw ad data flows into a “clean ads” step. Finally, clean orders and clean ads both flow into one “marketing ROI report.” Drawn out, it's a little tree of arrows with no cycles. The payoff is huge: the moment the raw ad feed changes, the system knows it only needs to rebuild the ads branch and the final report — not the untouched orders side. You stop rebuilding everything by hand and start rebuilding exactly what changed.

Who runs the graph on time and watches for failures? An orchestrator — tools such as Airflow, Dagster, or Prefect. See orchestration: it is the conductor that reads your DAG, schedules each step, retries failures, and alerts you when last night's run broke. You declare the steps and their dependencies; the orchestrator figures out the order and the rebuild logic so you don't have to hold it in your head.

Schemas and data contracts

A pipeline is only as trustworthy as the data flowing through it, and the quiet killer is data whose shape changes without warning. The defense is a schema — the agreed shape of a table: its column names, the data type of each (integer, float, text, date), which columns may never be empty, and which column is the primary key that uniquely identifies a row. A schema turns vague hopes about your data into explicit, checkable rules.

Now add a promise on top, and you get a data contract: an agreement between the team that produces the data (say, the engineers who log each order) and the teams that consume it (you, the analyst). The contract says, in writing, “order_id will always be a unique, non-null integer; amount will always be a non-negative number in dollars; currency will always be one of TWD, USD, JPY.” When everyone agrees and the agreement is enforced by automated checks, upstream changes can no longer silently rot your numbers.

Why does this matter so much? Imagine the app team, in a harmless-looking refactor, starts sending amount in cents instead of dollars. Nothing crashes. The column is still a non-negative number, so a naive pipeline accepts it — and every revenue figure downstream is suddenly 100 times too big. A contract test that knew the expected range would have caught it the same night, before it reached a slide in front of your CEO.

import pandera as pa
from pandera import Column, Check

orders_contract = pa.DataFrameSchema({
    "order_id": Column(int, unique=True, nullable=False),
    "amount":   Column(float, Check.in_range(0, 100000)),   # dollars, sane range
    "currency": Column(str, Check.isin(["TWD", "USD", "JPY"])),
})

orders_contract.validate(clean)   # raises an error the moment the data breaks the contract

1. Write down the schema: column names, types, nullability, and the key.
2. Add range and category checks that encode what a sane value looks like.
3. Run the checks at the pipeline's boundaries, on every run, automatically.
4. Make a failed check stop the pipeline — loud failure beats silent corruption.
5. Version the contract with the code so its history is visible too.

Versioning data and code together

To reproduce a past result exactly, you need to recover three things, not one: the code that ran, the data it ran on, and the environment it ran in. Miss any one and “the number from March” becomes unrecoverable. Code is the easy part — it goes in git, the standard tool that records every version of every text file and lets you check out exactly the code as it was on any past date.

Data is harder. git was built for small text files, not multi-gigabyte datasets, and it chokes on them. The standard answer is version control for data: you do not stuff the data into git itself, you store a small fingerprint (a content hash) of the exact data snapshot in git, while the bytes live in cheap storage. Tools like DVC and lakeFS, and warehouse table formats like Delta or Iceberg with their “time travel” feature, all do versions of this. The point is the same: a commit can pin you to one precise version of the data.

# version the code in git as usual
git add clean_orders.py revenue.py Makefile
git commit -m "revenue pipeline v1"

# version the data by content: the bytes go to storage, a tiny pointer goes to git
dvc add data/raw/orders.csv
git add data/raw/orders.csv.dvc
git commit -m "snapshot raw orders 2026-03-01"

The third leg is the environment — the specific versions of the language and libraries you used. A result computed with pandas 1.5 can differ from one computed with pandas 2.0; “it works on my machine” is the classic symptom of an unpinned environment. The fix is to pin dependencies in a lockfile (a requirements file or a lockfile that records exact versions), or to ship the whole environment in a container so a teammate runs your code inside an identical box.

1. Pin the code: every script committed to git, with a meaningful message.
2. Pin the data: record the exact snapshot/version the result used.
3. Pin the environment: a lockfile or container with exact library versions.
4. Fix random seeds so any sampling or shuffling is repeatable.
5. Use relative paths, not /Users/you/Desktop — paths must work on any machine.

A reproducibility checklist

Let's gather everything into one checklist you can actually run down before you call an analysis “done.” None of these items is exotic; together they are the difference between a number you can defend and a number you merely remember producing. Treat the list as the standard your future self will thank you for.

1. Raw data is read-only, with its exact snapshot/version recorded.
2. Every transformation is code in version control — no manual spreadsheet edits.
3. One command rebuilds everything from raw to final output.
4. The pipeline is a DAG of idempotent steps; rerunning is always safe.
5. Schema and data-contract checks run at the boundaries on every run.
6. The environment is pinned (lockfile or container) and random seeds are fixed.
7. A README lets a newcomer run it with zero tribal knowledge.
8. The whole thing reproduces, top to bottom, on a different machine.

This closes the data-wrangling track. You can now turn messy, missing, mis-shaped data into tidy data, clean and join it carefully (see cleaning, missing data, deduplication, joins), reshape and summarize it (see pivoting and group-by aggregation), and — the subject of this guide — wrap the whole thing in a scripted, versioned, reproducible pipeline. Where do these pipelines actually live and run? That is the next track: the modern data stack, where warehouses, SQL, dbt, orchestration, and the road to ML in production come together.