Multi-Agent Systems for Data Analysis: How to Automate Reports

Picture a Monday ritual at Acme Shop. Someone opens four dashboards, copies numbers into a document, and writes three paragraphs about what changed. It eats a morning, nobody enjoys it, and the numbers occasionally come from the wrong date range. This is the exact job people hope multi-agent systems will take over.

The hope is reasonable, and the risk is real. A chain of language models that writes a confident report with one wrong number is worse than no report at all. Therefore this article is as much about guardrails as about agents. You will build a four-role pipeline in plain Python: a planner, a SQL analyst, a reviewer, and a writer.

You already have the parts. The text-to-SQL article gave you a safe way to turn a question into rows, and the RAG article gave you grounded answers from documents. This article does not teach either again. It shows how to coordinate them, where the coordination breaks, and when a plain script beats an agent.

Workflow or Agent: Decide Before You Build

Anthropic’s guide Building Effective Agents draws a useful line. Workflows are systems where models and tools follow predefined code paths. Agents are systems where the model directs its own process. The guide recommends starting with the simplest solution and adding complexity only when it demonstrably improves outcomes.

A weekly report is a near-perfect workflow case. The questions barely change: sessions, signups, add-to-cart rate, purchases, top pages. You know the order. Consequently a script that runs five fixed queries and asks a model to write the summary is cheaper, faster, and easier to debug than any agent.

So why use multiple roles at all? Because the interesting reports are not fixed. “Why did the checkout conversion fall last week?” requires choosing the next query based on the last result. That is where a planner earns its place. In this article the planner proposes questions, but your code still controls the loop.

The Four Roles

Each role has one job, one input shape, and one output shape. Narrow roles are easier to test, and a failure points to a single place.

Notice what the writer never receives: raw tables, unreviewed results, or the planner’s reasoning. Restricting its input is the single cheapest guardrail in the design.

goal + date range
      |
      v
  [Planner] -- JSON list of questions --+
                                        |
        +-------------------------------+
        v
  for each question (max 5):
     [SQL analyst] --> sql + rows        (text-to-SQL pipeline)
           |
           v
     [Reviewer] --fail--> back to analyst (max 2 revisions)
           |pass
           v
     verified finding
        |
        v
  [Writer] --> draft report --> number check --> human approval

Stack and Setup

The code uses Python 3.12+ and the ollama client for a local model. Set CHAT_MODEL to any instruction-tuned model of 7B to 14B parameters that you have pulled. Smaller models follow JSON instructions less reliably, which is why every model output below passes through validation.

The analyst role wraps the pipeline from the text-to-SQL article. To keep the example runnable on its own, the function run_sql_question is a stub that returns canned Acme Shop rows. Replace its body with a call into your own text-to-SQL code. The orchestration does not change.

In one test run with a local model, the planner asked three questions, two passed review, and the writer reported the third as missing instead of inventing it. Your run will differ, and that variance is the reason for the logging described later.

The Orchestrator in Full

The complete listing follows. It is long, but each part maps to one idea explained after it. Illustrative parts are marked in comments.

# report_agents.py  (pip install ollama)
import json
import os
import re
import time

import ollama

CHAT_MODEL = os.environ["CHAT_MODEL"]
MAX_QUESTIONS = 5
MAX_REVISIONS = 2
MAX_LLM_CALLS = 20          # hard budget per report run
ROW_CAP = 50


class BudgetExceeded(RuntimeError):
    pass


class Budget:
    def __init__(self, max_calls: int):
        self.max_calls, self.calls = max_calls, 0

    def spend(self):
        self.calls += 1
        if self.calls > self.max_calls:
            raise BudgetExceeded(f"more than {self.max_calls} LLM calls")


def llm_json(budget: Budget, system: str, user: str) -> dict:
    """Call the model, demand JSON, retry once on a parse failure."""
    for attempt in range(2):
        budget.spend()
        resp = ollama.chat(
            model=CHAT_MODEL,
            format="json",
            options={"temperature": 0},
            messages=[{"role": "system", "content": system},
                      {"role": "user", "content": user}],
        )
        try:
            parsed = json.loads(resp["message"]["content"])
            if isinstance(parsed, dict):
                return parsed
        except json.JSONDecodeError:
            pass
        user += "\n\nYour last reply was not a JSON object. Reply with JSON only."
    raise ValueError("model did not return valid JSON twice")


# ---------- Role 1: planner ----------
PLANNER_SYSTEM = """You plan analytics questions for Acme Shop.
Reply with JSON: {"questions": ["...", "..."]}.
Ask 1 to 5 concrete questions that a SQL query on the events table can
answer. Only ask about session counts, signup counts and purchase counts.
Every question must name its date range. No other keys."""


def plan(budget, goal: str, start: str, end: str) -> list[str]:
    out = llm_json(budget, PLANNER_SYSTEM,
                   f"Goal: {goal}\nDate range: {start} to {end}")
    qs = out.get("questions")
    if not isinstance(qs, list) or not 1 <= len(qs) <= MAX_QUESTIONS:
        raise ValueError("planner returned an invalid question list")
    if not all(isinstance(q, str) and 0 < len(q) <= 300 for q in qs):
        raise ValueError("planner returned an invalid question")
    return qs


# ---------- Role 2: SQL analyst ----------
def run_sql_question(question: str) -> dict:
    """ILLUSTRATIVE STUB with canned rows. Replace with your text-to-SQL
    pipeline. It must use a read-only database role and return {"sql", "rows"}.
    The numbers below are made-up demo data, not measurements."""
    q = question.lower()
    where = "occurred_at >= '2025-09-22' AND occurred_at < '2025-09-29'"
    if "purchase" in q:
        return {"sql": "SELECT count(*) AS purchases FROM llm.events "
                       f"WHERE event_name = 'purchase_completed' AND {where}",
                "rows": [{"purchases": 412}]}
    if "signup" in q:
        return {"sql": "SELECT count(*) AS signups FROM llm.events "
                       f"WHERE event_name = 'signup_completed' AND {where}",
                "rows": [{"signups": 187}]}
    return {"sql": "SELECT count(DISTINCT session_id) AS sessions FROM llm.events "
                   f"WHERE {where}",
            "rows": [{"sessions": 9320}]}


# ---------- Role 3: reviewer ----------
REVIEWER_SYSTEM = """You review one analytics result.
Reply with JSON: {"verdict": "pass" or "fail", "issues": ["..."]}.
Fail if the SQL does not answer the question, ignores the stated date
range, or the rows look implausible. Do not rewrite the SQL."""


def review(budget, question: str, result: dict) -> dict:
    rows = result["rows"]
    # Deterministic checks run first. They are free and never hallucinate.
    if not rows:
        return {"verdict": "fail", "issues": ["query returned no rows"]}
    if len(rows) > ROW_CAP:
        return {"verdict": "fail", "issues": ["too many rows for a finding"]}
    out = llm_json(budget, REVIEWER_SYSTEM,
                   json.dumps({"question": question, "sql": result["sql"],
                               "rows": rows}, default=str))
    if out.get("verdict") not in ("pass", "fail"):
        return {"verdict": "fail", "issues": ["reviewer gave no verdict"]}
    return out


def analyze(budget, question: str) -> dict | None:
    """Analyst plus reviewer loop. Returns a verified finding or None."""
    for _ in range(MAX_REVISIONS + 1):
        result = run_sql_question(question)
        verdict = review(budget, question, result)
        if verdict["verdict"] == "pass":
            return {"question": question, **result}
        question = f"{question}\nPrevious attempt failed: {verdict.get('issues')}"
    return None


# ---------- Role 4: writer ----------
WRITER_SYSTEM = """You write a short weekly report for the Acme Shop team.
Use ONLY the findings provided. Every number must appear in a finding's
rows. Do not compute new numbers. If a finding is missing, say so.
Write in plain Markdown with one heading per finding."""


def numbers_in(text: str) -> set[str]:
    # A trailing full stop must not become part of the number ("412.").
    return set(re.findall(r"\d+(?:\.\d+)?", text.replace(",", "")))


def write(budget, findings: list[dict], unanswered: list[str]) -> str:
    budget.spend()
    resp = ollama.chat(
        model=CHAT_MODEL,
        options={"temperature": 0},
        messages=[
            {"role": "system", "content": WRITER_SYSTEM},
            {"role": "user", "content": json.dumps(
                {"findings": findings, "unanswered": unanswered}, default=str)},
        ],
    )
    draft = resp["message"]["content"].strip()
    allowed = numbers_in(json.dumps(findings, default=str))
    stray = numbers_in(draft) - allowed
    if stray:
        raise ValueError(f"draft contains numbers not in the data: {sorted(stray)}")
    return draft


# ---------- Orchestration ----------
def run_report(goal: str, start: str, end: str) -> str:
    budget = Budget(MAX_LLM_CALLS)
    started = time.time()
    questions = plan(budget, goal, start, end)
    findings, unanswered = [], []
    for q in questions:
        finding = analyze(budget, q)
        (findings if finding else unanswered).append(finding or q)
    draft = write(budget, findings, unanswered)
    with open("agent_runs.jsonl", "a", encoding="utf-8") as log:
        log.write(json.dumps({
            "goal": goal, "start": start, "end": end,
            "llm_calls": budget.calls, "seconds": round(time.time() - started, 1),
            "answered": len(findings), "unanswered": len(unanswered),
        }) + "\n")
    return draft


if __name__ == "__main__":
    print(run_report("Weekly commerce summary", "2025-09-22", "2025-09-29"))

What each guardrail does

The budget caps model calls per run. Without it, a reviewer that keeps failing an analyst that keeps retrying burns your machine for an hour. A hard stop turns an unbounded loop into a clear exception.

The JSON validation treats model output as untrusted input. The planner may return nine questions, a string, or an extra key. Code checks the shape and rejects anything else. The same rule you apply to events at the collector applies to agents.

The deterministic reviewer checks run before the model reviewer. An empty result set is a failure whether or not a model agrees. Checks that cost nothing should never wait behind checks that cost a call.

The number check in the writer step catches the most dangerous failure: a plausible but invented figure. The draft may only contain numbers found in the findings. The trade-off is real. If the writer wants to say “up 12 percent”, the percentage must already exist in the rows, so you have to compute it in SQL. That constraint is a feature, because arithmetic is where models slip. The check is also crude. It compares digits, so a draft that writes “22 September” passes, and a harmless “2” in a list marker can trip it. Tolerate a few false alarms, because a false alarm costs a rerun and a missed invented figure costs trust.

The Analyst Must Stay Read-Only

Agents amplify any permission you give them. The analyst role should reuse the llm_reader role and the llm views from the text-to-SQL article, with their grants, five second timeout, and read-only default. Do not create a second, broader role for the agents. That role sees no raw tables and no user_agent column, so a leaky prompt cannot expose them.

The multi-agent layer adds no database permissions. If an agent needs a new capability, you change the role or add a view deliberately, not the prompt. For the second layer of protection, PostgreSQL supports read-only transactions, described in the SET TRANSACTION documentation.

Cost and Latency: Count Calls, Not Agents

Every role is at least one model call. A run with four questions needs one planner call, at least one reviewer call per question, whatever the analyst pipeline spends per question, and one writer call. Revisions multiply this. The budget object in the listing gives you the exact number, and the agent_runs.jsonl log records it for every run.

Measure your own system before you promise anything. Run the report ten times, read the log, and note the median and worst-case call count and seconds. On a local model, cost is time and GPU load rather than money. Therefore the right question is whether the run finishes before the Monday meeting.

Two reductions pay off quickly. First, use the deterministic reviewer checks to avoid model calls. Second, cache analyst results for questions whose date range is closed. Last week’s purchase count will not change, so re-running it has no value.

Where Multi-Agent Systems Fail

Here is the honest list. These are the failures I would test for first in any reporting pipeline.

• Plausible wrong SQL. The query runs, returns a number, and answers a slightly different question. For example, it counts page_view events when you asked for unique visitors. A reviewer model often agrees with the analyst, because both share the same blind spots.
• Correlated errors. Using the same model for analyst and reviewer means they make the same mistakes. A second opinion from the same brain is not independent.
• Invented causes. The writer explains a traffic drop with “likely due to seasonality”. No finding says that. The number check does not catch causal claims, only figures.
• Silent scope drift. The planner asks about “last month” while you asked for last week. The date range must be a parameter your code injects, not text the model interprets.
• Retry storms. A failing step retries, the retry fails differently, and the pipeline spends its budget on one question. Budgets and revision limits stop this.
• Prompt injection through data. A product name or referrer string containing instructions flows from rows into a prompt. Treat all row values as data, and avoid giving agents write tools.

The Microsoft Research paper AutoGen: Enabling Next-Gen LLM Applications via Multi-Agent Conversation describes the conversational multi-agent style that many frameworks followed. It shows what is possible. It does not remove these failure modes, and conversational designs make them harder to bound than the fixed loop above.

A mistake I have seen in production is a two-model setup where the reviewer was told “make sure the answer is correct”. It approved nearly everything, because agreeing is the easy completion. The fix was to give the reviewer checkable criteria (date range present, row count sane, columns match the question) and to move the checkable ones into code.

Testing an Agent Pipeline

Test the parts separately. The planner gets a fixed set of goals and you assert the output shape. The reviewer gets known-bad results (empty rows, wrong date range) and you assert it fails them. The writer gets findings plus a draft that includes a fake number, and you assert the number check raises.

For the whole pipeline, keep a golden week. Pick a past week whose numbers you verified by hand, run the report, and compare every figure. Rerun it after each prompt or model change. Because outputs vary, compare numbers and structure, not exact wording.

Scheduling and Human Approval

Run the pipeline from a scheduler such as cron or a systemd timer on Monday morning. Write the draft to a file or a database row with a status of pending_review. A person reads it, fixes it, and publishes it. For delivery patterns such as webhooks, idempotency, and rate limits, see the article on automated actions.

Keep the human step for at least the first two months. Track how often the reviewer edits the draft, and which edits recur. Those edits tell you what to fix in the prompts or the code. When edits drop to near zero for a long stretch, consider sending the report directly with a visible “automated draft” label.

How Real Systems Do This

The common production pattern for multi-agent systems is not a swarm of autonomous agents. It is a workflow with model calls at a few steps, deterministic code everywhere else, and human sign-off at the end. This matches the advice in Anthropic’s agent guide, which lists patterns such as orchestrator-workers and evaluator-optimizer, and still tells you to prefer the simplest design that works.

Commercial analytics products also tend to offer natural-language features as an assist on top of governed data, not as a replacement for it. Whichever route you take, the visible query is the audit trail. If you build your own, keep the SQL and rows next to every number in the report.

Decision Framework

1. Are the report’s questions the same every week? If yes, write a fixed workflow and use one model call for the narrative.
2. Does any step depend on the previous result, as in a root-cause investigation? If yes, add a planner with a step limit.
3. Can a wrong number cause real harm, such as a budget decision? If yes, require human approval and deterministic checks.
4. Can the analyst role be read-only with a timeout? If not, do not proceed.
5. Do you have a golden week to test against? If not, build one first.
6. Do you know your median and worst-case call count? If not, measure it before scheduling.

When NOT to Use This

• The report is fixed. Five SQL queries and a template do the job with zero model risk. Add a model only for the commentary paragraph.
• You need guaranteed correctness. Financial or regulatory reporting needs reviewed, versioned queries. Use a BI tool with a semantic layer, or buy one.
• Your schema is undocumented. Agents compound confusion. Document the tables first, and use retrieval over your documentation only after that documentation exists.

Common Mistakes

• Letting the model choose the date range, which silently shifts the report to the wrong week.
• Using one model for analyst and reviewer, which makes errors correlated and review meaningless.
• Skipping call budgets, so one failing loop runs unattended for hours.
• Passing raw tables to the writer, which invites invented figures and causal stories.
• Granting the analyst write access “for convenience”, which turns one bad query into data loss.
• Publishing without a human check, which lets the first silent error reach the whole company.

Key Takeaways

• Start with a fixed workflow, and add agent behavior only where fixed steps fail.
• Give each role one job, one input shape, and one validated output.
• Put checks in code before checks in prompts: schemas, row counts, date ranges, number matching.
• Keep the SQL analyst on a read-only role with a timeout.
• Cap model calls and revisions, and log every run.
• Feed the writer verified findings only, and reject drafts with numbers that are not in the data.
• Keep a human approval step until the edit log shows it adds little.

FAQ

What is a multi-agent system in data analysis?

It is a set of language-model roles that split an analysis task, for example a planner, a SQL analyst, a reviewer, and a writer. Code coordinates them, passes structured data between them, and enforces limits. The result is usually a report or an answer with supporting queries.

Are multi-agent systems better than a single prompt?

Not by default. Separate roles make each step testable and let you apply checks between steps. However, they add calls, latency, and new failure modes. If one well-built prompt plus fixed queries gets the job done, use that.

Which framework should I use for multi-agent reports?

You can start without one. The listing above is plain Python with a model client. Frameworks such as AutoGen add conversation management and tooling, which help later, but they can hide the loop limits and validation you need to see.

How do I stop an AI agent from making up numbers?

Calculate every figure in SQL, pass only verified rows to the writer, and check in code that each number in the draft appears in the data. Then have a human approve the report. Prompts alone cannot guarantee this.

Can agents run reports on a schedule?

Yes. Trigger the pipeline from cron or a timer, store the draft with a pending status, and notify a reviewer. Log call counts and runtime for every run so you notice drift early.

Conclusion

Multi-agent systems for data analysis work when the model handles language and your code handles everything that must be right. Build the fixed workflow first, measure it, and add planning only where the questions truly vary. For the larger question of what this means for how teams decide, read the final article on decision intelligence.

Rule of thumb: let models write sentences, and let code guard every number.

Last updated on 9 October 2026.