Your SQL Passed Every Test. Why Is the Business Answer Still Wrong?

How to test business correctness—not just syntax—in AI-powered analytics.

Your Text-to-SQL agent just passed its checks:

• The SQL parses.
• Every referenced table exists.
• The query is read-only.
• The database executes it successfully.
• The result is a neat little revenue chart.

Then Finance says: “That is not revenue.”

This isn't necessarily a model hallucination or a database bug. It's a testing problem: we verified that the query runs, not that it answers the intended business question.

Let's build a small, reproducible test suite that catches these failures before they reach a dashboard or an autonomous agent.

1. The smallest possible counterexample

Imagine an order with two lines and three payments:

order_lines
order_id | line_id | amount
42       | 1       | 100
42       | 2       |  50

payments
order_id | payment_id | amount
42       | 10         |  60
42       | 11         |  50
42       | 12         |  40

A generated query looks perfectly reasonable:

SELECT SUM(l.amount) AS total
FROM order_lines l
JOIN payments p ON p.order_id = l.order_id
WHERE l.order_id = 42;

It returns 450, even though the line total is 150. The join multiplied two lines by three payment records. No SQL syntax checker will save you here.

The problem isn't that the relationship order_lines.order_id = payments.order_id is invalid. It is that joining two fact tables at incompatible grains changes the measure.

And 150 is only the order-line total; it is not necessarily recognized revenue. The metric definition is a separate contract.

2. Create a golden dataset with a known answer

A golden dataset should be deliberately tiny. Include the edge cases that production data hides: multiple child rows, duplicate keys, nulls, refunds, and boundary dates.

This example runs with Python's standard-library SQLite engine:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
CREATE TABLE order_lines (
    order_id INTEGER, line_id INTEGER, amount INTEGER
);
CREATE TABLE payments (
    order_id INTEGER, payment_id INTEGER, amount INTEGER
);
INSERT INTO order_lines VALUES (42, 1, 100), (42, 2, 50);
INSERT INTO payments VALUES
    (42, 10, 60), (42, 11, 50), (42, 12, 40);
""")

bad_sql = """
SELECT SUM(l.amount)
FROM order_lines l
JOIN payments p ON p.order_id = l.order_id
WHERE l.order_id = 42
"""
assert conn.execute(bad_sql).fetchone()[0] == 450

correct_sql = """
SELECT SUM(amount)
FROM order_lines
WHERE order_id = 42
"""
assert conn.execute(correct_sql).fetchone()[0] == 150

This is more useful than asking an LLM whether the SQL looks correct. The expected result is independent of the generator.

3. Test against a business metric contract

Before executing SQL, resolve the user's phrase to a governed metric. A contract might look like:

id: recognized_revenue
owner: finance
status: approved
fact_grain: revenue_event
measure: recognized_amount
aggregation: sum
time_field: recognition_date
excluded_statuses:
  - reversed

This is an illustrative contract, not an API exposed by a particular product.

It gives your test runner something specific to check:

• Did the agent choose recognized_revenue rather than booked_order_value?
• Did it use recognition_date, not order_date?
• Did it exclude reversals?
• Did it preserve the approved grain?

A query can pass all five syntactic checks and still violate every one of these semantic rules.

4. Test cardinality, not just join-key existence

A discovered relationship is a candidate path, not a guarantee of safe aggregation.

Before allowing a join into a metric query, inspect its expected cardinality and the actual effect on row counts.

For example:

SELECT
    COUNT(*) AS joined_rows,
    COUNT(DISTINCT l.line_id) AS distinct_lines
FROM order_lines l
JOIN payments p ON p.order_id = l.order_id
WHERE l.order_id = 42;

For this fixture, the output is:

joined_rows   = 6
distinct_lines = 2

That is a clear warning if the query intends to sum a line-grain measure. Don't use a generic rule that every row-count increase is wrong; one-to-many joins can be valid for other analytical purposes. Check fanout relative to the measure's declared grain.

If payment totals are needed, aggregate both facts to order grain first:

WITH lines AS (
  SELECT order_id, SUM(amount) AS line_total
  FROM order_lines
  GROUP BY order_id
),
payments_by_order AS (
  SELECT order_id, SUM(amount) AS paid_total
  FROM payments
  GROUP BY order_id
)
SELECT l.order_id, l.line_total, p.paid_total
FROM lines l
JOIN payments_by_order p USING (order_id);

Expected: 42 | 150 | 150.

5. Add metamorphic tests

Golden fixtures are excellent, but they cannot cover every generated query. Metamorphic testing checks how an answer should behave when inputs are deliberately changed.

Examples:

Transformation	Expected behavior
Duplicate an unrelated customer row	Revenue should not change
Add a payment record without changing recognized-revenue events	Recognized revenue should not change
Split one 100-unit line into two 40/60-unit lines	Order-line total should stay 100
Add a reversed revenue event	Approved revenue should exclude it
Shift a transaction exactly across the fiscal boundary	Only the correct period should change

These tests are powerful because they detect structural errors without requiring a manually calculated answer for every large dataset.

Caution: Each invariant must follow the actual metric definition. If your metric intentionally counts payment events, adding a payment event should change it.

6. Turn failures into a release gate

A minimal CI pipeline for a data agent can be:

Pull request / prompt change / semantic mapping change
                  ↓
          Golden fixtures
                  ↓
       Generated query tests
                  ↓
      Metric contract checks
                  ↓
      Grain + fanout checks
                  ↓
       Metamorphic tests
                  ↓
           Release gate

A simple test harness can compare the agent's output to an independently computed oracle:

def test_agent_revenue(agent, fixture_db):
    response = agent.query(
        "What is recognized revenue for order 42?",
        database=fixture_db,
    )
    assert response.metric_id == "recognized_revenue"
    assert response.value == 150  # fixture assumes all 150 is recognized

The agent.query interface above is pseudocode. Replace it with your application's actual entry point. The fixture's accounting assumption is explicit: in this test, all 150 units are recognized and none are reversed.

Also run negative cases: ambiguous “revenue,” unauthorized fields, a missing relationship, and a query that cannot be answered safely. A good agent should sometimes ask for clarification or refuse to guess.

7. What should be automated—and what needs human approval?

Automate what machines can verify deterministically:

• SQL parsing and read-only boundaries
• Schema and column existence
• Golden-fixture results
• Cardinality and fanout checks
• Metric-to-field consistency
• Regression and metamorphic tests

But do not ask the model to certify its own business definition. Finance, Operations, or the metric owner must approve what revenue, active customer, or gross margin actually means.

This is where semantic and relationship infrastructure becomes useful. Semora maintains business terminology, semantic mappings, metric/dimension definitions, clarification flows, and SQL generation/validation. IntaLink discovers candidate table/field relationships and provides relationship paths as context. Together, those capabilities can supply inputs for stronger testing—but automatic grain-safe query certification and this CI harness are proposed engineering practices, not claimed existing product features.

The important distinction: relationship discovery helps find how tables may connect; metric-aware tests determine whether a particular join preserves the intended calculation.

8. Track business correctness separately from execution success

Don't report only “98% SQL execution success.” Track at least:

Metric	What it tells you
Execution success rate	Does SQL run?
Metric-selection accuracy	Did it choose the governed business concept?
Grain/fanout test pass rate	Did joins preserve measure semantics?
Golden-answer accuracy	Does it match known results?
Metamorphic test pass rate	Does it respect expected invariants?
Clarification accuracy	Does it avoid guessing when meaning is ambiguous?

Also measure false rejections. A test system that blocks every complex query isn't trustworthy; it's unusable.

The takeaway

An AI analytics agent can generate valid SQL, pass a database permission check, and produce a beautiful chart—while answering the wrong business question.

The fix is not another generic “Is this SQL correct?” prompt.

It's a test strategy that treats business meaning, grain, relationships, and expected behavior as executable contracts.

Stop testing only whether the query runs. Start testing whether the business answer survives scrutiny.