A small, reproducible, tested ETL + analysis pipeline that ingests the City of Melbourne's Pedestrian Counting System open data, cleans and validates it, loads it into DuckDB, and answers a real question with three clear charts.
The question this answers:
When and where is Melbourne's CBD busiest on foot, and how does the rhythm differ between weekdays and weekends?
- A real ETL pipeline, not a script dump: four cleanly separated stages (
ingest → transform → load → analyze) with a single reproducible entry point (make pipeline). - The cleaning/aggregation logic is the tested core. Pure, side-effect-free functions in
transform.pywith 43 passing tests that assert the data-quality contract exactly (nulls, negatives, duplicates, type coercion, feature derivation, aggregation correctness). - A hand-rolled data-quality gate (
validate.py) that fails the pipeline fast if an invariant is violated, the kind of guardrail that keeps a dataset trustworthy in production. - Runs fully offline. A clearly-labelled synthetic sample (in the dataset's true schema) is committed, so
git clone && make pipelinejust works with no keys and no network. A live Socrata fetch is available behind a flag. - Idiomatic, strictly-typed Python:
from __future__ import annotations, typed signatures, docstrings,ruff-clean,src/layout,pyproject.toml, a Makefile and CI on Python 3.11 + 3.12.
City of Melbourne: Pedestrian Counting System (Socrata resource b2ak-trbp). A network of automated sensors across the CBD records an hourly pedestrian count at each location. It is auth-free public data and one of Melbourne's most-loved open datasets.
| Column | Meaning |
|---|---|
Date_Time, Year, Month, Mdate, Day, Time |
Timestamp of the reading (and its parts) |
Sensor_ID, Sensor_Name |
The sensor / location |
Hourly_Counts |
Pedestrians counted in that hour |
⚠️ Sample data: the committeddata/raw/pedestrian_counts_sample.csvis a synthetic sample generated byingest.generate_sample()in the exact upstream schema, with realistic commuter/leisure/seasonal patterns and a few deliberate defects (sensor dropouts, a negative reading, a stray-whitespace label, a duplicate row) so the cleaning stage has genuine work to do. It is not real measured data. To run against the real API:python -m data_pipeline.pipeline --source remote.
flowchart LR
A["ingest.py<br/>sample or Socrata API"] --> B["transform.py<br/>clean + derive features"]
B --> C["validate.py<br/>data-quality gate"]
C --> D["load.py<br/>DuckDB warehouse<br/>+ Parquet"]
D --> E["analyze.py<br/>3 charts → reports/"]
subgraph tested["unit-tested core"]
B
C
end
data/raw/*.csv ──▶ clean DataFrame ──▶ ✓ validated ──▶ pedestrian.duckdb ──▶ reports/*.png
(transform.clean) (validate) (fact + aggregates) (analyze)
Each stage is an importable, independently-testable module; pipeline.py wires them together and exposes a CLI.
(from the committed 70-day sample: 20,119 cleaned hourly readings, ~10.4M pedestrians across 12 CBD sensors)
Weekdays show the classic commuter double-peak: a morning spike at 08:00 (~977/hr) and a larger evening surge at 17:00 (~1,321/hr). Weekends collapse that into a single, later early-afternoon hump peaking at 13:00 (~1,151/hr). At 03:00 the city is ~130× quieter than its weekday evening peak.
Volume is dominated by the major interchanges and the Bourke Street retail core. Flinders Street Station Underpass tops the list (~1.33M total), followed by Melbourne Central and Town Hall (West).
The heatmap makes the structure obvious: bright weekday commuting corridors (brightest Friday at 17:00) that dissolve into the broad, later weekend afternoons. Weekday mornings carry ~75% more foot-traffic than weekend mornings (695 vs 397/hr avg), while afternoons are near-identical; the commuter signal is almost entirely a morning-and-evening, Monday-to-Friday phenomenon.
| Decision | Why |
|---|---|
| pandas for transforms | Mature, expressive, and the lingua franca of data work; the cleaning logic reads like the spec it implements. |
| DuckDB as the load target | An embedded, zero-config analytical engine: columnar SQL over the cleaned data with no server to run. It also exports the clean fact table to Parquet natively (no pyarrow dependency). |
| Synthetic-but-realistic sample, committed | The repo must tell its story offline and in CI with no keys or network. The generator is seeded, so runs are byte-for-byte reproducible. |
| Validation returns a report and can raise | Callers can log every check; raise_for_status() turns hard failures into a non-zero exit so a bad dataset never flows downstream silently. |
Object-oriented Matplotlib (Figure/FigureCanvasAgg) |
Renders headlessly in CI without mutating any global backend, so importing the package never breaks a notebook's inline plotting. |
| Pure transform functions, no I/O | The core is trivial to unit-test; aggregations are cross-checked against independent groupby computations in the tests. |
data-pipeline/
├── src/data_pipeline/
│ ├── config.py # paths + the true upstream schema (single source of truth)
│ ├── ingest.py # synthetic sample generator + optional Socrata download
│ ├── transform.py # cleaning + aggregations <- the tested core
│ ├── validate.py # hand-rolled data-quality contract
│ ├── load.py # DuckDB warehouse (fact table, aggregates, views, Parquet)
│ ├── analyze.py # 3 charts -> reports/
│ └── pipeline.py # orchestration + CLI (`python -m data_pipeline.pipeline`)
├── tests/ # pytest: transform / validate / load / pipeline (43 tests)
├── notebooks/analysis.ipynb # executed walkthrough with embedded charts
├── data/raw/ # committed synthetic sample (offline)
├── reports/ # committed chart images
├── Makefile # make pipeline / test / lint / verify
└── .github/workflows/ci.yml
Requires Python 3.11+.
git clone https://github.com/millerharry/data-pipeline.git
cd data-pipeline
make pipeline # creates a venv, installs deps, runs ingest->transform->load->reportmake pipeline writes the DuckDB warehouse to data/processed/ and refreshes the charts in reports/. Other targets:
make install # set up the local virtualenv only
make test # run the test suite
make lint # ruff
make verify # lint + test + pipeline (what CI runs)
make notebook # launch the analysis notebook
make help # list all targetsPrefer to drive it directly?
python -m pip install -e ".[dev]"
python -m data_pipeline.pipeline # offline sample (default)
python -m data_pipeline.pipeline --source remote # live City of Melbourne APINo secrets are needed. See .env.example for the (optional) remote-source settings.
make test # or: pytestThe suite (43 tests, fully offline) covers:
- Cleaning contract: nulls / negatives / duplicates are dropped, timestamps parsed, sensor names trimmed, types coerced,
is_weekend/daypartderived, andclean()proven pure (input frame untouched). - Aggregations: shapes, ordering, and values cross-checked against independent
groupbycomputations; totals reconcile to the source sum. - Data-quality validation: passes on clean data; each invariant (negatives, duplicates, out-of-range hours, row-count floor, missing columns) is asserted to fail when violated.
- Load: DuckDB tables/views are created, row counts round-trip, and aggregate totals reconcile.
- End-to-end: the whole pipeline runs into a temp dir and produces all artefacts, deterministically.
ruff check . is clean and enforced in CI.
Everything is deterministic (seeded RNG), so a fresh make pipeline reproduces the committed charts exactly. To explore interactively, open notebooks/analysis.ipynb (already executed with embedded outputs); it reuses the same package functions, so the notebook and the pipeline can never drift apart.
I wanted a compact, honest showcase of the data-operations discipline I practised at Clutterbot, where I worked on real-world ML dataset workflows on AWS: collecting raw data, cleaning and validating it, loading it somewhere queryable, and turning it into something a human can act on, all reproducibly and with the cleaning logic under test so the dataset stays trustworthy as it grows.
This project distils that pattern down to its essence on a public, genuinely interesting Melbourne dataset: ingest → transform → validate → load → analyse, reproducible end-to-end, with the core logic covered by tests.
Maps to: my data-collection & ML-dataset work at Clutterbot.
MIT © 2026 Harry Miller. See LICENSE.