How to Test a Schema Card Against a Set of Planted Questions

The question set is the asset. Get it from evidence, not imagination.

Sources, in order of value:

1. The query log of your SQL assistant or BI tool. This is the highest-value source because it reflects real phrasing.
2. Slack and ticket threads where an analyst asked for a number.
3. Saved dashboard definitions, which encode recurring questions.
4. Analyst interviews for questions that failed and never made it into a ticket.

Target 20 to 50 questions. Fewer than 20 and a single rename can swing recall by several points; more than 50 and the annotation cost exceeds the benefit at the start. Record every question verbatim, including typos and business jargon. "Refunds by region last month" is a better fixture entry than "aggregate refund amounts grouped by shipping region for the previous calendar month," because the agent reads the former.

Balance the set deliberately. A useful rough split:

The trap category matters because ambiguity is where agents fail quietly. Google's team calls out "What are the best-selling shoes?" as a question that is ambiguous about whether "best selling" means most orders or most revenue, and whether returned orders count Techniques for improving text-to-SQL. If your card resolves that ambiguity, the fixture should prove it.

Review work on LLM-based text-to-SQL systems also lists rare and complex SQL operations, including multi-table joins and nested subqueries, as a recurring source of failure From Natural Language to SQL. Those are exactly the questions you want in the join categories, because they are the ones a card edit is most likely to break.

Keep the file plain. A YAML file in the repo, reviewed like code, is enough:

questions:
  - id: q001
    text: "Refund total for EMEA last month"
    needs_columns: ["order_adjustments.adj_amount", "orders.ship_region"]
    needs_joins: ["orders.order_id = order_adjustments.order_id"]
  - id: q002
    text: "Best-selling shoes"
    needs_columns: ["products.product_name"]
    needs_notes: ["cat_id2 = 'Footwear' means footwear"]

Annotation is the step teams skip, and it is the step that makes the score meaningful. The rule: for each question, write down the smallest set of schema facts without which a correct query is impossible.

Three annotation fields do the work.

needs_columns is a list of table.column pairs. Be strict. If the answer requires ship_region, record orders.ship_region, not "region." If the question also needs a filter column, such as order_adjustments.adj_type, record it too. A useful test: could a competent analyst write a correct query from the card without seeing the DDL? If not, the annotation is incomplete.

needs_joins is a list of join predicates in a.key = b.key form, written exactly as you want them to appear on the card. For a three-table question, list each hop. This is what catches the failure mode where a model drops a join leg or applies a filter to the wrong table Text2SQL Accuracy best practices.

needs_notes is for business semantics that live in data values or in naming conventions, such as the footwear category code or a status column whose allowed values are pending | fulfilled | refunded | cancelled. Placement matters as well as presence: models use the start and end of a long context more reliably than the middle, so a note buried mid-card is weaker than one placed next to its table Schema Pruning for Data Agents.

Derive labels mechanically where you can. BudgetSchemaBench builds its relevance labels from gold SQL rather than from human or LLM annotation, which removes annotator noise and makes the set reproducible BudgetSchemaBench. You can do a lightweight version of this: for each question, write the gold SQL once, then extract the referenced tables and columns with a parser. That gives you a cross-check against your hand annotation. Where the two disagree, one of them is wrong, and finding out which is the useful work.

A worked annotation, with numbers. Suppose the card covers three tables and the audit finds these totals over 40 planted questions:

• 40 questions in total.
• 26 name at least one column (the other 14 are count or existence questions that only need the table).
• 40 depend on at least one column or table name being present.
• 18 depend on a specific join path.
• 9 depend on a business note.

Those counts set the ceiling on what recall can tell you. If only 18 questions exercise joins, a card that drops every join still scores 55 percent on a join-blind metric. That is why the balance table above asks you to spread questions across categories before you start scoring.

Recall is the fraction of planted questions whose required column or join the card still exposes. Compute it per dependency type, because a single blended number hides join regressions behind strong column coverage.

A dependency counts as exposed when its text appears on the card after normalization: case folded, whitespace collapsed, and matched on whole tokens so that status does not match status_updated. For joins, match the predicate in normalized form and accept either side of the equality, since cards write joins in both directions. For notes, match the key phrase, not the entire sentence.

import re, yaml

def norm(s):
    s = s.lower()
    s = re.sub(r"\s+", " ", s)
    return " " + s.strip() + " "

def has_token(card, needle):
    pattern = r"(?<![a-z0-9_])" + re.escape(needle.strip()) + r"(?![a-z0-9_])"
    return re.search(pattern, card.lower()) is not None

def join_exposed(card, pred):
    left, right = [x.strip() for x in pred.split("=")]
    a = left.split(".")[-1]
    b = right.split(".")[-1]
    return has_token(card, a) and has_token(card, b)

def score(card_text, spec):
    col_hits = col_total = join_hits = join_total = note_hits = note_total = 0
    misses = []
    for q in spec["questions"]:
        for col in q.get("needs_columns", []):
            col_total += 1
            if has_token(card_text, col.split(".")[-1]):
                col_hits += 1
            else:
                misses.append((q["id"], "column", col))
        for pred in q.get("needs_joins", []):
            join_total += 1
            if join_exposed(card_text, pred):
                join_hits += 1
            else:
                misses.append((q["id"], "join", pred))
        for note in q.get("needs_notes", []):
            note_total += 1
            if note.lower() in card_text.lower():
                note_hits += 1
            else:
                misses.append((q["id"], "note", note))
    return {
        "recall_columns": col_hits / col_total if col_total else 1.0,
        "recall_joins": join_hits / join_total if join_total else 1.0,
        "recall_notes": note_hits / note_total if note_total else 1.0,
        "misses": misses,
    }

if __name__ == "__main__":
    card = open("schema_card.md").read()
    spec = yaml.safe_load(open("planted_questions.yaml"))
    result = score(card, spec)
    print(result["recall_columns"], result["recall_joins"], result["recall_notes"])
    for m in result["misses"]:
        print("MISS", m)
    assert result["recall_columns"] >= 0.95, "column recall regressed"
    assert result["recall_joins"] >= 0.90, "join recall regressed"

Two implementation notes. First, the column check above uses the bare column name, which is deliberate for a first pass, but it will produce false positives when two tables share a column name. Tighten it by requiring the table name within a short window of the column, or by matching the exact table.column string if your card formats it that way. Second, do not use an LLM judge for the gate. Keep the scoring deterministic so a schema change is the only thing that moves the number.

For teams that want a semantic layer on top of the string match, an embedding model can score whether a paraphrase of the dependency appears even when the exact token does not. All the major text and embedding models are reachable through one API key with Quanta Inference, which keeps that optional layer outside your deterministic gate.

Set thresholds from a measured baseline, not from a round number. Run the scorer against the card as it exists, record the three recall values, then set the floor just below each one so ordinary edits pass and structural losses fail. A starting shape that holds up in practice:

Treat the per-question check as an allowlist exception. When a column is genuinely retired, update the question file and the card in the same pull request, so the diff shows the retirement rather than hiding it.

Wiring. The fixture is a script plus two files. Run it in three places:

1. Pre-commit, so authors see failures before pushing.
2. CI on every pull request that touches migrations or the card, so a schema change cannot merge without a card update.
3. A nightly job against the live card, so drift introduced outside the repo is caught too.

Keep the question file append-only in spirit: when a question stops being relevant, move it to an archive section rather than deleting it, because a question that was once real tends to come back.

Common failure modes and their fixes:

• False pass on shared column names. status appears on three tables, so a join to the wrong table passes. Fix by requiring the table name nearby.
• False fail on rewording. The card says "order placed timestamp" and the key is order_placed_at. Fix by keeping exact identifiers on the card next to the plain-language description; the identifier is part of what you are testing.
• Scope creep. Someone adds 200 questions and the gate becomes slow and noisy. Fix by capping the fixture at 50 and rotating archived questions in later.
• Scoring the DDL instead of the card. If you accidentally score the CREATE TABLE dump, recall is always 1.0. Fix by asserting the card file does not begin with CREATE TABLE.

The last point is not hypothetical. A team that scores the wrong artifact learns nothing and gates on everything passing, which is worse than no gate because it creates false confidence.

There is a second use for the same scripts. Validation checks written as SQL constraints, such as NOT NULL, UNIQUE and CHECK rules that abort an insert when data violates the rule, already give you a mechanical way to police the database itself Simple Data Validation in SQL. Pairing those constraints with card recall means an insert that violates a rule and a card that has dropped the rule's column both fail loudly, one at write time and one at review time.

Recall answers one question precisely: does the card still expose the facts a planted question needs? It does not measure whether the agent uses them well. Keep the distinction clear, because it decides what to fix when a number drops.

A useful diagnostic comes from BudgetSchemaBench, which sweeps how much schema fits in context and compares representations. With raw serialization, raising the schema budget from 2.5 percent to 50 percent of the catalog improved execution accuracy on 1,279 held-out questions by 18 percentage points under lexical retrieval but only 3 points under dense retrieval, because the dense retriever already found most required tables at the smallest budget BudgetSchemaBench. The lesson for your fixture: coverage of the right facts is the binding constraint under weak retrieval, and representation details matter far less once the needed columns are present. That is the case for measuring recall separately from accuracy.

The other side of the ledger is execution. Benchmark work reports that even strong models sit well below 100 percent execution accuracy on realistic schemas, and that schema linking is the largest driver of misses Text2SQL Accuracy best practices. Review work on LLM-based text-to-SQL systems catalogs the same difficulties: schema understanding, rare SQL operations and cross-domain generalization all degrade accuracy From Natural Language to SQL. Recall cannot fix an agent that ignores its context, but it removes the excuse.

So run two checks, in this order:

1. Recall on every schema change. Fast, deterministic, no model calls. This is the gate.
2. Execution accuracy on a smaller subset of the planted questions, run against a staging database, after the card passes. This catches the case where the card is complete but the agent is misconfigured.

You can also borrow the remove-the-needed-table probe. BudgetSchemaBench found that when required tables were removed from the context, 94.6 percent of correct predictions still named one of them exactly, consistent with models reconstructing absent schema from parametric knowledge BudgetSchemaBench. That is a warning: a card can look like it works while the model is quietly guessing. The planted question set and its exact identifiers make guessing visible, because you score the card, not the model.

A related move is to audit which statements actually touch the tables your card describes. Database engines expose audit records with fields such as event_time, object_name, statement and succeeded, which let you confirm that the tables and columns on your card are the ones queries really use SQL Server Audit Records. Questions drawn from that audit trail are better fixtures than questions drawn from memory.

When the fixture is green and you want to move from checking a card to shipping the query surface itself, a specialist can take the schema, the question file and the requirements and build alongside your team through Quanta. For teams deciding how far to automate this, a free scoping conversation is available through Quanta Consulting.