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

In a previous [post](https://zhaidata.com/better-column-descriptions-made-the-ai-right-more-often-not-reliably) 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](https://github.com/Sean-Liu-GitHub/genbi-ecommerce/blob/main/docs/DEFINITIONS.md) — ten
business decisions, each traceable to a [profiling finding](https://github.com/Sean-Liu-GitHub/genbi-ecommerce/blob/main/docs/SCHEMA_NOTES.md). 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:**

```sql
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:**

```sql
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:**

```sql
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:**

```sql
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:**

```sql
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:**

```sql
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](https://github.com/Sean-Liu-GitHub/genbi-ecommerce/blob/main/scripts/profile.sql) 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](https://github.com/Sean-Liu-GitHub/genbi-ecommerce)

Key files:
- [Business definitions](https://github.com/Sean-Liu-GitHub/genbi-ecommerce/blob/main/docs/DEFINITIONS.md)
- [Schema profiling notes](https://github.com/Sean-Liu-GitHub/genbi-ecommerce/blob/main/docs/SCHEMA_NOTES.md)
- [Profiling SQL](https://github.com/Sean-Liu-GitHub/genbi-ecommerce/blob/main/scripts/profile.sql)
- [The intermediate model that resolves the join path](https://github.com/Sean-Liu-GitHub/genbi-ecommerce/blob/main/dbt_project/models/intermediate/int_order_items_enriched.sql)