Principles of an ETL Pipeline

Every Monday, an analyst downloads three exports, cleans them in a spreadsheet and pastes them into a tracking file. One copy-paste mistake, and the executive committee receives wrong figures. An ETL pipeline automates this work in a reliable, reproducible way.

The three steps

• Extract: read the data where it lives (CSV files, API, production database) without modifying it.
• Transform: clean, type, normalize, join, aggregate, compute indicators.
• Load: write the result to the destination (data warehouse, analytical database, file).

ETL or ELT?

In ETL, you transform before loading. In ELT, you first load the raw data into the warehouse, then transform it in SQL directly inside the warehouse. ELT has become widespread with powerful cloud warehouses and tools like dbt: you keep the raw history and can replay the transformations. The principles in this course hold for both: only the steps change place.

Splitting into functions

A readable pipeline is made of small single-responsibility functions:

import io
import pandas as pd

def extraire(texte_csv):
    # reads the source without modifying anything
    return pd.read_csv(io.StringIO(texte_csv))

def transformer(df):
    # pure function: does not modify its input, returns a new DataFrame
    sortie = df.copy()
    sortie["client"] = sortie["client"].str.strip().str.lower()
    sortie = sortie[sortie["montant"] > 0]
    return sortie.groupby("client", as_index=False)["montant"].sum()

def charger(df, destination):
    # fully replaces the contents of the destination
    destination.clear()
    destination.extend(df.to_dict("records"))

source = "client,montant\n Alice ,10\nbob,5\nalice,7\nbob,-2\n"
entrepot = []                       # a list plays the role of the destination here
charger(transformer(extraire(source)), entrepot)
print(entrepot)   # [{'client': 'alice', 'montant': 17}, {'client': 'bob', 'montant': 5}]

A pure function always returns the same result for the same input and changes nothing around it. You can test it with a small DataFrame, with no database or network.

Idempotency

A pipeline will be rerun: after an outage, a fix, a backfill. It must be idempotent: rerunning it on the same data produces the same final state, with no duplicates.

charger(transformer(extraire(source)), entrepot)   # second run
print(len(entrepot))                               # still 2 rows: idempotent

If charger simply appended (extend without clear), the second run would double the data: this is one of the most frequent bugs in production. Idempotent strategies are replacement (of a table or a partition, for example one day) and upsert (insert or update based on a key), covered in lesson 5.

Parameterize rather than hard-code

The processed date, the source path or the connection are parameters: executer_pipeline(date="2026-11-09", source=..., destination=...). You can then replay any day and test with fake data.

Key takeaways

• Extract without modifying, transform with pure functions, load idempotently.
• ETL and ELT differ in where the transformation happens.
• Explicit parameters; test that running twice changes nothing.