A data agent shouldn't get to guess what revenue means

A data agent picks a meaning and sounds sure either way. Make it look up every business term in a small, owned registry before it writes SQL.

Question Resolve terms Registry SQL from definition Reconcile check Answer Ask, don't guess every business term owner, version on a fixed test set version stamp unknown or ambiguous term
Every business term passes through the registry first. Unknown or ambiguous terms branch off to a question instead of a guess, and the reconcile check runs on a fixed test set.
On this page
  1. Start with the words that start arguments
  2. The agent has to look every word up
  3. Asking the EMEA question again
  4. Unknown terms are the backlog
  5. Checking the agent’s SQL against the canonical query
  6. Fewer answers, on purpose

Three teams can count active customers correctly and come up with three different numbers. In a meeting that’s survivable, because someone asks “which active?” and the room settles it. A data agent skips that question. It picks a meaning, writes tidy SQL for it, and sounds just as sure whichever one it picked.

Take a subscription business I’ve invented. Finance counts a customer as active with a paid invoice in the last 30 days, product with a login in the last 28, sales with an open contract. A regional VP asks the agent how many active customers EMEA has. It finds a logins table, counts the accounts seen in the last 28 days, and replies with a number about 40% above finance’s. Nothing in the query is wrong except the meaning it chose.

The usual fixes are an ontology or a semantic layer. Both can wait. I’d start with something much smaller and keep it that way until the questions coming in force it to grow.

Start with the words that start arguments

Pick the 15 to 25 terms people fight about: numbers that disagree between dashboards, words that restart the same thread every quarter. Revenue, active customer, churn and pipeline will probably make the list.

Each meaning gets one file in a repo, reviewed like code. A word with three meanings gets three files, and the ids say so: active_customer.billing, active_customer.product, active_customer.sales. Every file has an owner, someone with standing to say “no, that’s not what it means,” plus a plain-language meaning, the grain, the exclusions, and the one query that counts as correct.

If you already have data contracts between teams, this sits on top of them. A contract promises a table’s columns, types and grain. This file promises what a word means.

# definitions/active_customer_billing.yml
# A fictional company. One file per meaning, not per word.
id: active_customer.billing
version: 4
owner:
  name: Revenue accounting lead
  team: Revenue accounting
  contact: [email protected]
meaning: >
  A customer account with at least one paid invoice in the 30 days
  ending on the as-of date. This is the number finance reports.
grain: one row per billing account (core.accounts.account_id)
parameters:
  - name: region
    meaning: Region code such as EMEA. Null means every region.
  - name: as_of
    meaning: Last day of the 30-day window. Use yesterday when the question means now.
canonical_sql: |
  SELECT COUNT(DISTINCT a.account_id) AS active_customers
  FROM billing.invoices AS i
  JOIN core.accounts AS a
    ON a.account_id = i.account_id
  WHERE i.status = 'paid'
    AND i.refunded_in_full = FALSE
    AND i.paid_at >= CAST(:as_of AS date) - INTERVAL '29 days'
    AND i.paid_at <  CAST(:as_of AS date) + INTERVAL '1 day'
    AND a.is_internal = FALSE
    AND a.is_test = FALSE
    AND (CAST(:region AS text) IS NULL OR a.region = :region)
    AND NOT (
      a.trial_converted_at IS NOT NULL
      AND (SELECT COUNT(*)
           FROM billing.invoices AS p
           WHERE p.account_id = a.account_id
             AND p.status = 'paid'
             AND p.refunded_in_full = FALSE
             AND p.paid_at < CAST(:as_of AS date) + INTERVAL '1 day') < 2
    )
excludes:
  - Internal and test accounts
  - Invoices refunded in full
  - Accounts converted from a free trial, until their second paid invoice
synonyms:
  - active customers
  - paying customers
  - billed customers
valid_from: 2025-11-01
changelog:
  - version: 4
    valid_from: 2025-11-01
    change: Trial conversions count only from their second paid invoice.
  - version: 3
    valid_from: 2025-07-01
    change: Invoices refunded in full no longer count.

The agent fills in :region and :as_of. It can wrap the query, say to compare two defined numbers, but it doesn’t edit what’s inside.

synonyms is how “paying customers” finds this file. I’d match on it exactly, after lowercasing and trimming, because embedding similarity invites confident wrong matches, the failure this exists to remove. Strict matching will miss “paying customer,” singular. I can live with that. The miss shows up in the unknown-term log, and the fix is one line. valid_from says when finance started counting this way, for the day someone asks why November’s count dropped.

The agent has to look every word up

The agent gets a tool called resolve_term and one rule: call it for each business term in the question before writing any SQL. One match, and the agent uses that definition’s query. Several, and it asks the user which they mean, in the owners’ words. None, and it says it doesn’t know the term and stops.

I wouldn’t trust the system prompt alone to hold that rule. The SQL tool can refuse any query that doesn’t cite a definition id resolved earlier in the conversation.

The tool definition is in MCP’s format, and the input schema carries over to most tool-calling APIs with only the key name changed.

{
  "name": "resolve_term",
  "description": "Look up a business term in the definitions registry. Call this for every business term in the question before writing SQL. Never use a meaning the registry did not return.",
  "inputSchema": {
    "type": "object",
    "properties": {
      "term": {
        "type": "string",
        "description": "The term as the user wrote it, for example \"active customers\"."
      },
      "question": {
        "type": "string",
        "description": "The user's full question, stored with the lookup so unknown terms arrive with context."
      }
    },
    "required": ["term", "question"],
    "additionalProperties": false
  },
  "outputSchema": {
    "type": "object",
    "oneOf": [
      {
        "properties": {
          "status": { "const": "match" },
          "definition": { "$ref": "#/$defs/definition" }
        },
        "required": ["status", "definition"]
      },
      {
        "properties": {
          "status": { "const": "ambiguous" },
          "candidates": {
            "type": "array",
            "minItems": 2,
            "items": { "$ref": "#/$defs/candidate" }
          },
          "instruction": {
            "const": "Ask the user which meaning they want. Do not choose for them."
          }
        },
        "required": ["status", "candidates", "instruction"]
      },
      {
        "properties": {
          "status": { "const": "unknown" },
          "term": { "type": "string" },
          "lookup_id": { "type": "string" },
          "instruction": {
            "const": "Tell the user this term has no definition yet. Do not guess one."
          }
        },
        "required": ["status", "term", "lookup_id", "instruction"]
      }
    ],
    "$defs": {
      "candidate": {
        "type": "object",
        "properties": {
          "id": { "type": "string" },
          "version": { "type": "integer", "minimum": 1 },
          "owner": {
            "type": "object",
            "properties": {
              "name": { "type": "string" },
              "team": { "type": "string" },
              "contact": { "type": "string" }
            },
            "required": ["name", "team"]
          },
          "meaning": { "type": "string" }
        },
        "required": ["id", "version", "owner", "meaning"]
      },
      "definition": {
        "type": "object",
        "properties": {
          "id": { "type": "string" },
          "version": { "type": "integer", "minimum": 1 },
          "owner": {
            "type": "object",
            "properties": {
              "name": { "type": "string" },
              "team": { "type": "string" },
              "contact": { "type": "string" }
            },
            "required": ["name", "team"]
          },
          "meaning": { "type": "string" },
          "grain": { "type": "string" },
          "excludes": { "type": "array", "items": { "type": "string" } },
          "canonical_sql": { "type": "string" },
          "parameters": {
            "type": "array",
            "items": {
              "type": "object",
              "properties": {
                "name": { "type": "string" },
                "meaning": { "type": "string" }
              },
              "required": ["name", "meaning"]
            }
          }
        },
        "required": ["id", "version", "owner", "meaning", "grain", "canonical_sql", "parameters"]
      }
    }
  }
}

Candidates deliberately come back without SQL. Until the user picks, the model has nothing to run.

Asking the EMEA question again

Now it’s October, the registry is in place, and the billing file is at v3. The agent calls resolve_term with “active customers” and gets three candidates back. It asks the VP to choose, quoting each owner’s wording, and the VP picks billing. The agent binds the region to EMEA and the as-of date to yesterday, runs the v3 query, and the reply carries a stamp: active_customer.billing@v3.

In November finance stops counting trial conversions until their second paid invoice, and that’s v4. The agent picks up the new file on its next lookup. Numbers people already pasted into decks don’t, and that’s what the stamp is for. Every answer and every registry-backed dashboard tile logs its definition versions, along with the question and who asked.

So one query turns up eight saved answers and two dashboards still on v3. Their owners get a note before the old number reaches a regional review, the way a restated figure gets a footnote instead of a quiet swap.

Unknown terms are the backlog

Count the share of questions that hit an unknown or ambiguous term. I call it the ask rate, and it starts high. Every lookup is logged, so once a week you sort the unknown terms by count and give the top few files and owners. The registry grows from real questions instead of a modeling workshop’s guesses.

If people pick the same candidate nine times out of ten, the owners should talk, maybe renaming the product meaning to “active users” so the words stop colliding. The agent still shouldn’t choose for them. An ask rate near zero is its own warning sign, because it can mean the synonym lists have grown too generous.

Checking the agent’s SQL against the canonical query

The agent can still fill in the right query wrong. “Active customers at the end of last quarter” needs :as_of set to the right day, which is easy to miss. So keep a fixed set of a few dozen questions, each with its expected outcome, definition, parameters and tolerance. Include some that should make the agent ask, and a couple that should come back unknown. An agent that stops asking has regressed too.

Any change to the registry, the prompt or the model triggers a run. For each question, run the agent’s SQL and the definition’s own query with the expected parameters, then compare. That comparison is the diagram’s reconcile check: the reconcile test from the post on data models nobody reads, pointed at the agent.

-- reconcile.sql: compare the agent with the canonical query for one eval run.
-- canonical_results holds each question's expected definition, run with its
-- expected parameters. Both sides used the same snapshot. result_value is numeric.
WITH paired AS (
  SELECT
    q.question_id,
    q.question_text,
    q.expected_outcome,              -- 'answered', 'asked' or 'unknown'
    q.expected_definition_id,
    q.tolerance_pct,
    a.outcome            AS agent_outcome,
    a.definition_id      AS agent_definition_id,
    a.definition_version AS agent_version,
    a.result_value       AS agent_value,
    c.definition_version AS canonical_version,
    c.result_value       AS canonical_value
  FROM eval.questions AS q
  LEFT JOIN eval.agent_results AS a
    ON a.question_id = q.question_id AND a.run_id = :run_id
  LEFT JOIN eval.canonical_results AS c
    ON c.question_id = q.question_id AND c.run_id = :run_id
  WHERE q.active
),
judged AS (
  SELECT
    p.*,
    ROUND(100.0 * ABS(agent_value - canonical_value)
          / NULLIF(ABS(canonical_value), 0), 2) AS diff_pct,
    CASE
      WHEN agent_outcome IS NULL THEN 'no_agent_result'
      WHEN agent_outcome <> expected_outcome THEN 'wrong_outcome'
      WHEN expected_outcome <> 'answered' THEN 'ok'
      WHEN canonical_version IS NULL THEN 'no_canonical_result'
      WHEN agent_definition_id IS DISTINCT FROM expected_definition_id THEN 'wrong_definition'
      WHEN agent_version IS DISTINCT FROM canonical_version THEN 'stale_version'
      WHEN agent_value IS NULL OR canonical_value IS NULL THEN 'missing_value'
      WHEN ABS(agent_value - canonical_value)
           > COALESCE(tolerance_pct, 0) / 100.0 * ABS(canonical_value) THEN 'value_mismatch'
      ELSE 'ok'
    END AS verdict
  FROM paired AS p
)
SELECT question_id, question_text, verdict,
       agent_definition_id, agent_version, canonical_version,
       agent_value, canonical_value, diff_pct, tolerance_pct
FROM judged
WHERE verdict <> 'ok'
ORDER BY verdict, question_id;

Put both sides on one snapshot and most questions can run at zero tolerance. Run the two queries a minute apart on a live table and you’ll need a tolerance wide enough to hide real mistakes.

Fewer answers, on purpose

A registry this small answers fewer questions than a full semantic layer. Ask about net revenue retention by cohort before anyone has written that file, and the agent says it doesn’t know. That’s the default I want. A wrong number in a regional review costs more than a “not yet,” and the “not yet” joins the backlog with a count next to it.

It won’t settle ownership fights either. If finance and sales can’t agree on who owns “pipeline,” the file can’t merge without an owner, so the term stays unknown. Its ask rate keeps climbing until both teams are looking at a number that’s hard to ignore.