Building a Smarter BI Tool with AI Agents

"The next generation of analytics" is a phrase we hear a lot, and it usually ends in the same place: AI agents. This is the story of how we built an AI agent layer for a fintech and payments analytics platform — turning a traditional BI experience into something that feels less like a reporting tool and more like a conversation with your data.

The Challenge

The product was a solid business intelligence tool for payments and interchange data — dashboards, reports, drill-downs into merchants and verticals and take-rate. It had all the table-stakes features: chart builders, scheduled reports, role-based access. But it shared the same friction every BI tool has.

"It takes too long to get an answer."

Even with a well-designed UI, the typical workflow for getting a single number out of a BI tool looks something like this:

1. Figure out which table(s) contain the data you need
2. Write or tweak a SQL query (or use the visual query builder, which still requires understanding the schema)
3. Run the query, wait for results
4. Pick the right chart type
5. Format everything, tweak axes and labels
6. Pin it to a dashboard
7. Repeat for the next question

For a power user who knows SQL and the schema well, this takes maybe 5–10 minutes per question. For everyone else — product managers, executives, operations leads — it means filing a request with someone who does, and waiting. Most people in an organization never write a query at all; they're purely consumers of dashboards someone else built.

The goal was to change that: let any user — regardless of technical skill — type a question in plain English and get back a chart, a table, or a dashboard card in seconds. That's where we came in.

The Approach: AI Agents, Not Just a Chatbot

Early on, we made a critical distinction that shaped the entire project: we weren't building a chatbot. We were building an agent system.

A chatbot takes a prompt, sends it to an LLM, and returns whatever text comes back. That's fine for answering questions about your return policy. It's not fine when the response needs to be a syntactically valid SQL query that runs against a large analytics warehouse and returns results that get rendered as a stacked bar chart. The margin for error is essentially zero — a hallucinated column name doesn't just look bad, it throws an error in the user's face.

An agent system, by contrast, breaks the problem into discrete steps, each with its own validation and error handling. The agent can reason about what it needs to do, fetch context (like the database schema), generate a plan, execute it, verify the output, and recover from mistakes. Think of it less like autocomplete and more like a junior analyst who happens to work at the speed of an API call.

We settled on a multi-step agent pipeline. At a high level, a question flows through routing and scope analysis, schema and metadata assembly, query planning and SQL generation, validation, execution, and finally result formatting. Each stage is its own module — each one can fail independently, retry, or short-circuit. Let's walk through the key parts.

Technical Decisions

Choosing LLM Providers

We built the system to be provider-agnostic rather than betting on a single model. Under the hood it can call Anthropic (Claude), OpenAI (GPT), and Google (Gemini), each behind a common client interface, with Anthropic as the default. We also kept the model used for SQL generation separate from the one used for summarization, so we could tune each independently and swap providers without touching the rest of the pipeline.

In practice, the reasoning quality on complex joins and subqueries mattered more than any single benchmark number, and being able to route different steps to different models — or fail over between providers — turned out to be more valuable than standardizing on one.

A Custom Agent Framework

We built our own lightweight orchestration layer rather than adopting a general-purpose agent framework. This is an opinionated take, but for a production agent system where you need fine-grained control over every step — retries, timeouts, logging, token budgets, and tight integration with the product's auth and data layers — we've found that the abstraction layers in off-the-shelf frameworks tend to get in the way more than they help. You end up fighting the framework instead of building your product.

The result is a pipeline of specialized components — a query router, a scope analyzer, a query planner, a validator, and a recovery manager — each with a single responsibility, shared context flowing between them, and observability at every step.

Architecture Deep Dive

Step 1: Routing and Scope

Not every user message is a data question. Some are definitional ("what do you mean by churn?"), some are follow-ups ("break that down by region"), and some need a full analytical query against the warehouse. A lightweight router classifies each message — roughly, definitional vs. purely analytical vs. domain-guided — and decides whether it even needs to hit SQL. Definitional questions short-circuit here and are answered directly, which keeps the expensive path reserved for questions that actually need data.

Step 2: Schema and Metadata

You can't just dump an entire warehouse schema into the prompt — it would blow through the context window and confuse the model. Instead of vector search, we lean on curated metadata: the relevant tables, columns, definitions, and business context are assembled from a maintained metadata layer and from pre-built materialized views for the most common analytical patterns. That curation is a big part of why the generated SQL is accurate — the model is working from vetted, well-described schema rather than guessing at raw table names.

Step 3: SQL Generation

With the relevant schema in hand, we prompt the LLM to generate a SQL query. The system prompt is heavily engineered — it includes the schema context, the ClickHouse dialect the warehouse runs on, example queries for common patterns, and explicit instructions about what not to do (never invent column names, always qualify columns when joining, avoid SELECT *, apply sensible limits).

Step 4: Query Validation

This is the safety net — and honestly, the step that took the most iteration. Before a generated query ever touches the warehouse, it passes through a validation layer we call the EnhancedValidator. Rather than parsing the SQL into a full syntax tree, it runs a battery of pattern checks tuned to our ClickHouse dialect:

• Read-only enforcement: any write or DDL statement (INSERT, UPDATE, DELETE, CREATE, ALTER, DROP) or system-catalog access is rejected outright. The agent only ever gets read access.
• Schema grounding: every referenced table and column must exist in the retrieved schema and be correctly database-prefixed. This catches hallucinated names — the single most common failure mode.
• Dialect guardrails: constructs that behave badly on ClickHouse — things like COUNT(DISTINCT …) or FULL OUTER JOIN — are blocked, and query complexity is capped (at most one CTE and a couple of joins).

When a query fails validation, the specific reason is fed back to the generation step so the model can correct itself. Including the exact error ("Unknown column: orders.total_ammount — did you mean orders.total_amount?") dramatically improves the model's ability to self-correct on the next attempt. A dedicated recovery manager handles this loop, classifying the failure and regenerating with the failure context, up to a capped number of attempts.

Step 5: Execution

Once validated, we execute the query with a statement timeout. Nothing fancy here — the important work happened in validation. The results are handed to the formatting step.

Step 6: Result Formatting and Visualization

The last step is choosing how to present the results. We send the column names, data types, a sample of the data, and the original question to the LLM and ask it to recommend a chart type and configuration — bar, line, area, pie, table, or a single value — along with axes, grouping, and a title. Obvious cases (a single number, or too many rows for a pie chart) are handled with simple heuristics before we ever make the call.

The Hard Problems

Hallucination Prevention

Hallucinated column and table names were the number one source of errors in early testing. The model would confidently reference orders.revenue when the actual column was orders.total_amount, or join on user_id when the foreign key was actually account_id.

Three things made the biggest difference:

1. Better schema context. We stopped passing raw DDL and started including column descriptions, sample values, and common query patterns. Telling the model that orders.total_amount is "the order total in USD cents" is much more useful than just total_amount INTEGER.
2. Validation feedback. When the validator catches an unknown column, it suggests the closest match and feeds that back into the retry prompt. "You referenced 'revenue' but that column doesn't exist. Did you mean 'total_amount' (in the orders table)?"
3. Few-shot examples. We include a handful of example question-to-SQL pairs in the prompt, tailored to the schema and its domain-specific terminology. These make a disproportionate difference.

Handling Ambiguity

"Show me our top customers" sounds simple, but it's full of ambiguity. Top by what — revenue, order count, lifetime value? Over what time period — this month, this year, all time? How many is "top" — 5, 10, 100?

Our first approach was to have the agent ask clarifying questions. This was technically correct but terrible UX — nobody wants to answer three questions before getting a chart. Instead, we built a reasonable defaults system. The agent makes assumptions (top 10 by revenue, trailing 12 months) and states them explicitly alongside the result: "Showing top 10 customers by total revenue, last 12 months. Want to change the metric or time range?"

Performance at Scale

Analytics workloads can hit tables with hundreds of millions of rows, so we couldn't just let the agent generate any arbitrary query and hope it runs fast. A few techniques we used:

• Materialized views for common patterns: we pre-compute daily/weekly/monthly aggregates for the most frequently queried metrics, and the schema layer surfaces them when appropriate.
• Statement timeouts: every query runs with a timeout. If it times out, the agent suggests adding filters or choosing a smaller time window.
• Complexity limits in validation: as noted above, the validator caps joins and CTEs and blocks patterns that are known to be slow on ClickHouse.

Putting It All Together

End to end, a question flows through the pipeline like this: the router decides whether it needs data at all; definitional questions are answered directly, while analytical ones move on to schema assembly, SQL generation, and validation. If validation fails, the recovery manager regenerates with the error context and tries again. Once a query passes, it executes against the warehouse, and the formatter turns the result into the right visualization. The UI shows which step the agent is on as it works, so users have confidence that something is happening.

Lessons Learned

A few things we'd tell anyone building a similar system:

1. Validation is more important than generation. It's tempting to focus all your energy on getting the LLM to produce perfect SQL. Don't. Invest in robust validation and retry loops instead. The model will get it right on the second try most of the time — you just need to tell it why the first attempt was wrong.
2. Schema quality is everything. The single biggest lever on accuracy is the quality of the schema metadata you feed the model. Column descriptions, sample values, and curated examples are worth more than prompt engineering tricks.
3. Make assumptions, don't ask questions. Users want answers, not interrogations. Default to reasonable assumptions and let users refine from there. This is a UX insight as much as a technical one.
4. Stay provider-agnostic. Keeping OpenAI, Anthropic, and Gemini behind one interface — and separating the model used for SQL from the one used for summaries — let us tune and fail over without rewrites.
5. Observability from day one. We log every step of every agent run — the prompt, the response, the latency, the token count, the validation result. This data is invaluable for debugging, improving prompts, and finding where the agent struggles.

What's Next

The natural next step for a system like this is multi-step agents that build entire dashboards from a single prompt ("create a dashboard showing our Q1 sales performance"), and a feedback loop that learns from which results users accept, modify, or reject.

If you're building an analytics product and thinking about adding an AI layer — or if you have any other complex AI challenge — we'd love to hear about it. We specialize in taking projects like this from concept to production. Get in touch.