Goal: To turn Boston’s public 311 service request data into a clean, queryable warehouse that supports storyfinding, accountability journalism, and civic insight.
This project ingests 311 data from the City of Boston’s open data portal, normalizes records across the city’s vendor transition, and loads everything into a Supabase warehouse for analysis and dashboard.
It’s built for newsrooms, civic analysts, and curious residents who want to ask questions like:
For historical CSV exports from the pre-transition period, use pipeline/backfill_historical.py. It processes files in 1000-row batches and resumes from a checkpoint if interrupted.
The project relies on a single public dataset maintained by the City of Boston through Analyze Boston:
During the current vendor transition, the city maintains two distinct resource endpoints under the same dataset umbrella:
| Resource | Status |
|---|---|
Legacy 311 system (1a0b420d-...) |
Historical data through mid-2026 |
New 311 system (254adca6-...) |
Live data as services migrate |
The pipeline ingests from both endpoints and merges them into a single, unified warehouse so that users see a seamless historical view regardless of which vendor generated each ticket.
A Python ETL pipeline pulls from both CKAN endpoints, applies a normalization layer, and loads the results into a single warehouse. The warehouse is built around three core principles:
case_topic and case_status are normalized so that users never need to know which system generated a record.subject → reason → case_title → queue) is preserved through hierarchical category mapping.source_system field, and raw payloads are retained so any transformation decision can be traced back to the original data.Different services migrated from legacy to new systems on different dates, creating a fragmented mix of two taxonomies in the live API.
Solution: The pipeline iterates over two distinct resource IDs and tags every record with its source system, turning a messy data problem into a clean, endpoint-driven architecture.
Initial assumptions about the legacy schema were wrong — what looked like a simple structure turned out to be a 4‑level hierarchy.
Solution: We redesigned the lookup table to store hierarchical categories, allowing users to query at any level of granularity (from broad department down to specific queue).
The same department appears under slightly different names (e.g., “Public Works Department” and “Public Works Department (PWD)”).
Solution: We documented the issue and designed a normalization layer to consolidate known variants into canonical names.
MIT License