Beyond the generic agent
In the previous guide, we connected Claude Code and Codex to BigQuery. That gives you a general-purpose agent that can query your warehouse. It works, but it hits limits quickly: it doesn't know your business metrics, it forgets what it learned last session, and when it produces a wrong answer, you have no way to trace why.
The next step is to move from a general-purpose LLM with database access to a set of specialized analytics agents, each designed for a specific type of investigation, with managed context, structured traces, and observability built in from the start.
This guide assumes you have a working BigQuery connection via the bq CLI, as described in the previous post. The patterns here apply to both Claude Code and Codex, and to programmatic agent frameworks like LangGraph and the Claude Agent SDK.
Agent specialization
A generic "ask anything about our data" agent sounds appealing but performs poorly in practice. It has too little context about any one domain to be reliable. This matches findings from multi-agent systems research: specialized agents consistently outperform generalists on domain-specific tasks (Wu et al., "AutoGen," 2023). Specialized agents are the opposite: narrowly scoped, deeply contextualized, and much more accurate within their domain.
Why specialize
Consider the difference between asking a generic agent "why did churn increase?" versus asking a churn-specialized agent the same question. The generic agent has to figure out what table churn lives in, what the definition of churn is, what time period matters, and what constitutes "increase." The specialized agent already knows all of this, it's baked into its instructions.
Specialization buys you three things:
- Accuracy: the agent has the right metric definitions, knows which tables to use, and understands the business rules
- Speed: less exploration, fewer wrong turns, shorter context windows
- Reliability: the agent's behavior is more predictable and easier to validate
Designing agent personas
Each specialized agent needs a persona: a system prompt or instruction file that defines its domain, its data access patterns, its metric definitions, and its analytical approach. Here are four agents that cover most analytics needs.
Example: Revenue Analyst agent
Here's what a specialized agent definition looks like in practice. This example uses a CLAUDE.md file for Claude Code, but the same content works as a system prompt for the Claude Agent SDK or as an instructions.md for Codex.
# Revenue Analyst Agent
You are a revenue analyst. Your job is to investigate revenue-related
questions using BigQuery. You have deep knowledge of SaaS revenue
metrics and our specific data model.
## Your data access
- `my-project.revenue.invoices`, line-level invoice data
- `my-project.revenue.subscriptions`, subscription lifecycle
- `my-project.revenue.plans`, plan definitions and pricing
- `my-project.customers.accounts`, account metadata and segments
## Metric definitions (use these exactly)
- **MRR**: SUM of active subscription monthly amounts, excluding trials
- **ARR**: MRR * 12
- **Net Revenue Retention**: (Starting MRR + Expansion - Contraction
- Churn) / Starting MRR, measured over trailing 12 months
- **Gross Revenue**: invoices.amount_cents / 100 (USD)
- **Net Revenue**: invoices.amount_cents - invoices.refund_cents / 100
## Investigation methodology
1. Always start by confirming the time period and segment scope
2. Pull the headline metric first, then decompose
3. Compare against prior period AND plan/budget
4. Segment by geo, plan tier, and customer size
5. Identify the top 3 drivers of any variance
6. Check data freshness before reporting (max_date in invoices)
## Query conventions
- Use standard SQL: --use_legacy_sql=false
- Always add: --label=agent:revenue-analyst
- Always add: --maximum_bytes_billed=2000000000
- Filter by date on partitioned tables
- Use LIMIT 200 for exploratory queriesThe key insight is that the metric definitions are not suggestions, they are the source of truth. When the agent is told that Net Revenue means amount_cents - refund_cents, it won't accidentally use gross revenue. This eliminates the most common class of semantic errors, which the BIRD benchmark (Li et al., 2023) identifies as the leading cause of text-to-SQL failures in real-world databases.
Routing between agents
With multiple specialized agents, you need a way to route questions to the right one. In CLI tools like Claude Code, the simplest approach is separate project directories, each with their own CLAUDE.md.
analytics-agents/
├── revenue/
│ ├── CLAUDE.md # Revenue Analyst persona
│ └── queries/ # Saved query templates
├── churn/
│ ├── CLAUDE.md # Churn Investigator persona
│ └── queries/
├── product/
│ ├── CLAUDE.md # Product Analyst persona
│ └── queries/
└── operations/
├── CLAUDE.md # Operations Monitor persona
└── queries/For programmatic agents (Claude Agent SDK, LangGraph), you can build a router agent that reads the incoming question, classifies it, and delegates to the appropriate specialized agent. The router itself doesn't need data access, it only needs to understand which domains exist.
from claude_agent_sdk import Agent, tool
router = Agent(
model="claude-sonnet-4-6",
instructions="""You are a routing agent. Classify the incoming
question into one of: revenue, churn, product, operations.
Delegate to the appropriate specialized agent. If the question
spans multiple domains, start with the primary domain and
note which secondary agents should be consulted."""
)
revenue_agent = Agent(
model="claude-sonnet-4-6",
instructions=open("revenue/CLAUDE.md").read(),
tools=[bigquery_tool]
)
churn_agent = Agent(
model="claude-sonnet-4-6",
instructions=open("churn/CLAUDE.md").read(),
tools=[bigquery_tool]
)Disk-based input and output
An agent that reads a question from a chat prompt and writes an answer back to the chat is limited in what it can produce. The analysis output is trapped in a conversation thread. You can't share it, version it, or feed it into another system. The input is equally constrained: you can't easily pass a complex brief, a CSV of accounts to investigate, or the output of a previous analysis.
The fix is to treat the filesystem as the agent's interface. Questions go in as input files. Answers come out as structured output files. This turns the agent from a chatbot into a pipeline component.
Input: the analysis brief
Instead of typing a question into the chat, write a brief file that the agent reads at the start of each session. This lets you include more context than a single prompt, and it creates a record of what was asked.
# Analysis Brief: Q1 Revenue Variance
## Question
Why did Q1 2026 revenue come in $1.2M below plan? Which segments
drove the shortfall, and were there any offsetting gains?
## Scope
- Time period: 2026-01-01 to 2026-03-31
- Compare against: Q1 plan (in revenue.quarterly_plan table)
and Q1 2025 actuals
- Segments to check: geo, plan_tier, customer_size
## Specific accounts to investigate
See input/flagged-accounts.csv for 12 accounts that the
sales team has flagged as at-risk.
## Output requirements
- Executive summary (3-5 sentences)
- Variance waterfall by segment
- Top 10 accounts driving the shortfall
- Recommended actionsThe brief can reference other input files like CSVs, prior analysis outputs, or lists of entities to investigate. Tell the agent about this pattern in its instructions:
## Input
- Always start by reading input/brief.md for the current analysis
- Check input/ for any supplementary files (CSVs, prior reports)
- If input/brief.md doesn't exist, ask the user what to investigateOutput: structured deliverables
The agent should write its output to disk in a structured format. This makes the results shareable, versionable, and machine-readable.
## Output
Write all output to the output/ directory:
- output/summary.md, executive summary in markdown
- output/findings.json, structured findings with supporting data
- output/queries.sql, every SQL query you ran, in order
- output/data/, any intermediate CSVs or result sets
The summary.md should be readable by a non-technical stakeholder.
The findings.json should be parseable by another agent or script.
The queries.sql should be re-runnable to reproduce the analysis.This pattern is powerful because the outputs become inputs for the next stage. A revenue analyst's findings.json can be read by a churn investigator. An operations monitor's anomaly report can trigger a product analyst investigation. The filesystem becomes the coordination layer between specialized agents.
# 1. Revenue analyst investigates the shortfall
cd revenue/ && claude
# 2. Feed revenue findings into churn analysis
cp revenue/output/findings.json churn/input/revenue-context.json
cd churn/ && claude
# 3. Combine both outputs for the exec report
cp revenue/output/summary.md exec-report/input/revenue.md
cp churn/output/summary.md exec-report/input/churn.md
cd exec-report/ && claudeCSV as interface
For bulk analysis, such as investigating a list of accounts, evaluating a set of campaigns, or scoring a pipeline, CSV files are the natural interface. The agent reads a CSV of entities and writes back an enriched CSV with its findings.
## Bulk analysis mode
If input/accounts.csv exists, switch to bulk mode:
1. Read the CSV to get the list of account IDs
2. For each account, run the standard health check queries
3. Write results to output/account-analysis.csv with columns:
account_id, account_name, mrr, health_score, churn_risk,
key_finding, recommended_action
4. Write output/summary.md with aggregate statistics and
the top 5 accounts requiring immediate attentionBuilding domain context
A specialized agent is only as good as the domain context you give it. The CLAUDE.md file is the foundation, but deep domain context goes beyond metric definitions. It encodes analytical methodology, business rules, common pitfalls, and institutional knowledge that would otherwise take a new analyst months to learn.
Metric hierarchies
Don't just define metrics in isolation. Show the agent how they relate to each other. This mirrors the approach of formal semantic layers like Cube and dbt MetricFlow, which encode metric hierarchies as machine-readable definitions. Even without a formal semantic layer, providing this structure enables the agent to decompose a top-level metric into its components without being told to.
## Metric hierarchy
ARR ($)
├── New Business ARR (new subscriptions created this period)
├── Expansion ARR (upgrades + add-ons from existing customers)
├── Contraction ARR (downgrades from existing customers, negative)
└── Churned ARR (lost subscriptions, negative)
Net Revenue Retention = (Start ARR + Expansion - Contraction - Churn) / Start ARR
## Decomposition rules
When asked "why did ARR change?", always decompose into the four
components above. When asked about any single component, decompose
by: geo → plan_tier → customer_size → individual accounts.
Never report a top-level metric movement without decomposing it
at least one level. "ARR dropped 8%" is not an answer.
"ARR dropped 8%, driven primarily by $600K in Enterprise churn
concentrated in EMEA" is an answer.Business calendar and seasonality
Agents don't know that your fiscal year starts in February, that Q4 is always high because of annual renewals, or that the sales team runs a discount campaign every March. This context prevents false alarms.
## Business calendar
- Fiscal year starts February 1
- Q4 (Nov-Jan) is always the highest revenue quarter due to
annual renewal clustering
- March typically shows a spike from the spring promo campaign
- July-August has lower new business due to European holidays
## Known data quirks
- The `segment_id` column was replaced by `segment_v2_id` on
2025-06-15. Always use segment_v2_id for analysis after that date.
- Trial subscriptions (plan_type = 'trial') should be excluded
from all revenue metrics but included in funnel metrics.
- The `deleted_at` column on accounts uses soft deletes. Always
filter WHERE deleted_at IS NULL unless specifically investigating
churned accounts.
- Invoice amounts in the EU region were double-counted between
2025-03-01 and 2025-03-15 due to a billing system bug. Exclude
this period or apply the correction factor of 0.5 for EU invoices.Table context files
The most effective way to help an agent understand your data is to create a context file for each important table. This goes far beyond column descriptions. It captures how the table is used, what's tricky about it, and how it connects to the rest of the model.
# revenue.invoices
## Purpose
Line-level invoice data for all customer billing. This is the
source of truth for recognized revenue. One row per invoice line
item, not per invoice. An invoice can have multiple line items.
## Key columns
| Column | Type | Notes |
|--------|------|-------|
| id | STRING | Primary key |
| account_id | STRING | FK → customers.accounts.id |
| invoice_id | STRING | Groups line items into an invoice |
| amount_cents | INT64 | Gross amount in USD cents |
| refund_cents | INT64 | Refund amount. Usually 0. |
| net_cents | INT64 | amount_cents - refund_cents |
| currency | STRING | Always 'USD' (converted at billing) |
| date | DATE | Invoice date, partition key |
| recognized_at | TIMESTAMP | When revenue was recognized |
| plan_tier | STRING | 'starter', 'growth', 'enterprise' |
| geo | STRING | Billing region: NA, EMEA, APAC |
## Common pitfalls
- **Do NOT sum amount_cents to get net revenue.** Use net_cents
or subtract refund_cents explicitly.
- **Rows are per line item, not per invoice.** COUNT(DISTINCT
invoice_id) for invoice count, not COUNT(*).
- **Soft deletes exist.** Filter `WHERE deleted_at IS NULL`
unless specifically investigating cancelled invoices.
- **EU double-count bug:** Invoices with geo='EMEA' between
2025-03-01 and 2025-03-15 were duplicated. Apply a DISTINCT
on invoice_id or exclude this period.
## Partitioning
Partitioned by `date` (DAY). Always include a date filter
to avoid full table scans.
## Joins
- accounts: `JOIN customers.accounts a ON invoices.account_id = a.id`
- subscriptions: `JOIN revenue.subscriptions s ON invoices.subscription_id = s.id`
- Do NOT join directly to segments, go through accounts.
## Row count
~4.2M rows as of March 2026. ~150K new rows per month.Create one of these for every table the agent will query. Store them in a context/tables/ directory and reference them in the agent's instructions:
## Table documentation
Before querying any table, read its context file:
context/tables/{table_name}.md
These files contain column definitions, common pitfalls, join
patterns, and known data issues. Do not guess at column meanings
when a context file exists.This approach scales well. When the schema changes, you update one context file. When a new data quality issue is discovered, you add it to the relevant table's pitfalls section. The agent always reads the current version.
Relationship maps
Beyond individual tables, the agent needs to understand how tables connect. A relationship map prevents wrong joins, which are the second most common source of incorrect results after semantic misinterpretation.
# Data relationships
## Core entity graph
accounts (1) ←→ (N) subscriptions ←→ (N) invoices
accounts (1) ←→ (N) contacts
accounts (1) ←→ (N) opportunities
accounts (1) ←→ (N) support_tickets
subscriptions (1) ←→ (1) plans
## Join rules
- Always join through accounts for cross-domain analysis
- NEVER join invoices directly to support_tickets (no FK)
- Use account_id as the bridge between revenue and support
- The segments table uses segment_v2_id (not segment_id)
- Opportunities use close_date for timing, not created_at
## Grain warnings
- invoices: one row per LINE ITEM, not per invoice
- events: one row per event (can be 100+ per session)
- activities: one row per sales touch (calls, emails, meetings)
- sessions: one row per user session (aggregated from events)Reference queries
The fastest way to make an agent accurate is to give it validated queries. These aren't templates, they're the exact queries your analysts use, tested and trusted. The agent can adapt them rather than writing from scratch.
-- Validated MRR by segment query
-- Owner: analytics team | Last validated: 2026-03-28
-- Note: excludes trials, uses segment_v2_id, handles soft deletes
SELECT
s.segment_v2_id as segment,
DATE_TRUNC(sub.period_start, MONTH) as month,
SUM(sub.monthly_amount_cents) / 100 as mrr
FROM `my-project.revenue.subscriptions` sub
JOIN `my-project.customers.accounts` a ON sub.account_id = a.id
JOIN `my-project.customers.segments` s ON a.segment_v2_id = s.id
WHERE sub.status = 'active'
AND sub.plan_type != 'trial'
AND a.deleted_at IS NULL
AND sub.period_start >= '2025-01-01'
GROUP BY segment, month
ORDER BY month, mrr DESC## Reference queries
Before writing a query from scratch, check the queries/ directory
for a validated version. Adapt existing queries when possible
rather than writing new ones. If you write a new query that works
well, save it to queries/ with a comment header noting the date
and what it does.Tuning the agent for better analysis
The default behavior of an LLM when asked a data question is to generate SQL, run it, and report the result. That's step one of an investigation, not the whole thing. Research on chain-of-thought prompting (Wei et al., 2022) and tree-of-thought reasoning (Yao et al., 2023) shows that structured reasoning steps dramatically improve LLM performance on complex analytical tasks. Prompt tuning means shaping the agent's analytical behavior so it conducts a proper investigation rather than a quick lookup.
Force decomposition
The most impactful prompt instruction is requiring the agent to decompose every finding. Without this, agents tend to stop at the first number they find.
## Analytical standards
### Decomposition rule
Never report a metric without decomposing it. If revenue dropped,
show WHICH segments drove the drop. If churn increased, show
WHICH cohorts are churning. Always go at least two levels deep:
top-line → segment → sub-segment or individual driver.
### Comparison rule
Never report a number in isolation. Every metric must be compared
against at least one reference point:
- Prior period (MoM, QoQ, YoY)
- Plan/budget
- Cohort benchmark
"Revenue was $2.1M" is incomplete. "Revenue was $2.1M, down 8% QoQ
and 3% below plan" is a finding.
### Anomaly rule
If any segment shows a movement larger than 2x the overall trend,
flag it explicitly and investigate. This is where the insight
usually lives.Enforce a thinking structure
Left to its own devices, an agent will jump straight to querying. Better results come from requiring it to think before it queries and reflect after each step. Agents that evaluate their own outputs and revise their approach consistently outperform those that don't (Shinn et al., 2023).
## Investigation protocol
For every analysis, follow this structure:
### 1. Frame the question (before any queries)
- Restate the question in your own words
- Identify what "answering" this question looks like
- List the metrics and dimensions you'll need
- Identify potential confounding factors
### 2. Plan the investigation
- Write out 3-5 steps you plan to take
- Identify which tables and columns each step needs
- Note any data quality risks
### 3. Execute with reflection
- After each query, state: what did I learn? Does this change
my plan? What should I look at next?
- If a result surprises you, investigate the surprise before
moving on. Surprises are where the insights live.
### 4. Synthesize
- Do not just list your findings chronologically
- Organize by importance: lead with the primary driver
- Quantify everything: "significant drop" → "$340K decline"
- State your confidence level and what would change it
- List what you did NOT investigate that might matterTeach the agent to doubt itself
Agents are overconfident by default. LLMs frequently assign high confidence to incorrect outputs, particularly in data-intensive tasks (Kadavath et al., 2022). The most dangerous failure mode is a confidently wrong answer. Add explicit instructions to self-check.
## Self-verification checklist
Before presenting your final answer, run these checks:
1. **Sanity check the numbers.** Does total revenue roughly match
what you'd expect? If MRR is $50M, that implies ~$600M ARR.
Does that seem right for the company size?
2. **Check the denominator.** If you're computing rates or
percentages, verify the base number. A "50% churn rate" might
just mean you filtered out most of the accounts.
3. **Verify joins didn't multiply rows.** After any JOIN, check
if the row count is what you expected. A many-to-many join is
the most common source of inflated numbers.
4. **Cross-validate with a different approach.** If you computed
revenue by summing invoices, verify by checking subscription
amounts. If the numbers disagree by more than 5%, investigate.
5. **Check for NULL handling.** Did any key columns have NULLs
that were silently excluded? Report the exclusion.Output formatting instructions
How the agent formats its answer matters as much as whether the answer is correct. A wall of text with buried numbers is useless to a stakeholder. Shape the output format explicitly.
## Output format for summaries
Structure every analysis output as:
### Headline (1 sentence)
The single most important finding. Lead with the number.
Example: "Q1 revenue missed plan by $1.2M (8%), driven primarily
by Enterprise churn in EMEA."
### Key findings (3-5 bullets)
Each bullet: metric + comparison + driver.
Quantify everything. No adjectives without numbers.
### Supporting detail
Tables and breakdowns that support the key findings.
Use markdown tables for structured data.
### Caveats and limitations
What you didn't check. What data quality issues you found.
What assumptions you made. What would change your conclusion.
### Recommended next steps
What should the reader do with this information?
Be specific: "investigate accounts X, Y, Z" not "look into churn."Managing context
The biggest practical challenge with analytics agents isn't getting them to run SQL. It's managing what they know. Grounding LLMs with retrieved context dramatically reduces hallucination (Lewis et al., 2020). Context management determines whether your agent produces a useful answer or an expensive hallucination.
The three layers of context
Static context: the instruction file
We covered this in the specialization section. The key principle is to be explicit and precise. Don't tell the agent "revenue is in the invoices table." Tell it the exact column, the unit, and the common pitfalls.
Session context: the warm-up phase
Before an agent starts investigating a question, it should orient itself to the current state of the data. Add a warm-up routine to your agent instructions.
## Session warm-up (run before every investigation)
Before answering any question, run these checks silently:
1. Check data freshness: SELECT MAX(date) FROM each primary table
2. Check row counts for the relevant time period
3. Verify that key joins still work (accounts ↔ subscriptions)
4. Note any anomalies (sudden drops in row counts, NULL spikes)
If any check fails, report the data issue BEFORE answering the
question. Do not proceed with stale or broken data.This warm-up costs a few seconds and a handful of small queries, but it prevents the agent from confidently analyzing data that was last updated three days ago.
Investigation context: managing the window
Long investigations accumulate context: query results, intermediate tables, dead-end hypotheses. This is where agents start to degrade. The context window fills up, earlier findings get pushed out, and the agent starts repeating queries it already ran.
Two strategies for managing this:
- Summarize aggressively. After each query, instruct the agent to extract the key finding and discard the raw result. "Revenue dropped 12% in EMEA Enterprise" is better context than 500 rows of query output.
- Write intermediate findings to files. Have the agent save structured findings to disk as it goes, then reference the files rather than holding everything in memory.
## Context management rules
- After each query, summarize the key finding in 1-2 sentences
- Do NOT paste full query results into your reasoning. Extract
the relevant numbers and discard the rest.
- For investigations with more than 5 queries, write a running
summary to ./investigation-log.md with:
- Question being investigated
- Each step taken (query + finding)
- Current hypothesis
- Next planned step
- If you lose track of what you've already checked, read
investigation-log.md before proceedingWhat's next
The patterns in this guide get you to a working specialized agent. Two topics deserve more space than a section at the end of a long post.
Advanced context management covers cross-session memory: building a knowledge base that persists across investigations, handling schema drift over time, and context budgeting when investigations run long.
Observability and traceability covers structured trace files, query-level tagging for cost attribution, BigQuery's native INFORMATION_SCHEMA for agent performance monitoring, and alerting on the failure modes that matter most.
Start here, then build in that order. The trace files especially pay for themselves from the first investigation.
References & further reading
- Wu, Q., et al. (2023). "AutoGen: Enabling Next-Gen LLM Applications via Multi-Agent Conversation." arXiv:2308.08155.
- Li, J., et al. (2023). "Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQL (BIRD)." NeurIPS 2023.
- Wei, J., et al. (2022). "Chain-of-Thought Prompting Elicits Reasoning in Large Language Models." NeurIPS 2022.
- Shinn, N., et al. (2023). "Reflexion: Language Agents with Verbal Reinforcement Learning." NeurIPS 2023.