Pipelines: ETL vs ELT, Parquet & Idempotency

A notebook is great for exploring and terrible for production: cells can run out of order, state hides in memory, and "it worked yesterday" is not a guarantee. A pipeline is the same logic split into small, named stages with clear inputs and outputs:

profile.py → validate.py → clean.py → store.py, orchestrated by run_pipeline.py.

Each stage reads files, does one job, writes files and logs what it did (row counts, durations). That gives you three things a notebook can't: you can rerun one stage without the others, you can test each stage, and a scheduler like Airflow can retry the stage that failed instead of everything.

Modern analytics leans towards ELT because storage is cheap and engines like DuckDB, BigQuery or Snowflake are fast: land the raw data once, then build cleaned tables with SQL that can be changed and rerun at any time. Our course pipeline is a hybrid — Python cleaning, then DuckDB for modelling and queries.

CSV is row-oriented text: every query has to read and parse every byte of every column, and the types are guessed each time ("0012" becomes 12, dates become strings).

Parquet is a binary, columnar format:

• Column pruning — values of each column are stored together, so SELECT AVG(price_ugx) reads only that column. Reading 2 of 10 columns reads ~20% of the data.
• Compression that works — a column of market names repeats the same few strings, so dictionary + run-length encoding shrinks it dramatically.
• Typed schema — integers stay integers, dates stay dates.
• Row-group statistics — each block stores min/max per column, so a filter like date >= '2025-03-01' can skip whole blocks (predicate pushdown).

Bytes scanned ≈ CSV size × (columns read ÷ total columns) ÷ compression ratio.

A stage is idempotent if running it once or ten times on the same input leaves the same result: f(f(x)) = f(x). This matters because schedulers will retry failed tasks and run backfills.

The classic violation is append: each retry inserts the batch again, so a table with 10,000 rows and a 1,500-row batch has 14,500 rows after three runs instead of 11,500.

Two standard fixes:

• Files: write to a temporary path and atomically swap it in — os.replace("out.parquet.tmp", "out.parquet"). Readers see either the old complete file or the new complete one, never half a file. Or overwrite the whole partition for that date.
• Tables: enforce a primary key on the business key and upsert (INSERT OR REPLACE, ON CONFLICT DO UPDATE), or DELETE WHERE date = :d then INSERT, inside one transaction.