Before the Chatbot: How We Got Our Data House in Order

Every client conversation lately ends up at the same request: "can we just ask our data questions in plain English?" It's a fair ask, and these days a reasonable one. When I moved from data analyst into a data and AI scientist role at Performalytics, that chatbot felt like the obvious thing to build first. I didn't build it first. My first few months went into the warehouse instead, not the model, and that's the part of the story worth telling.

Custom Connector for PA MCP Server

Why not just point an AI at the data?

Large language models aren't employees of your company. They're smart outsiders. Ask one to write SQL, and it'll do a decent job. What it can't do is know how your business actually defines its numbers, because that logic was never in its training data. Ask a chatbot, "What was our gross revenue last quarter?" and it runs straight into questions it can't see. Which date counts, the sale date, the invoice date, or the delivery date? Whose quarter, calendar or fiscal? None of that is written down anywhere the model can read. It lives in someone's dashboard or in someone's head. So without that context, the model doesn't fail loudly. It just guesses, confidently. And confidently wrong is worse than not answering at all.

A 2025 Analytics8 study of over 100 North American mid-market companies found only 14% describe their data as fully AI-ready, and 15% say 10% or less of it even qualifies. The gap tracks business performance closely too: 87% of fast-growing companies have most of their data AI-ready, versus just 11% of slower-growing ones, and none of the declining companies cleared that bar at all. A lot of companies are buying the car before they've paved the road.

So the first few months were unglamorous, on purpose. Instead of a big launch, the approach has been small steps that build on each other: find one manual task, automate it properly with AI, earn trust in it, then move to the next one. The first task I picked wasn't glamorous at all. One of our clients had a morning ritual: refresh a set of pivot tables in an Excel file, then build a data-backed status report from it for their leadership team, by hand, every single morning. Instead of jumping straight to "let's put a chatbot on this," I started with that one report. Everything it summarized became the first draft of our semantic layer, a handful of tables, metrics, and dimensions grounded in something an employee already relied on daily, not some abstract data model nobody had checked against actual use. I wrote a batch of test questions against it and gave myself one simple rule: it doesn't count as working until the answers match what our BI dashboards already produce, every time. It got there.

Turning that first pass into something we could actually put in front of clients meant building two layers underneath it. The first is a medallion architecture: raw data lands untouched in a bronze layer, gets cleaned and standardized in silver, then shaped into business-ready tables in gold, which is our mart. Each stage exists so that by the time anything, a person or a model, is querying the data, it's querying something you can trust rather than a raw dump from whatever system produced it.

The second is the semantic layer. Every metric that matters—netet revenue, average order value, churn—gets defined once, as version-controlled code, instead of living as knowledge scattered across ten different dashboards. Each definition spells out the formula, where it comes from, and which ways you're allowed to slice it. That one file becomes the only place "revenue" or "last quarter" gets decided, whether the question comes from a chart, a report, or a chatbot.

A Glimpse of the Semantic Layer

A semantic layer is also how you stop hallucination. This isn't just tidiness for its own sake. AtScale's research found that LLMs guess table joins wrong about 80% of the time once a query needs more than four tables. dbt Labs' own benchmarking found raw text-to-SQL, an AI querying tables directly with nothing to guide it, sits around 40-60% accuracy, versus 85-100% when the AI is grounded in a semantic layer instead. That's not a small improvement. That's the difference between a demo you show once and something a client can actually rely on.

Dashboards and AI should share one brain. None of this means dashboards are going away, they're not. But the logic behind them shouldn't live inside Tableau or Power BI, tools built to visualize data, not to decide what "revenue" means. The AI and the BI layer should both be headless: define each metric once, centrally, and let every dashboard, report, or chatbot call that same source instead of quietly inventing its own version. Skip this, and both dashboard drift and AI hallucination trace back to the same root cause: nobody centralized the logic, so every tool built its own version of the truth, and nobody notices until a client asks why two numbers don't match.

Where does it stand now?

That foundation is what our "ask your data" experience sits on today. We built it using the Model Context Protocol, Anthropic's open standard for letting an AI safely call external tools instead of guessing or writing raw queries itself. Our MCP server sits between Claude's chat interface and our BigQuery Mart, so when a client asks a question, the AI never touches SQL directly. It calls a small set of governed tools that read the same metric definitions a human analyst would use, and the server builds a safe, scoped query from there.

It's live today as a working proof of concept with a pilot client: real questions, real answers, pulled from governed data with proper safety guardrails, not improvised by a model. From here it's the usual next steps after a proof of concept: tighter cost controls and guardrails, clean multi-tenancy as we bring on more clients, and eventually an interface simple enough that clients just log in and ask.

The chatbot is the easy 20% of this. The first few months, one Excel file and a semantic layer built to answer it exactly, were the other 80%. In the next few blogs I'd try to share how we built our semantic layers and MCP servers and strung everything together.

References

1. Solving the data readiness conundrum — dbt Labs, guest research from Analytics8 (2025)

2. What is Headless BI? — Luzmo

3. What is Medallion Architecture? — Databricks

4. Semantic layer for trusted AI — AtScale

5. Semantic Layer vs. Text-to-SQL — dbt Labs

6. Introducing the Model Context Protocol — Anthropic