Most tutorials show you pandas in a notebook. Real data work is a scheduled job with three sources, and one of them is broken, and nobody is watching when it runs.
That gap is why I built etl-data-pipeline: a small, composable extract-transform-load framework in Python with a full test suite and CI. It ingests deliberately messy sample data, stray whitespace, inconsistent casing, a missing email, duplicate rows etc cleans it, loads it into SQLite, and reads the table back to prove the roundtrip. Twenty-one tests. You can run the whole demo in under a minute:
pip install -r requirements.txt
python demo.py
pytest
It is not Airflow. It is not dbt. It is a few hundred lines that do one job, and building it taught me four things that notebooks never will.
Lesson 1: errors are data, not exceptions
My first version raised on the first bad row. Then I imagined the scheduled-job reality: one dead API source at 3am, an exception halfway through the run, and half the data loaded with no record of what survived. So the extractors and loaders in this framework never raise. Every stage returns a result object with an errors list, and the pipeline produces a structured run report: per-stage record counts, successes, errors, timing.
A full report at the end of a broken run beats a stack trace in the middle of it. Errors became rows in the report, and the report became the thing I actually read.
Lesson 2: schema mapping is a whitelist, not a copy
Upstream APIs grow fields without warning, and "just copy everything" is how an unreviewed column sneaks into your database. The FieldMapper stage only copies fields you explicitly mapped and deliberately drops everything else. Mapping became a security decision, not just a renaming convenience.
Lesson 3: SQL identifiers cannot be parameterised
Values in SQLite can be bound as parameters. Table and column names cannot . They have to be interpolated into the DDL string. So the loader validates the table name and quotes identifiers, while all values stay parameterised. There is an injection test in the suite that documents exactly this edge, because it is the one place a "just use parameterised queries" rule quietly fails.
If you learn one paranoid habit from this post: decide, per identifier, whether it comes from your code (fine) or from anywhere else (validate it first).
Lesson 4: tests never touch the network
The APIExtractor tests mock requests.get. Everything else runs against pytest's tmp_path. That decision, more than any cleverness, is why the suite runs in seconds and never flakes. It also forced the code into honest seams, if you cannot mock the boundary, the boundary is in the wrong place.
The composable part
Every stage is one abstract method. Subclass Extractor, Transformer, or Loader, implement the one method, and the pipeline treats it like any other stage. I added an extractor for a new JSON response shape by writing a few lines, not a new framework.
That is the quiet punchline for other career-changers like me (I wrote this while finishing a Computing and IT degree after 13 years in operations): the big orchestration tools are composable stages with run reports and whitelists underneath. Building the small version once teaches you what the big version is actually doing. Now when a job description says "experience with ETL tooling", I can say what I did, show the code, and point at the tests.
Try it
The repo is public: github.com/Reaver1000/etl-data-pipeline. Fork it, break it, extend it. The design notes in the README cover the extension points, and the CI badge is green.
I am a junior developer looking for my first software engineering title. If your team does data work in Python and this style of thinking fits, everything about me is at reaver1000.github.io.
Top comments (0)