How to Build Your In-House Analytics Agent Fully with Open Source

The Case for In-House Analytics Agents

The most successful analytics agents are increasingly built in-house. This pattern appears in public examples like OpenAI’s data agent work and Vercel’s tooling simplification efforts.

Building in-house with open source components gives teams better control and adoption, but requires work to rebuild the full loop: agent runtime, context builder, analytics UI with visualization, and evaluation workflow.

This guide provides both process and technical guidance for assembling existing components into a reliable, trusted analytics agent that teams use to chat with data.

Step 1. Pick one painful analytics workflow

Start with a use case where demand is high, the question comes back often, and ROI is easy to prove.

The best first workflows are usually:

Answering recurring questions on main company metrics
Handling repeated data retrieval requests from business teams
Checking weekly sales or pipeline performance
Reviewing retention or activation metrics
Explaining monthly finance variance

These work well because they are frequent, visible, and easy to validate. If the agent gets them right, people use it repeatedly. If it saves time on these workflows, adoption starts fast.

Step 2. Choose your analytics agent stack

The goal is to choose a stack that stays fully open source while remaining maintainable in production.

Most teams evaluating analytics agents look at five main solution paths:

Claude + MCP: plug your context as MCPs plus rules inside a Claude team org
LangChain: python agent framework to build fully customized agents
LibreChat: open-source chat interface you can extend with MCPs for data connection
Vercel Knowledge Agent: open template you can adapt to analytics by adding SQL, dbt, retrieval and an evaluation loop
nao: vertical analytics-agent stack combining context builder, analytics chat UI, and evaluation in one workflow

If you want to compare these approaches in detail, benchmark comparison resources are useful starting points.

With nao, context building, analytics UI, and evaluation run in one product workflow, making it the only vertical, all-in-one option in this list.

Step 3. Define clear context on a small scope

The goal is to give the agent only the minimum high-quality context needed to be reliable.

Start with 5 to 10 tables and structure context in four blocks:

Databases: schema metadata, sampled rows, profiling signals
Repos: your dbt project and analytics codebase
Rules context: business rules, joins logic, SQL guardrails, metric definitions
Custom context: lightweight business notes (data_quality.md, KPI references, figures)

One practical rule matters significantly: it is better to have an agent with 5 tables used by 100 people than an agent with 100 tables used by 5 people. With 5 tables for 100 people, quality is usually higher and trust grows faster. With 100 tables for 5 people, reliability drops and rollout usually stalls before reaching the wider company.

Step 4. Build your agent unit tests and eval framework

At this point, reliability must become measurable before rolling the agent out more broadly.

Convert real business questions into unit tests that pair a natural-language prompt with ground-truth SQL.

Keep each test simple and explicit. A good structure is:

name: weekly_signups
prompt: How many signups did we have per week in the last 4 weeks?
sql: |
  select week, sum(n_new_users) as n_signups
  from prod_silver.fct_users_activity_weekly
  where week >= date_trunc(current_date - interval 4 week, isoweek)
  and week < date_trunc(current_date, isoweek)
  group by week
  order by week

Then define KPI targets and success criteria up front:

Pass rate target
Metric correctness on high-priority questions
Latency and cost per run
Regression tolerance after context changes

Step 5. Run tests and measure quality

This step establishes a baseline trust score for the agent.

Run the full suite and track pass rate, accuracy by question type, latency, and cost. That baseline tells you how much you can trust current answers and how much context improvement is still required.

Current benchmarks suggest approximately 80% is a reasonable reliability target. Much of the remaining gap is often interpretation edge cases, not just SQL generation.

This is also the right moment to compare your result against what good looks like. If you are far below the roughly 80% reliability range, the benchmark indicates the agent is not ready for broad trust yet.

Step 6. Iterate on context quality

The goal now is to improve quality by fixing context, not by blindly changing prompts.

For each test, inspect failures and successes. When it fails, identify the exact gap: definitions, joins, metric context, or rules. When it succeeds, make that useful context easier to retrieve. If performance improves without certain documentation, clean or rewrite it. Also test where semantic layer and dbt should help and document why they did not.

This usually means updating context files, rerunning tests, and keeping only changes that improve benchmark outcomes.

Step 7. Roll out and monitor

The final step is turning benchmark reliability into sustained real-world adoption.

Roll out progressively, review real chats, track repeated failure patterns, and keep a steady evaluation cadence.

This means collecting in-product user feedback, reviewing monitoring signals, and feeding production failures back into your unit-test suite.

Final takeaway

A reliable analytics agent is not one clever prompt. It is a system: focused scope, explicit context, repeatable tests, measurable quality, and continuous iteration across your data stack.

It is also not a one-time job. Context drifts as schemas, definitions, and business logic change, so evaluation has to stay active over time. A strong practice is to run these checks in your CI/CD pipeline so regressions are caught before users lose trust.