How to Prevent AI-Generated SQL From Using the Wrong Metric

AI-generated SQL can fail even when the query is syntactically correct.

One of the most common reasons is simple:

> The AI used the wrong metric.

A user may ask for “revenue,” “active customers,” “retention,” “margin,” or “customer lifetime value.” These terms sound straightforward, but in enterprise environments they often have multiple definitions, formulas, source tables, filters, and owners.

If an AI system does not know which definition is approved, it may choose a field that looks right and generate SQL quickly.

The result may run.

The chart may look reasonable.

But the answer may not match the business definition.

Preventing this problem requires more than a stronger SQL model. It requires governed metric context before SQL is generated.

---

## Why Metric Mistakes Are So Common

Enterprise metrics are rarely as simple as their names.

“Revenue” may mean:

- gross revenue;

- net revenue;

- recognized revenue;

- booked revenue;

- recurring revenue;

- revenue after refunds;

- revenue before tax;

- revenue by order date;

- revenue by settlement date.

“Customer” may mean:

- registered customer;

- paying customer;

- active customer;

- account-level customer;

- billing customer;

- end user.

“Region” may mean:

- billing region;

- sales region;

- delivery region;

- operating region;

- current customer region;

- historical transaction region.

Humans often understand these differences from business context. AI systems do not automatically know them from table names or column names.

If the metric context is missing, the AI may guess.

---

## The Risk: Correct SQL, Wrong Business Logic

Metric errors are dangerous because they often do not break the query.

For example, the AI may generate SQL using a column called `revenue_amount`. The query runs. The answer looks clean.

But the business may define official net revenue as:

```text

gross revenue

- settled refunds

- approved credits

- tax exclusions

```

The column `revenue_amount` may represent gross order value, not net revenue.

This is not a syntax problem. It is a governance problem.

The AI did not fail to write SQL. It failed to use the approved metric definition.

---

## What AI Needs Before It Generates SQL

To avoid wrong metrics, AI systems need access to governed metric context.

A useful metric definition should include:

- metric name;

- plain-language business definition;

- approved formula;

- source tables and fields;

- allowed dimensions;

- required filters;

- default time logic;

- owner;

- certification status;

- version;

- access policies;

- lineage requirements;

- examples of correct usage;

- known exclusions or warnings.

This context should be available before SQL generation, not added afterward.

The goal is to make the AI ask:

> Which approved metric should I use?

before it asks:

> Which SQL should I write?

---

## Start with a Metric Registry

A metric registry is a structured source of truth for business metrics.

It does not have to be complex at the beginning. The first version can focus on the most important and most frequently used metrics.

For each metric, define:

| Field | Example |

|---|---|

| Metric name | Net Revenue |

| Business definition | Revenue after refunds and approved credits |

| Formula | Gross revenue - settled refunds - eligible credits |

| Owner | Finance Analytics |

| Certified status | Certified |

| Source fields | `orders.gross_amount`, `refunds.refund_amount` |

| Time rule | Settlement date |

| Allowed dimensions | Billing region, product category, customer segment |

| Required filters | Exclude cancelled orders |

| Policy notes | Customer-level details masked for non-finance users |

This gives AI a governed reference point.

Without this layer, the model may search the schema and choose whatever looks most relevant.

---

## Map Business Terms to Physical Fields

A metric definition is only useful to AI if it can be connected to actual data fields.

For example:

```text

Business term: Net Revenue

Formula:

orders.gross_amount

- refunds.settled_refund_amount

- credits.approved_credit_amount

Time field:

orders.settlement_date

Default dimension:

billing_accounts.billing_region

```

This mapping prevents the AI from using fields only because their names look similar.

It also helps the system explain the answer later:

> This query used the approved Net Revenue definition owned by Finance Analytics, mapped to orders, refunds, and credits.

That is much stronger than returning a number with no evidence.

---

## Define Approved Dimensions

Metrics are often safe in one breakdown but misleading in another.

For example, net revenue by billing region may be approved, while net revenue by current customer profile region may be misleading for historical reporting.

A governed metric should define which dimensions are allowed.

Examples:

- revenue by billing region;

- retention by customer cohort;

- margin by product category;

- active users by product line;

- order volume by channel.

It should also define unsupported or risky dimensions.

If a user asks for a metric by a dimension that is not approved, the AI should not force a query. It should ask a clarification question or explain the limitation.

---

## Add Versioning and Ownership

Metric definitions change.

A finance team may update a revenue formula. A data team may migrate a source table. A governance team may change masking rules. A business unit may redefine an active customer.

AI-generated SQL needs to know which version of a metric it used.

At minimum, important metrics should have:

- an owner;

- a current version;

- change history;

- approval status;

- effective dates when needed.

This matters for audit and trust.

If an executive asks why a number changed, the team should be able to determine whether the data changed, the definition changed, or the AI used a different context version.

---

## Use Clarification When the Metric Is Ambiguous

A good AI data system should not always generate SQL immediately.

If a user asks:

> Show revenue by region.

The system may need to ask:

> Do you mean gross revenue, net revenue, recognized revenue, or booked revenue?

If a user asks:

> How many active customers did we have last quarter?

The system may need to ask:

> Should active customer mean login activity, purchase activity, subscription status, or account status?

Clarification is not a failure. It is a safety mechanism.

AI-generated SQL becomes more reliable when the system knows when it does not have enough context.

---

## Connect Metrics to Relationship Context

Even with the right metric definition, the SQL can still fail if the AI joins the wrong tables.

For example, a metric definition may say that revenue should be grouped by billing region. But the AI still needs to know how orders connect to billing accounts and how billing accounts connect to region mapping.

That means metric context should work together with relationship context.

A reliable workflow should include:

1. identify the approved metric;

2. retrieve its formula and source mappings;

3. identify approved dimensions;

4. retrieve trusted join paths;

5. apply access policies;

6. generate SQL;

7. return lineage and explanation.

Metric governance answers what to calculate.

Relationship context answers how the data should be connected.

Both are needed.

---

## Preventing Wrong Metrics in Practice

Data teams can reduce metric errors by starting with a focused set of high-value metrics.

### 1. Identify risky metrics

Start with metrics that are widely used, often disputed, or important for executive reporting.

Common examples:

- revenue;

- margin;

- active customers;

- retention;

- churn;

- customer lifetime value;

- conversion rate;

- order volume;

- inventory availability;

- service level.

### 2. Define the approved version

For each metric, capture the business definition, formula, source fields, owner, and approved dimensions.

Do not try to define every possible metric at once. Start with the metrics where wrong answers would create the most risk.

### 3. Mark unsupported variants

If certain interpretations are deprecated, experimental, or not approved for AI-generated SQL, mark them clearly.

This prevents the AI from selecting old logic silently.

### 4. Add examples

Examples help users and systems understand correct usage.

For instance:

- “net revenue by billing region” is supported;

- “net revenue by current profile region” requires clarification;

- “gross revenue before refunds” is a different metric.

### 5. Require explanation

Every generated SQL answer should explain which metric definition was used.

A short explanation can include:

- metric name;

- definition version;

- owner;

- formula summary;

- source tables;

- dimensions;

- policy notes.

This makes the result easier to review.

---

## A Practical Example

User question:

> What was revenue by region last quarter?

A weak AI workflow may generate SQL directly from likely fields.

A governed workflow should first resolve the metric:

1. “Revenue” is ambiguous.

2. Approved options include gross revenue, net revenue, and recognized revenue.

3. The default certified executive metric is net revenue.

4. Region should use billing region for finance reporting.

5. Last quarter should use settlement date.

6. The user can view regional aggregates but not customer-level detail.

The system can then either generate SQL using the approved default or ask the user to confirm the metric.

A better response may be:

> I can calculate this using the certified Net Revenue definition by billing region for last quarter. Should I proceed, or do you want gross revenue instead?

This prevents the AI from turning ambiguity into a wrong answer.

---

## A Metric Safety Checklist

Use this checklist to evaluate whether AI is likely to use the right metric.

| Area | Key question | Ready if... |

|---|---|---|

| Definition | Does the metric have an approved meaning? | The business definition is clear and owned. |

| Formula | Can AI retrieve the official calculation? | The formula is structured and versioned. |

| Source mapping | Does AI know which fields to use? | Business terms are mapped to physical tables and columns. |

| Dimensions | Can the metric be grouped safely? | Allowed and risky dimensions are documented. |

| Time logic | Does the metric have a default date rule? | Date fields and time windows are defined. |

| Ownership | Is there accountability? | Each critical metric has an owner and status. |

| Policy | Can access rules be applied? | Permissions and masking rules are available before SQL generation. |

| Relationships | Can required tables be joined correctly? | Trusted join paths are available. |

| Clarification | Does ambiguity trigger a question? | The system asks when multiple definitions are possible. |

| Lineage | Can the answer be explained? | The metric version, fields, joins, and policies are traceable. |

---

## Where Arisyn Fits

Arisyn is designed to provide governed query context before AI generates SQL.

**Semora** helps map business terms, metrics, dimensions, formulas, and approved logic to governed data fields. This helps prevent AI systems from choosing the wrong metric or inventing calculation logic.

**IntaLink** complements this by helping discover, validate, score, and govern table relationships and trusted join paths. This helps ensure that once the right metric is selected, the required data can be connected correctly.

Together, Semora and IntaLink help AI agents, NL2SQL workflows, and BI tools understand both:

- which metric should be used;

- how the underlying data should be joined.

The goal is not to make AI generate SQL faster at any cost. The goal is to make generated SQL more consistent, explainable, and governed.

---

## Final Thought

Wrong metrics create wrong decisions.

AI-generated SQL should not rely on field names, table names, or guesses to decide what a business metric means.

Before generating SQL, AI needs governed metric context: approved definitions, formulas, source mappings, dimensions, policies, relationships, and lineage.

The safest AI analytics systems do not just answer quickly.

They know which metric they are answering with.