How to do AI reporting right: the multi-step pipeline behind a number you can trust

A user asks a question in English. A model produces a confident number. Nobody can tell if it's right. That isn't a reporting pipeline — it's a guess with a chart on top. It will be right often enough to feel useful, and wrong often enough to quietly destroy trust the first time someone takes it into a board meeting.

The fix isn't a smarter model. The fix is a more disciplined pipeline around the model. This is the approach we use, end to end, to turn a plain-English request into a structured answer that holds up under scrutiny.

What's actually inside a “report request”

Every plain-English question contains three things, whether the asker realizes it or not, and the pipeline has to surface all three before it runs anything:

Intent — what is actually being measured. “Show me churn” is an intent; the pipeline has to decide whether the user means a count, a rate, a dollar amount, or a comparison.
Scope — the time window, the filters, the entities. “Last quarter” can mean four different things depending on whose calendar you're using.
Implicit definition — the business meaning buried inside the words. “Customer” might mean account, contact, or paying logo. “Active” means something different in every company.

A pipeline that doesn't name these three before producing a number is, by construction, guessing.

The right way, in five steps

The rest of this piece is the pipeline itself. Five steps, in order, with the work each one is doing.

Step 1 — Translate intent against a semantic layer, not raw tables

The model does not write free-form SQL against your warehouse. It picks from a curated set of metrics, dimensions, and filters that are already defined — the semantic layer. “Revenue” exists once, with one definition, joined to the right dimensions, available to the model as a named building block. So does “active customer.” So does every other term that would otherwise be redefined a dozen times.

This is where the warehouse work pays for itself. A semantic layer is what makes the model's output deterministic in the dimensions that matter: ask the same question twice and you get the same number, because both runs are reaching for the same predefined metric.

Step 2 — Plan before you query

For anything beyond a trivial lookup, the AI produces a structured plan first. The plan names the sub-queries it intends to run, the inputs they take, and the QC checks that will be applied to each. Nothing executes until the plan is built.

Two things this buys you. First, a plan is small enough for a human or a second agent to sanity-check before any data moves — you can catch a wrong filter or an off-by-one time window without paying the cost of running the query. Second, the plan is what gets stored as the audit record of why the number came out the way it did. Here's what one of these plans actually looks like:

json

{
  "request": "Which of our top-50 customers look at-risk heading into Q3?",
  "extracted": {
    "intent":     "rank customers by composite at-risk score",
    "scope":      { "cohort": "top_50_by_arr", "window": "trailing_90_days" },
    "definition": { "at_risk": "weighted blend of usage_decline, support_load, ar_aging" }
  },
  "steps": [
    {
      "id": "s1_top50",
      "metric": "arr_by_account",
      "inputs": { "as_of": "today", "limit": 50, "order_by": "arr_desc" },
      "qc": ["row_count_eq_50", "arr_total_reconciles_to_finance_close"]
    },
    {
      "id": "s2_usage",
      "metric": "usage_trend_by_account",
      "inputs": { "accounts": "$s1_top50.accounts", "window": "trailing_90_days" },
      "qc": ["null_rate_lt_0.05", "spot_check_3_random_rows"]
    },
    {
      "id": "s3_support",
      "metric": "support_volume_by_account",
      "inputs": { "accounts": "$s1_top50.accounts", "window": "trailing_90_days" },
      "qc": ["row_count_lt_or_eq_50", "ticket_total_reconciles_to_helpdesk"]
    },
    {
      "id": "s4_ar",
      "metric": "ar_aging_by_account",
      "inputs": { "accounts": "$s1_top50.accounts", "as_of": "today" },
      "qc": ["ar_total_reconciles_to_gl"]
    },
    {
      "id": "s5_score",
      "compose": ["s2_usage", "s3_support", "s4_ar"],
      "weights": { "usage_decline": 0.5, "support_load": 0.2, "ar_aging": 0.3 },
      "qc": ["score_distribution_in_expected_range"]
    }
  ],
  "needs_clarification": []
}

Notice what's in there: every step names a predefined metric, every input is typed, and every step carries its own QC checks. There is no free-form SQL anywhere in the plan, because there won't be any in the execution either.

Step 3 — Ask, don't guess, on ambiguity

When the request is ambiguous, the correct move is a clarifying question, not a confident guess. “Last quarter” — calendar or fiscal? “Customers” — accounts or contacts? “Top” — by revenue, by usage, by recency?

The pipeline surfaces these as a short list of choices before any data moves. One round of clarification, the user picks, the plan finalizes. Compared to silently guessing and producing a wrong number, the cost of asking is trivial. The cost of not asking is the user discovering, three meetings later, that the number meant something different than they thought.

Step 4 — Pull data through approved queries only

Every metric in the plan resolves to a parameterized, pre-reviewed query with typed inputs. Same allowlist principle that governs tools in an orchestration layer: the model can call things on the menu, and only things on the menu. Free-form SQL stays off the production path.

This is also where the boring infrastructure pays off. Row-level permissions, column masking for sensitive fields, and full audit logging all live at the query layer — not in the model, not in the prompt. The user's identity flows down to the query, the query enforces what they're allowed to see, and the audit captures who asked for what.

“A number without provenance isn't an answer. It's a guess with a timestamp.”

— RevenuePoint design principle

Step 5 — QC the result before the user sees it

Every result runs a battery of automated checks before anything renders to the user. The checks are cheap, mechanical, and the difference between a pipeline that's usually right and one that's defensibly right.

Row counts in expected ranges. A query that should return fifty rows and returns three is a flag, not an answer.
Totals reconcile to a known ground truth. Revenue numbers cross-check against the close. AR totals cross-check against the GL. Pipeline totals cross-check against the CRM roll-up. If a number doesn't reconcile, the pipeline says so.
Null rates, duplicate rates, referential integrity. All compared against the historical norms for that metric. A sudden jump in nulls usually means an upstream schema change, not a real business event.
Spot-checks. A handful of randomly-sampled rows get re-read directly from the source system to confirm the warehouse copy still matches.

When any check fails, the pipeline does not return an answer. It returns a result flagged as needs review, with the failing check named and the data attached. The user sees the flag, not a wrong number dressed up as a right one.

Present results so they can be trusted

The pipeline isn't finished when the number is produced. The last job is presenting the result so a thoughtful person can decide whether to use it. Every number the user sees ships with its full lineage: the original question, the extracted intent and scope, the plan, the queries that ran, the sources they touched, a timestamp, and which QC checks passed. The user can click from any number back to the rows that produced it.

Provenance is a first-class part of the surface, not a footnote. The reason is simple: people trust numbers they can trace. The fastest way to lose trust in an AI reporting layer is to give someone a number they can't defend in a meeting. The fastest way to build it is to give them the audit trail along with the answer.

One question, two pipelines

The difference between this and naive AI reporting is easiest to see on a single, ordinary request.

Naive one-shot AI

“Which of our top-50 customers look at-risk heading into Q3?”

The model interprets the question, writes some SQL on the fly, and returns a confident-looking ranked list. No clarifying question on what “at-risk” means. No reconciliation. No way to tell why a given customer made the list — or which ones should have made the list and didn't.

The user shares the list in a meeting. Someone asks how it was built. Nobody can answer. The list quietly stops being used.

The five-step pipeline

Same question. The pipeline extracts intent, scope, and definition; surfaces one clarifying question (which signal gets the most weight); produces the plan above; runs each step through the approved metric layer; QCs the totals against finance, the helpdesk, and the GL; and returns a ranked list with each score traceable to the rows behind it.

The user shares it in the meeting. Someone asks why a given customer made the list. The user clicks the score and shows them — usage decline, ticket spike, AR aging, weighted as agreed. The list gets used.

Five-stage pipeline diagram: English request, plan, approved queries, QC checks, trusted result — The pipeline, end to end. Provenance travels alongside the data at every step.

How we think about this at RevenuePoint

The point of an AI reporting layer isn't the speed. The speed is a side effect. The point is that the pipeline underneath is more disciplined than what most analysts would manage under deadline: a real semantic layer, a written plan, clarifying questions, approved queries, automated reconciliation, and provenance attached to every number. Plan, pull, check, show your work. Done in that order, the result of the pipeline is something a user can take into a meeting and defend — which is the only definition of “trusted” that actually matters.