Data Pipelines and ETL: From Raw Data to Insights

The Problem: Three Scoresheets, One Leaderboard

Suppose your class wants to build a live top-scorers leaderboard for an ongoing IPL season. You are not the BCCI, so you cannot get one perfect, ready-made table. Instead you get three separate, differently shaped pieces of data, because that is how real information actually shows up in the world:

• An official match scorecard, exported as a CSV file, with a player ID and the runs and balls faced — but no names, because the scoring system only tracks IDs internally.
• A team roster, a small lookup table mapping each player ID to a full name — kept separately because rosters change less often than match scores.
• A fantasy-cricket app's JSON export, giving fantasy points per player ID — except this app's IDs are sometimes lowercase, because it was built by a different team with different conventions.

None of these three sources, on its own, answers your question. The CSV has numbers but no names. The JSON has points but no runs. And even the "same" player ID might be written two different ways across sources. To get one clean, sorted leaderboard, you need to pull data out of all three places, fix the mismatches, combine them correctly, and store the result somewhere your app can actually read from. That entire journey — from scattered raw data to one trustworthy table — is what a data pipeline does, and the specific three-step pattern it usually follows is called ETL: Extract, Transform, Load.

What Exactly Is a Data Pipeline?

A data pipeline is a sequence of automated steps that moves data from one or more sources to a destination, changing its shape along the way so it becomes usable. The word "pipeline" is a good mental image: think of the water supply to a house. Water starts out in several different places — a river, a borewell, a rainwater tank — each with its own impurities and mineral content. It gets pumped through a treatment plant that filters out sediment, kills bacteria, and adjusts mineral levels. Only after treatment does it flow into a clean overhead tank that every tap in the house draws from. Nobody drinks straight from the borewell, and nobody re-treats the water every single time they open a tap — the treatment happens once, upstream, and the clean tank is what gets used repeatedly.

A data pipeline plays the same role for information. Raw data sitting in a CSV export, a JSON API response, or a scraped web page is like untreated water: it might be accurate, but it is not yet in a shape your program can safely and efficiently use. ETL is the most common pattern for this treatment process, and it names its three stages precisely:

• Extract — pull the raw data out of each source, in whatever format it natively comes in, without trying to fix anything yet.
• Transform — clean, standardize, combine, and compute on that raw data until it matches one consistent, correct structure.
• Load — write the finished, clean data into a destination (a database table, a file, an in-memory structure) that applications can query quickly and reliably.

Before writing any code, it helps to fix some vocabulary, because you will need it precisely. In our CSV source, each line like P101,72,45 is a record (also called a row), and each individual value inside it — the player ID, the runs, the balls — is a field (also called a column). The set of field names and what type of value each holds (text, whole number, decimal) is that source's schema. A key is a field used to identify a record and to match it against records in another source — here, player_id is the key that will let us connect the CSV, the roster, and the JSON export. Matching records from two sources using a shared key is called a join. You will see all four of these ideas — record, field, schema, key, join — appear directly in the code below.

Stage 1: Extract — Getting Data Out, As-Is

The rule for Extract is simple: pull the data into your program's memory in a structured form, but do not try to fix, rename, or recompute anything yet. Extract's only job is to get raw data out of a source's native format (CSV text, a JSON string, a database query result) and into a form your programming language can work with, such as a list of dictionaries.

Here is the Extract stage for all three of our cricket sources. Read it as: "the CSV becomes a list of row-dictionaries, the JSON string becomes a list of point-dictionaries, and the roster is already a simple lookup dictionary."

import csv, json, io

# Source 1: official scorecard, exported as CSV (numbers only, no names)
raw_csv = """player_id,runs,balls
P101,72,45
P102,15,12
P103,0,3
P104,54,38
P101,72,45"""

def extract_scores(csv_text):
    reader = csv.DictReader(io.StringIO(csv_text))
    return list(reader)

scores = extract_scores(raw_csv)
print(len(scores))          # 5 rows -- notice the duplicate P101 line
print(scores[0])            # {'player_id': 'P101', 'runs': '72', 'balls': '45'}

# Source 2: team roster, a small lookup table (id -> full name)
roster = {
    "P101": "Virat Kohli",
    "P102": "Ruturaj Gaikwad",
    "P103": "Ishan Kishan",
    "P104": "Shubman Gill"
}

# Source 3: a fantasy-league app's JSON export (points, not runs)
raw_json = '''
[
  {"id": "P101", "fantasy_points": 128},
  {"id": "P102", "fantasy_points": 34},
  {"id": "p103", "fantasy_points": 5},
  {"id": "P104", "fantasy_points": 96}
]
'''
fantasy = json.loads(raw_json)
print(fantasy[2])           # {'id': 'p103', 'fantasy_points': 5} -- lowercase id!

Trace through this carefully, because tracing code line by line is exactly the skill this chapter is building. csv.DictReader reads the header line (player_id,runs,balls) and uses it as keys for every following row, so list(reader) produces five dictionaries — one per data line, including the accidental repeat of P101's row near the bottom. That repeat is not a typo in our example; it models a very real situation, where a scoring system re-uploads a corrected file and the pipeline accidentally reads both the old and new copy. Notice also that scores[0] stores '72' and '45' as text, not numbers — CSV files have no concept of data types, so every value that comes out of csv.DictReader is a string, even if it looks like a number. And in the JSON source, fantasy[2] has an id of "p103", lowercase, while every other id and the roster use uppercase P103. Extract does not fix either of these problems. It just faithfully reports what each source actually contains, warts and all. That is by design: if Extract silently "fixed" things, you would lose the ability to know what your raw data actually looked like, which makes bugs far harder to trace later.

Stage 2: Transform — Where a Pipeline Earns Its Keep

A common misconception is that Extract is the hardest part of building a pipeline, since it has to deal with several external systems. In practice it is usually the opposite: Extract is often the fastest stage to write, because most sources have a standard reader (a CSV library, a JSON parser). Transform is where the real engineering work happens, because this is where every inconsistency between sources — mismatched IDs, duplicate rows, different units, missing values — has to be found and resolved by logic you write yourself. Our small example already contains three genuine transform problems: a duplicated row, a case-mismatched key, and the need to join three separate structures into one.

Let's handle each one. First, deduplication: we must not double-count P101's 72 runs. Second, key normalization: "p103" and "P103" must be treated as the same player before we try to join on them — if we compared the strings directly, they would not match, since string comparison in most programming languages is case-sensitive. Third, we need a derived field: strike rate, a standard cricket statistic that did not exist in any of our three raw sources and must be computed. Strike rate has a simple formula built from arithmetic you already know:

strike rate = (runs ÷ balls faced) × 100

It answers "how many runs would this batter score, on average, per 100 balls?" For Virat Kohli's row, runs = 72 and balls = 45, so strike rate = (72 ÷ 45) × 100 = 1.6 × 100 = 160.0. That single number is a derived field: a new piece of information computed from existing fields, something Transform does constantly, and something Extract should never attempt (Extract's job is to copy, not to calculate).

def transform(scores, roster, fantasy):
    seen = set()
    cleaned = []
    for row in scores:
        pid = row["player_id"].strip().upper()
        runs = int(row["runs"])
        balls = int(row["balls"])
        dedupe_key = (pid, runs, balls)
        if dedupe_key in seen:
            continue                      # drop the repeated P101 row
        seen.add(dedupe_key)
        strike_rate = round(runs / balls * 100, 1) if balls > 0 else 0.0
        cleaned.append({"id": pid, "runs": runs, "balls": balls, "strike_rate": strike_rate})

    # normalize every fantasy-app id to uppercase before using it as a join key
    fantasy_by_id = {f["id"].strip().upper(): f["fantasy_points"] for f in fantasy}

    merged = []
    for row in cleaned:
        pid = row["id"]
        merged.append({
            "name": roster.get(pid, "Unknown player"),
            "runs": row["runs"],
            "balls": row["balls"],
            "strike_rate": row["strike_rate"],
            "fantasy_points": fantasy_by_id.get(pid, 0)
        })
    return merged

clean_rows = transform(scores, roster, fantasy)
for r in clean_rows:
    print(r)

Trace this too. The loop walks through all five rows from scores. For each one it builds pid by stripping whitespace and forcing uppercase — this single line is what makes P101 always match P101 regardless of how it was typed. It converts runs and balls from text to actual integers with int(...), because you cannot do arithmetic on the string '72' the way you can on the number 72. It builds a dedupe_key from the ID and values together and checks it against a set called seen; when the second P101 row arrives with an identical key, it is already in seen, so continue skips it — this is exactly how a real pipeline guards against accidentally re-processing the same record twice. After the loop, cleaned holds exactly four rows, not five. Then fantasy_by_id is built as a dictionary comprehension that uppercases every fantasy-app ID as it builds the lookup — this is the fix for the "p103" problem: "p103".strip().upper() becomes "P103", which now matches the roster and the scores. Finally the merge loop looks up each player's name from roster and their points from fantasy_by_id using the normalized ID as the shared key — this is the join. Run it, and the printed output is:

{'name': 'Virat Kohli', 'runs': 72, 'balls': 45, 'strike_rate': 160.0, 'fantasy_points': 128}
{'name': 'Ruturaj Gaikwad', 'runs': 15, 'balls': 12, 'strike_rate': 125.0, 'fantasy_points': 34}
{'name': 'Ishan Kishan', 'runs': 0, 'balls': 3, 'strike_rate': 0.0, 'fantasy_points': 5}
{'name': 'Shubman Gill', 'runs': 54, 'balls': 38, 'strike_rate': 142.1, 'fantasy_points': 96}

Check the arithmetic yourself: Ruturaj Gaikwad's strike rate is (15 ÷ 12) × 100 = 1.25 × 100 = 125.0. Ishan Kishan faced 3 balls and scored 0, so (0 ÷ 3) × 100 = 0.0 — a legitimate result, not an error. Shubman Gill's is (54 ÷ 38) × 100 = 142.105263..., which round(…, 1) correctly rounds to 142.1. Notice too the if balls > 0 else 0.0 guard in the code: if a player faced zero balls (run out without facing a delivery, for instance), dividing by zero would crash the program, so the pipeline must explicitly handle that edge case rather than assume every record is "normal." Handling edge cases like this is a permanent part of Transform, not an optional extra.

Stage 3: Load — Writing the Result Somewhere Useful

Load takes the finished, correct data from Transform and writes it into a destination built for repeated, fast access — a database table, a structured file, or an in-memory table your app queries directly. The key design decision in Load is usually ordering and structure: you want the data stored in the shape and order that whoever uses it next will actually need, so they never have to redo Transform's work themselves.

For our leaderboard, "useful" means sorted by runs, from highest to lowest, and saved as a clean CSV that any spreadsheet or app can open directly.

def load(rows, filename="leaderboard.csv"):
    ranked = sorted(rows, key=lambda r: r["runs"], reverse=True)
    with open(filename, "w", newline="") as f:
        writer = csv.DictWriter(
            f, fieldnames=["name", "runs", "balls", "strike_rate", "fantasy_points"]
        )
        writer.writeheader()
        writer.writerows(ranked)
    return ranked

leaderboard = load(clean_rows)
for i, r in enumerate(leaderboard, start=1):
    print(i, r["name"], r["runs"], r["strike_rate"], r["fantasy_points"])

sorted(rows, key=lambda r: r["runs"], reverse=True) reorders the four dictionaries by their "runs" field, largest first. Tracing it against our four rows (72, 15, 0, 54), the sorted order by runs descending is 72, 54, 15, 0 — Kohli, then Gill, then Gaikwad, then Kishan. The function then writes a proper CSV file with a header row, and returns the same ranked list so the rest of the program can use it immediately without re-reading the file. The printed trace is:

1 Virat Kohli 72 160.0 128
2 Shubman Gill 54 142.1 96
3 Ruturaj Gaikwad 15 125.0 34
4 Ishan Kishan 0 0.0 5

That is the entire pipeline: three mismatched sources in, one trustworthy, sorted table out — produced automatically, and reproducible any time new scorecards arrive, without a human manually retyping names or recalculating strike rates.

Seeing the Whole Pipeline

Figure: raw records enter from three independently maintained, differently shaped sources. Extract copies them into memory unchanged. Transform is the widest box in the diagram on purpose — normalizing IDs, dropping the duplicate row, joining on the shared key, and computing the derived strike-rate field all happen here. Load sorts the result and writes it to a single destination table that an application can query directly, without repeating any of that work.

Batch or Streaming: When Does a Pipeline Actually Run?

Our example pipeline ran once, from start to finish, on a fixed snapshot of data. Real pipelines are almost always meant to run repeatedly, because the underlying data keeps changing — new deliveries get bowled, new UPI payments get made, new sensor readings arrive. There are two broad patterns for when a pipeline runs:

• Batch processing: the pipeline runs on a schedule — every few minutes, every hour, once a night — and each run processes whatever new data has accumulated since the last run. A large system showing "live" information, like a train's seat-availability display or a bank's account summary, is very often powered by a pipeline refreshing on a short but non-instant schedule rather than recalculating everything from scratch the instant you look at it.
• Streaming processing: the pipeline processes each new record the moment it arrives, one at a time, rather than waiting to collect a batch. This suits situations where even a few seconds of delay matters, such as fraud detection on a payment as it is being authorized.

Our cricket leaderboard, as written, is a batch job: you would re-run extract → transform → load after every over, or every few minutes, rather than trying to update the table after every single ball in real time. That's a completely reasonable design choice — batch pipelines are simpler to build, easier to debug, and perfectly adequate whenever "a few minutes old" is an acceptable answer.

A Modern Variation: ELT

You will also encounter the term ELT — Extract, Load, Transform, with Load and Transform swapped. The idea is that with today's powerful cloud data warehouses, it is sometimes cheaper and simpler to load the raw, unmodified data in first, and then run the transformation logic afterward, inside the warehouse itself, using its own processing power. ETL transforms data in a separate processing step before it ever reaches its destination; ELT dumps raw data into the destination immediately and transforms it there, on demand. Both patterns solve the same underlying problem, and the Transform logic you write — deduplication, normalization, joins, derived fields — is conceptually identical either way. What changes is only when and where that logic runs.

A Second Misconception, Corrected

It is tempting to think a pipeline just needs to run once, get it right, and be done. But a pipeline that only ever ran on one fixed sample of data (like the five rows in our example) has not actually been tested for the situations it will hit in production: what if a fourth source appears with a player ID your roster does not have? Our transform function already anticipates this — roster.get(pid, "Unknown player") returns a safe default instead of crashing, and fantasy_by_id.get(pid, 0) does the same for missing fantasy points. A pipeline that assumes every record will always look exactly like your test data is a pipeline that will eventually crash in front of real users; good Transform code always asks "what if this field is missing, misspelled, or duplicated?" and handles that case deliberately, the way both lookups above do.

Check Your Understanding

1. In the pipeline above, which stage would be responsible for converting the text "38" into the number 38 — Extract, Transform, or Load? Explain why that logic does not belong in the other two stages.
2. Suppose a fourth scorecard row arrives: P105,29,20, but P105 does not exist in roster or in the fantasy JSON. Trace the transform function's merge loop for this row and state exactly what name and fantasy_points would be set to.
3. Compute the strike rate by hand for a batter who scored 33 runs off 22 balls. Show the division and multiplication steps, then round to one decimal place.
4. A classmate suggests skipping the .strip().upper() normalization step because "the IDs will probably match anyway." Using the "p103" vs "P103" example from this chapter, explain concretely what would go wrong in the final leaderboard if that step were removed.
5. Is the cricket leaderboard pipeline in this chapter an example of batch processing or streaming processing? Justify your answer using the definition given above.

Answers: (1) Transform, via int(row["runs"]) — Extract must preserve the source's raw values exactly as given (as text, if that's how the CSV stored them), and Load only writes out data that is already in its final form, so type conversion belongs in the middle stage where cleaning happens. (2) pid becomes "P105"; since "P105" is not a key in roster, roster.get(pid, "Unknown player") returns the default string "Unknown player"; since it is also missing from fantasy_by_id, fantasy_points becomes 0. (3) (33 ÷ 22) × 100 = 1.5 × 100 = 150.0. (4) Without normalization, "p103" and "P103" would be treated as two different keys; the lookup fantasy_by_id.get("P103", 0) would fail to find "p103"'s entry and silently fall back to the default of 0, so Ishan Kishan's row would show 0 fantasy points instead of the correct 5 — a wrong answer with no error message, which is the most dangerous kind of pipeline bug. (5) Batch processing: it processes a fixed snapshot of accumulated records (five CSV rows) in one run, rather than reacting to each new ball or run the instant it happens.

Summary

A data pipeline automatically carries data from raw sources to a usable destination. ETL names its three canonical stages: Extract pulls records out of each source's native format without altering them; Transform normalizes keys, removes duplicates, joins related sources together on a shared key, computes derived fields such as strike rate, and explicitly handles missing or malformed data; Load writes the finished, correctly ordered result into a destination built for repeated queries, such as a database table or a clean CSV file. Extract usually looks harder than it is, because most formats have ready-made readers; Transform is where the genuine engineering effort and the genuine bugs live, because it is where every mismatch between sources has to be resolved deliberately. Pipelines are normally designed to run repeatedly — either on a schedule (batch) or continuously as new records arrive (streaming) — and the modern ELT variation simply changes when transformation happens relative to loading, not what the transformation logic itself has to do. Whenever you combine data from more than one source correctly and repeatably, you have built a data pipeline, whether or not you ever call it one.