Natural language SQL metric contracts: test business meaning before deployment

Valid SQL can still produce the wrong business answer.

An AI SQL assistant may choose real tables, legal joins, and executable filters while misunderstanding what “active customer,” “revenue,” “churn,” or “open pipeline” means.

Natural language SQL needs tests for business meaning, not only syntax and execution.

Turn each important metric into a contract

A metric contract should define the answer another analyst must reproduce:

• business name, description, owner, and version;
• entity and grain, such as one row per account per calendar month;
• population, tenant, environment, and legal-entity scope;
• numerator, denominator, state transitions, and exclusions;
• time field, time zone, calendar, and late-arrival policy;
• currency, conversion source, and precision;
• approved source views and join paths;
• freshness budget and upstream completion signal;
• expected empty, partial, ambiguous, and stale behavior.

“Monthly churn” is a label. “Production subscription accounts active at the start of a UTC calendar month that reached a terminal canceled state before month end, divided by eligible starting accounts, definition v5” is a contract.

Expose a governed semantic surface

Schema context explains tables and relationships. A semantic surface adds which definitions are approved, which grains can be joined, which filters are mandatory, and who owns the meaning.

Prefer versioned reporting views or narrow operations for important metrics. The model can still compose filters and groupings, but it should not rediscover revenue logic from column names during every conversation.

Start with natural language SQL and schema context for MCP database agents.

Build a golden-question suite

For each contract, write realistic natural-language questions with expected normalized intent and expected results on a controlled dataset.

Include paraphrases: “customers lost last month,” “logo churn,” and “how many accounts canceled?” may or may not mean the same thing. The expected outcome can be a metric selection, clarification request, or refusal to guess.

Store the expected contract version, scope, time interpretation, source, result, and tolerance—not only the final prose.

Test ambiguity explicitly

Production questions omit details. Test prompts with missing time zone, currency, tenant, environment, definition, and comparison period.

The assistant should not inherit a risky default from a previous conversation. Define which omissions have safe organization defaults and which require clarification.

A good ambiguity test asks whether the system notices the missing decision before generating SQL.

Use edge-case fixtures

Create a small deterministic dataset containing:

• internal, trial, suspended, deleted, and reactivated accounts;
• null, zero, negative, duplicate, refunded, and late-arriving records;
• events on month-end and daylight-saving boundaries;
• multiple currencies and a missing conversion rate;
• accounts that move between segments during the period;
• an upstream source that is stale or incomplete;
• one-to-many joins that can multiply amounts.

These fixtures reveal semantic failures that a happy-path database snapshot hides.

Assert invariants, not only exact totals

Exact expected values are useful, but invariants catch broader classes of mistakes:

• the part cannot exceed the eligible population;
• segment totals must reconcile to the overall total when segments are exhaustive;
• adding an excluded internal tenant must not change the metric;
• reordering source rows must not change the answer;
• a later as-of time cannot make already-ingested immutable events disappear;
• currency totals must identify the rate date and source;
• truncated input cannot produce an unqualified complete result.

Property-based tests can generate boundary dates, populations, and duplication patterns around these rules.

Compare independent implementations

For material metrics, compare the natural-language workflow with a trusted control query, reporting view, or finance/operations report. The paths should not share every transformation, or both can repeat the same error.

Set tolerances before running the test. When results differ, preserve the selected definition, normalized scope, query plan, source versions, and a movement bridge.

Use database query reconciliation for the answer-level control.

Test schema and definition drift

Run the suite when a source schema, reporting view, metric contract, tool description, prompt template, model, or policy changes.

Bind the test result to those versions. A passing result from definition v4 does not approve definition v5. A renamed column that preserves SQL execution can still alter grain, null behavior, or ownership.

Fail closed when required schema context or metric definitions are stale relative to the source.

Measure the right failures

Separate failure classes:

• intent failure: the wrong metric or scope was selected;
• contract failure: an ambiguity was guessed instead of surfaced;
• query failure: SQL was invalid, unsafe, or exceeded budget;
• semantic failure: SQL ran but violated the metric definition;
• evidence failure: the answer cannot be tied to source and version;
• presentation failure: the structured result was summarized incorrectly.

A single “accuracy” score hides which boundary needs repair.

Concrete example: active customers

The question is, “How many active customers did we have last month?”

The contract defines external production accounts with at least one billable subscription day during the Europe/Copenhagen calendar month, excluding staff and test tenants. Reactivated accounts count once. The approved source is finance_active_accounts_v3.

The fixture includes a trial, a staff account, a mid-month cancellation, a reactivation, and an event at the daylight-saving boundary. The assistant selects the correct contract, asks whether “active” means billable or product-active when no definition is named, and returns the source version and period with the result.

The SQL being valid is the least interesting part of the test.

Production gate checklist

1. Version the metric contract and semantic source.
2. Create golden questions, paraphrases, and ambiguity cases.
3. Use deterministic edge-case fixtures.
4. Assert totals, invariants, scope, freshness, and evidence.
5. Compare material metrics with an independent control.
6. Run on schema, definition, model, tool, and policy changes.
7. Require clarification or abstention when the contract is incomplete.
8. Canary new versions and preserve the previous approved path.

Where Conexor fits

Conexor provides MCP infrastructure for connecting AI clients to databases and APIs through governed tools. Teams can expose curated schema context and approved operations, enforce query limits, and return structured evidence around natural-language database workflows.

Explore natural language SQL with governed database access

Pair contract tests with natural language SQL guardrails.