The definitions document isn't documentation. It's an accuracy intervention

In a previous post I showed that column descriptions improve the accuracy of AI-generated SQL but don't make it deterministic. This is the follow-up: what happens when you go further and give the model a structured business definitions document?

Short answer: it fixed every error I tested, and — more interesting — it changed the kind of answer the model gave.

Setup

I built a dbt project over BigQuery's thelook_ecommerce dataset. The marts layer has two main tables: fct_order_items (line-item grain) and fct_orders (order grain, pre-aggregated). Both are clean, well-named, documented in the schema yaml.

Before building anything AI-related, I wrote a definitions document — ten business decisions, each traceable to a profiling finding. Things like: what does "revenue" mean (gross or net)? Which statuses count as "sold"? Which timestamp anchors a time period? Which join path reaches the product table?

Then I asked Claude the same three questions, twice: once with just the schema, once with the definitions document pasted as context.

Question 1: "How many items did we sell in 2023?"

Without definitions:

SELECT COUNT(*) AS items_sold
FROM fct_order_items
WHERE EXTRACT(YEAR FROM ordered_at) = 2023
  AND line_status != 'Returned'

It filtered out returns — reasonable. But it kept Cancelled items. In this dataset, a cancelled item isn't a sale. The model made a judgment call about which statuses count as "sold" and got it partially right.

With definitions:

SELECT COUNT(*) AS items_sold
FROM fct_order_items
WHERE EXTRACT(YEAR FROM ordered_at) = 2023
  AND line_status NOT IN ('Returned', 'Cancelled')

Correct. And it cited the reasoning: "the definitions specify that Processing lines count as sales because the customer committed to buy."

The difference isn't just the filter. Without definitions, the model explained what it did. With definitions, it explained why, referencing a convention rather than improvising one.

Question 2: "What was our revenue in 2023?"

This is the question with two defensible answers.

Without definitions:

SELECT SUM(gross_revenue) AS revenue_2023
FROM fct_orders
WHERE EXTRACT(YEAR FROM ordered_at) = 2023
  AND order_status != 'Cancelled'

Three problems:

1. It picked gross_revenue — which includes returned items. In this dataset, returns are 10% of revenue. That's a material difference.
2. It added an ad-hoc order_status != 'Cancelled' filter on the header, but gross_revenue was already computed from all lines regardless of status. The filter and the column contradict each other.
3. It never mentioned that the table has a net_revenue column, or that the choice between gross and net existed.

With definitions:

SELECT SUM(net_revenue) AS revenue_2023
FROM fct_orders
WHERE EXTRACT(YEAR FROM ordered_at) = 2023

Correct. No ad-hoc filter needed — net_revenue already excludes Returned and Cancelled at the line level. And it explained why the previous approach was wrong: "adding AND order_status != 'Cancelled' would actually under-count by dropping entire orders that have a Cancelled header status."

This is the result that surprised me most. Without definitions, the model didn't just pick the wrong column — it built an inconsistent query that mixed a gross measure with a net filter. With definitions, it picked the right column and caught the mistake it would have made.

Question 3: "What was our gross margin by category in 2023?"

Without definitions:

SELECT category, SUM(line_margin) AS gross_margin, ...
FROM fct_order_items
WHERE EXTRACT(YEAR FROM ordered_at) = 2023
GROUP BY category

No status filter. Margin included returned and cancelled items. The number is defensible in isolation — you could argue margin should include all items — but it's inconsistent with how the same model answered question 1, where it filtered out returns.

That inconsistency is the real problem. Across three questions, the model without definitions made three different decisions about what "sold" means. A user asking these questions in sequence would get numbers that don't reconcile with each other.

With definitions:

SELECT category, SUM(line_margin) AS gross_margin, ...
FROM fct_order_items
WHERE EXTRACT(YEAR FROM ordered_at) = 2023
  AND line_status NOT IN ('Returned', 'Cancelled')
GROUP BY category

Correct filter, consistent with questions 1 and 2.

The pattern

Without definitions, the model was:

• Partially right on status filtering (caught returns, missed cancellations)
• Inconsistent across questions (filtered status for counts, not for margin)
• Silent about ambiguity (never mentioned that "revenue" has two meanings)
• Incoherent within a single query (gross column + net filter on Q2)

With definitions, the model was:

• Correct on every status filter
• Consistent across all three questions
• Explicit about conventions ("per the business definitions...")
• Self-correcting ("this differs from my earlier draft because...")

The definitions document didn't just improve accuracy. It gave the model a coherent framework to reason from, and the reasoning was visible in the response.

What a definitions document actually is

This is the point I want to make clearly, because it changes how you think about the work.

Most teams treat business definitions as documentation — something you write for onboarding or compliance, stored in Confluence, read once, and never updated. The definition of "active user" is written somewhere, and nobody checks whether the dashboard agrees with it.

What this experiment suggests is that a definitions document is an accuracy intervention — it directly changes the correctness of AI-generated queries, measurably, on every question that touches a defined term. That makes it engineering work, not paperwork. And it makes maintaining it operational, not optional.

The ten definitions I wrote took about two days, including the profiling that produced the evidence behind each one. Every one resolved an ambiguity that would otherwise be resolved differently on every query, by every user, with no way to detect the inconsistency.

Caveats

Three questions, one model (Claude), tested 2026-09-03. I didn't run N repetitions on this one because I used the chat interface rather than the API — so I can't report variance. The findings are directional, not statistical. The full eval harness with systematic runs across multiple models is coming.

This also tested the ideal case: the definitions were pasted directly into context. In a real deployment, the definitions would need to be retrieved, selected, and injected automatically — which is a harder problem. What this shows is the ceiling: if the model has the definitions, it uses them well.

Part of a project building an evaluation harness for AI-generated SQL. The definitions document, the profiling methodology, and the dbt project are open-source.

Repo: github.com/Sean-Liu-GitHub/genbi-ecommerce

Key files:

• Business definitions
• Schema profiling notes
• Profiling SQL
• The intermediate model that resolves the join path