AI 智能體寫 SQL 前應看資料表結構還是樣本列？文章提出比較方法

Schema-Only or Row Samples: A Debate Over What an Agent Sees Before Writing SQL

A review queue where two reviewers disagree

The scenario repeats in most teams that let an agent draft SQL: a query passes review, runs in staging, and returns something plausible that is still wrong. One reviewer blames the prompt for hiding the data, while another blames the habit of pasting production rows into a model context. Both are describing the same defect from opposite ends of the pipeline, and neither has a measurement that settles the argument.

An illustrative example: the request is "active customers in the EU last quarter." Schema-only context produces WHERE status = 'active', but the column actually stores single characters such as A, I, and P, so the filter matches zero rows. Add five sample rows to the context and the model copies the encoding, but now a customer email and a partial address have also entered the prompt. The disagreement is real, and it is not resolved by intuition.

Position A: the schema is the contract

The first position holds that DDL, comments, and constraint definitions are the only context an agent should receive, because they are stable, versionable, and reviewable. A schema can be diffed in a pull request, pinned to a commit, and replayed later, which makes agent output reproducible in a way that a row dump never is. Proponents also argue that if the model needs literal values to answer correctly, the missing knowledge belongs in a governed lookup table rather than in a prompt.

The privacy argument is equally concrete. Sample rows are the easiest way to move regulated data into a third-party inference call, and most review processes have no record of which rows were sent. Under this view, schema-only grounding is not a limitation but a control, and any accuracy loss is a signal that documentation or reference data needs work.

Position B: distributions are part of the contract

The second position holds that many conventions live in values rather than in DDL, and no amount of column comments recovers them. Encoded status flags, soft-delete sentinels, negative quantities for refunds, amounts stored in minor units, timestamps in UTC with a local offset column, and trailing spaces from legacy imports are all invisible to a model reading PRAGMA table_info. Reviewers who have debugged these failures tend to stop trusting schema-only prompts quickly.

This camp usually accepts redaction rather than rejection. If samples are generated through a masking function that preserves shape and cardinality while replacing sensitive literals, the accuracy benefit survives and the exposure narrows. The counterargument, of course, is that a masking function is code, and code needs its own tests before anyone trusts it as a boundary.

Where the disagreement actually sits

Both positions agree that the model needs the information; they disagree about where that information should live and who owns it. Schema-only advocates want conventions promoted into documented reference data, while sample advocates want conventions observed empirically and masked. A team can hold both views at once by treating samples as a diagnostic tool and reference tables as the production fix.

A reproducible harness

The only way out of the argument is a small, boring measurement, so the workflow below scores both contexts against the same frozen evaluation set. Everything runs locally in SQLite, and the harness template is unexecuted here, so treat the numbers you get as yours rather than mine.

Step 1: freeze a 30-item evaluation set

Each item stores the question, a gold query, and the expected result, never the model output. Thirty items is deliberately small: it is enough to expose value-convention failures and cheap enough to re-run after every prompt change.

[
  {
    "id": "eu-active-01",
    "question": "Active customers in the EU last quarter",
    "gold_sql": "SELECT COUNT(*) FROM customers WHERE status='A' AND region='EU' AND last_order_at >= '2026-07-01'",
    "expected_hash": "<filled_by_harness>"
  }
]

Step 2: build two context builders

One builder emits DDL only; the other emits DDL plus a small, masked sample per table. Sample with an explicit seed so the context is reproducible across runs and reviewers.

# harness.py - template, not executed in this article
import sqlite3, hashlib, json

MASK = {"email": lambda v: v.split("@")[-1], "name": lambda v: "x" * len(v)}

def schema_context(conn, tables):
    return "\n".join(conn.execute(f"SELECT sql FROM sqlite_master WHERE name='{t}'").fetchone()[0]
                     for t in tables)

def sample_context(conn, tables, per_table=5):
    blocks = []
    for t in tables:
        cur = conn.execute(f"SELECT * FROM {t} ORDER BY rowid LIMIT {per_table}")
        cols = [d[0] for d in cur.description]
        rows = [[MASK.get(c, lambda v: v)(v) for c, v in zip(cols, r)] for r in cur.fetchall()]
        blocks.append(f"-- sample: {t}\n{cols}\n{rows}")
    return schema_context(conn, tables) + "\n" + "\n".join(blocks)

Step 3: score on result sets, not strings

String comparison punishes formatting and rewards nothing useful, so hash the sorted result rows instead. That single choice removes most reviewer disagreement about whether a query "looks right."

def result_hash(conn, sql):
    rows = conn.execute(sql).fetchall()
    return hashlib.sha256(json.dumps(sorted(map(str, rows))).encode()).hexdigest()

def score(items, draft_fn):
    passed = []
    for it in items:
        try:
            sql = draft_fn(it["question"])
            passed.append(result_hash(CONN, sql) == it["expected_hash"])
        except Exception:
            passed.append(False)
    return sum(passed) / len(items)

Step 4: run both arms at k=3 and log failures by cause

Run every item three times per context, because a single pass hides flakiness that reviewers will otherwise discover in staging. Label each failure as encoding, null_handling, unit_scale, or syntax, and keep the labels in the same repository as the eval set.

python harness.py --context schema --k 3 --out results/schema.jsonl
python harness.py --context samples --k 3 --out results/samples.jsonl
python report.py results/*.jsonl --group-by cause

Step 5: decide with a rule, not a preference

Read the failure labels first and the aggregate accuracy second, because a context that wins by two points on syntax while losing every encoding case is the wrong default for a reporting workload. Then apply the rule in the next section.

The decision table

The decision rule

Start with schema-only and run the harness once; if the failure labels contain no value-convention causes, stop and spend the effort on documentation instead. If at least a third of failures are encoding or unit_scale, add masked samples for the affected tables only, then re-run the same eval set. Escalate per table and per failure cause, never per project, so the prompt stays as narrow as the evidence allows.

Running the comparison without a budget fight

This matrix multiplies calls: thirty items, three repetitions, two contexts, plus re-runs after each prompt edit. MonkeyCode's free model access removes the cost objection for the drafting side, and the free server option covers running the harness and SQLite fixtures next to the data. Confirm the current token allowance and server terms on the project page before you plan around them, since promotions change and this article does not treat them as permanent.

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

I have not audited the MonkeyCode repository or its license independently, so check the license terms yourself before adopting it as a dependency, and treat the free tier as an evaluation path rather than a capacity guarantee. The harness itself is plain Python and runs anywhere, which keeps the measurement portable if your tooling changes.

Limitations and who should skip this

Thirty items is a smoke test, not a statistically sound benchmark, and it will miss rare conventions that appear once a quarter. Masking that preserves shape can still leak information through cardinality or timestamp patterns, and no sample context should be used where a proper data-processing agreement is absent. Teams without result-set comparison in their pipeline should build that first, because string-diff review will make the harness output unreadable.

If this eval-set format saves you a review cycle, the most useful thing to send back is the failure taxonomy you ended up with, since that is the part most teams rebuild from scratch.