Ask before you query: when an AI should clarify revenue

“What was revenue in September?” looks like a database question. Often, it is a business-definition question first. An agent can find the right tables, generate valid SQL, and return an exact number without knowing which version of revenue you meant.

Three possible answers are gross order value, the value of paid orders, and paid-order value after refunds. None becomes the right answer just because its query executes successfully. Before choosing the SQL, choose the meaning.

This walkthrough was inspired by AI Hallucinates Because It Doesn't Know Your Business. Here's the Fix from AI for the Working Data Engineer. Its description proposes team-owned Markdown business context; its published 12:27 chapter highlights detecting ambiguity instead of filling a gap. We reviewed the description and chapters, not a transcript. The Postgres example below is our own. The video's model-building workflow is separate from Bufflehead, which reads existing data rather than building or updating those models.

Ask one useful question before calculating

Suppose the request already specifies September 2026 in UTC and USD, but says only “revenue.” A useful response is:

Do you mean gross order value, paid-order value, or paid-order value after refunds? If after refunds, should I subtract refunds recorded before October 1 for those September orders?

That is more useful than silently picking a column named total. It identifies a decision that changes the answer. Schema inspection may help reveal the available choices, but it cannot determine the business's intended metric. Ask before running the metric query, not necessarily before any read-only schema lookup.

If a current, agreed definition already resolves the request, use it and state it. Do not make someone answer the same question on every query. If the definition conflicts with the request, is out of date, or leaves a material choice unresolved, ask a targeted follow-up.

A small fixture with three different answers

This is a fictional, completed-month dataset, not a report of real September activity. Prepare it in a fresh disposable Postgres database with an ordinary SQL client, not through Bufflehead. Database setup writes data; the subsequent AI workflow only reads it. Connect Bufflehead with a role limited to the intended schema and SELECT access.

CREATE SCHEMA revenue_demo;

CREATE TABLE revenue_demo.orders (
  order_id integer PRIMARY KEY,
  placed_at timestamptz NOT NULL,
  payment_status text NOT NULL
    CHECK (payment_status IN ('paid', 'pending')),
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);

CREATE TABLE revenue_demo.refunds (
  refund_id integer PRIMARY KEY,
  order_id integer NOT NULL REFERENCES revenue_demo.orders(order_id),
  refunded_at timestamptz NOT NULL,
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);

INSERT INTO revenue_demo.orders VALUES
  (1, '2026-09-01 00:00:00+00', 'paid', 100),
  (2, '2026-09-05 12:00:00+00', 'paid', 200),
  (3, '2026-09-10 12:00:00+00', 'paid', 300),
  (4, '2026-09-20 12:00:00+00', 'pending', 400),
  (5, '2026-08-31 23:59:59+00', 'paid', 500),
  (6, '2026-10-01 00:00:00+00', 'paid', 700);

INSERT INTO revenue_demo.refunds VALUES
  (11, 1, '2026-09-12 12:00:00+00', 25),
  (12, 1, '2026-09-20 12:00:00+00', 25),
  (13, 2, '2026-10-02 12:00:00+00', 40),
  (14, 5, '2026-09-15 12:00:00+00', 60);

Every amount is USD and excludes tax and shipping. In this fixture, paid means the order was paid before the reporting cutoff and remains marked paid even after a refund. The statuses are a fixed reporting snapshot. There are no discounts, cancellations, partial payments, failed refunds, or missing amounts. Production data needs explicit rules for those cases; a mutable status column alone cannot reconstruct historical payment state.

• Gross order value: $1,000. All four September orders, including the pending $400 order. “Gross” is this example's label for recorded order value before refunds, not a universal accounting definition.
• Paid-order value: $600. September orders 1, 2, and 3 only.
• Paid-order value after refunds: $550. The same $600, less the two $25 refunds on order 1 recorded before October 1.

The $40 October refund is outside that cutoff. The $60 September refund belongs to an August order, so it is outside this September order cohort. Subtracting every refund issued during September would answer a different question. These are operational order metrics, not a statement of recognized accounting revenue or cash collected during the month.

Give the agent a short business-definition note

Put an approved note in the context you provide to your AI tool. That could be a project Markdown file or the conversation itself; this is not a claim that Bufflehead automatically discovers or maintains business definitions.

Revenue demo definitions — approved for this fictional fixture

Currency: USD. Amounts exclude tax and shipping.
Order month: placed_at, using UTC and an exclusive end boundary.
Gross order value: amount of every order placed in the window.
Paid-order value: amount of paid orders placed in the window.
After refunds: subtract refunds on those paid orders recorded
before the reporting cutoff. Do not include other order cohorts.

There is no default meaning for the unqualified word "revenue".
Ask which measure is wanted. For an after-refunds measure, confirm
the refund cutoff. State the selected definition with the answer.

In a real project, include the owner and revision date, the relevant tables and columns, and the approved handling of exclusions. A short note is useful only when someone maintains it. Do not let the agent invent a missing policy and then treat its invention as business context.

After clarification, query the agreed metric

Suppose the user confirms: “Paid September orders, less refunds on those orders recorded before October 1, in UTC.” The following reference query shows the three components side by side so the choice is inspectable.

WITH september_orders AS (
  SELECT order_id, payment_status, amount
  FROM revenue_demo.orders
  WHERE placed_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'
    AND placed_at < TIMESTAMPTZ '2026-10-01 00:00:00+00'
), refunds_by_order AS (
  SELECT order_id, SUM(amount) AS refunded_amount
  FROM revenue_demo.refunds
  WHERE refunded_at < TIMESTAMPTZ '2026-10-01 00:00:00+00'
  GROUP BY order_id
)
SELECT
  COUNT(*) AS september_orders,
  COUNT(*) FILTER (WHERE o.payment_status = 'paid') AS paid_orders,
  COALESCE(SUM(o.amount), 0) AS gross_order_value,
  COALESCE(SUM(o.amount)
    FILTER (WHERE o.payment_status = 'paid'), 0) AS paid_order_value,
  COALESCE(SUM(COALESCE(r.refunded_amount, 0))
    FILTER (WHERE o.payment_status = 'paid'), 0) AS cohort_refunds,
  COALESCE(SUM(o.amount - COALESCE(r.refunded_amount, 0))
    FILTER (WHERE o.payment_status = 'paid'), 0) AS net_paid_order_value
FROM september_orders o
LEFT JOIN refunds_by_order r USING (order_id);

Expected: 4 September orders; 3 paid orders; gross order value $1,000; paid-order value $600; cohort refunds $50; net paid-order value $550.

Aggregate refunds to one row per order before joining. Order 1 has two refund records; a raw join would repeat its order amount. The WITH clauses keep cohort selection and refund aggregation separate. Here, zero means no matching orders or refunds, because amounts are required and the fixture is complete. Do not use that convention to disguise missing data in a real report.

A complete answer is: “September paid-order value after refunds was USD 550: $600 across three September orders, less $50 in refunds on those orders recorded before October 1 UTC. This excludes the October refund and refunds for orders outside September.” Show the SQL alongside that explanation.

Test whether the agent asks, not just whether SQL runs

Try the ambiguous question with the note supplied, then the clarified question. For the first, look for a focused clarification instead of an unlabeled number. For the second, check the cohort, refund cutoff, join behavior, and explanation against the reference result. A prompt requesting clarification is an instruction to the model, not a guarantee it will comply.

We executed this reference SQL against the fictional fixture in PostgreSQL 14 in a read-only transaction. We also checked that moving the refund cutoff to November 1 changes the cohort refund total to $90 and the net to $510, and that an empty order cohort returns zero counts and amounts. These checks validate the example SQL, not an end-to-end AI run or a general accuracy claim.

Where Bufflehead fits

Bufflehead provides the read-only bridge from an AI tool to existing Postgres data. It does not decide what revenue means, provide built-in semantic validation, create a context database, or guarantee that an agent asks the right question. Business definitions and the AI's instructions remain separate from the access layer.

The workflow is straightforward: give the agent the relevant definitions, resolve material ambiguity, query through scoped read-only access, and return the result with its meaning attached. An exact number becomes useful when everyone agrees what it measures.

Download Bufflehead to connect to existing data. For checking the resulting answers, see Your AI ran valid SQL. Did it answer the question? For limiting what an agent can read, see Read-only SQL is not a data-access policy.