Build a Self-Service Analytics Agent with Claude: Steps

Architecture diagrams are easy to admire and hard to build. This post is the opposite: a hands-on, do-this-then-that walkthrough for standing up a working self-service analytics agent on Claude, the kind a business user can ask "what were refunds last month?" and get a real, query-backed answer. I'll assume you have a warehouse (Postgres, BigQuery, Snowflake — the pattern is the same), the Claude Agent SDK or an MCP-capable client, and read access you can lock down. We'll build it in the order you should actually build it, because doing these steps out of sequence is how projects stall.

Step 1: Lock down a read-only data path before anything else

Do not start with prompts. Start with permissions. Create a dedicated database role that can SELECT from a specific schema and nothing else — no writes, no DDL, no access to PII tables you have not vetted. If your warehouse supports row-level security or column masking, turn it on now. This single step removes an entire class of disasters: even a maximally confused model cannot drop a table it has no permission to touch.

Wrap that role behind a thin execution service — a function that accepts a SQL string, enforces a statement timeout and a row cap, rejects anything that is not a single read statement, and returns rows plus metadata. This service, not the model, owns your credentials. Test it by hand with a few good and bad queries before Claude ever enters the picture. When the foundation is a hardened, read-only execution boundary, everything you build on top inherits that safety.

Step 2: Expose schema as a tool, not a wall of text

Your second move is to give Claude a way to learn the schema on demand. Define a list_tables tool and a describe_table tool. The first returns table names with one-line business descriptions; the second returns columns, types, and — crucially — curated notes like "amount_cents is in cents, divide by 100" or "status='void' means refunded." Do not paste your entire 400-table catalog into the system prompt. Let the agent fetch only what each question needs. This keeps context lean and makes the agent scale to large warehouses.

flowchart TD
  A["User: refunds last month?"] --> B["Claude plans approach"]
  B --> C["Tool: list_tables"]
  C --> D["Tool: describe_table(payments)"]
  D --> E["Claude drafts read-only SQL"]
  E --> F{"Validator: single SELECT & allow-listed?"}
  F -->|No| G["Return error; Claude rewrites"]
  G --> F
  F -->|Yes| H["Execute with timeout & row cap"]
  H --> I["Claude summarizes & shows SQL"]

That diagram is the actual control flow you are wiring. Each box is a tool call or a model turn. The loop between the validator and the model is what you will spend the most time tuning, because it is where real questions go to either get answered or get rejected gracefully.

Step 3: Write the system prompt as a job description

Now write the prompt — and treat it as a contract, not a vibe. Tell Claude exactly what it is: a careful analytics assistant that answers business questions by querying a governed warehouse. Spell out the non-negotiables: always discover schema before writing SQL; emit only a single read-only statement; never invent column or table names; if a question is ambiguous, ask one clarifying question instead of guessing; always show the SQL it ran. Give it your dialect (Postgres vs. BigQuery syntax differs) and your fiscal calendar if dates matter.

The mistake here is being vague. "Be helpful and accurate" does nothing. "When the user says 'last month,' interpret it as the previous full calendar month in America/New_York, and state the date range you used" produces consistent behavior. Write the prompt the way you would brief a new analyst on their first day: concrete rules, worked examples, and the failure modes you have seen before.

Step 4: Add the verification turn

A single query is rarely the whole answer. After execution, have the agent inspect what came back before it speaks. Did the query return zero rows when you expected some? Are there suspicious nulls? Does the total reconcile against a known figure? You can encode this as an instruction to run a quick sanity check — for example, comparing a filtered sum against an unfiltered one — and to flag mismatches rather than reporting blindly. This verification turn is cheap and catches the embarrassing errors that erode trust fastest.

In code, this means your loop does not stop at the first tool result. The execution tool returns rows and metadata (row count, execution time, columns), and the model decides whether to answer or to run one more validating query. Letting the agent take a second look is the difference between "refunds were $0 last month" (because of a silent join bug) and "I found no refund rows; that looks unusual, so I checked the payments table and confirmed the date filter — here is the SQL."

Step 5: Render the answer with its evidence

Finally, shape the output for a human. The agent should return three things together: a one- or two-sentence plain-English answer, the supporting numbers (a small table or a chart), and the exact SQL it executed. Putting the SQL right under the answer is not clutter — it is what lets a skeptical analyst trust the result in five seconds instead of re-deriving it. If you have a charting tool, expose it as another tool call so the model can request a bar or line chart when the shape of the data warrants one.

Once these five steps work for one question, expand by feeding real user questions through the system and watching where it stumbles. Every failure is a signal: a missing schema note, an ambiguous synonym, a dialect quirk. Fixing those in the semantic notes and the prompt — not in the model — is how a rough prototype becomes a dependable internal product over a couple of iterations.

Frequently asked questions

Which Claude model should I start with?

Begin with a mid-tier model like Sonnet for the planning and SQL generation; it is fast and capable enough for most analytics questions. Reserve Opus for genuinely gnarly multi-step analyses, and consider Haiku for cheap, high-volume classification steps if you add routing. Optimize the model choice after the pipeline works, not before.

How do I stop runaway or expensive queries?

Enforce limits in the execution service, not the prompt. A statement timeout, a row cap, and a rejection of anything that is not a single SELECT will stop the vast majority of problem queries deterministically. The prompt can encourage good behavior, but the service must guarantee it.

Do I need MCP, or is the SDK enough?

Either works. MCP servers are ideal when you want your schema and query tools reusable across multiple Claude surfaces and other clients. The Agent SDK is great for a single tightly integrated app. Many teams start with SDK tools and graduate to MCP when reuse matters.

How do I handle ambiguous questions?

Instruct the agent to ask exactly one focused clarifying question when intent is unclear — for example, which date range or which definition of "active" the user means — rather than guessing. One good clarification beats a confidently wrong answer and trains users to phrase questions the system can serve.

Bringing agentic AI to your phone lines

CallSphere takes this same build-it-in-layers discipline to voice and chat: agents that answer every call, fetch live data through governed tools mid-conversation, and book work 24/7. Try it at callsphere.ai.

---

Source & attribution: This is an independent, original explainer inspired by Anthropic's coverage on the Claude blog. Claude, Claude Code, Claude Cowork, Claude Opus, and the Model Context Protocol are products and trademarks of Anthropic. CallSphere is not affiliated with or endorsed by Anthropic.