Defining revenue for an SQL agent, one row at a time

Before an agent writes a revenue query, I want one sentence settled: what does one row of revenue represent?

An order? An item? A paid invoice? Those choices change the calculation even when every table name and join key is correct. NVIDIA's Auto Ontology puts business definitions and relationships into the context supplied to agents. Its repository describes a catalog and ontology layer, query validation, provenance, and interfaces through MCP, CLI and API. [1] The useful question for me is what a definition must say to make a concrete query checkable.

Here is a deliberately small example, using synthetic integer amounts rather than a real business dataset. It runs in Python's standard-library SQLite. I am not running NVIDIA's stack or evaluating an LLM here.

Two orders, three joined rows

The metric for this example is the sum of paid order amounts, counting each order once. That last phrase specifies the grain: the entity one contribution belongs to.

import sqlite3 connection = sqlite3.connect(":memory:") connection.executescript(""" CREATE TABLE orders(id INTEGER PRIMARY KEY, status TEXT, amount INTEGER); CREATE TABLE items(order_id INTEGER, sku TEXT); INSERT INTO orders VALUES (1, 'paid', 100), (2, 'paid', 100), (3, 'cancelled', 500); INSERT INTO items VALUES (1, 'A'), (1, 'B'), (2, 'C'), (3, 'D'); """) queries = { "joined": "SELECT SUM(o.amount) FROM orders o JOIN items i ON o.id=i.order_id WHERE o.status='paid'", "distinct_amount": "SELECT SUM(DISTINCT o.amount) FROM orders o JOIN items i ON o.id=i.order_id WHERE o.status='paid'", "order_grain": "SELECT COALESCE(SUM(amount), 0) FROM orders WHERE status='paid'", } for name, sql in queries.items(): print(name, connection.execute(sql).fetchone()[0])

Output:

joined 300 distinct_amount 100 order_grain 200

Original diagram of the executed synthetic fixture. Amounts have arbitrary units; the example defines its own metric.

Order 1 has two items, so the join repeats its amount. The joined sum is 100 + 100 + 100. Order 2 is perfectly real, but SUM(DISTINCT amount) collapses its 100 together with the other order's 100. Distinct values are not distinct orders.

The direct query stays at order grain. COALESCE adds another explicit choice: return zero when no paid orders exist. Without it, SUM over no eligible rows returns NULL. I checked the empty-eligible-set case as well.

When an item condition is required

Suppose the question means paid orders with at least one item. There is no need to multiply order rows just to check that an item exists:

SELECT COALESCE(SUM(o.amount), 0) FROM orders o WHERE o.status = 'paid' AND EXISTS ( SELECT 1 FROM items i WHERE i.order_id = o.id );

This also returned 200. It would exclude an itemless paid order, while the direct order query would include it. Which one is correct depends on the agreed question. Adding an item-category filter would make that scope decision even more visible.

A definition supplied to an agent should therefore include the grain, eligible status, join behavior and empty-set behavior. This fixture can act as a compact regression case: two different orders share an amount, and one order has multiple items. Both details earn their place.

Michael Tuszynski makes the related governance point in “An Ontology Your AI Generated Is a Guess With a Schema”: an inferred definition still needs an accountable owner. In its discussion, ontomato also asks for ownership of grain and access to the source rows being counted. [2] A graph can store the chosen meaning; generating the graph does not settle that choice.

For an agent workflow, I would keep this expected answer beside the metric definition. Checking that SQL executes is one check. Checking which entities contributed to the answer is another.

References

NVIDIA, Auto Ontology. This article examines a definition problem; it makes no benchmark claim about that project.
Michael Tuszynski, An Ontology Your AI Generated Is a Guess With a Schema, including ontomato's comment.
SQLite, aggregate functions.