ETL Pipelines: Extract, Transform, Load Data Efficiently

A scoreboard that refuses to add up

Suppose you are asked to build one combined stats page for a domestic cricket season. You are handed three sources: a stadium's own scoring software exports match summaries as CSV files, an app that streams ball-by-ball commentary saves each delivery as a row in a database, and a third venue still sends you scorecards typed into a spreadsheet by hand. Every source disagrees with the others in small, silent ways. One file spells the same team "RCB" and another spells it "Royal Challengers Bengaluru". One system logs extras as a single "extras" column; another splits them into "wides", "no-balls" and "byes". And every single source records the number of overs bowled as a number like 18.4 — which looks like a decimal, reads like a decimal, and is not a decimal at all.

If you write one Python script that reads all three files, glues them together, and prints a report, you have solved today's problem. But tomorrow there is a new match, and the day after that, another. You do not want to hand-edit a script every single night for the rest of the season. You want a pipeline: something that runs on a schedule, pulls in whatever is new, cleans it the same way every time, and deposits it into one warehouse table that every dashboard reads from. That pipeline has a name, and it is the subject of this chapter: ETL — Extract, Transform, Load.

What ETL actually means

ETL is not a library or a specific piece of software — it is an architecture, a pattern for how you move data from where it is produced to where it is analysed. It has exactly three stages, always in this order:

• Extract — read data out of one or more source systems (databases, CSV exports, APIs, log files) without altering the sources.
• Transform — clean, standardise, validate, and reshape that data so every source ends up meaning the same thing the same way.
• Load — write the transformed data into a destination — usually a data warehouse — in a way that is safe to repeat.

The word that separates an ETL pipeline from a one-off script is repeatable. A script you run once by hand is not a pipeline. A pipeline is triggered automatically — nightly, hourly, or after every match — and it must produce a correct warehouse whether it runs once or is accidentally triggered five times in a row. Keep that requirement in mind; it drives almost every design decision in this chapter, and it is exactly what the loop in the diagram below represents.

The pipeline, visualised

Notice what the dashed loop connects: it runs from the Warehouse back to the Source systems, not from Extract straight to Load. That is deliberate. Every night, the pipeline starts over from the very beginning — Extract, then Transform, then Load, in that order, every time. There is no shortcut that lets fresh data skip the Transform stage, because unstandardised overs, mismatched team names, and duplicate deliveries would flow straight into the warehouse and quietly corrupt every report built on top of it.

Extract: reading data without missing or duplicating rows

The naive way to extract is a full extraction: every run, read every row from every source, from the beginning of time. This works on day one. It stops working the moment your source has millions of rows, because you would be re-reading last season's matches every single night for no reason.

The fix is incremental extraction using a watermark — a column that only ever increases, such as an auto-incrementing ID or a strictly increasing "last updated" timestamp. Each run remembers the highest watermark value it saw last time, and only asks for rows newer than that:

SELECT match_id, team, overs, runs, wickets, updated_at
FROM raw_scorecards
WHERE updated_at > '2026-08-13 23:00:00'   -- last run's watermark
ORDER BY updated_at ASC;

One subtlety worth getting right: the watermark must be a value the data itself guarantees is increasing — never "yesterday" computed from the clock on the machine running the pipeline. A server in a different time zone, a daylight-saving shift, or a job that starts late will silently make "yesterday" mean the wrong window and rows will go missing. The watermark should always come from the last value actually extracted, stored by the pipeline itself, not recomputed from wall-clock time.

Transform: the stage where pipelines quietly break

Transform is where most real-world ETL bugs live, because it is the only stage that has to understand what the data means, not just move it around. Four jobs happen here, and a production pipeline usually does all four:

• Cleaning — handling missing values, fixing types (a "runs" column that arrives as text), rejecting rows that fail basic sanity checks.
• Standardising — making sure "RCB" and "Royal Challengers Bengaluru" become one agreed name, and that units mean the same thing everywhere.
• Deduplicating — the same delivery or match summary can arrive twice if a source re-sends data or a network retry fires.
• Enriching / aggregating — joining in reference data (venue, format) and computing derived numbers such as run rate.

Let's make "standardising units" concrete, because it is where the misconception from the opening example lives.

Common misconception: "18.4 overs" is the decimal number 18.4. It is not. Cricket overs are recorded as completed overs dot balls into the current over, and one over is 6 balls — so the digit after the point runs from 0 to 5, never higher, and it does not mean tenths. 18.4 overs means 18 full overs plus 4 balls: 18 × 6 + 4 = 112 balls, which in true decimal-over terms is 112 / 6 = 18.667 overs — not 18.4. If a transform step treats the raw number as a real decimal, every run rate computed from it is wrong.

def overs_to_balls(overs_notation):
    completed = int(overs_notation)
    balls_this_over = round((overs_notation - completed) * 10)
    return completed * 6 + balls_this_over

def run_rate(runs, overs_notation):
    balls = overs_to_balls(overs_notation)
    return round(runs * 6 / balls, 2)

print(overs_to_balls(18.4))     # 18*6 + 4  -> 112
print(run_rate(145, 18.4))      # 145*6/112 -> 7.77

Trace it: int(18.4) = 18, and (18.4 - 18) * 10 = 4.0, rounded is 4, so overs_to_balls returns 18*6+4 = 112. Then run_rate(145, 18.4) computes 145*6 = 870, and 870 / 112 = 7.767..., which rounds to 7.77. Compare that with the naive, wrong approach of dividing runs directly by the raw number: 145 / 18.4 = 7.88. The two answers disagree in the second decimal place — small enough to look plausible, large enough to rank two teams in the wrong order on a leaderboard. That is exactly the kind of bug that survives testing (the code runs, it returns a number) but fails correctness (the number is wrong), and it is why the Transform stage, not just "does the code run", is where an ETL engineer has to reason about what the raw values actually mean.

Deduplication deserves its own worked comparison, because the naive approach and the correct approach differ not just in code length but in how they scale:

def dedupe_naive(rows):
    unique = []
    for r in rows:
        if r not in unique:      # scans the whole 'unique' list each time
            unique.append(r)
    return unique

def dedupe_hashed(rows):
    seen = set()
    unique = []
    for r in rows:
        key = (r['match_id'], r['ball_id'])
        if key not in seen:      # average O(1) set lookup
            seen.add(key)
            unique.append(r)
    return unique

dedupe_naive checks each new row against every row already kept, so for n rows it does roughly n²/2 comparisons in the worst case — O(n²). dedupe_hashed uses a hash set, where membership testing takes constant time on average, so the whole pass is O(n). The gap is not academic: for a season with n = 100,000 ball-by-ball rows, the naive version does on the order of 5 × 10⁹ comparisons, while the hashed version does on the order of 10⁵ lookups — five orders of magnitude apart. That is the difference between a Transform step that finishes comfortably inside a nightly batch window and one that does not finish before the next match starts.

Load: writing data in without corrupting what is already there

The simplest load strategy, append-only, just inserts every transformed row as a new row. It is fast, but it is unsafe for a repeatable pipeline: if last night's job crashed halfway and gets re-run from the start, append-only loading will insert the successfully-loaded rows a second time, and the warehouse now has duplicates that Transform already tried to remove.

The fix is to make Load an upsert (update-or-insert), keyed on a stable identifier such as match_id, using SQL's MERGE statement:

MERGE INTO warehouse.match_summary AS target
USING staging.match_summary_new AS source
ON target.match_id = source.match_id
WHEN MATCHED THEN
  UPDATE SET runs = source.runs, wickets = source.wickets, run_rate = source.run_rate
WHEN NOT MATCHED THEN
  INSERT (match_id, runs, wickets, run_rate)
  VALUES (source.match_id, source.runs, source.wickets, source.run_rate);

This gives the pipeline the property it needs to be safely repeatable: idempotency. A step (or a whole pipeline) is idempotent if running it twice on the same input leaves the system in exactly the same state as running it once. With an upsert keyed on match_id, re-running last night's load a second time overwrites each row with the identical values it already had — no duplicates, no drift. This is precisely the property the looped arrow in the diagram is testing: the pipeline can be triggered again from Source all the way through to Load, and the warehouse still ends up correct.

Throughput: why the slowest stage sets the pace

A pipeline is a chain, and like any chain of stages passing work along, its overall speed is limited by its slowest link, not its fastest. If Extract can read 5,000 rows/second from the source database, Transform (doing the overs conversion, dedup, and enrichment) can only process 800 rows/second, and Load can write 1,200 rows/second, the pipeline's sustained throughput is:

throughput = min(5000, 800, 1200) = 800 rows/second

Transform is the bottleneck. To process a full domestic season's ball-by-ball data — say N = 5,000,000 rows — the pipeline needs approximately:

time = N / throughput = 5,000,000 / 800 = 6,250 seconds

Convert that to something you can reason about against a batch window: 6,250 / 60 ≈ 104.2 minutes, or about 1.74 hours. If the pipeline is triggered at 2 AM after the last match ends and must finish before a 6 AM deadline (a 4-hour window), 1.74 hours comfortably fits. But this is exactly why data engineers watch the bottleneck stage as data volume grows: if next season's dataset doubles to 10,000,000 rows, the same 800 rows/second Transform stage now needs 10,000,000 / 800 = 12,500 seconds, about 3.47 hours — still inside the window, but with far less margin. The fix is never to speed up Extract or Load (they were never the constraint); it is to speed up Transform specifically, for example by running the cleaning logic on multiple chunks of rows in parallel.

ETL vs ELT: transforming before or after loading

Everything above assumes Transform happens before Load — the classic order the acronym describes. Modern cloud data warehouses (products such as Snowflake, BigQuery, and Redshift are common examples) can also run the stages as ELT: Extract the raw data, Load it into the warehouse essentially unchanged, and only then run Transform as SQL queries inside the warehouse itself. The appeal is that the warehouse's own compute — which can be scaled up on demand — does the heavy lifting instead of a separate transform server, and the untouched raw data stays available if a transformation rule turns out to be wrong and needs to be redone from scratch. The three responsibilities — extract, transform, load — do not disappear in ELT; only their order changes, and the correctness requirements from this chapter (a durable watermark for extraction, correct handling of source semantics like the overs format during transformation, idempotent writes) apply exactly the same way regardless of which order you pick.

Where this fits in your exam preparation

Be precise about this rather than vague: IIT-JEE and BITSAT test Physics, Chemistry, and Mathematics (plus logical reasoning for BITSAT) — ETL pipelines will not appear on either paper, and no fair mapping should pretend otherwise. Where this topic is directly examinable is CBSE itself: the database and data-handling units in CBSE's Artificial Intelligence and Information Technology skill subjects at the Class 9–10 level build exactly the vocabulary this chapter uses — reading records from a source, validating and cleaning them, writing them to a table — and the SQL you meet formally in the Class 11–12 Computer Science "Database Query using SQL" unit is the same SQL used in the Extract and Load examples above. Longer term, database management (including the query design, normalisation, and data-integrity ideas behind this chapter) is one of the core sections of GATE's Computer Science paper — the qualifying exam for M.Tech/PhD admission and PSU (public-sector undertaking) recruitment. It is not a general requirement for private-sector hiring, but if a research degree or a PSU technical role is on your radar, this is genuine groundwork for that specific paper.

Active recall

1. A source table has a column last_modified that is set by each application server's local clock, and those servers are not perfectly synchronised. Explain concretely why using last_modified as a watermark for incremental extraction can cause rows to be silently skipped, and what property a safer watermark column would need instead.
2. A match scorecard shows overs = 42.7. Explain why this value is invalid on its own terms (not just "looks odd") — what does the digit after the point mean in overs notation, and what range must it fall in?
3. Convert overs = 33.5 to balls bowled, then compute the run rate for a team that scored 210 runs in those overs. Show both steps.
4. A pipeline's Extract stage runs at 4,000 rows/second, Transform at 4,500 rows/second, and Load at 600 rows/second. What is the pipeline's sustained throughput, and which stage would you optimise first if you needed to process 3,000,000 rows inside a 90-minute window? Show the time calculation that justifies your answer.
5. A teammate writes the Load stage as a plain INSERT (append-only) rather than a MERGE/upsert, arguing "it's simpler and the pipeline only runs once a night anyway." Using the term idempotent, explain the specific failure scenario where this design produces a corrupted warehouse table, even though the pipeline genuinely does only run once a night under normal conditions.

Summary

ETL is the standard architecture for moving data from scattered, inconsistent sources into one warehouse that dashboards and analysis can trust: Extract reads new data using a durable watermark rather than full re-reads or wall-clock guesses; Transform is where correctness actually gets earned — cleaning, standardising units and vocabulary, deduplicating with a hash-based approach rather than a quadratic one, and computing derived fields correctly; and Load must be idempotent, typically via an upsert keyed on a stable identifier, so that re-running the pipeline after a failure repairs the warehouse instead of duplicating it. A pipeline's overall speed is bounded by its slowest stage, which is where you should focus any optimisation effort. And whether the heavy transformation logic runs before loading (ETL) or inside the warehouse after loading (ELT), the same three responsibilities and the same correctness requirements apply — only their order changes.

Think About It

Think about this: How would you explain etl pipelines: extract, transform, load data efficiently 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.