DEV Community: Ashish sinha

Moving AWS Glue jobs to OCI Data Flow: a working map

Ashish sinha — Sun, 27 Sep 2026 20:04:58 +0000

If you run Spark ETL on AWS Glue and need to move it to Oracle Cloud, the good news is that most of it maps cleanly. The bad news is the few parts that don't, and they are exactly the parts Glue made easy. Here is the map I use.

Why teams move: cost

Most teams I talk to move for one reason: the bill. OCI list prices for compute, block storage and Autonomous Database are often lower than the AWS equivalents, and OCI includes the first 10 TB of outbound data transfer each month at no charge, which matters for pipelines that ship data out. Don't take my word for it, though: price your own workload. That is one reason the tool at the end of this post shows the monthly cost of every part before it builds anything.

The short version

AWS	OCI	Notes
Glue ETL job (PySpark)	OCI Data Flow application	Managed Spark. You upload the script to Object Storage and run it.
S3	Object Storage	Paths change from `s3://bucket/key` to `oci://bucket@namespace/key`.
Glue Data Catalog	OCI Data Catalog (as a Hive metastore)	Data Flow can use a Data Catalog metastore for your tables.
Glue crawlers	Data Catalog harvesting	Harvest a bucket to discover the schema.
Glue triggers / workflows	Airflow (or another scheduler)	Your DAGs keep working. Point the operators at Data Flow.
Lambda glue code around the job	OCI Functions	Small handlers move over with little change.

What changes in the script

1. Drop the Glue wrappers. GlueContext, DynamicFrame and job.commit() are Glue-only. Data Flow runs plain Spark, so use a normal SparkSession and DataFrames.

# Before (Glue)
from awsglue.context import GlueContext
glue = GlueContext(SparkContext.getOrCreate())
df = glue.create_dynamic_frame.from_catalog(database="raw", table_name="events").toDF()

# After (Data Flow)
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("events").getOrCreate()
df = spark.read.parquet("oci://raw@mynamespace/events/")

2. Change the paths. Every s3:// path becomes oci://bucket@namespace/. Put the bucket and namespace in job arguments, not in the code, so the same script runs in dev and prod.

3. Replace job bookmarks. Glue bookmarks remember what was already processed. Data Flow has no built-in equivalent, so keep a small watermark yourself: a table or file with the last processed date or file name, read at the start of the run and written at the end.

4. Iceberg. If Glue was writing Iceberg tables, Spark on Data Flow can write them too. Add the Iceberg runtime to the application and point the catalog at Object Storage.

What stays the same

Your Spark logic. Joins, window functions, UDFs, partitioning: all of it is Spark, not Glue, and runs unchanged. In my experience the transformation code is the smallest part of the move. The paths, the catalog and the scheduling are where the work is.

Try the move before you commit to it

The hardest part of a migration is not the code, it is getting a place to try it. A ticket, a week, a compartment, and then nobody deletes it.

So I built a chat for that on OCI. You paste a mapping like the table above, or a Git folder with your DAG and Spark job. It builds the bucket, the Data Flow application, the Data Catalog, Airflow with your DAG loaded, and an Autonomous Database for the gold tables, shows you the monthly price of each part first, and deletes all of it when the sandbox expires (1 to 30 days).

3-minute demo: https://youtu.be/PGnDhkchqUY
Overview, cost breakdown and FAQ: https://ashishsinha1602.github.io/oci-sandbox-factory/
Free one-click install in your own tenancy: https://github.com/ashishsinha1602/oci-sandbox-factory
Worked example (Airflow + Spark, "move this to OCI"): https://github.com/ashishsinha1602/oci-sandbox-factory/tree/main/examples/telemetry-pipeline

If you have moved Glue jobs to OCI and hit something this map misses, tell me in the comments. I'll add it.

I built a chat that spins up Oracle Cloud sandboxes and deletes them when you're done

Ashish sinha — Sat, 26 Sep 2026 23:22:51 +0000

Every team I've worked with has the same problem with cloud sandboxes: they take a ticket to get, a week to arrive, and nobody ever deletes them. So I built a factory for them on Oracle Cloud. You describe what you need in a chat, it builds it in your tenancy with Terraform, hands you the links and credentials, and destroys it when its lifetime ends.

Here it is, start to finish, in under 2 minutes:

Full write-up, cost breakdown and FAQ: https://ashishsinha1602.github.io/oci-sandbox-factory/

What the video shows

A chat with nothing running.
Grafana from one sentence. "Deploy Grafana for my team from the public image on port 3000." The assistant plans the container, prices it from Oracle's published price list, and notices the code needs an admin password. It asks for it in a box on the page. The value never reaches the AI. One click, and it's building.
A migration question. "I have Airflow writing to S3 and Glue building Iceberg tables. Move it to OCI." It maps each piece: S3 to Object Storage, Glue jobs to Spark on Data Flow, the Glue catalog to Data Catalog, and Airflow stays Airflow. Your DAGs run unchanged.
The repository. Paste a GitHub folder link. It reads the DAG and the Spark job and builds the pipeline: a bucket for raw and gold data, a Data Flow application, a Data Catalog, Airflow with the DAG loaded, and an Autonomous Database for the gold tables.
The tour. Grafana, signed in with the password you gave. Airflow with the DAG green in four steps. The gold tables served as REST straight away.
Ask the data. A chat UI over the database, and an MCP endpoint so Claude, Cursor or any agent can discover the tools, pick the tables and run the query.

What it will build

Ask for	You get
A database to explore or to wire into agents	Autonomous Database with Select AI and REST on, a chat UI, an MCP endpoint
Your app from a Git folder with a Dockerfile	The image built inside OCI, served on HTTPS
An AWS Lambda, or any small handler	An OCI Function. Lambda code runs unchanged, optionally on a schedule
A Glue or Spark job	A Data Flow application, optionally writing Iceberg tables, with a query app to read them
An Airflow plus Spark pipeline	Airflow with your DAGs, Data Flow, Data Catalog, gold tables in Oracle
Queues, NoSQL, Kafka, buckets	OCI Queue, NoSQL tables, a Kafka cluster, Object Storage

It builds the design you name and nothing you didn't ask for. If you say "no database", there is no database. If you describe code you don't have yet, it writes it.

The parts I'm proud of

Honest pricing. Every proposal comes with a cost table from Oracle's live price list, per part, with the free allowances and the per-use services called out. A paid Autonomous Database is about $500 a month while it exists, so the assistant defaults to the pay-per-use pieces and prices the database as an option you can drop.

It follows your architecture. Paste a mapping and it builds exactly that list. It may say in one sentence that another design would be better. Then it builds yours.

Sandboxes die. Each one has a lifetime of 1 to 30 days and a budget. A reaper destroys it when time is up. Every card has a Destroy button for sooner.

Nothing leaves your tenancy. The install is one Resource Manager stack. The workers, the database that runs the app, the AI calls to OCI Generative AI, all of it stays inside your account. Users sign in to the app with their own login, no OCI account needed.

Install it

One click. It needs a tenancy administrator and a Pay-As-You-Go or paid account, and it takes about 15 minutes.

The Terraform, the prerequisites and the example workloads are on GitHub: ashishsinha1602/oci-sandbox-factory. Read the prerequisites first; they list exactly what the install creates and what it grants.

How it's built

Terraform on OCI Resource Manager for everything, the install and every sandbox. No local tooling.
APEX on an Autonomous Database for the app. The assistant runs inside the database and calls OCI Generative AI.
Container instances as workers. They turn requests into stacks, build images inside OCI with kaniko, and never hold a laptop's credentials.
Select AI switched on in every database, so any table can be asked in plain English.
An end-to-end suite that runs inside OCI and builds every sandbox type for real, checks it live, and destroys it.

What's next

RAG and agents with only what Oracle provides: Select AI's vector index crawling a documents bucket, images described as text first so pictures and scans are searchable, Select AI Agents over the documents and the tables, and Oracle's own MCP server.

Try it, break it, and tell me what you'd want it to build next.

Overview and install: https://ashishsinha1602.github.io/oci-sandbox-factory/

Same question, same database, two callers

Ashish sinha — Wed, 23 Sep 2026 02:34:49 +0000

Most text-to-SQL stacks send the model the whole schema and put the access check after the query is written. That ordering is the bug, and it is easier to show than to argue about.

The whole idea in one picture

Left -- the caller holds no roles. hr_compensation never reaches the model at all: 9 of 42 objects selected, 570 prompt tokens instead of 2,511, one object hidden from this caller.

Right -- same question, same schema, same database. The caller now holds the payroll role, so hr_compensation is the first table in the prompt, and nothing is hidden.

Nothing about the question changed. The identity did.

Running against a real schema

That is the Studio on a claims schema: 14 of 51 objects selected, 1,212 prompt tokens against 3,237 for the full schema, and three objects withheld because this caller has neither actuarial nor phi. The restricted ones are listed on the left, so you can see what was withheld rather than guess.

Install

pip install schemagate

SQLite, PostgreSQL 16, Oracle 26ai, SQL Server 2022 and MySQL 8.4 are each certified against a live instance. There is also an MCP server -- now in the official MCP registry -- a LangChain retriever, and a CLI.

The honest part

A selection step is only as good as its retrieval, and mine is not state of the art. On Spider pooled into one catalog (876 tables, no hint about which database the question belongs to), every gold table is present 82.6% of the time at top 10. On Spider 2.0-lite, which is real BigQuery and Snowflake schemas rather than benchmark ones, it is 64.0% at top 10 across all 247 usable questions. Both numbers, the harness, and the two measurement errors I had to correct along the way are in BENCHMARKS.md.

Apache-2.0: github.com/ashishsinha1602/schemagate

38% of an analyst's questions get "no records found" when the data is right there

Ashish sinha — Wed, 23 Sep 2026 01:04:31 +0000

Your text-to-SQL agent has a failure mode that logs nothing, alerts nothing, and returns a confidently wrong answer. I finally put a number on how often it can happen, and on a small schema with an ordinary role split the number is 38.5%.

The failure

Someone asks a question whose answer lives in a table they are not allowed to read.

The model is handed the whole schema, because that is what nearly every text-to-SQL stack does. It writes a perfectly correct query against that table. The query executes. Row-level security removes every row. The application receives an empty result set and reports, truthfully as far as it knows, that there are no records.

"No rows matched" and "you are not allowed to see the rows that matched" arrive at the application as the same thing: an empty list. There is no exception to catch, so no alert fires, no retry triggers, and nothing appears in the logs to review later.

And the two readings lead somewhere different. "No unpaid invoices" is an answer someone might act on. "You cannot see the unpaid invoices" is a reason to go ask someone else. Collapsing the second into the first is not a degraded answer. It is a confidently wrong one.

Why nobody measures it

Because there is nothing to count. Every observable signal -- exit status, row count, latency, logs -- is identical to the honest empty result.

So I stopped trying to catch it at runtime and defined it structurally instead.

A question is unanswerable for a caller when the tables its correct answer needs include at least one the caller may not read.

That needs gold labels -- question to tables -- which is the same thing Spider and BIRD already give you. What it does not need is an LLM. No model is run and no SQL is executed. The rate is a property of your schema, your role model and your question mix, not of whichever model you happen to have wired up this week.

silent-denial rate = unanswerable questions / total questions

The number

A 42-object demo schema. Thirteen labelled questions. An unremarkable role model: finance tables to a finance role, salary to payroll, everything else open.

  caller           answerable  unanswerable  silent-denial
  analyst                   8             5         38.5%
  finance                  12             1          7.7%
  hr                        9             4         30.8%
  cfo                      13             0          0.0%

  detected before any SQL runs:  naive 0 of 10   scoped 10 of 10

Two questions in five that the analyst asks come back "no records found" while the data sits there. Not because retrieval failed. Not because the model is weak. Because the permission check happens after generation instead of before it.

The last line is the part I care about most. A full-schema pipeline detects none of them, because an empty result is its only signal and the empty result is indistinguishable from the honest one. A pipeline that scopes the schema by caller identity detects all ten, for free, before any SQL is written: the table the answer needs is simply not in the set this caller may see, so the system knows the question is unanswerable and can say so.

That is not a smarter model. It is the same information, one step earlier.

When you have no gold labels

Most databases do not have them, and a useful answer is still available.

For each restricted table, build a probe question out of that table's own words -- its name, its hint, its description -- and ask whether an unscoped selection puts it in the top k. If it does, then a question phrased the way your schema describes itself lands on a table the caller cannot read, and a full-schema pipeline will write SQL against it.

On the same schema, all five restricted tables come back at rank 1.

The probe is generated from the schema rather than written by hand, deliberately. A hand-written probe proves only that its author could think of a question, which makes the result a property of the author.

The bug I shipped in the first draft

Worth writing down, because it is the failure this whole area keeps producing.

My first reachability run reported 0 of 5 reachable. Total confidence, clean output, completely wrong.

The cause: I ran the probe with principal=None. But None is not "unscoped" -- a caller with no roles is denied, because absence of a role is absence of permission. So the probe removed exactly the tables it was looking for, and reported that nothing was reachable.

A measurement whose apparatus deletes the thing being measured will report zero, every time, with no error. Which is the same shape as the bug the tool exists to find. There is now a named regression test for it, and every test in the suite is paired: one case where the metric must be zero, one where it must not, so the metric can be shown to move at all.

What this does not claim

A reachability "no" is weak evidence. It means a table is hard to reach by its own vocabulary, not that no question reaches it. Only a gold set gives you a rate.
No rate here is a model score. Whether a particular LLM picks the denied table on a given run is a different question. This measures whether the path exists, which is the part you can actually fix.
A bad label is not a finding. Gold tables the catalog has never heard of are reported separately and excluded from every rate, so a typo cannot inflate your number.
This is not a security control. It is a measurement. Your database's grants and RLS policies remain the thing that enforces access. What scoping buys you is that the model stops generating queries RLS then has to reject -- which is where the silence comes from.

Reproduce it

The whole measurement is about fifteen lines on top of schemagate, which is the thing that already knows your catalogue and its roles. Nothing else is needed and no model is called:

from schemagate import Catalog, Principal
from schemagate.models import allowed

def unanswerable(catalog, gold, principal):
    """Questions whose gold tables include one this caller may not read."""
    leaf = lambda n: n.rsplit(".", 1)[-1]
    denied = {leaf(q) for q, d in catalog._docs.items()
              if not allowed(d.roles, principal)}
    return [q for q, needs in gold if {leaf(n) for n in needs} & denied]

# your database, your roles, your labelled questions
cat = Catalog().bootstrap("postgresql://host/db")
cat.restrict("hr_compensation", ["payroll"])
gold = [("salary and pay grade by employee", {"hr_compensation", "hr_employee"}),
        ("headcount per department", {"v_employee_headcount"})]

hits = unanswerable(cat, gold, Principal("okta:analyst", frozenset()))
print(f"silent-denial rate: {len(hits)}/{len(gold)} = {len(hits)/len(gold):.1%}")

On the two questions above that prints 1/2 = 50.0% for the analyst and 0/2 = 0.0% for a caller holding payroll, which is the sanity check worth running first: if the number does not move when you change the roles, it is not measuring the roles.

The fuller version I ran for the table -- multiple callers, the unlabelled reachability mode, and the guard that stops a mistyped gold label inflating the rate -- is a few hundred lines around that core. Say so in the comments if you want it packaged and I will put it on PyPI; I would rather know someone will run it than publish another thing nobody installs.

If you want the fix rather than the measurement, the scoping in that last column is what schemagate does -- it takes the caller's identity at the schema-selection step instead of filtering rows afterwards. pip install schemagate, Apache-2.0.

I would genuinely like to see this number from a real warehouse rather than a demo schema. If you run it on yours, post what you get -- especially if it is 0%, because I would like to know what a role model that avoids this looks like.

My retrieval counted the same text three times

Ashish sinha — Sat, 19 Sep 2026 14:49:19 +0000

A week ago I published a measurement: I had generated descriptions for 1,245 database objects to make retrieval better, and retrieval got worse. The post did well by my standards. Thirty-seven comments, several from people who had seen the same thing in their own systems.

The measurement was real. The conclusion I drew from it was wrong, and this week I found out why. It was my code.

What the numbers said

Spider 2.0-lite is a text-to-SQL benchmark built from real BigQuery and Snowflake databases, and unlike the older benchmarks it ships the warehouses' own documentation. That makes it the one place I could test the claim on somebody else's prose rather than my generator's.

So I deleted the descriptions and re-ran. Removing them won at every cut, on both embedders, five of six cells significant, and the widest cell was thirteen questions fixed against one broken.

Read on its own, that table says delete your descriptions. Which should have been the tell. A design whose whole premise is that prose helps should not lose to deleting the prose. The question was not whether to believe the table. It was what the table was pointing at.

What it was pointing at

The same column comment was being indexed three times.

It went into _prose_text, which feeds the prose channel. It also went into embed_text() — which feeds the body channel and the vectors. Three of the four ranking signals, all carrying the same words.

A Spider 2.0 table carries a description per column. So any table with many columns matched on three of four channels for any question that shared a single word with any one of its columns. The widest tables became magnets. They crowded out the narrow, correct table on question after question.

Deleting the descriptions "helped" because it removed two thirds of a triple-count. It was never evidence about prose. It was evidence about my scoring.

Decomposing it

The useful thing about a triple-count is that you can take it apart one channel at a time. Same 212 questions, one source removed per run:

remove column comments from the prose channel alone → recovers 4 questions at k=10
remove them from embed_text() alone → recovers 11 questions at k=10

Eleven of the fifteen were in the embedding path. That is where the damage was, and it is the one I would not have guessed — the prose channel was the obvious suspect because it is the one named after prose.

The fix, and the constraint that shaped it

Take column comments out of the body channel and the prose channel. Leave embed_text() alone.

That last part is not laziness. The vectors are a published, pinned guarantee: change what goes into them and every stored index built by anyone using the library silently becomes wrong. A retrieval fix that invalidates your users' indexes is not a fix, it is a migration you didn't announce. So the damage gets removed from the two channels that can change freely, and the embedding path — where most of the effect lived — is left byte-identical.

It works anyway. Re-run paired, same 212 questions, with the fix in:

  k    with prose        without prose      b   c     p
  5    143/212 67.5%     143/212 67.5%      6   6   1.0000
  10   177/212 83.5%     177/212 83.5%      2   2   1.0000
  20   188/212 88.7%     187/212 88.2%      2   3   1.0000

Thirteen-to-one became two-to-two. The penalty is gone. Descriptions now neither help nor hurt on this benchmark — which is the floor a fielded design was supposed to guarantee and mine did not.

A second bug, found on the way

While sampling the raw files I found that description in Spider 2.0's schema JSON is not a table description at all. It is a per-column list, aligned by index to nested_column_names when a table has nested fields and to column_names otherwise, entries sometimes null and never a string — 150 of 150 sampled. My loader had been joining that list into a paragraph and indexing it as a table description.

The same loader built columns from column_names alone, so a nested table — gnomAD's v3_genomes__chr7, 61 top-level columns and 181 flattened — arrived missing the very columns its gold SQL reads.

Fixing that moved the resolvable comparison set from 203 questions to 212, and k=20 from 67.2% to 71.3% over the 247-question denominator. It also moved k=10 down, 77.8% to 77.4%, because the nine questions that joined are harder than the average of the 203. Both directions recorded, because quoting only the cut that rose is how you end up publishing the last thing I published.

The format question is filed upstream as a documentation request rather than a data defect, because the files are consistent — the field name just doesn't mean what it looks like: Spider2 issue #222

What I'd take from this

A measurement can be completely true and still not mean what you think. Mine was reproducible, it replicated across embedders, and the direction was stable. All of that was real. None of it made the conclusion right.

If a result argues against your design's core premise, suspect the implementation before the premise. Not because premises are always right, but because you can check the implementation in an afternoon and the premise takes a research programme.

Watch for one field reaching more than one ranking signal. This is the generic version and it is easy to do accidentally: a field that goes into a lexical channel and also into the text you embed is counted twice, and if a third channel derives from the same text, three times. Nothing errors. Your tests pass. Retrieval just quietly prefers whatever documents have the most of that field.

The fix should not invalidate your users' indexes. If it has to, that is a version bump and a note, not a patch release.

Shipped in 0.1.57. The full McNemar grid, the channel decomposition and the re-measured numbers are in BENCHMARKS.md in the repo, along with the withdrawn claim, which stays visible rather than deleted.

Replacing an AWS Glue ingestion job with DBMS_CLOUD_PIPELINE in Autonomous Database: lessons learned

Ashish sinha — Thu, 17 Sep 2026 16:59:00 +0000

Our pipeline ran Mongo → S3 parquet → Glue/Spark → Iceberg → Oracle ADB. Autonomous Database can read parquet from S3 natively, so the Spark step was mostly paying to move files.

We replaced it with this:

S3 parquet → DBMS_CLOUD_PIPELINE → landing table (all VARCHAR2) → PL/SQL MERGE every 5 min → curated table

It now covers 13 collections and 24 feeds, with tables up to ~30M rows. Row counts match Iceberg, freshness is about 10 minutes, and no Spark is involved.

Things that caught us, in case they save someone time:

The pipeline load is positional, with an exact column count. Schemaless sources produce files with different column sets, which fail with ORA-00913 or ORA-00947. We land every column as VARCHAR2(4000) and apply types by name in the MERGE.
TO_DATE('15-SEP-26','YYYY-MM-DD') returns year 0015 with no error. Classify each value's shape with a regex first, then parse it with FX.
A pipeline's location can't change while it runs, and a reset forgets which files were loaded. Regex locations loaded 0 files. Wildcards picked up every older matching folder. What worked was creating one pipeline per date folder ahead of time (tomorrow's at 22:00) and dropping old ones after a grace period.
Check completeness by outcome. Every 15 minutes, list the S3 files, compare them with the files the pipeline recorded as loaded, and load any missing file by name.
Full re-pulls re-stamp unchanged records. Guard updates and deletes on version, and reconcile on content rather than timestamps.

Is anyone else using DBMS_CLOUD_PIPELINE for date-partitioned S3 folders? I'm curious whether there's a cleaner pattern than one pipeline per day.

"Money we gave back to shoppers" matches no table name. Four searches find it anyway.

Ashish sinha — Thu, 17 Sep 2026 05:08:50 +0000

Someone asks your text-to-SQL agent: "money we gave back to shoppers."

The table that answers it is called billing_credit_note.

Those two strings share no word. Not a low score — zero overlap. Any retriever that works on table names is blind here, and the schema has 42 tables, so guessing is not an option either.

Here is what four separate searches do with that question, measured on a real run rather than described.

One question, four rankings

names — BM25 over table and column names only.

nothing matched

Zero. "money", "gave", "back", "shoppers" appear in no identifier in the schema. This is the channel that scores 100% recall@6 when people phrase questions in the schema's own vocabulary, and it contributes exactly nothing to this one.

prose — BM25 over the generated descriptions.

1  v_supplier_spend
2  billing_credit_note      <- the answer
3  v_monthly_revenue
4  sup_supplier

Second place. The description reads "Records refunds or corrections issued against a specific invoice." The word refunds is the bridge from "gave back" to credit_note — a bridge that exists nowhere in the DDL.

vector — embedding similarity.

1  v_monthly_revenue
2  v_supplier_spend
3  ship_carrier
...
22 billing_credit_note

Twenty-second. It got the neighbourhood right — revenue, spend, money-shaped things — and the specific table wrong. That is the characteristic dense-retrieval failure, and the reason it is hard to debug: there is no per-term number to inspect. You just get a ranking that feels slightly off.

body — BM25 over everything concatenated: names, columns, types, comments, descriptions.

1  billing_credit_note
2  v_supplier_spend
3  v_monthly_revenue
4  sup_supplier

First. Best of the four here — and also the exact configuration that collapsed on a 1,245-object schema, because with everything in one bag the descriptions dilute the identifiers. More on that below.

Merging them: rank, not score

You cannot add these scores together. A cosine distance and a BM25 score are different units; 0.83 plus 14.2 is not a number that means anything.

Positions are comparable, so positions are what get added. Reciprocal Rank Fusion:

contribution = weight / (K + position)        K = 60

First place pays 1/61, second 1/62, twenty-second 1/82, absent pays nothing. For billing_credit_note:

names    absent    0.00000
prose    2nd       0.01613
vector   22nd      0.01220
body     1st       0.01639
                   -------
                   0.04472   -> top 6

The important line is the first one. A channel that is completely blind costs the answer nothing. It contributes zero and the other three carry it. That is the entire argument for having four: they are not redundant, they fail on different questions, and it is rare for all four to miss at once.

The cost of rank-based fusion is the mirror image, and a commenter on my last post caught it: a channel that ranked things for a meaningless reason still votes at full strength. If every description contains the word "about", every object ties in the prose channel, and the tie is broken by whatever order the catalog was built in. I reproduced that — shuffling the insertion order changes which tables come back on 1 to 2 questions out of 12. Recall does not move; the prompt does. Both fixes he proposed are right, and both are now filed.

Why not just concatenate everything

This is the question I got wrong first, and it cost me a public retraction.

Putting names and descriptions in one bag seems obviously better — more text, more to match on. Measured on the same fixtures, by someone else, from the published wheel:

identifiers only          29/52   55.8%
one flat bag              41/52
prose as its own channel  48/52
shipped configuration     49/52   94.2%

The flat bag beats identifiers alone. It is also seven questions worse than keeping prose in its own index. Inside one bag, the sentences compete with the identifiers for term frequency, and the identifiers lose because there are more words of prose than there are words of name.

At 1,245 objects it stops being a seven-question gap and becomes a failure. Every description written by the same model in the same voice put the token contact into roughly 1,072 of 1,245 documents. Its IDF went from 4.27 — computed over names, where it appears in 17 of 1,245 — to 0.15. The word users actually type had become a stopword. The table literally called contacts fell from rank 3 to below rank 40 for the question "show the contacts of xmagnet".

The descriptions were accurate. I read them. Accurate text can still be index poison, because IDF is a property of the corpus, not of the sentence.

Fielded scoring fixes it for a structural reason rather than a tuning reason: in the names index, contact is still 17 of 1,245 and still worth 4.27. The prose can be as repetitive as it likes over in its own index without touching that.

What it costs and what it buys

That same question, end to end on the 42-object schema: 6 ranked tables plus 3 pulled in by foreign-key closure, 490 tokens instead of 2,205 for the whole schema.

And the honest limit, because the comments on the last post will find it otherwise: 100% recall@6 when questions use the schema's vocabulary, 46–86% depending on schema when they do not. Fielded fusion narrows that gap. It does not close it.

schemagate is Apache-2.0. Every number above is from tests/run_paraphrase_eval.py and the checked-in fixtures.

Seven times my test harness reported success while measuring nothing

Ashish sinha — Thu, 17 Sep 2026 04:15:59 +0000

Last week a stranger reproduced my benchmarks. Over four days he found six defects. Every single one of them had been reporting success.

That is the part worth writing down. Not that I had bugs — everyone has bugs. That my instrumentation, six separate times, returned a number that looked fine and meant nothing. A crash is loud. A harness that prints 86.1% when it measured the wrong thing is not, and I published every one of those numbers.

Here they are, in the order they were found.

1. `except Exception: continue`

The ablation loader walked a Spider 2.0 checkout and read each table's JSON:

for f in path.rglob("*.json"):
    try:
        d = json.loads(f.read_text())
    except Exception:
        continue

On Windows, open() refuses a path over 260 characters. 2,868 of the 7,892 paths in that checkout are over 260 characters. The except caught FileNotFoundError and counted every one of those tables as a table that does not exist.

So the benchmark ran against a database missing roughly 39% of its tables, and reported 86.1% recall at k=20. A smaller schema is an easier schema. The number was not wrong because the retrieval was bad; it was wrong because the corpus was a third smaller than the corpus I said it was.

The detail that makes this worse: the shortfall depends on where you cloned the repo. A 182-character root gives 2,868 unreadable files. A 183-character root gives 3,056. The benchmark's result was a function of the reviewer's directory layout, and nothing in the output said so.

2. A comment that had been lying for a day and a half

# Bounded, and only ever additive -- it cannot displace a ranked pick.

Forty lines below it:

if len(chosen) >= top_k:
    for i in range(len(chosen) - 1, -1, -1):
        if chosen[i].reason not in ("pinned", "covers"):
            taken.discard(chosen[i].doc.qname); del chosen[i]; break

It displaces a ranked pick. It has since a commit I made a day and a half earlier. I had quoted the comment in a public reply as though it were the behaviour, because I read the comment instead of the code directly above and below it.

Comments do not have tests. This one shipped in three releases.

3. An enum missing a value the code writes

Scored.reason documented five values: hybrid | vector | lexical | fk | pinned. The selector writes a sixth, covers. Anyone branching on that field — and the whole point of the field is that you branch on it — would silently fall through on one case in six.

4. A check with power zero by construction

He claimed 52 of 52 questions had a step where the returned-set size decreases as K grows. My check returned 0 of 52, and I was one keystroke from posting that as a contradiction.

My check compared returned(K) against returned(K−1). Top-K picks are nested and both expansion passes are unions, so returned cannot decrease in K. The test could never fire. It was not a disagreement with his result; it was a test with no power, returning the only answer it was capable of returning.

Comparing the overages instead of the totals: 52 of 52, exactly as he had it.

This is the one that still bothers me. A test that returns 0 looks like evidence of absence. It is indistinguishable, from the outside, from a test that measured something.

5. The aggregate that was never printed

The held-out evaluation built six fixtures. One of them declares three extra schemas and puts four objects in them — three tables all named account, plus a view joining across two. That name collision is most of what the fixture exists to test.

The harness opened a bare SQLite connection with no ATTACH, so executescript raised unknown database "billing" on the first of those statements and the run died there. After the five per-schema rows had printed. Before the TUNE / HELD OUT / OVERALL lines.

The file exists to produce an aggregate. It had never printed one. Five plausible rows of output scrolled past every time and I read them as the run having worked.

6. The fix that turned a crash into a silence

This is the one I would most like you to take away.

The obvious repair is to ATTACH DATABASE ':memory:' before running the DDL. It works. The script completes, the harness prints its aggregate, exit code 0.

In-memory databases are dropped when the connection closes. The harness closes that connection and bootstrap opens a new one. So:

256 objects indexed, all in `main`
objects named `account`: none

The three same-named tables and the cross-schema view — the hardest case in the fixture, the reason the fixture is there — are still absent. The difference is that now nothing crashes. The repair converted a loud failure into a quiet one, and both of us verified the repair by reading a number that the repair had not changed.

File-backed databases attached on a connect listener, with the schema list passed to bootstrap, gives 260 objects — main 256, billing 2, crm 1, sec 1. Recall did not move. The measurement did.

And one the reviewer didn't find

While writing this I checked the benchmark script that guards my README. Its last line is:

bench OK: README claims hold

The gate above that line checks recall thresholds and a 70% token-reduction floor. It does not check the token count the README prints (the README said 2,583; the script prints 2,812). It does not check the 260-object row at all — that schema is never built by that script. It asserted that the README holds, while not reading most of the README.

Seventh.

The pattern

Every one of these reported success or absence for a reason unrelated to the thing being measured.

an exception handler that turned missing into does not exist
a comment that described code that had changed
an enum that described code that had grown
a comparison that was monotone and therefore constant
a crash sequenced after the output that made it look like a run
a fix that replaced a crash with an absence
a gate whose message claimed more than its assertions

None of these produce an error. All of them produce a number. That is the shape: the failure mode of a measurement is not a wrong answer, it is a plausible one.

The test I have taken from him is mechanical, and I recommend it. Sweep the apparatus. Change the K grid, the predicate, the clamp, the domain, the platform. If the number moves, you measured your apparatus. If it holds, you may publish it.

It caught all seven above. It also caught a number I had sent him two days ago: I reported 22 questions whose ceiling rises on a dense sweep; he measured 24. He was right — I had included a K value that is not in the published grid. Sweeping four grids gives 22, 23, 24, 29. The count is a property of the grid, not of the system, so the curve is what gets published and the count does not.

Why I am writing this instead of quietly fixing it

Because the numbers were public, and a correction that is not as visible as the claim is not a correction.

And because the thing that produced all seven fixes was not my discipline. It was a stranger on the internet with no stake in the project, who reproduced every figure before disagreeing with any of it, and who kept going for four days. I have never had review like that from a paid reviewer.

If you maintain something and somebody turns up to check your arithmetic: that is the most valuable thing that will happen to your project this year. Give them everything they ask for.

The library is schemagate — identity-scoped schema selection for text-to-SQL. Every figure above is reproducible from the repo; the benchmark file records what was withdrawn as well as what held.

I audited all 25,125 servers in the MCP registry

Ashish sinha — Wed, 16 Sep 2026 03:43:47 +0000

Every MCP directory, every "browse servers" page, every agent that discovers tools at runtime reads from the same place: the Model Context Protocol registry. I pulled a snapshot of it on 4 September 2026 — 25,125 servers — and ran an integrity audit over the whole thing.

The interesting part isn't what I found. It's the number I nearly reported.

What's actually in there

25,125 servers
  24,863 active · 262 deprecated

904 servers (3.6%)  share a description identically with another server
1,252 (4.98%)       are near-duplicates of each other at Jaccard >= 0.7
12 records          repeat a name (see the correction below)
0                   are identical across every field the audit evaluates

That last line is the one that matters, and I'll come back to why.

The number I nearly led with

My own tool's summary says this:

"schema_issues": 101383

A hundred thousand schema issues in a registry of twenty-five thousand servers. That is a headline. It is also, as reported, meaningless.

Every one of those 101,383 is a missing_field on an optional field — repository, websiteUrl, packages. The audit infers the majority type of each field across the corpus and flags records that omit it, which is the right behaviour for a dataset where fields are mandatory and the wrong framing for a registry where they aren't.

What's true underneath it is duller and more useful:

title      present in 14,384 of 25,125   (10,741 have none)
remotes    present in 13,866 of 25,125   (11,259 have none)

So: a bit under half the registry has no title, and a bit under half declares no remote. That's a completeness observation about a young registry, not a corruption finding. "101,383 schema issues" would have been the more shareable sentence and it would have been wrong.

Why "0 identical across every field" is the real result

The audit separates near-duplicate clusters into two kinds: records that match on every evaluated field, and records that share a text stem but differ elsewhere.

In the MCP registry, 487 clusters covering 1,252 servers come back as near-duplicates on description. Zero of them are identical across every field. They're servers doing similar things, described similarly — A Model Context Protocol server for X repeated across many X — with different names, versions and packages.

Which is exactly what you'd expect from a registry, and exactly what a naive duplicate count would report as a 5% redundancy problem.

This is a general failure, not an MCP one

I hit the same gap measuring benchmark datasets, which is what the tool was written for.

Spider's training split: a naive text-similarity pass reports 77 duplicate clusters. Only 8 are real. The other 69 share a question stem while differing in the reference SQL, the database, or both — one phrasing deliberately reused against different schemas. Reporting the raw count overstates by roughly 9x.

BIRD-CRITIC: 4 clusters flagged, 1 genuine. Overstated by 4x.

Same shape every time. The obvious implementation of a duplicate check counts questions that look alike. The thing you actually care about is records a model could bank the same answer twice for, which requires the answer to match too.

Cross-split contamination has the identical problem. Counting identical questions between Spider's train and dev splits says 6 dev items are contaminated. Additionally requiring the reference SQL to match says 2, out of 1,034 — and both are trivial SELECT count(*) questions landing on coincidentally same-named tables in entirely different databases. Reporting the first number would have framed it as three times worse than it is.

What this doesn't say

The audit uses MinHash-LSH above 3,000 records, and LSH offers no recall guarantee, so the duplicate counts here are lower bounds. Every report states which method produced it.

Similarity is computed over the description field only. The snapshot is from 4 September 2026 and the registry has grown since. Correction, added after publishing. I originally wrote that "12 servers share a name" was worth the registry looking at. It isn't, and I should have checked before saying so.

The registry stores one row per version. Querying the live API: a 100-row page contains 62 distinct names, 24 of which recur — ac.inference.sh/mcp appears four times at 1.0.0, 1.0.1, 2.0.0 and 2.0.1, all active. A repeated name is the data model, not a collision.

Which makes this the second raw number in the same audit that meant something other than what it looked like, in a post arguing that raw numbers mean something other than what they look like. The snapshot I measured appears to have been reduced to one row per name already, so the 12 are worth understanding before they are worth reporting — and I have not done that work.

I'm not claiming the MCP registry has a quality problem. I'm claiming it now has a measured baseline, which it didn't before, and that the first number my own tool handed me would have misrepresented it.

Run it yourself

Pure standard library except for fetching:

git clone https://github.com/ashishsinha1602/dataset-integrity-audit
cd dataset-integrity-audit
pip install datasets

python audit.py prep --data mcp-latest.jsonl \
  --text-field description --answer-field name --group-field _status

prepared/REPORT.md gives you the table. Point --text-field and --answer-field at any two columns and it works on any dataset — there's a hosted version if you'd rather not install anything.

Repo: github.com/ashishsinha1602/dataset-integrity-audit, MIT. Every number above is in prepared/ as committed JSON, so you can check any of them without re-running anything.

If there's one thing to take from this: when a duplicate check hands you a big number, find out how many of those records are identical everywhere before you quote it. On three different corpora that ratio has been between 4x and 9x, always in the direction that makes the problem look worse than it is.

Vanna is archived. The failure mode none of the replacements fix.

Ashish sinha — Tue, 15 Sep 2026 04:11:40 +0000

Someone in finance asks your text-to-SQL agent what the compensation band is for a role they're hiring for. The agent writes correct SQL. The query runs. Row-level security strips every row. The user is told:

No records found.

That answer is wrong, and it is wrong in the worst available way. There are records. The user simply isn't allowed to see them. Nothing in the chain can tell the difference between "this data does not exist" and "you may not have this data" — so the system picks the first and says it with confidence.

Your logs show a successful query. Your RLS policy shows it working exactly as configured. Everybody's dashboard is green, and the person who asked now believes something false about your company.

Where this comes from

It comes from the order of operations, and almost every text-to-SQL stack has the same order.

question → pick tables → write SQL → execute → apply row-level security → answer

Access control is the second-to-last step. By the time it runs, the model has already seen the table, already reasoned about its columns, already written a query that names it. RLS is doing its job — it protects the data. It cannot protect the answer, because the answer was shaped four steps earlier by a model that had no idea the table was off-limits.

This is not a Vanna problem. It's the default shape.

Which matters now, because Vanna is archived

vanna-ai/vanna was archived on 29 March 2026 and is read-only. 23.8k stars, 2.5k forks — a lot of people integrated it, and a fair number are now deciding what to move to.

Vanna 2.0 was a rewrite around user-aware agents: it resolves a User with group memberships, gates tools on those groups, and applies row-level security when the SQL runs. That's more identity handling than most of its alternatives have. It still has the ordering above.

So if you're picking a replacement, this is worth checking before you pick: does the model ever see a table this caller cannot read? For nearly everything on the shortlist the answer is yes, and the protection is downstream.

The other order

question + who's asking → pick tables THEY can read → write SQL → execute → answer

A restricted table isn't ranked lower. It isn't filtered afterwards. It is absent from the prompt. The model cannot write SQL against a table it was never shown, so there's no query to strip rows from and no confident wrong answer to deliver.

The knock-on effect is the one I didn't expect when I started: when the model can't reach the data, it says so. "I don't have a table that covers compensation" is a true statement about what it was given, and a user can act on it — ask someone, request access, escalate. "No records found" is a dead end built out of a false premise.

If you're migrating

This is the mapping I use. It's deliberately boring:

Vanna	equivalent
`vn.train(ddl=...)` (1.x) / tools reading a configured DB (2.x)	`cat = Catalog().bootstrap("postgresql://…")` — reflects the schema once
`vn.train(documentation=...)`	`cat.hint("orders", "…")` — a human note that outranks everything
`User(id=…, group_memberships=[…])`	`Principal("okta:jdoe", roles={…})`
a tool's `access_groups`	`cat.restrict("hr_compensation", ["payroll"])` — on the table, not the tool
`vn.ask(question)`	`sel = cat.select(question, principal=p)`, then `sel.prompt_fragment()` in your prompt
`vn.generate_sql(...)`	keep whatever model call you already have

Your User object drops in more or less unchanged — id becomes the principal's subject, group_memberships become its roles:

from schemagate import Catalog, Principal

cat = Catalog().bootstrap("postgresql://localhost/app")
cat.restrict("hr_compensation", ["payroll"])          # once, at startup

def build_prompt(question, user):                      # per request
    p = Principal(f"okta:{user.id}", roles=set(user.group_memberships))
    sel = cat.select(question, top_k=6, principal=p)
    return f"Schema:\n{sel.prompt_fragment()}\n\nQuestion: {question}"

Whatever generated SQL before still does. It just never sees hr_compensation unless the caller holds payroll.

Column-level works the same way — cat.restrict_column("employees", "salary", ["payroll"]) — so a table can be visible with a column withheld, which is the common real case.

What you give up, honestly

Vanna trained on question/SQL pairs and learned from feedback. This doesn't learn. It reflects the schema and ranks it.

So: if your accuracy came from a large question–SQL memory, keep that memory and use the identity gate only for the selection step. If your accuracy came from schema documentation, cat.hint() covers the same ground with less machinery. If it came from the feedback loop, you will miss it, and I'd rather say that here than have you find out in week three.

Certification status, plainly: live-tested on PostgreSQL 16, Oracle 26ai and SQLite. SQL Server and MySQL reflect but I haven't had a live instance to run them against — not because I expect trouble, but because I haven't done it. There's a certify_dialect.py script if you want to run it and tell me what happens.

Try it without installing anything against your database

pip install schemagate
schemagate demo "which customers owe us money" --answer

That runs against a bundled 42-object schema. No database to set up. No API key required — without one it prints a prompt you can paste into any chat. There's a browser demo too, no signup.

Repo: github.com/ashishsinha1602/schemagate, Apache-2.0. It reflects any SQLAlchemy database, and it ships an MCP server, so if your agent lives in Claude Desktop, Cursor or Zed it gets the same per-caller boundary.

If you're migrating off Vanna and you take one thing from this, let it be the question rather than the library: when your agent says "no records found", can you tell whether that's true? If you can't, that's worth fixing regardless of what you migrate to.

Spider 2.0 deleted a claim I made this morning

Ashish sinha — Sun, 13 Sep 2026 04:09:10 +0000

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.

I described 1,245 tables with an LLM and retrieval got worse

Ashish sinha — Sat, 12 Sep 2026 23:35:28 +0000

The cataloguing step is supposed to be the easy win. You have a schema whose
tables are called ecm_template_link and v_pmpm, your users ask questions
in English, and the gap between those two vocabularies is why retrieval
misses. So you point a model at every table, get back a sentence describing
each one, index the sentences alongside the names, and now the corpus speaks
English too.

I did that to a real 1,245-object schema. Recall went down.

Not by a little. The table literally called contacts sat at rank 3 for the
question "show the contacts of xmagnet" before cataloguing. After
cataloguing it was below rank 40 — off the end of anything I would put in a
prompt. The descriptions were fine. I read them. They were accurate,
specific, and they made the system worse.

This post is what was actually happening, why the obvious fix doesn't work,
and the one that does. None of it is specific to text-to-SQL. If you are
enriching documents before indexing them — summaries, generated titles,
hypothetical questions, keyword expansion, anything — the same mechanism is
available to bite you, and it will not announce itself.

The mechanism, in one line

Every description you generate is written in the same vocabulary as every
other description, so enrichment raises the document frequency of exactly
the words your users type.

BM25 scores a term by inverse document frequency:

idf(t) = log(1 + (N - n_t + 0.5) / (n_t + 0.5))

N is the corpus size, n_t the number of documents containing the term.
A term in few documents is informative and scores high; a term in most
documents is worthless and scores near zero. That is the entire point of
IDF, and it is normally a good instinct.

Now think about what a model writes when you ask it to describe a table in a
CRM schema. It writes about contacts. It writes about contacts when
describing contacts, and also when describing contact_lists,
campaign_recipients, email_events, tenants, users, and the audit
table that logs changes to any of them — because in a CRM, almost everything
is about contacts in some defensible sense. The descriptions are not
wrong. They are correlated.

After cataloguing, the token contact appeared in roughly 1,072 of the
1,245 documents — a figure I can reconstruct from the IDF it produced,
which was 0.15.

For comparison, here is what the same schema gives you for a genuinely rare
term:

term	documents containing it	idf
`contact`, after cataloguing	~1,072 of 1,245	0.15
`contact`, in the name field only	17 of 1,245	4.27
`tenant`, in the name field only	30 of 1,245	3.71
`the`	0 of 1,245	—

The tokenizer does not strip stopwords, so the and and are in that
index too, sitting near zero because they are in everything. At 0.15,
contact had joined them. The word the user typed was, for scoring purposes,
a function word.

The second half, which is worse

IDF collapse alone would flatten the ranking. What actively inverted it was
length normalisation.

The b parameter in BM25 penalises long documents, on the sound theory that
a long document containing your term is less about your term than a short
one that contains it:

score += idf(t) * f * (k1 + 1) / (f + k1 * (1 - b + b * len / avg_len))

Ask which object in a CRM schema has the longest document, and the answer is
the central one. contacts in this schema has 55 columns. Add a generated
description and a row of alias words and its document is several times the
corpus average. Meanwhile contact_import_log has six columns and a
one-line description, so it is short, tidy, and — as far as the length
prior is concerned — much more about contacts.

So the two effects compound in the same direction:

IDF collapse removes the signal that would have separated contacts from the forty other tables mentioning contacts.
Length normalisation then actively sorts what's left by inverse centrality, because the most important table in a schema is reliably the one with the most columns.

Cataloguing didn't add noise. It added correlated noise, and correlated
noise attacks the exact query it was meant to help. The questions that
degraded most were the ones cataloguing exists to serve — plain English, no
schema words. Questions that named a table outright were mostly fine, because
they had a rare token to hang on. That is a nasty failure profile: the
feature looks fine on your smoke tests and fails on your users.

The fix that doesn't work

The obvious response is to trust descriptions less. One index, but weight the
generated text below the real text.

I did this first. It helps a bit and it is the wrong lever, for a reason
that took me a while to see: weight and dilution act at different stages.

Down-weighting scales the contribution of a term after IDF has already been
computed over a corpus the descriptions polluted. contact is still worth
0.15 in the name's own score, because name and description live in one bag of
words and IDF is a property of the bag. You have made a bad channel quieter
without making the good channel accurate again.

And the cost is real. Starving the prose weight cost me
"per member per month cost" → v_pmpm, which is the single best example in
the whole schema of a question only a description can answer. There is no
lexical path from that phrase to that name. The description was the only
bridge and I had just defunded it.

So: down-weighting trades away the wins to partially mitigate the losses. You
end up tuning a scalar that makes both worse than they need to be.

The fix that works

Score the fields separately and fuse the rankings, rather than concatenating
the fields and scoring once.

Three BM25 indexes over the same objects:

self._bm25       = _BM25([doc.embed_text() for doc in docs])   # everything
self._bm25_name  = _BM25([_name_text(doc)  for doc in docs])   # identifiers
self._bm25_prose = _BM25([_prose_text(doc) for doc in docs])   # written text

where

def _name_text(doc):
    """Just the identifiers: schema, name, and the name split on underscores."""
    return " ".join(x for x in (doc.schema, doc.name,
                                doc.name.replace("_", " ")) if x)

def _prose_text(doc):
    """Everything written *about* the object: hint, description, comments."""
    parts = [doc.hint or "", doc.description or ""]
    parts.extend(c.comment or "" for c in doc.columns)
    return " ".join(x for x in parts if x)

Then fuse by reciprocal rank rather than by score:

for q in candidates:
    s = 0.0
    if q in vec_rank:   s += vector_weight  / (RRF_K + vec_rank[q]   + 1)
    if q in lex_rank:   s += lexical_weight / (RRF_K + lex_rank[q]   + 1)
    if q in name_rank:  s += NAME_WEIGHT    / (RRF_K + name_rank[q]  + 1)
    if q in prose_rank: s += PROSE_WEIGHT   / (RRF_K + prose_rank[q] + 1)

Both halves of the bug die at once, and it is worth being precise about why,
because "just use fielded search" is advice people give without the
mechanism:

IDF is recomputed per field. In the name index, the only text is
identifiers. Nothing a model writes can ever enter it. contact appears in
17 names out of 1,245, so its IDF is 4.27 instead of 0.15 — 28× the
discriminating power, restored by construction rather than by tuning.

Length is per field too. The name index's document length is the length
of the name. contacts is two tokens whatever else you attach to the object.
The 55 columns cannot inflate it, so the length prior stops punishing
centrality.

Fusion is over ranks, not scores. This is the part that contains a bad
catalogue, and it's why I could raise the prose weight back to parity. A
channel can only ever contribute its own ranking. If a weak model writes
"Stores data about users and their settings" about all 1,245 objects, the
prose channel becomes uniformly useless — every object ranks the same, the
channel contributes nothing that discriminates, and the name and body
channels decide the result unchanged. The floor becomes "no better than
before cataloguing" instead of "worse than before cataloguing".

That last property is the one I actually care about. It means pointing a
small local model at your schema is safe. Not good, necessarily — a 1.5B
model writes considerably worse descriptions than a frontier model, and I'd
rather you use the good one. But safe: bad prose can no longer bury the
object it describes, so the downside of trying is bounded.

What this generalises to

The pattern is not about databases. It is:

Generated text about a corpus is written in the corpus's own vocabulary,
so enrichment inflates document frequency for the domain's central terms —
the ones users search with — and inflates document length most for the
items that matter most.

Anywhere you generate text and index it next to original text, in the same
field, you have signed up for both effects:

Summaries prepended to chunks. Every summary in a corpus about Kubernetes says "Kubernetes".
Hypothetical-question generation (HyDE-style indexing). You are synthesising the user's own phrasing, at scale, across every document. That is IDF dilution as a product feature.
Keyword and synonym expansion. Same shape, more concentrated.
LLM-written titles or alt-text merged into the body field.

None of these are bad ideas. I still catalogue schemas; recall on
business-phrased questions is far better with descriptions than without. The
claim is narrower: enrichment belongs in its own field, always. The cost
of separating fields is one more index and a fusion step. The cost of not
separating them is a regression that shows up only on your most important
queries and looks like "retrieval is just hard".

If you want to check whether this is happening to you, it is one query and
no instrumentation: take the ten nouns your users actually type, and print
their document frequency before and after your enrichment step. If any of
them are now in more than half your documents, that term is doing nothing,
and it was probably doing something before.

What it doesn't fix

Honesty about the edges, since the above reads tidier than the week did:

Fielded scoring does not make a bad catalogue good. It makes it harmless. If
your descriptions are generic, you get the pre-cataloguing ranking back, not
a better one — which is the right outcome, but don't read it as a licence to
skip evaluating the model that writes them.

It also introduces a knob per field, and I do not have a principled method
for setting them. Mine are all at parity because that tested best across six
schemas, not because parity is theoretically correct.

And separating fields cannot fix a term that is genuinely common in the
names too. A schema with 300 tables actually called contact_something has
a real ambiguity problem, and no amount of field isolation invents the
information to resolve it.

The measurements here come from schemagate, an open-source library
(Apache-2.0) that does the retrieval step for text-to-SQL. The relevant code
is in catalog.py
— the comments around the three _BM25 constructions are where I wrote this
down while it was still fresh. There's a browser demo at
ashishsinha1602.github.io/schemagate
that runs the real selector client-side on six sample schemas, if you'd
rather poke at the ranking than read about it.

If you run the document-frequency check on your own corpus, I'd like to know
what it says — particularly if it says nothing is wrong, because I'd like to
know what makes a corpus immune.