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.
So the production test should not stop at:
- did the SQL parse?
- did the query run?
- did it stay read-only?
It should test a versioned metric contract.
For every important metric, define:
- entity and grain
- eligible population and exclusions
- tenant and environment scope
- numerator, denominator, and state transitions
- time field, timezone, and late-arrival policy
- currency and precision
- approved sources and joins
- freshness budget
- expected behavior when the question is ambiguous
Then build a controlled fixture with the awkward cases:
- internal and trial accounts
- cancellations and reactivations
- nulls, refunds, and duplicates
- one-to-many joins
- month-end and daylight-saving boundaries
- stale upstream data
- missing conversion rates
Assert more than exact totals.
Useful invariants include:
- the part cannot exceed the eligible population
- exhaustive segments must reconcile to the total
- adding an excluded tenant must not change the metric
- reordering rows must not change the answer
- truncated input cannot produce an unqualified complete result
And test ambiguity on purpose.
If “active customer” has two approved meanings, the correct output may be a clarification request—not a confident query.
The key is to separate failure classes:
- wrong intent
- guessed contract
- invalid or unsafe SQL
- valid SQL with wrong business meaning
- missing source evidence
- incorrect final summary
One accuracy score hides which boundary failed.
Full guide: Natural language SQL metric contracts: test business meaning before deployment
Top comments (1)
Amazing idea, I would have loved to have this built into Microsoft SQL Management Studio and later VS Code. This is definitely a fantastic idea.