Open city data pipeline: from raw 311 requests to a public dashboard

Maple & Dune has been asked by a mid-sized city council to show how quickly it closes residents' service requests: potholes, missed bin collections, broken streetlights, graffiti and noise complaints. The city publishes these requests as open data, but the files are messy. Column names change between years, dates come in two formats, about 8% of rows have no ward, and some requests are duplicated when a resident reports the same pothole twice.

Today a council analyst downloads the file each month, fixes it by hand in a spreadsheet and pastes charts into a slide. It takes two days and nobody can repeat it exactly.

You will build the pipeline that replaces this, using a real public dataset: Toronto's 311 service requests from the City of Toronto open-data portal, or a similar dataset from Dubai Pulse if you prefer. The pipeline will: - download the raw files on a schedule and keep them unchanged; - clean and standardise them with tests that fail on bad data; - load them into a small warehouse (DuckDB) as a fact table and dimension tables; - produce a dashboard showing requests per week, median days to close by service type and ward, and the 10 slowest service types.

The council's target is clear: the dashboard must refresh with one command in under 10 minutes, and every number on it must be reproducible from the raw files. Done looks like a repo with the pipeline, data tests, the warehouse model, the dashboard and a short data dictionary the council analyst can use.