Data Pipelines: ETL & Data Cleaning

Your school's Annual Sports Day just ended. Four houses — Ashoka, Gupta, Maurya, Chola — competed in nine events. Each house captain recorded results on their own phone, in their own Google Sheet, in their own handwriting style. Now someone has to combine all three sheets into one scoreboard and declare a winner. This sounds like a five-minute copy-paste job. It is not. It is the exact problem that data engineers at banks, railway systems, and exam boards solve every single day, and the techniques you will learn in this chapter are the real ones they use.

From Messy Sheets to One Clean Scoreboard

Here is what the three captains actually submitted, combined into one raw list:

1. Ashoka,   100m Race,  10
2. ashoka ,  100m race,  10
3. Gupta,    Long Jump,  8
4.  MAURYA,  Relay,      (blank)
5. Gupta,    long jump,  8
6. Chola,    Shot Put,   5
7. Ashoka,   High Jump,  7
8. Maurya,   Relay,      10
9. Chola,    Shot Put,   6

Look closely and you'll find every problem a real dataset ever throws at you, hiding in just nine rows. Row 1 and row 2 look like two different houses ("Ashoka" and "ashoka ") but they're the same house, entered twice — once by the captain and once by a volunteer who didn't know it was already logged. Row 4 has no score at all, because the Maurya captain forgot to note it down before the relay results were announced. Row 6 and row 9 both claim to be Chola's Shot Put result, but they disagree — 5 points in one, 6 in the other. And every single score, even the correct ones, is stored as text ("10") rather than a number, because that's what a Google Form always gives you, whether you type a number or a word.

If you just paste these nine rows onto a notice board, the scoreboard will be wrong, and worse, it will look confident while being wrong. This is the central problem of data engineering: raw data is never trustworthy by default. It has to earn trust through a defined process. That process is called a data pipeline, and its most common form has a name: ETL.

What a Data Pipeline Actually Is

A data pipeline is a repeatable sequence of steps that moves data from where it is produced to where it is used, changing its shape and fixing its problems along the way. The three sports day sheets are where the data is produced — inside each captain's phone, event by event. The scoreboard on the notice board is where the data is used — a single trusted source everyone can read. Everything that happens in between is the pipeline.

The most common shape a pipeline takes is called ETL, which stands for three stages, always performed in this order:

• Extract — pull raw data out of every source it lives in, and bring it into one place, in whatever messy shape it's in.
• Transform — fix, clean, standardise, and reshape that raw data until it is correct and consistent.
• Load — write the now-clean data into its final destination, where people or programs will actually rely on it.

Notice the order matters. You cannot transform data you haven't extracted yet, and you should never load data you haven't transformed — that's exactly how a wrong scoreboard gets put up on the notice board. Keeping these three stages separate, rather than mashing them into one tangled step, is what makes a pipeline debuggable: if the final scoreboard is wrong, you can check each stage independently to find out which one failed.

Stage 1: Extract — Getting the Raw Data Together

Extraction sounds simple — "just combine the sheets" — but in real systems the sources are rarely as similar as three Google Sheets. An Indian Railways data pipeline might extract from a ticket-booking database, a payment gateway's transaction log, and a coach-occupancy sensor feed, all at once, all in different formats. A UPI app's pipeline might extract from the bank's core ledger and the app's own notification log. The job of the Extract stage is only this: get every relevant raw record into one working collection, without trying to fix anything yet. Fixing during extraction is a common beginner mistake — it mixes two jobs together and makes both harder to check.

In Python, we would represent our nine raw sports day rows as a list of dictionaries — one dictionary per row, holding exactly what was submitted, typos and all:

raw_data = [
    {"house": "Ashoka",  "event": "100m Race", "points": "10"},
    {"house": "ashoka ", "event": "100m race", "points": "10"},
    {"house": "Gupta",   "event": "Long Jump", "points": "8"},
    {"house": " MAURYA", "event": "Relay",     "points": ""},
    {"house": "Gupta",   "event": "long jump", "points": "8"},
    {"house": "Chola",   "event": "Shot Put",  "points": "5"},
    {"house": "Ashoka",  "event": "High Jump", "points": "7"},
    {"house": "Maurya",  "event": "Relay",     "points": "10"},
    {"house": "Chola",   "event": "Shot Put",  "points": "6"},
]

This is a completely honest copy of the raw sheets — nothing has been cleaned. That honesty is the whole point of Extract: it gives every later stage a fixed, known starting point to work from.

Stage 2: Transform — Fixing What's Wrong, One Problem at a Time

Transform is where almost all of the real thinking in a data pipeline happens. It helps to break "clean the data" into separate, smaller problems, because each one needs a different fix. Let's look at each problem on its own with a tiny example before combining them.

Problem 1: Wrong data type. Every score in our raw data is text, not a number, because that's what forms produce. Text and numbers behave completely differently in code:

>>> "10" + 5
TypeError: can only concatenate str (not "int") to str

>>> int("10") + 5
15

You cannot add up house totals while the scores are still text — Python will refuse. The fix is type casting: converting the string "10" into the integer 10 using int(). This has to happen before any arithmetic, which is why it belongs in Transform, not Load.

Problem 2: Inconsistent formatting. "Ashoka" and "ashoka " look like the same house to a person, but not to a computer:

>>> "Ashoka" == "ashoka "
False

Python compares text character by character, and capital A is a different character from lowercase a; the trailing space is one more mismatched character. Left alone, this bug would create a phantom fifth house on the scoreboard. The fix is normalisation — applying the same formatting rule to every value so that equivalent things become identical:

>>> "ashoka ".strip().title()
'Ashoka'
>>> "Ashoka".strip().title()
'Ashoka'

.strip() removes leading and trailing spaces; .title() capitalises the first letter of each word. Run both raw strings through the same two functions and they land on the identical value, "Ashoka" — which is exactly what makes them comparable.

Problem 3: Duplicate records. Rows 1–2 are the same 100m Race result entered twice; rows 3 and 5 are the same Long Jump result entered twice. Once house and event names are normalised, both pairs collapse to the same key — ("Ashoka", "100M Race") and ("Gupta", "Long Jump") — and a duplicate is easy to detect: the second record has a key you've already seen. The fix is to check every new record's key against the ones already processed, and skip (or merge with) any repeat.

Problem 4: Missing values. Row 4 has no points at all — an empty string. Representing "we don't have this yet" honestly (rather than guessing a number or silently dropping the row) matters, because a later, more complete record might arrive. In our data, exactly that happens: row 8 reports Maurya's Relay score as 10. A well-designed Transform stage should be able to fill the earlier gap in with the later, real value, rather than losing the record entirely.

Problem 5: Conflicting duplicates. This is the hardest case. Rows 6 and 9 are both labelled Chola's Shot Put — but one says 5 points and the other says 6. Unlike Problem 3, these are not the same value entered twice; they are two different values claiming to be the same fact. A pipeline that silently keeps whichever one arrived first (or last) is quietly making up an answer. The responsible fix is to flag the conflict for a human to resolve, rather than guessing.

Stage 3: Load — Writing the Trusted Result

Once Transform has normalised the text, fixed the types, merged the true duplicates, filled the genuinely-resolvable gaps, and flagged the genuine conflicts, what remains is safe to load: write into the destination that people will actually rely on. For our sports day, that destination is a small master table — one clean row per (house, event) — from which house totals can finally be added up correctly. In a bigger system, "load" might mean writing into a database table, updating a dashboard, or generating the CSV a coach downloads. The defining feature of the Load stage is that nothing downstream of it should ever need to guess, re-clean, or double-check the data again — that work is already done.

Full Worked Pipeline: Cleaning the Sports Day Scoreboard

Here is the whole pipeline as one diagram, followed by the code that actually implements it.

Now the actual code. First, the normalisation and type-fixing step, applied to every raw record:

def clean_record(r):
    house = r["house"].strip().title()
    event = r["event"].strip().title()
    raw_points = r["points"].strip()
    points = int(raw_points) if raw_points != "" else None
    return {"house": house, "event": event, "points": points}

cleaned = [clean_record(r) for r in raw_data]

Trace this by hand for row 4, the blank one: house = " MAURYA".strip().title() strips the leading space to get "MAURYA", then .title() lowercases everything except the first letter of each word, giving "Maurya". raw_points = "".strip() stays "", so points is set to None — an honest "unknown," not a guessed zero. After this step, rows 1 and 2 both become {"house": "Ashoka", "event": "100M Race", "points": 10} — identical dictionaries, because .title() capitalises the letter right after the digit in "100m" too, turning both "100m Race" and "100m race" into "100M Race".

Next, deduplicate and resolve conflicts, keyed on (house, event):

master = {}
for r in cleaned:
    key = (r["house"], r["event"])
    if key not in master:
        master[key] = r["points"]
    elif master[key] is None and r["points"] is not None:
        master[key] = r["points"]          # fill a gap using a later record
    elif master[key] is not None and r["points"] is not None \
            and master[key] != r["points"]:
        print(f"CONFLICT at {key}: {master[key]} vs {r['points']}")

Walking through the nine cleaned records in order: record 1 creates the entry for ("Ashoka", "100M Race") with value 10. Record 2 has the same key with the same value 10 — it falls through all three conditions harmlessly, effectively discarded as a true duplicate. Records 3 and 5 do the same for ("Gupta", "Long Jump"), both value 8. Record 4 creates ("Maurya", "Relay") with value None. Record 6 creates ("Chola", "Shot Put") with value 5. Record 7 creates ("Ashoka", "High Jump") with value 7. Record 8 matches the existing ("Maurya", "Relay") key, finds it currently None, and fills it with 10 — the gap is closed using real data, not a guess. Record 9 matches ("Chola", "Shot Put"), finds an existing value of 5, and its own value is 6 — different from 5 — so it prints CONFLICT at ('Chola', 'Shot Put'): 5 vs 6 and leaves the original 5 in place, waiting for a teacher to check the paper scoresheet.

Finally, load the totals:

totals = {}
for (house, event), points in master.items():
    if points is None:
        continue  # still unresolved - do not silently count it as zero
    totals[house] = totals.get(house, 0) + points

print(totals)
# {'Ashoka': 17, 'Gupta': 8, 'Maurya': 10, 'Chola': 5}

Add it up by hand to check the code: Ashoka has 10 (100M Race) + 7 (High Jump) = 17. Gupta has 8 (Long Jump). Maurya has 10 (Relay, filled in from the duplicate). Chola has 5 (Shot Put) — the flagged value, correctly still counted while it awaits confirmation, since 5 is our best current evidence, not a blank. This is why the master scoreboard above marks Chola's total with an asterisk: the number is usable, but it is not yet fully trusted the way the other three are.

Misconception: "Cleaning Data Means Deleting Anything Odd"

A very common misconception — one that produces quietly wrong results — is treating "data cleaning" as "delete every row that looks strange": blank cells, duplicates, mismatched values, all thrown out. Our pipeline shows exactly why this is wrong. If we had deleted every row with a blank points field, Maurya would have lost its Relay result entirely and finished with 0 points instead of 10 — a real house robbed of a real medal because of a paperwork delay, not a data entry error. If we had deleted every row involved in a conflict, Chola would have lost its Shot Put result too, even though we have strong evidence (5 points, reported by the house's own captain) that the event did happen and did score something. Deleting is the easiest fix to code, which is exactly why it is dangerous: it hides the fact that information is missing behind a scoreboard that looks complete. Correct data cleaning treats every irregularity as a question to be answered — filled where enough evidence exists, flagged where it doesn't — never as an excuse to make the row disappear.

A Pipeline Runs Again and Again

It is tempting to think of this as a one-off script you run once, right after Sports Day, and forget. Real pipelines almost never work that way. A school's admission portal extracts new applicant forms every day during the admission window, transforms every batch through the same normalisation and duplicate checks, and loads the results into the same master applicant table — every single day, using the exact same code. A railway reservation system extracts new bookings continuously, transforms them, and loads seat-availability updates in a repeating cycle measured in seconds, not days. This is why keeping Extract, Transform, and Load as three separate, named stages matters even in a small example like ours: the same three functions we wrote for one Sports Day can be re-run, unchanged, the next time nine more messy rows come in from three different captains next year.

Test Yourself

Q1. A fifth house, Chandragupta, submits a row: {"house": " chandragupta", "event": "Kabaddi", "points": "12"}. After running it through clean_record(), what exact dictionary is produced?
Answer: {"house": "Chandragupta", "event": "Kabaddi", "points": 12} — the leading space is stripped, .title() capitalises the C, and "12" becomes the integer 12.

Q2. Suppose row 9 had instead reported "6" for the same key as row 6's "5", but row 6 arrived after row 9 in the list, not before. Would the printed conflict message change?
Answer: The values reported (5 vs 6) would still be flagged, but the message would read CONFLICT at ('Chola', 'Shot Put'): 6 vs 5, and the value kept in master would be 6, not 5 — because the code always keeps whichever value arrived first. This shows why order can silently change a flagged-but-kept value, which is exactly why a genuine conflict needs a human to resolve it, not just a flag.

Q3. Debug this. A classmate writes this line to compute totals and it crashes:

totals[house] = totals[house] + points

What is wrong, and how does the working version in this chapter avoid it?
Answer: On a house's first event, totals[house] doesn't exist yet, so Python raises a KeyError instead of treating it as zero. The chapter's version uses totals.get(house, 0) + points, which safely returns 0 the first time a house is seen instead of crashing.

Q4. Why must Extract always happen before Transform, and Transform always before Load — could a pipeline ever skip Extract and transform data "in place" inside each captain's original sheet?
Answer: Transforming in place would mean fixing three different documents with three different sets of rules, and there would be no single combined view to check for duplicates or conflicts across sheets — you can't notice that Ashoka's 100m Race appears twice until both copies are sitting in the same collection. Extract exists precisely to create that one shared collection before any fixing starts.

Summary

• A data pipeline moves data from its source to where it's trusted and used, through a repeatable sequence of steps — not a one-time fix.
• ETL names the three stages, always in this order: Extract (gather raw data as-is, from every source, without fixing anything yet), Transform (clean, normalise, and reshape it), and Load (write the trusted result to its final destination).
• Transform covers several distinct problems that each need their own fix: wrong data types (text "10" needs int() before arithmetic), inconsistent formatting (.strip().title() to make equivalent values compare equal), duplicate records (same key, same value — merge), missing values (represent honestly as None, fill from later evidence when it exists), and conflicting values (same key, different values — flag for a human, never silently pick one).
• Deleting every irregular row is not data cleaning — it is data loss disguised as a clean-looking result. Good cleaning fills what can be confidently filled and flags what can't.
• Because pipelines are repeatable code, not one-off manual edits, the same Extract/Transform/Load functions keep working correctly the next time new, equally messy data arrives.

Think About It

Think about this: How would you explain data pipelines: etl & data cleaning to a friend who has never seen a computer? What real-world analogy would you use? Imagine you had to build a system using these concepts — what would be your first step? Try this: before moving on, write down three things you learned and one question you still have.

Practice Exercises

Now it is time to practice! Complete these challenges to solidify your understanding:

• Exercise 1: Write a short program that demonstrates the core concept from this chapter. Test it with at least 3 different inputs.
• Exercise 2: Find a real-world example where data pipelines: etl & data cleaning is used in an Indian company (like TCS, Infosys, Flipkart, or ISRO). Write a paragraph explaining the connection.
• Exercise 3: Create a mind-map connecting data pipelines: etl & data cleaning to at least 3 other topics you have studied.