KAIZEN Book a call
Open main menu
Book a call

Government

Adding plain-English queries to a city data warehouse

We built a plain-English chat tool on top of the DC Department of Public Works data warehouse. It was about two hundred lines of code, with no new dependencies. It worked because of the data integration we have done for the department since 2016.

By Ashish Tonse 6 min read

Chat tool
~200 lines
New dependencies
0
Systems underneath
A dozen
Telemetry readings
~100 million

Someone at the DC Department of Public Works wants to know how many blocks a crew missed on a particular route last week, broken down by the reason the crew logged.

That is an ordinary question. The department asks questions like it all the time. Before the warehouse, the answer took a person, a spreadsheet and most of a morning, because the data sat in three places: a work-order system, a feed of vehicle location data, and a map of the routes that a third system owns.

One question, three places to look Before the warehouse, an ordinary question, such as how many blocks a crew missed on one route last week by the reason the crew logged, took a person and a spreadsheet. The person had to pull data from three places in turn: the work-order system, the feed of vehicle location data, and the route maps, which a third system owns. Putting it together took most of a morning. BEFORE THE WAREHOUSE THE QUESTION Blocks a crew missed on one route last week, by the reason logged A person AND A SPREADSHEET Work-order system WHAT CREWS LOGGED Vehicle location feed WHERE THE VEHICLES WENT Route maps OWNED BY A THIRD SYSTEM The answer MOST OF A MORNING
Before the warehouse, one ordinary question meant a person visiting three systems in turn and building a spreadsheet by hand. The answer took most of a morning.

We built a chat box on top of the warehouse. The chat box is the smallest part of this story.

What was already underneath

Since 2016, we have worked with the department on data integration that nobody would put in a demo.

A dozen agency systems, each bought separately and none built to share data, now connect through one enterprise service bus: a shared layer that moves data between them. About a hundred million telemetry readings from a fleet of two hundred vehicles arrive in near real time. Route maps let a resident's address match the streets the department plows or sweeps. All of it lands in one warehouse that can be queried as a single picture of the operation.

That work continued through several chief information officers and a pandemic, and at no point did it produce anything that would impress someone on a screen. Reports that used to arrive weekly as a spreadsheet now update within minutes. A result like that gets one line in a status report.

It is also the reason a plain-English tool is possible at all. You can only answer a question asked in English if there is one place where the answer exists.

The chat tool is the thin layer on top Four layers. At the bottom, a dozen agency systems, each bought separately and none built to share data, including work orders, vehicle locations and route maps. Above them, an enterprise service bus that moves the data between them, which we have built with the department since 2016. Above that, one warehouse with about 100 million telemetry readings, where reports update within minutes. Dashboards and reports already ran on the warehouse. The plain-English chat tool sits on top: about 200 lines of code with no new dependencies, hours of work on top of years of integration. A dozen agency systems EACH BOUGHT SEPARATELY, NONE BUILT TO SHARE SUCH AS WORK ORDERS, VEHICLE LOCATIONS, ROUTE MAPS Enterprise service bus ONE SHARED LAYER THAT MOVES THE DATA SINCE 2016 One warehouse ONE PICTURE OF THE OPERATION ABOUT 100 MILLION TELEMETRY READINGS REPORTS UPDATE WITHIN MINUTES YEARS OF INTEGRATION HOURS OF WORK Chat tool ABOUT 200 LINES, NO NEW DEPENDENCIES Dashboards and reports ALREADY RAN HERE
The chat tool is about 200 lines of code on top of years of integration work. The warehouse underneath is the one place where the answer to a question exists.

The chat tool is two hundred lines

Once the warehouse exists, the interface is small: a chat window, a model that can call tools, and one tool that runs a read-only query against the warehouse and returns the rows.

The steps are simple. The question goes to the model along with the database schema. The model calls the query tool. The rows come back. The model reads them and answers in a sentence, with the numbers in it.

We wrote the model calls and the tool-call loop ourselves, without a framework. We considered two. One would have added about thirteen dependencies, in return for an easier way to switch model providers and built-in streaming. The other, a full agent framework, would have added closer to forty, and would have managed the whole loop, the schemas and the process lifecycle.

For a proof of concept, neither was worth the cost. Our own version took hours to build, added no dependencies, and whoever inherits it can debug it line by line. A framework is the better choice for a long-lived production system where several features share the same structure. Our rule: add a dependency when a second and a third feature will use it, and not before.

What each way of building the chat tool would add A bar chart of the new dependencies each option would have added to the proof of concept. Our own model calls and tool-call loop added none, took hours to build, and can be debugged line by line. A lighter framework would have added about thirteen, in return for easier switching between model providers and built-in streaming. A full agent framework would have added closer to forty, and would have managed the whole loop, the schemas and the process lifecycle. Our rule: add a dependency when a second and a third feature will use it, and not before. NEW DEPENDENCIES EACH CHOICE WOULD ADD 010203040 Our own loop HOURS TO BUILD, DEBUGGED LINE BY LINE 0 A lighter framework PROVIDER SWITCHING, BUILT-IN STREAMING about 13 A full agent framework RUNS THE LOOP, SCHEMAS, PROCESS LIFECYCLE closer to 40 OUR RULE Add a dependency when a second and a third feature will use it, and not before.
Writing the loop ourselves added no dependencies. A lighter framework would have added about thirteen, and a full agent framework closer to forty.

Show the query

The most important detail: the tool showed the query it ran.

The query appeared next to the answer, where the person reading it would see it. It was not hidden in a debug panel or behind an administrator setting.

This matters more in a government department than almost anywhere else. A number from a tool like this might end up in a council briefing, a public records response or a performance report. The person who puts it there is accountable for it, more than most private-sector analysts are, and nobody will accept "the AI said so" as a defense.

What a reader checks in the query An illustrative mock of the chat tool. A question asked in plain English, how many blocks a crew missed on a route last week by the reason the crew logged, gets an answer in a sentence, and the query it ran is shown right under the answer. Someone who knows the data reads the query and checks three things: that it filtered on the right route, that it counted the right week, and that it kept the rows with no reason code instead of dropping them. The person who uses the number is accountable for it. ASKED IN PLAIN ENGLISH How many blocks did a crew miss on this route last week, by the reason the crew logged? THE ANSWER, IN A SENTENCE THE QUERY IT RAN select reason, count(*) as blocks_missed from missed_blocks where route = :route and week = :last_week group by reason 1 2 3 SOMEONE WHO KNOWS THE DATA 1 The right route? Not filtered on the wrong one. 2 The right week? Not the week before. 3 No reason code? Those rows kept, not dropped. The person who puts the number in a briefing is accountable for it. ILLUSTRATIVE QUERY
With the query on screen, someone who knows the data can check the route, the week and the rows with no reason code before the number goes anywhere. The query shown is an illustration.

When people can see the query, they can check the answer. Someone who knows the data can look at it and see at once that it counted the wrong week, filtered on the wrong route, or dropped the rows with no reason code. That reader is the safety check, and the design should make their job easy.

Every answer shows the query it ran The person's question goes to the model with the database schema. The model calls one tool, which runs a read-only query against the warehouse. The rows come back to the model, which answers in a sentence with the numbers in it and shows the query it ran next to the answer, so someone who knows the data can check it. The person asking The model Query tool READ-ONLY The warehouse QUESTION, WITH THE SCHEMA CALLS THE QUERY TOOL READ-ONLY QUERY ROWS ROWS ANSWER, NUMBERS AND THE QUERY The query sits next to the answer SOMEONE WHO KNOWS THE DATA CAN CHECK IT
The question and the database schema go to the model, which runs one read-only query through a tool and answers in a sentence with the numbers in it. The query appears next to the answer, so someone who knows the data can check it.

If you do not have the warehouse yet

This is where most of these projects go wrong.

If your data still lives in a dozen systems that do not share data, a plain-English tool will not fix that. It will give fluent answers from whichever piece of the data it can reach. Those answers will be wrong in ways nobody catches, because how fluent an answer sounds tells you nothing about whether the data behind it is complete.

So the integration comes first. It is slower, it costs more, it does not look impressive, and it makes everything after it possible.

A chat tool without a warehouse Without a warehouse, the data lives in a dozen systems, none built to share it. A chat tool placed on top reaches only the pieces it can, and gives a fluent answer from them. That answer is wrong in ways nobody catches, because how fluent an answer sounds says nothing about whether the data behind it is complete. So the order is: integration first, which is slower and costs more; then one warehouse, the one place where the answer exists; then the chat tool, a couple of hundred lines on top. WITHOUT THE WAREHOUSE A DOZEN SYSTEMS, NONE BUILT TO SHARE DATA Chat tool REACHES WHAT IT CAN A fluent answer WRONG IN WAYS NOBODY CATCHES How fluent it sounds tells you nothing about whether the data behind it is complete. SO THE ORDER IS 1. Integration SLOWER, COSTS MORE, COMES FIRST 2. One warehouse THE ONE PLACE THE ANSWER EXISTS 3. The chat tool A COUPLE OF HUNDRED LINES
Over a dozen disconnected systems, a chat tool answers fluently from whatever it can reach, and nobody can tell what it missed. Integration comes first, then the warehouse, then the chat tool.

The integration is worth doing even if you never add a model. The department's dashboards and reports already ran on the warehouse. The chat tool was an extra convenience on top of a system that was already paying for itself.

What to ask about any chat tool over data

When someone shows you a plain-English interface over their data, set aside the questions about the model, the prompt and the framework. Ask this: what does it query, and how was that built?

If the answer is a real, connected and maintained data warehouse, the interface on top is a couple of hundred lines and a good idea. If the answer is vague, you are looking at a demo with nothing behind it.

Have a similar problem in your department?

We have built data systems for the DC Department of Public Works since 2016. Tell us about your department in a 30-minute call.

Our work for government

Book a 30-minute call