The short version. Dot Square Lab built a text-to-SQL agent over a streaming analytics warehouse so commercial staff can ask it anything in plain English and get back the answer, a chart and the SQL that produced it. There is no fixed menu of reports and no ticket to raise: the question does not have to be one anybody anticipated, and the follow-up is just the next message. The figures are grounded by construction, because every number comes out of SQL that actually ran against the warehouse rather than out of the model, and a golden-set evaluation suite runs against the real database before every release so the answers stay right when prompts, models and schemas change.
At a glance
- Sector: a network analytics platform
- Problem: commercial staff needed answers from a large analytics warehouse but could not write SQL, so every question queued behind an engineer
- Approach: a single reasoning agent that writes and runs its own SQL, supported by a schema-synced business glossary, entity resolution and a data-freshness check, with read-only guardrails around execution
- Tech: FastAPI, Agno, OpenAI, ClickHouse, Kafka, Phoenix and OpenTelemetry
- Outcome: live in production across multiple environments; open-ended questions answered with charts, including correlations across sources that no single report could show
The challenge: every question went through an engineer
The platform ingests usage records continuously. Files land, stream through Kafka into ClickHouse, and are joined there against the reference data that gives them meaning: subscribers, services, network topology, product categories. The data was correct, current and complete.
It was also unreachable for most of the people who needed it. Answering a commercial question meant knowing the schema, knowing which table held the attribute you cared about, and writing ClickHouse SQL. That skill sat with a small number of engineers, so every question became a ticket and every ticket waited.
Dashboards do not solve this, because a dashboard only answers the questions someone thought of when they built it. Real commercial questions arrive in chains: which segment dropped last month, then whether it dropped in every region, then what those same customers were doing the month before. Every link in that chain is a new query, and under the ticket model every link is a new wait.
So the requirement was not a better report. It was for anyone in the business to ask any question, get an answer they could act on, and keep asking.
The approach: let people ask, and make the answers true
What it does
A user asks in plain English. The agent works out what they mean, asking a clarifying question when the request is genuinely ambiguous rather than guessing. It pulls the schema and the business glossary, resolves the names in the question to real identifiers, writes the SQL, runs it and answers.
The response is more than prose. It carries the rows and columns, a chart rendered when the answer is better seen than read, the exact SQL that produced it, and the log of which tools ran in what order. Sessions keep their history and can be exported and imported, so a line of questioning can be picked up later or handed to someone else.
Because everything sits in one warehouse, a question is not confined to one source. Usage records, subscriber attributes, service configuration and network topology can be correlated in a single query, which is what turns "this segment dropped" into "it dropped where this other thing changed". That is usually the question people actually have, and it is the one separate reports answer worst: you export two of them and line them up by hand, if you bother at all.
The agent also knows how current the data is. It can check the latest timestamps behind an answer and say that the period only runs to yesterday, instead of presenting a partial month as a complete one.
Why the agent writes the SQL
Generating SQL is not automatically the right architecture. It is right when the question space is genuinely open, and here it was. Users compare, slice and drill across a wide schema in combinations nobody can enumerate in advance. A fixed set of pre-written queries would have answered the first week of questions and blocked the rest.
Where the question set is knowable and the difficulty sits underneath it, in reconciliation and domain judgement rather than in query construction, the trade goes the other way: that architecture is covered in our Horus case study.
Why the answers are right, not just plausible
The failure that matters in an analytics tool is not a crash. It is a plausible number that is wrong, because nobody downstream can tell.
One structural property does most of the work against that. The model never calculates anything. Every figure in an answer comes from SQL that ran against the warehouse, so the model is choosing the query and explaining the result while the database produces the number. That is a stronger guarantee than asking a model to be careful, and it is why this class of system is grounded by construction.
What remains is making sure the query asks the right thing, and two pieces of unglamorous engineering decide that.
The glossary. The model needs to know what a column means in this business, not what its name suggests. The glossary carries that meaning, and it is synced from the live schema by script rather than maintained by hand, so a schema change does not silently leave the model working from a stale definition.
Entity resolution. Users type half-remembered names. A system that returns an empty result set because the name was not an exact match gets abandoned in a week. Fuzzy matching to the real identifier is the difference between a demo and a tool people use.
On top of that, every answer is checkable. The SQL, the rows and the tool-call log come back with it, so a user who wants to verify the working can, and an engineer debugging a bad answer can see exactly what ran. Trust in an analytics agent is built by being checkable, not by being right.
Evaluation: knowing it still works next month
The system carries a golden set: datasets pairing natural-language questions with known-good answers, run in-process against the real model and the real database. Every run is archived, so behaviour is compared against history rather than discovered by a user.
This matters because everything underneath an agent moves. Prompts get edited, models get upgraded, schemas migrate. Any one of those can quietly change an answer that used to be right, and an analytics tool has no natural error signal: a wrong number looks exactly like a right one.
We build an evaluation suite into every agentic system we ship, not just this one. It is the only thing that lets you change a prompt, swap a model or migrate a schema and know that the answers did not move with it.
Alongside the evals, every run in production is traced with Phoenix and OpenTelemetry. An individual query can be opened up afterwards: which tools were called, with what arguments, what each returned and how long it took. When someone reports an answer that looks wrong, that trace is the difference between reproducing the problem and guessing at it, and it is where a failing eval case gets diagnosed rather than just counted.
Guardrails
Running generated SQL against a live database needs the obvious controls, so they went in from the start. The raw prompt is screened for dangerous intent before the model sees it. The generated SQL has to pass read-only keyword checks, be a single statement, reference only allowlisted tables, avoid blocklisted columns and carry an enforced row limit, which is a cost control as much as a safety one. The connection itself uses a read-only database user, so the account cannot write even if both layers above it failed.
Three independent controls, each sufficient on its own for the destructive case. This is table stakes rather than the interesting part of the build, but it has to be right before anything else matters.
What we delivered
A production text-to-SQL agent, deployed across multiple environments through a GitOps pipeline, sitting on a streaming pipeline that keeps the warehouse current.
What changed is who gets to ask, and how often. Work that used to be a ticket in an engineer's queue is now a conversation, and a follow-up costs nothing, so the questions that were never worth raising a ticket for get asked too.
Where this approach applies
Generated SQL is the right architecture when the question space is genuinely open: a wide schema, exploratory use, and users who slice the data in ways you cannot list in advance.
If that is your situation, the work that decides success is not the prompt:
- Build the glossary early. A model guessing column semantics from column names is the most common source of confidently wrong answers.
- Resolve entities. Exact-match lookups against human typing will kill adoption faster than any other single thing.
- Return the SQL with the answer. People trust a system they can check, and the first time someone verifies a number for themselves is the moment the tool gets adopted.
- Ship the evaluation suite with version one. Not after. It is what lets you change the prompt, the model or the schema later without guessing.
- Layer the execution guardrails and end at the database. Prompt screening, SQL validation, read-only connection user. Never rely on the model behaving.
If instead your users ask the same twenty questions every week, generated SQL is probably the wrong trade: you take on a class of risk for flexibility you will not use, and the domain logic underneath, which is usually the real work, has to be built either way. The alternative architecture is covered in our Horus case study.
Frequently asked questions
Can non-technical staff query a data warehouse in plain English? Yes. A text-to-SQL agent translates the question into SQL, runs it and returns the answer with a chart and the query it used, so nobody needs to know the schema. The real test is not the demo question, it is the third follow-up. Whether it passes depends on the schema context and business glossary you give the model, not on the model itself.
What can you actually ask a text-to-SQL agent? Anything the schema supports, including the questions nobody anticipated: comparisons, breakdowns, trends, and follow-ups that refine the previous answer inside the same session. That is the difference from a dashboard, which answers a fixed set of questions decided when it was built.
Can it tell you why something changed, not just what changed? Within the data, yes, and this is where a warehouse-wide agent beats a set of reports. Because it writes its own SQL over everything in one place, it can correlate across sources in a single query: line a drop in usage up against the subscribers, services or parts of the network it happened in, and narrow down what was different about them. It cannot tell you about a cause that is not in the data, but the second and third question, the ones that actually locate the problem, stop being a new export each time.
How do you know the answers are right? Three things together. Every number comes out of SQL that ran against the database, so the model is not producing figures of its own. A business glossary synced from the live schema tells the model what each column means, which is where confidently wrong answers usually begin. And a golden set of questions with known-good answers runs against the real model and the real database before each release, archived per run, so a regression shows up before a user finds it.
Is text-to-SQL safe to run against a production database? Yes, with layered controls, and they are not exotic. Screen the prompt for dangerous intent, validate the generated SQL (read-only keywords, single statement, table allowlist, column blocklist, enforced row limit), then connect through a read-only database user so the account itself cannot write. Each layer should be independently sufficient. The row limit matters as much as the rest: most warehouse incidents caused by an agent are an unbounded scan, not anything malicious.
When should you not use text-to-SQL? When the questions are a known set and the hard part sits underneath them. If the difficulty in your data is reconciliation, agreed definitions and domain judgement rather than query construction, that work has to be built either way, and once it exists the agent is better off calling it than re-deriving it on every question.
Build it properly
If you are considering natural-language access to your data, the architecture decision is worth getting right before anyone writes code, because reversing it later means rebuilding the agent layer. Our guide to AI agent development for business covers the wider set of those decisions, and an AI roadmap is the cheapest way to make them before committing a build budget.
Dot Square Lab builds production AI systems end to end, from the ingestion pipeline to the agent to the evaluation suite that keeps it honest. Tell us your challenge and we will tell you which approach fits, including when the answer is that you do not need an agent at all.
