The Analytics Agent Answered "What Was Q3 Revenue?" From a Deprecated Table. Make It Name Its Source Before Its Number.

A finance lead asks the internal analytics agent a plain question: "What was Q3 revenue?" Ten seconds later there is a clean answer, a tidy SQL query, and a number that is off by a meaningful amount. The query was valid. The sum was correct. The agent had read from revenue_summary, a table nobody had updated since the billing system was replaced, instead of fct_invoices_recognized, the table finance actually reports from.

Nothing in the transcript looks broken. That is the problem with this failure mode: the agent did its job perfectly on the wrong source, and the answer arrived with the same confidence a right answer would have.

🔍 Why the agent picked the wrong table

A text-to-SQL agent usually starts by listing tables, reading a few column names, and choosing whatever looks closest to the question. To a language model, revenue_summary is a near-perfect match for "Q3 revenue". fct_invoices_recognized is a worse lexical match, even though it is the right answer to anyone who works in finance.

Three things made the wrong choice easy:

• Name similarity was the only signal. The schema listing showed table names and columns. It did not show which tables are current, which are deprecated, or which one finance treats as the source of truth.
• The stale table still had data. A dropped table fails loudly. A table that stopped updating months ago still returns rows, and a sum over those rows still returns a plausible number.
• The answer format hid the source. The agent replied with the figure first. The query was there, folded under a "show SQL" toggle that nobody opened.

None of this is a model-quality problem you can prompt your way out of with "be careful". The agent had no information that would let it tell the two tables apart, and no step that forced it to say which one it used.

The fix: source first, number second

The fix has two halves. Give the agent the information that distinguishes a trustworthy source from a lookalike, then change the output contract so the source is committed and visible before any figure appears.

1. Put trust metadata where the agent will read it

Do not rely on table names. Expose a small catalog the agent consults before writing SQL, with the facts a human analyst would carry in their head:

- table: fct_invoices_recognized
  status: certified
  owner: finance-data
  grain: one row per invoice line, recognized revenue
  freshness_sla: daily
  use_for: [revenue, bookings by period]

- table: revenue_summary
  status: deprecated
  replaced_by: fct_invoices_recognized
  note: stopped updating after billing migration

The catalog tool should return status and replaced_by alongside the schema, so the deprecation travels with the table rather than living in a wiki page the agent never sees. If a table is deprecated and has a replacement, the agent should be told the replacement in the same response.

2. Make the agent declare its source as a separate step

Split the work so choosing a source is its own committed action, not a side effect of writing a query:

declare_source(
  question: "Q3 revenue",
  table: "fct_invoices_recognized",
  reason: "certified table for recognized revenue",
  filters: "invoice_date in 2026-07-01..2026-09-30"
)
-> run_query(sql)
-> answer(number, source)

declare_source is where your guardrail lives. It can reject any table marked deprecated, warn on anything not certified, and check the table's last load time against its freshness SLA. A rejection comes back as a clear error naming the replacement, so the agent can correct course in one step instead of guessing.

3. Change the answer template

The reply should lead with what was measured, then the number:

Source: fct_invoices_recognized (certified, loaded this morning) Definition: recognized revenue, invoice date in Q3 Q3 revenue: [figure]

This looks like a cosmetic change. It is not. When the source sits on the first line, a finance lead who sees an unfamiliar table name stops and asks. When it sits behind a toggle, nobody does.

Before and after

Before:

1. Agent lists tables, sees revenue_summary.
2. Writes a correct SUM over Q3.
3. Replies "Q3 revenue was [figure]."
4. The figure goes into a slide deck.

After:

1. Agent calls the catalog, sees revenue_summary marked deprecated with a replacement.
2. Calls declare_source with the certified table. The tool checks status and freshness and accepts.
3. Runs the query.
4. Replies with source, definition, then figure.

If the agent had still tried revenue_summary, step 2 would have failed with "deprecated, use fct_invoices_recognized", which is a cheap, visible correction instead of a silent wrong answer.

⚠️ What this does not fix

Source declaration catches the wrong table. It does not catch the wrong definition on the right table. "Revenue" can mean booked, billed, or recognized, and a certified table can still be queried with the wrong filter. Two habits help:

• Put the definition in the answer template, as above, so a mismatch is visible.
• Keep a short list of known-answer questions ("Q2 recognized revenue" with the figure finance already published) and run them against the agent whenever the catalog or prompt changes. If the agent drifts from a number that has already been reported, you find out before a user does.

It also depends on someone keeping the catalog honest. A catalog that marks everything certified is the old problem with extra steps. Start small: certify the handful of tables behind the questions people actually ask, and deprecate loudly when a table is replaced.

The takeaway

A data agent's most dangerous answers are the ones that are computed correctly from the wrong place. Fluency in SQL does not tell you anything about whether the agent knew which table to trust. Give it the trust signals a human analyst has, make choosing a source an explicit, checkable step, and put that source on the first line of every answer. A number with no named source should be treated as a draft, no matter how confident it sounds.