Building a Data Analyst Agent: SQL, Charts and Summaries
Why a Data Analyst Agent is the perfect first "real" agent to build
Every AI engineering course starts with toy examples: a weather bot, a calculator, a to-do list assistant. They teach syntax, but they don't teach judgment. A data analyst agent is different. The moment you build one, you run into every hard problem in agent engineering at once — untrusted tool output, ambiguous user intent, multi-step planning, and the constant tension between "let the model be flexible" and "don't let the model destroy the database."
Think about what a data analyst actually does in a day. Someone from marketing asks, "why did signups drop last week?" The analyst doesn't have a pre-written query for that. They think about what tables might hold the answer, write a SQL query, look at the result, notice something odd, write a follow-up query, plot a chart to see the trend visually, and then write two paragraphs explaining what happened in language a non-technical stakeholder can understand.
That loop — question, query, inspect, refine, visualize, explain — is exactly the loop we want our agent to run. It's a great teaching vehicle because it forces you to combine three distinct skills: text-to-SQL generation, safe tool execution, and multi-modal output (charts plus prose). This article walks through building that agent end to end, with real code, not pseudocode. By the end you'll have a working pattern you can drop into any internal analytics tool, and you'll understand exactly why each guardrail exists.
Designing the agent's toolset
The single biggest design decision in a data analyst agent is deciding what tools the model is allowed to call. Resist the temptation to give it one giant run_sql tool with no restrictions. In production, that's how you end up with an LLM accidentally running DROP TABLE because a prompt injection snuck in through a customer support ticket that got summarized into context.
A better shape is three narrow tools:
list_tables— returns table names and column schemas, no arguments neededrun_query— accepts a SQL string, but only executes it against a read-only database connectionrender_chart— accepts structured data (not raw SQL) plus a chart type, and returns an image or a spec
Splitting things this way means the model's "creativity" is contained. It can write any SELECT it wants, but it physically cannot write anything else, because the database user backing that connection has no INSERT, UPDATE, DELETE, or DROP privileges. This is a database-level guarantee, not a prompt-level one — and that distinction matters enormously. Prompt-level guardrails ("please only write SELECT statements") are a suggestion. Database-level read-only roles are a fact.
Here's how you'd define that toolset using the Claude API's tool-use format:
tools = [
{
"name": "list_tables",
"description": "List all tables in the analytics database with their column names and types.",
"input_schema": {
"type": "object",
"properties": {},
},
},
{
"name": "run_query",
"description": (
"Execute a read-only SQL SELECT query against the analytics "
"database and return the resulting rows as JSON."
),
"input_schema": {
"type": "object",
"properties": {
"sql": {
"type": "string",
"description": "A single SELECT statement. No writes, no DDL.",
}
},
"required": ["sql"],
},
},
{
"name": "render_chart",
"description": "Render tabular data as a bar, line, or scatter chart and return an image.",
"input_schema": {
"type": "object",
"properties": {
"chart_type": {"type": "string", "enum": ["bar", "line", "scatter"]},
"x_field": {"type": "string"},
"y_field": {"type": "string"},
"rows": {"type": "array", "items": {"type": "object"}},
"title": {"type": "string"},
},
"required": ["chart_type", "x_field", "y_field", "rows"],
},
},
]Notice render_chart doesn't take SQL. It takes rows that were already returned by run_query. That means the model can't sneak a query execution into the charting step, and it means charting logic never touches the database connection at all. Each tool has exactly one job.
Enforcing the read-only boundary at the database layer
Let's make the read-only guarantee concrete. If you're on Postgres, create a dedicated role with no write grants:
CREATE ROLE analyst_agent WITH LOGIN PASSWORD 'use-a-secret-manager-here';
GRANT CONNECT ON DATABASE analytics TO analyst_agent;
GRANT USAGE ON SCHEMA public TO analyst_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst_agent;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst_agent;That last line is easy to forget — it makes sure that new tables created later automatically inherit the read-only grant, so you don't end up with an agent that can't see next month's tables, or worse, someone manually granting broad write access "just to fix it" during an incident.
On the application side, run_query should still validate the incoming SQL before execution, as defense in depth. A simple guard checks that the statement starts with SELECT or WITH (for CTEs) and rejects anything containing semicolon-separated statements, which is a classic way to smuggle a second command:
import re
FORBIDDEN_KEYWORDS = re.compile(
r"\b(INSERT|UPDATE|DELETE|DROP|ALTER|TRUNCATE|GRANT|REVOKE|CREATE)\b",
re.IGNORECASE,
)
def validate_select_only(sql: str) -> None:
statements = [s.strip() for s in sql.split(";") if s.strip()]
if len(statements) > 1:
raise ValueError("Only one statement is allowed per query.")
stripped = statements[0].lstrip().upper()
if not (stripped.startswith("SELECT") or stripped.startswith("WITH")):
raise ValueError("Only SELECT queries are permitted.")
if FORBIDDEN_KEYWORDS.search(sql):
raise ValueError("Query contains a forbidden keyword.")This isn't bulletproof on its own — a sufficiently deviant SQL dialect trick could theoretically slip past a regex — which is exactly why it's paired with the database role restriction above. Two independent layers that both have to fail before something bad happens is the whole point of defense in depth. Also set a statement timeout on the connection (SET statement_timeout = '10s') so a runaway query from a bad JOIN doesn't lock up your database while the agent "thinks."
The core agentic loop
With tools defined and the database locked down, the loop itself is refreshingly simple. It's the standard "call the model, execute whatever tool it asks for, feed the result back, repeat until it stops calling tools" pattern that shows up in every serious agent framework.
import anthropic
client = anthropic.Anthropic()
def run_analyst_agent(user_question: str, conversation=None):
messages = conversation or [{"role": "user", "content": user_question}]
while True:
response = client.messages.create(
model="claude-sonnet-4-5",
max_tokens=2000,
system=ANALYST_SYSTEM_PROMPT,
tools=tools,
messages=messages,
)
messages.append({"role": "assistant", "content": response.content})
if response.stop_reason != "tool_use":
return response, messages
tool_results = []
for block in response.content:
if block.type != "tool_use":
continue
result = execute_tool(block.name, block.input)
tool_results.append({
"type": "tool_result",
"tool_use_id": block.id,
"content": result,
})
messages.append({"role": "user", "content": tool_results})execute_tool is a plain dispatch function that routes to list_tables, run_query, or render_chart based on the tool name, catches exceptions, and returns them as error strings inside the tool result rather than raising — the model needs to see "your query failed because column singup_date doesn't exist" so it can self-correct and retry with signup_date. That self-correction loop is one of the most valuable emergent behaviors you get almost for free once you wire tool errors back into the conversation instead of crashing.
The system prompt is where you encode analyst judgment — the difference between an agent that dumps raw numbers and one that behaves like an actual colleague:
You are a data analyst agent with read-only access to the analytics
database. When answering a question:
1. Call list_tables first if you don't already know the schema.
2. Write the smallest SQL query that answers the question. Prefer
aggregations over raw row dumps.
3. If a result set has a time dimension and more than 3 data points,
render a line chart. For category comparisons, render a bar chart.
4. Always end with a short plain-English summary of what the data
shows, written for a non-technical stakeholder. State the finding
before the methodology.
5. If a query returns zero rows, say so explicitly. Never guess or
fabricate numbers.That fourth instruction — finding before methodology — sounds like a small stylistic note, but it's the difference between a report that reads like an engineer wrote it and one that reads like an analyst wrote it. Busy stakeholders want "signups dropped 18% and it correlates with the pricing page change" as sentence one, not a recap of the SQL joins used to get there.
Turning rows into charts without hallucinated numbers
The render_chart tool is worth dwelling on because it's the step where a lot of agent implementations quietly introduce bugs. The trap is letting the model describe the chart in prose and having a downstream step "interpret" that description into a plot — that's where numbers get invented. Instead, force the model to pass the actual row data it got back from run_query straight into the chart tool as structured input. The model never re-types numbers from memory; it just forwards the JSON it already has.
Here's a minimal implementation using matplotlib that takes exactly that structured input and returns a base64 image the model never has to see or reason about further:
import io
import base64
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
def render_chart(chart_type: str, x_field: str, y_field: str, rows: list, title: str = ""):
xs = [row[x_field] for row in rows]
ys = [row[y_field] for row in rows]
fig, ax = plt.subplots(figsize=(7, 4))
if chart_type == "bar":
ax.bar(xs, ys, color="#3b6ef0")
elif chart_type == "line":
ax.plot(xs, ys, marker="o", color="#3b6ef0")
elif chart_type == "scatter":
ax.scatter(xs, ys, color="#3b6ef0")
else:
raise ValueError(f"Unsupported chart type: {chart_type}")
ax.set_xlabel(x_field)
ax.set_ylabel(y_field)
ax.set_title(title or f"{y_field} by {x_field}")
plt.xticks(rotation=45, ha="right")
fig.tight_layout()
buffer = io.BytesIO()
fig.savefig(buffer, format="png", dpi=120)
plt.close(fig)
buffer.seek(0)
return base64.b64encode(buffer.read()).decode("utf-8")Because rows is literally the array of dicts that came back from the database, there's no path for the model to insert a number that isn't grounded in the query result. If you're building a chat UI, you can then send that base64 PNG back to the frontend and render it inline next to the agent's text summary, or if you want the chart to stay interactive, swap matplotlib for a spec-based library like Vega-Lite and let the frontend render the spec with a JS library instead of a static image.
Handling ambiguous questions and multi-step reasoning
Real stakeholder questions are rarely as clean as "what were total sales in March." More often you get "why did revenue dip last week" — a question that requires the agent to decide, on its own, what "dip" means, what time window "last week" covers, and which of several plausible causes to check first.
This is where letting the model run multiple tool calls in sequence within one turn really pays off. A good trace looks like this:
list_tablesto see what's available (orders, customers, marketing_spend, page_events)run_queryto get daily revenue for the last 30 days, confirming there is in fact a diprun_queryagain to check whether order count dropped or average order value dropped (these have very different root causes)run_querya third time to check marketing_spend for the same window, since a dip often correlates with a paused ad campaignrender_chartto plot daily revenue with the dip highlighted- A final text summary connecting the dots
You don't have to hand-script this sequence. If your system prompt establishes the analyst's investigative mindset ("don't stop at the first number, check adjacent tables for correlated changes before concluding"), a capable model will chain these calls on its own. What you do need to guard against is runaway loops — cap the number of tool-call rounds (10-12 is usually generous) and, if the cap is hit, have the agent summarize its findings so far rather than silently failing.
MAX_TOOL_ROUNDS = 12
def run_analyst_agent(user_question: str):
messages = [{"role": "user", "content": user_question}]
for round_num in range(MAX_TOOL_ROUNDS):
response = client.messages.create(
model="claude-sonnet-4-5",
max_tokens=2000,
system=ANALYST_SYSTEM_PROMPT,
tools=tools,
messages=messages,
)
messages.append({"role": "assistant", "content": response.content})
if response.stop_reason != "tool_use":
return response
messages.append({
"role": "user",
"content": [
{
"type": "tool_result",
"tool_use_id": block.id,
"content": execute_tool(block.name, block.input),
}
for block in response.content if block.type == "tool_use"
],
})
return client.messages.create(
model="claude-sonnet-4-5",
max_tokens=1000,
system="Summarize what you've found so far. You are out of tool budget.",
messages=messages,
)Guardrails that separate a demo from something you'd trust
A weekend hackathon version of this agent stops after the code above runs once against a sample database. A version you'd actually let real employees query in production needs a few more layers.
- Row limits on every query. Append a hard
LIMIT 1000to any query the model writes that doesn't already have one, so a mistakenSELECT * FROM eventson a billion-row table doesn't return gigabytes of data or blow up your context window. - Query cost estimation. On Postgres, run
EXPLAINbeforeEXPLAIN ANALYZEand reject queries whose estimated cost exceeds a threshold, rather than discovering the problem after the query has already been running for two minutes. - PII-aware column filtering. If your
list_tablestool exposes acustomerstable with anemailorssncolumn, either exclude those columns from the schema the model sees, or add a system prompt rule that the agent must neverSELECTraw PII and should aggregate instead (count of customers, not their emails). - Audit logging. Log every SQL statement the agent executes, tagged with the user who asked the question. When someone eventually asks "how did the agent get that number," you want a paper trail, not a shrug.
- Per-user rate limits. Cap how many queries a single user's session can trigger per minute. This protects both your database and your API budget from a user who starts asking rapid-fire follow-up questions.
- A "confirm before executing" mode for expensive queries. If a query's estimated cost is high, have the agent show the SQL to the user and ask for confirmation before running it, rather than silently executing.
None of these are exotic. They're the same instincts a competent backend engineer already has about any user-facing endpoint that touches a database — the agent doesn't get a pass on them just because an LLM is generating the query instead of a developer typing it by hand.
Testing the agent like you'd test any data pipeline
It's tempting to "test" an agent by chatting with it a few times and eyeballing the answers. That's necessary but nowhere near sufficient. Build an evaluation set of 20-30 real questions with known-correct answers computed independently (by hand, or with a query you trust), and run the agent against all of them whenever you change the system prompt or swap models.
eval_cases = [
{
"question": "What was total revenue in Q1 2026?",
"expected_value": 482_910.55,
"tolerance": 0.01,
},
{
"question": "Which product category had the most returns last month?",
"expected_value": "Electronics",
"tolerance": None,
},
]
def score_case(case, agent_output):
if case["tolerance"] is not None:
extracted = extract_number(agent_output)
return abs(extracted - case["expected_value"]) <= case["tolerance"] * case["expected_value"]
return case["expected_value"].lower() in agent_output.lower()Run this suite after every prompt change. Text-to-SQL agents are notoriously sensitive to small wording changes in the system prompt — a tweak meant to make summaries punchier can quietly make the model skip a GROUP BY it used to always include. An eval suite catches that in seconds instead of a stakeholder catching it in a meeting three weeks later.
Where to go from here
What we've built here — schema discovery, a locked-down SQL execution tool, structured chart rendering, an agentic loop with a tool-call budget, and an eval harness — is a complete, defensible pattern for a data analyst agent. It's also, not coincidentally, a template for building almost any tool-using agent: narrow tools with hard boundaries, a loop that keeps calling the model until it stops requesting tools, and evaluation that treats correctness as non-negotiable rather than "close enough."
The gap between this and a genuinely production-grade internal analytics tool is mostly about scale and polish — connection pooling, streaming partial results back to the UI, handling multiple database dialects, adding caching for repeated questions — but the architecture doesn't change. Get the tool boundaries and the loop right, and everything else is refinement.
If you want to build this exact agent step by step, with a real Postgres database, real chart rendering, and a full eval suite reviewed line by line, that's precisely what we walk through inside 30 Days of Hermes Agent — our hands-on course on teachyou.ai where you build progressively more capable agents, including this data analyst pattern, from first principles through production hardening.
BootcampA 30-day guided bootcamp: build, harden and ship a production autonomous agent from scratch.
AI AgentsUnderstand how AI agents really work: the loop, the tools, the memory, and why most agent projects fail.