A civic-data app that shows its work
A Databricks Genie app over governed City of Chicago open data, built so a user can inspect the evidence behind every answer. It won Gold in the Databricks Genie-Powered App Challenge.
Author: data pipeline, Genie space, app and evaluation
The problem
City open-data portals hold valuable records, but access is not usability. A resident asking how 311 demand changed in their neighborhood should not first have to find the right Socrata dataset, decode its columns, learn Chicago's Community Area identifiers, pick a complete reporting period and write a defensible aggregation. Natural language removes that friction, but it adds a new failure mode: a fluent answer can quietly hide the wrong metric, an incomplete month, stale data or a query built at the wrong grain. In a civic context that is uncomfortable, so the product has to show its work.
What I did
- Built the full data pipeline: roughly fifteen notebooks covering Bronze ingestion, Silver transforms, Gold metrics, spatial enrichment, a semantic base and Unity Catalog Metric Views, with a daily ingest–transform–validate workflow.
- Configured the Databricks Genie space to reason over governed measures rather than raw operational columns on every question.
- Wrote the FastAPI boundary: normalisation, allowlisted parameterised query templates, server-side resource binding and a service-principal auth model.
- Built the React front end and its three surfaces (Ask, Neighborhood Pulse, Data Health), including provenance and missing-data states.
- Authored a 30-question Genie benchmark with verified SQL, and made it part of the maintenance loop.
Architecture
- A good Genie app starts below the chat box. Answers are grounded in Unity Catalog Metric Views that define measures such as total 311 requests, open building violations and month-over-month change, with governed dimensions for month and Community Area.
- Genie owns the open-ended natural-language conversation inside its governed space. Everything else (maps, comparisons, Data Health) uses allowlisted parameterised templates, so the browser can never submit arbitrary SQL or pick a different space, warehouse or job.
- The app binds a Genie space, a SQL Warehouse and the daily refresh job at deploy time and authenticates as a dedicated service principal, so no resource credential ever reaches browser code.
- Data Health separates source freshness from pipeline success: it reads real Jobs API run state for ingest, transform and validate rather than inferring health from MAX(_ingested_at).
- Two quiet display rules prevent large analytical errors: neighborhood metrics use only fully completed calendar months, and missing source coverage renders as N/A, never a misleading zero.
- A public snapshot runs on Appwrite Sites through a restricted Appwrite Function gateway, while the governed Databricks App stays the first-class target for authorised workspace users.
What went wrong first
A fluent answer is not a correct one
A polished demo question usually only proves the demo question works. The 30-question benchmark spans basic metrics, neighborhood comparisons, time trends, cross-dataset reasoning and freshness, and is run before and after any Genie change. Feedback is evidence to investigate, not ground truth: proven corrections become verified SQL examples or instructions, not an automatic retrain.
"Latest" is ambiguous, and geography is not free
When a source updates mid-month, "latest" silently compares a partial month against a full one. I standardised on the latest completed month and made the reporting period visible. City records also do not all arrive with usable Community Area fields, so the pipeline adds official boundaries and spatial enrichment so every neighborhood shares one geographic vocabulary.
Genie is asynchronous, and Free Edition is strict
A Genie response can be pending, complete, empty, failed, cancelled or expired. The FastAPI layer normalises those into explicit application states so the React app has a stable contract. Running on Apps Free Edition also forced deterministic mock providers, local lifecycle testing and a production fail-closed rule that refuses to start in mock mode.
Results
- 1st in Track A
- Gold1st in Track AReal World Problem Solving
- contest score
- 35.7contest scoretop Track A entry
- Community Areas covered
- 77Community Areas covered
- benchmark questions with verified SQL
- 30benchmark questions with verified SQL
The medal is the visible outcome; the reusable lesson is that a Genie app becomes credible when governance, evaluation, provenance and operational truth are designed in from the start. Genie handles open-ended questions over governed semantics, while fixed product journeys use constrained APIs, and a user can always inspect why an answer should be trusted.