## TL;DR
Build a golden set of fixed questions, each paired with verified SQL and an expected result snapshot, then run the agent against it on every prompt or model change and diff the outputs. Assert on result semantics, row counts, key aggregates, set equality, not on exact SQL text, because two different queries can both be right. It works because testing an agent is testing a nondeterministic function: pinned expectations are the only thing that catches silent regressions from a model upgrade.

```text
how to test a data agent against known-good outputs
```

## Use this when
- Shipping a new agent version or changing the model
- Tweaking prompts and needing to know what broke
- You need a regression gate before rollout
- Comparing two agent setups objectively

## Not for
- One-off agent experiments nobody will repeat
- Subjective analysis quality, like narrative insight
- Replacing production monitoring of live agent behavior

## Steps

1. Collect representative questions covering your real workload:

```yaml
# evals/golden.yaml
- id: q01
  question: "What was total revenue by category in Q3 2026?"
  verified_sql: "SELECT category, SUM(amount_cents)/100.0 AS revenue FROM analytics.orders WHERE ordered_at BETWEEN '2026-07-01' AND '2026-09-30' AND is_test = false GROUP BY 1"
  tags: [aggregation, date-filter, exclusion]
- id: q02
  question: "Which customers churned last month?"
  verified_sql: "..."
  tags: [join, window-function]
```
Expected output: a versioned file of 20 to 50 questions spanning your hardest patterns: joins, window functions, date math, and exclusion filters.

2. Snapshot the expected results by running the verified SQL:

```sql
-- run each verified_sql, store results as CSV in evals/expected/q01.csv
-- freeze them: expected outputs never change unless the question does
SELECT category, revenue FROM (/* verified_sql for q01 */) ORDER BY category;
```
Expected output: a directory of expected result files, committed to version control. These are the known-good outputs the agent is measured against.

3. Build the harness that runs the agent and captures everything:

```python
for case in golden_cases:
    result = agent.ask(case["question"])
    record = {
        "id": case["id"],
        "agent_sql": result.sql,
        "agent_rows": result.rows,
        "sql_ran": result.executed_ok,
    }
    save(record, f"evals/runs/{run_id}/{case['id']}.json")
```
Expected output: one record per case per run with the SQL the agent wrote, whether it executed, and the rows it returned.

4. Compare with tolerance, never exact text match:

```python
def results_match(expected, actual):
    # same row count, same key columns as sets, floats within 0.1%
    if len(expected) != len(actual): return False
    for e_row, a_row in matched_by_key(expected, actual):
        if not floats_close(e_row, a_row, rel_tol=0.001): return False
    return True
```
Expected output: a pass or fail per case. Row order differences and float rounding do not fail; missing rows and wrong aggregates do.

5. Track the pass rate over time and gate releases on it:

```sql
SELECT run_id, model_version, prompt_version,
       SUM(CASE WHEN passed THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS pass_rate
FROM ops.agent_eval_runs GROUP BY 1, 2, 3 ORDER BY run_id;
```
Expected output: a pass-rate series per model and prompt version. A new version ships only if its pass rate meets or beats the current one.

## Variant phrasings

### evaluate text-to-SQL agent
The golden set plus semantic diffing is the evaluation. Exact-SQL matching undercounts correct answers and teaches nothing.

### golden dataset for data agent
Curate it from real user questions, not invented ones. The eval is only as good as its coverage of what users actually ask.

### regression testing LLM SQL
Run the full set on every prompt change, no matter how small. Prompt edits are the number one source of silent regressions.

## Why it happens
Agent behavior drifts for reasons outside your control: model updates, prompt tweaks, even nondeterministic sampling. Without pinned expectations every change is a blind gamble, and regressions surface as user complaints weeks later. The golden set converts drift into a number you can watch, and semantic comparison (rather than text matching) keeps the test honest about what correctness actually means: the right answer, however phrased in SQL.

## Edge cases
- Live data changes under the eval: freeze expected outputs against a snapshot, or rerun verified SQL at eval time and diff agent-vs-verified on the same data.
- Flaky agent outputs: run each case 3 times and score the majority, so one bad sample does not fail a good version.
- Schema evolution breaks old cases; version the golden set with the schema and retire cases that no longer apply.
- Partial credit: a query that is almost right is still wrong for a gate, but log near-misses separately to guide prompt work.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_9ZNN1v-5KEWjRz1qer-0-g