Repository navigation
A chat answer is never checked against the data #520
Description
#501 measures the proposals the explorer makes. Nothing measures the thing those proposals exist for: whether a person asking a question in chat gets the right number back.
The two are not the same measurement and one does not imply the other. A confirmed definition can be perfect and the answer still wrong — the model can pick the wrong source, join badly, filter on the wrong date column, or ignore the semantic layer and guess from the schema document. Conversely a base with no confirmed mappings at all still answers questions, by reading the schema document and writing its own SQL, and sometimes it is right. Today there is no number for either case.
What is checkable
query_data records very little: its step is {kind, label: source, detail: purpose} — the SQL it ran and the rows it got go only into tool_exchange, truncated, as the message replayed to the model. So the material for scoring a turn is:
- the assistant's final text,
- the tool calls in
tool_exchange, which carry the SQL the model actually ran.
That second one is the useful one. Re-run the SQL the model ran and compare its result with the gold query's — the same numbers-not-names judgement #501 uses, and it survives the truncation because the SQL is short even when its output was cut.
Then two columns rather than one:
sql_right— the SQL it ran computes what the question askedanswer_right— the number it printed is that number
They come apart. A model that runs the right query and then rounds, mislabels a unit, or narrates a different figure is wrong in a way a SQL-only score would call right, and that is exactly the failure a person reading the answer would suffer.
Corpus
Reuse truth/tpch.mappings.json. Each of its 24 definitions is already a question: "what is the total discounted revenue", "how many orders are there", "what is the stock value at cost". The gold SQL is already written. That gives 24 single-value questions for free, in truth/tpch.questions.json keyed by the same id, so a failed answer points at the definition it needed.
A second tier — TPC-H's own 22 queries, which group and order and return many rows — comes after. Their business questions are in the specification; scoring compares result sets, sorted. Single-value first because it is the case where "the answer is a number" is unambiguous, and because it is the tier that closes the loop below.
The loop this is for
The point is not one score. It is:
- run the questions, see which are wrong;
- for each wrong one, ask whether the definition it needed has a confirmed mapping — the script can answer this, since questions and mappings share an id;
- if not, that is a coverage gap: explore again, or write the mapping by hand;
- if it does have one and the answer is still wrong, the fault is in the prompt, the tool, or the definition itself;
- repeat.
"The mappings are complete" then has a definite meaning — every question's definition has a confirmed mapping — and "chat is accurate" has a number that moves. #503 raised proposal coverage from 25% to 33% on TPC-H; this issue is where that number is supposed to show up as answers that are right.
Not in scope
An LLM judging the answers. The gold SQL makes the judgement objective; a judge model would add a second thing that can be wrong, and it would be wrong in ways correlated with the model being judged.
Activity
WaylandYang commentedon Sep 8, 2026
First run, TPC-H, DeepSeek-V3.2, same base for both rows so the only variable is the semantic layer.
--seed)The one failure at baseline is worth writing down, because it is the shape of failure this whole line of work is against. Asked "how many order lines reached the customer later than the date we committed to", the model ran:
l_shipdate, notl_receiptdate. It answered 37171 where the answer is 44197, and nothing about the answer looks wrong — it is a plausible number, from a query that runs, over the right table, on columns that exist. A confirmed definition saying lateness is measured at receipt fixes it, and that is exactly what the seeded row did.The 5x speedup was not the point of the exercise and is the more reusable finding: with a definition in the prompt the model stops searching the schema document and reasoning about columns, and just runs the query it was handed.
What this does not show
TPC-H is too clean to show what a semantic layer is for. Eight tables, honest column names, a comment on every analytical column — the model gets 96% from the schema document alone, so the layer has one question's worth of room. The corpus that would separate them is the wide table with traps from #501: flattened joins,
*_idcolumns that sum without error, names that mislead. On a schema like that the baseline should fall a long way and the seeded run should not.--seedis an upper bound, not the product path. It writes the truth straight intoconcept_mappings. What exploration can actually propose is #501's number, and after #503 that is 8 of 24 definitions covered. The honest middle row — confirm what exploration found, ask again — has not been run yet.Two fixes to the judge, both found by the first run
SELECT COUNT(*), AVG(...), MIN(...), MAX(...)in one go. Reading only the first column scored five correct queries as wrong.roughly, notsame, for the SQL a model ran. It rounds —ROUND(AVG(p_retailprice), 2)gives 594.75 against a truth of 594.74915, which is just pastsame's 1e-6.mappedstill usessame, because that one is picking one definition out of 27 and a near miss there is a real miss.--replayre-scores from the conversations already in the base, so both fixes cost seconds rather than another 40-minute round. That matters more than it sounds: the judge changed twice in the first sitting, and it will change again.WaylandYang commentedon Sep 8, 2026
The middle row is in. Same base throughout, so the only variable is what the semantic layer holds.
The product path already reaches the upper bound, at 10 definitions out of 27. The other 17 are questions the model answers correctly from the schema document alone, so a definition adds nothing to them — which is a statement about TPC-H, not about semantic layers.
The one question that moved, and why
late_lines— "how many order lines reached the customer later than the date we committed to". At baseline the model chosel_shipdate > l_commitdateand answered 37171 against a truth of 44197. With the layer in place it usesl_receiptdateand is right, three runs out of three.The definition that fixed it was proposed by exploration, not written by hand:
So the whole chain closed on its own: exploration read the schema, proposed the definition, a person confirmed it, and the answer changed from wrong to right. That is the first clean evidence in this repo that the semantic layer buys an answer nobody would otherwise get.
It is also the only question of the 24 where lateness is a business convention rather than a column name. Every other question in this corpus names its own answer — "how many customers", "total account balance" — and the model finds it in the schema document. The layer earned its keep on exactly the question where the schema does not say what the business means, which is the case it exists for.
What is still missing from these numbers
late_lineswas stable 3/3 with a definition; nobody has run the whole 24 three times. A single 100% is one sample.WaylandYang commentedon Sep 9, 2026
The wide-table corpus from #501 exists now (
schemas/wide.sql, 55 columns, 60k rows, one flattened e-commerce order table), and it does what tpch could not: it separates the rows.The layer is worth 88 points here and 4 points on tpch. Same code, same model, same judge — the difference is entirely the schema.
Why the baseline collapses
Every definition in this corpus carries a convention the columns do not state: amounts are in cents,
is_test = 1is excluded, an order counts only atord_st in (2,3,4), net figures subtractamt_rfnd. The comments say what each column is — that is what a real warehouse comment says — and say nothing about which rows count.Asked for GMV, the model ran
SUM(amt_total)and answered ¥45,992,467. The truth is ¥32,931,921. It picked the pre-discount column, kept unpaid orders, and kept internal test orders, and the answer reads perfectly normally — a round number, from a query that runs, over the right table, with the units converted correctly.The most instructive failure is
valid_lines. The model read the status comment, inferredord_st IN (2,3,4)by itself, and got 49036 against a truth of 48028. It missed by exactly the 1008 test rows — the one convention that appears nowhere in the schema. It got everything the schema could tell it and still returned a wrong number.valid_ordersis the honest one: the model said, in its own words, "let me search for more documentation on the definition of a valid order", found none, and guessed.The bottleneck moved
Exploration on this corpus scored 0 of 18 definitions covered (tpch: 8 of 24). Not one metric proposal was right; three of them had no aggregate function at all, returning the first row's value. So the middle row of the table above — confirm what exploration proposed, then ask — is not worth running here: there is nothing correct to confirm.
That is the finding. On a schema like this the semantic layer is decisive, and the explorer cannot fill it, because what it needs to know is not in the schema it reads. #502 (keys and sampling) helps with
buyer_idversusqty; it does nothing for "test orders don't count". That convention lives in a document, in past SQL, or in somebody's head, and exploration currently reads none of those.One imperfect case, and it is arguably correct behaviour
The seeded run's single non-RIGHT is
gross_profit: the model ran exactly the right query, got the right number, and then asked the user which of three similar metrics they meant instead of reporting it. Three definitions with adjacent names were in the prompt. Scored as SQL-ONLY, which is what the two-column split is for — but a person asking "毛利是多少" did not get their number, so it is not a false alarm either.WaylandYang commentedon Sep 9, 2026
Conventions as prose, measured. Same base as the post-#553 baseline (01a08642), same 18 questions, zero code and zero mappings: a one-page markdown of the six conventions dropped into the "Data schemas" folder (which #553 makes retrieval-only —
skipped, 2 chunks), reached throughsearch_chunkslike the schema document.--seed)The record (0035) listed a conventions text box among the dead ends and gave a reason of principle without a number. The number is 78%: two-thirds of the gap closes with a page of prose and no structure at all. That entry needs a revision note, and it will get one with the bench branch.
The four misses are the argument for structure, in miniature
净销售额 = GMV − 退款金额) and got 31,120,731.26 against 32,309,293.86. The page is wrong: its "refund total" includes fully-refunded orders that GMV never contained. Prose let me write two definitions that do not compose; an expression over attributes would not have.ord_st) and 已支付 (pay_st) and says AOV divides by the former; the model divided by the latter (29,142 vs 28,808). Two near-synonymous conventions on one page, and the reader picked the other one.So: prose gets a base most of the way for the cost of writing a page, and the misses it leaves are ambiguity — the thing a definition as an expression removes by construction. Both things are true, and the order of work follows from it: #562/#563 gives people a place to put definitions now; #554–#556 is what makes them unambiguous.
WaylandYang commentedon Sep 9, 2026
Two more rows, from #570 (exploration writes a description and asks for the conventions it cannot find; a person writes them into
knowledge_bases.data_conventions; both prompts read them).The description alone moves nothing, which settles the earlier question: the model was never short of grain or units, it was short of rules. Six general rules are worth nine questions; the prose page that also spelled out eighteen definitions was worth three more; structured definitions three more again. And for the first time exploration proposes correct definitions on this corpus — three — with the wrong ones now carrying the right filters and missing on a column choice, which is the same ambiguity that cost the prose run
aovandnet_sales.