Spider 2.0 deleted a claim I made this morning

title: Spider 2.0 deleted a claim I made this morning
published: true

tags: sql, ai, database, python

This morning I wrote that swapping the hashed vectoriser in my schema
retriever for a sentence-transformer was a consistent win. I had numbers:
on a pooled Spider 1.0 catalog, strict recall at k=10 went from 82.6% to
88.1%. Consistent, I said. Larger than my own benchmarks suggested.

This evening I ran the same comparison on Spider 2.0 and the effect vanished.

What follows is the run, what I think happened, and the experiment that would
actually settle it, which I have not done yet.

Spider 2.0 is the benchmark this problem needed

Spider 1.0's databases have a median of three tables. That is why my
per-database numbers there are 100% and worthless: returning the entire schema
also scores 100%. I had to invent a pooled variant — every database merged into
one 876-table catalog — to make the task resemble retrieval at all, and an
invented variant is exactly the kind of thing a reader is right to discount.

Spider 2.0-lite needs no such construction. Measured from the distribution:
162 databases, 7,892 tables, a median of 15 tables per database and a maximum
of 785, drawn from real BigQuery and Snowflake warehouses. The databases are
already big.

spider2-lite ships 547 examples, but only the questions whose gold SQL is
public are usable — the rest is held out — which leaves 158 across 103
databases. Table recall on those:

top_k	all gold tables present	per-table recall
5	70.9%	81.7%
10	82.9%	88.5%
20	86.1%	90.5%

Nothing was tuned for this. I downloaded it, pointed the same code at it, and
those are the numbers.

With the caveat the sample size demands. n=158 gives a 95% confidence
interval of roughly [63.4, 77.4] on that 70.9%, and [76.3, 88.0] on the 82.9%.
So the honest comparison with my Spider 1.0 pooled figures is not "within a
point or two" — the intervals are too wide to resolve a point or two. It is
that the two are indistinguishable at this sample size, on databases an order
of magnitude larger and questions written to be hard. That is still the
interesting result. It is just a weaker sentence than the one I wanted to
write.

The claim that died

Spider 1.0 pooled, n=1,034, strict recall at k=10: hashed 82.6%, sentence
model 88.1%. That is the number I quoted this morning.

Spider 2.0-lite, n=158:

top_k	hashed	sentence model
5	70.9%	71.5%
10	82.9%	82.3%
20	86.1%	85.4%

Better at one k, worse at two, and here is the discipline I failed to apply to
my own good news this morning: every one of those differences is a single
question. 112 versus 113. 131 versus 130. I cannot call that a regression any
more than I could have called it a win. The correct statement is that there is
no measurable difference at n=158.

That still kills "consistent win," which is what I said and what was wrong.

The Spider 1.0 effect, meanwhile, is real: at n=1,034 the intervals around
82.6% and 88.1% do not overlap. So the finding is not "the embedder does
nothing." It is measurably useful on one benchmark and unmeasurable on the
other, and the reason matters.

What I think is going on

Spider 2.0's tables carry real descriptions, harvested from the warehouses'
own data dictionaries. Spider 1.0's tables carry none — just identifiers.

When a table has prose describing it, lexical scoring over that prose already
closes the gap between the words a user types and the words a schema uses. A
question about "revenue" finds a table whose description says revenue, without
any embedding involved. The sentence model was earning its keep on Spider 1.0
by compensating for the absence of that text. Give the corpus the text and
there is less left to compensate for.

This is a hypothesis fitted to two data points and I am labelling it as such.

It is also the same finding as the one before it

A few days ago I wrote up the opposite-looking result: adding LLM-generated
table descriptions to a 1,245-object schema made retrieval worse, because
generated prose inflated the document frequency of the domain's own nouns until
"contact" scored an IDF of 0.15 and the contacts table fell out of the top 40.

Those two results look unrelated. They are the same axis:

The value of any semantic layer — a learned encoder, or generated
descriptions — depends on how much prose the schema already has. Where prose
exists, lexical scoring over it does most of the work. Where it doesn't, you
need something to bridge the vocabulary gap. And if you add prose to the same
field as the identifiers, you damage the identifiers.

Which gives a practical rule I can actually stand behind:

• Bare schema, no comments — the common case, and the worst one. A sentence model helps. So do generated descriptions, in their own field.
• Schema with a real data dictionary — you already have the prose. Expect indexing cost from an encoder and little else. Spend the effort on keeping the fields separate instead.

The experiment that would settle it

Two benchmarks differing in everything is a correlation, not a mechanism. The
controlled version is one line of setup: strip the descriptions out of Spider
2.0 and re-run the same comparison on the same questions and the same
databases.

Spider 2.0-lite, k=10	hashed	sentence model
with data-dictionary prose	82.9%	82.3%
descriptions removed	?	?

If the encoder's advantage reappears once the prose is gone, the hypothesis is
confirmed inside one corpus with everything else held constant. If it doesn't,
the explanation is something else — dialect, question style, table size — and I
should stop telling this story.

I'll run it and post the row either way.

The bug that only real data finds

Spider 2.0 carries description as a list for some tables. That crashed
indexing with:

TypeError: sequence item 2: expected str instance, list found

Naming neither the object nor the field. A catalog of 800 tables was
unindexable because one of them described itself in a list instead of a string.

Fixed at the boundary rather than defensively at each reader — ObjectDoc
normalises description, hint and column comments once on construction,
and None stays None.

That bug would have hit anyone pointing this at a real data dictionary. It did
not show up across six schemas I wrote myself, my own test suite, or a live run
against a 127-object Oracle database, because I built all of those and I would
never have thought to put a list there. It took ninety seconds to find on
someone else's data.

Which is the actual argument for running public benchmarks, more than any
number in the tables above. They are not there to prove you are good. They are
there to be data you did not shape.

---

The library is schemagate,
Apache-2.0, currently 0.1.48. The benchmark scripts are in benchmarks/ and
the data comes from the original sources.
BENCHMARKS.md
carries Spider, BIRD and Spider 2.0, and quotes both embedder results rather
than the flattering one.