Python ETL Fundamentals

Lesson 01 · Data Engineering with Python · ~12 min read

1. What "ETL" really means

ETL stands for Extract, Transform, Load — the three verbs that describe every data pipeline ever written. You pull data from somewhere (extract), reshape it into the form you need (transform), and put it somewhere useful (load). That's it. Every technology, every framework, every cloud service in this course is just an opinion about how to do those three things more reliably, at larger scale, or with less human intervention.

The term comes from the data warehousing world of the 1990s, when companies started pulling data out of operational relational databases (OLTP systems) and loading it into separate analytical databases (OLAP or "data warehouse" systems). The core insight was that the format optimised for writing records fast (OLTP, row-oriented) is not the same format as the one optimised for scanning billions of rows with aggregations (OLAP, columnar). You needed a middle step — the transform — to bridge them.

In the 2010s, cloud object storage (S3, GCS) became cheap enough that companies started loading raw data first and transforming it later, inside the warehouse itself. This is ELT — same letters, different order. When the warehouse compute is cheap (pay-per-query Snowflake or BigQuery), ELT often wins: raw data is preserved, transformations are versioned SQL, and you can re-transform historical data whenever the business logic changes. We do ETL today because we haven't introduced the warehouse yet; Module 3 switches to ELT when we bring in dbt.

2. gharchive.org as our running dataset

Every lesson in Module 1 uses the same dataset: gharchive.org, a public archive of every GitHub event since 2011. GitHub exposes a streaming event API; gharchive captures it every hour and publishes the dump as a gzipped JSON-lines file. No authentication, no quotas, terabytes available — and genuinely messy in the ways real data is messy.

URL scheme: https://data.gharchive.org/YYYY-MM-DD-H.json.gz — for example, the hour starting at 15:00 UTC on 2024-01-01 is https://data.gharchive.org/2024-01-01-15.json.gz. Each file is roughly 50–200 MB compressed; a full day is roughly 5 GB. Busy hours (US morning, new release announcements) run larger; quiet hours (early morning UTC, weekends) run smaller.

Format: JSON-Lines (also called NDJSON) wrapped in gzip. Every line is one independent JSON object — one GitHub event. The top-level keys every event has are: id, type, actor, repo, created_at, and payload. The payload key is wildly variable — a PushEvent payload includes commit SHAs, author names, and diff sizes; a WatchEvent payload is nearly empty. This schema drift is intentional: Lesson 03 will teach you to validate it.

Figure 1 — The three ETL phases of Lesson 01's pipeline.

3. The 50-line script, narrated

The pipeline has four pieces: fetch_hour, count_by_type, write_csv, and the __main__ glue. Here's what each does well — and what it leaves undone.

fetch_hour — extract

def fetch_hour(hour: str, out_dir: pathlib.Path) -> pathlib.Path:
    out_dir.mkdir(parents=True, exist_ok=True)
    dest = out_dir / f"{hour}.json.gz"
    if dest.exists():
        return dest
    resp = requests.get(URL.format(hour=hour), timeout=30)
    resp.raise_for_status()
    dest.write_bytes(resp.content)
    return dest

Done well: the timeout=30 argument to requests.get means a stalled CDN connection will raise a requests.exceptions.Timeout after 30 seconds instead of hanging forever. The raise_for_status() call converts HTTP 4xx/5xx responses into Python exceptions immediately — you won't silently write a 404 HTML page into your data directory. The existence check (if dest.exists(): return dest) makes repeated runs free: the second run of the same hour takes milliseconds.

Left undone: there are no retries. If the CDN drops the connection at byte 40 000 000, the function raises an exception and leaves a corrupt partial file. Lesson 06 will add a retry budget and atomic writes (write to a temp file, rename only on success).

count_by_type — transform

def count_by_type(path: pathlib.Path) -> Counter:
    counts = Counter()
    with gzip.open(path, "rt") as f:
        for line in f:
            ev = json.loads(line)
            counts[ev["type"]] += 1
    return counts

Done well: Counter is not just a dict — it initialises missing keys to zero, it supports .most_common(n), and two Counters can be merged with += or Counter.update(). That last property is what Homework Task 1 exploits. The for line in f loop is a streaming reader: Python decompresses and decodes one line at a time, keeping memory usage bounded regardless of file size.

Left undone: there is no schema validation. If a line is malformed JSON, json.loads raises JSONDecodeError and the whole run aborts. If GitHub quietly drops the type key from some future event (this has happened), you get a KeyError. Lesson 03 wraps this with Great Expectations.

write_csv — load

def write_csv(counts: Counter, path: pathlib.Path) -> None:
    with path.open("w", newline="") as f:
        w = csv.writer(f)
        w.writerow(["type", "count"])
        for evtype, n in counts.most_common():
            w.writerow([evtype, n])

Done well: newline="" is required when using the csv module on Windows to prevent double newlines. Sorted by .most_common() (descending count) makes the output human-readable.

Left undone: the write is not atomic. If the process is killed mid-write, the downstream reader gets a truncated CSV — and has no way to detect the truncation. Lesson 06 fixes this with a tmp → rename pattern.

The __main__ glue

if __name__ == "__main__":
    import sys
    hour = sys.argv[1]
    raw = fetch_hour(hour, pathlib.Path("data/raw"))
    counts = count_by_type(raw)
    write_csv(counts, pathlib.Path(f"data/out/{hour}.csv"))
    print(f"wrote {sum(counts.values())} events across {len(counts)} types")

The if __name__ == "__main__" guard ensures the three functions are importable as a library without triggering a network download. This matters: Homework Task 1 imports fetch_hour and count_by_type from extract.gharchive and transform.events without wanting to download anything at import time.

4. Idempotency, the first time

Our cache-by-filename trick is accidentally idempotent — run the script three times for the same hour and the download only happens once, the CSV is overwritten with the same content each time, and downstream consumers see a consistent file. Congratulations, you've implemented a basic form of idempotency without trying.

But it's fragile. If the script is interrupted after the CSV is partially written, the next run will overwrite the corrupt file — but only because open(path, "w") truncates on open. If two parallel runs write the same file simultaneously, you get a race condition. Real idempotency means the output can be rewritten without affecting downstream consumers mid-flight. That's Lesson 06's whole topic: atomic writes, idempotent upserts, and what happens when Airflow retries a task that already half-succeeded.

5. Why CSV is wrong (preview of Lesson 02)

We're using CSV today because everyone knows what a CSV is. But it's the wrong format for data engineering, and not just because it's slower than Parquet. CSV has no types — every value is a string, so the number 45212 and the string "45212" are indistinguishable. CSV has no schema — column meaning is positional, so adding a column in the middle of the file silently breaks every downstream reader that expects column 3 to be count. CSV reads row-by-row — but analytics almost never needs all columns; a query that counts PushEvents still scans all the actor and repo data you don't need. And CSV has no statistics — the query engine has to scan every row to answer every question.

Parquet solves all five of these. Lesson 02 replaces write_csv with write_parquet and shows you the numbers.

6. Common pitfalls

References

• gharchive.org — the dataset this course is built on.
• GitHub Webhook Events and Payloads — the full schema reference for every event type in the archive.
• Python requests quickstart — the HTTP client used in fetch_hour.
• Python gzip stdlib docs — the standard-library module used to stream-decompress the archive files.