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
!Two equal-value paid orders contribute 200 at order grain, 300 after joining their three items, and 100 with distinct amounts.
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
1. NVIDIA, Auto Ontology. This article examines a definition problem; it makes no benchmark claim about that project.
2. Michael Tuszynski, An Ontology Your AI Generated Is a Guess With a Schema, including ontomato's comment.
3. SQLite, aggregate functions.
Asset credits
Original diagram from the executed SQLite fixture; no third-party image assets.
AI disclosure
AI was used to draft the article.
Mentions
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 !Two equal-value paid orders contribute 200 at order grain, 300 after joining their three items, and 100 with distinct amounts. 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 1. NVIDIA, Auto Ontology. This article examines a definition problem; it makes no benchmark claim about that project. 2. Michael Tuszynski, An Ontology Your AI Generated Is a Guess With a Schema, including ontomato's comment. 3. SQLite, aggregate functions. Asset credits Original diagram from the executed SQLite fixture; no third-party image assets. AI disclosure AI was used to draft the article.
Nvidia – open-source auto-ontology
Auto Ontology is a Python framework for building semantic, graph-based reasoning layers over data. It enables deterministic query generation, better context retrieval, and more explainable AI for LLM and agent workflows. NVIDIA/auto-ontology
Nvidia – open-source auto-ontology | Discussion
GitHub: NVIDIA/auto-ontology Eval harness: NVIDIA/auto-ontology-eval MCP docs: github.com/NVIDIA/auto-ontology/blob/main/docs/mc… NVIDIA page: developer.nvidia.com MarkTechPost write-up: marktechpost.com
NVIDIA open-sourced Auto Ontology under Apache 2.0 🧩 It builds a semantic layer from enterprise metadata so AI agents write SQL against trusted business definitions, served over MCP, CLI and API. #AI #AIAgents #OpenSource NVIDIA/auto-ontology
#NVIDIA Auto Ontology is an open library that builds that layer from enterprise metadata, reconciles definitions across systems, and exposes it to any agent harness through MCP, CLI, and API. It complements the semantic models customers already own: NVIDIA/auto-ontology t.co
Huge props to NVIDIA for this architecture—building graph-based reasoning layers over data makes deterministic query generation and explainable agent workflows an absolute breeze. 🧠 🔗 项目地址：NVIDIA/auto-ontology