Blog /
Your query history knows what your schema does not
Published . Updated . Written for nlqueries-core 0.3.
query-history knowledge-base text-to-sql
Most text-to-SQL systems start by reading the schema. Tables, columns, types, foreign keys, maybe a comment if someone was conscientious in 2021. It is the obvious place to start, and it is not enough, for a reason that becomes clear the moment you look at a real warehouse.
The schema tells you what is possible. It does not tell you what is meant.
What introspection misses
Take a column called status on a table called orders. Introspection tells you it exists, that it is a varchar(32), and that it is not nullable. Here is what it does not tell you:
- The column has eleven distinct values, of which two account for 94% of rows.
- Three of those values were used by a system that was retired in 2023 and only appear in old rows.
- The value everyone means by "completed" is spelled
FULFILLED, notCOMPLETE, which also exists and means something subtly different. - Every analyst on the team filters
WHERE status != 'TEST'without thinking about it, and nobody has ever written that down.
A model reading the schema will guess. It will guess reasonably. It will guess status = 'COMPLETE' because that is what the word means in English, and it will return a number that is wrong by a factor nobody can predict.
The information needed to get this right exists. It is just not in the schema. It is in the queries people have already written.
What history shows reliably is the shape: that people filter on status, and with which joins. Which value means "completed" is something to write once into the knowledge base's glossary and rules, because NLQueries replaces every literal in a mined query with a placeholder, and pg_stat_statements has already done the same before it gets there.
Query history as a source of truth
The query log is an unusual asset because it is a record of decisions rather than structure. Every query somebody wrote and kept is a small piece of evidence about how this organisation thinks about its data.
Run enough of them through analysis and patterns come out:
Which tables are actually live. Four hundred tables exist. Sixty are queried in a given quarter. Knowing which sixty tells whoever curates the knowledge base where descriptions and rules will pay off first.
Which joins are the real ones. The foreign key graph permits an enormous number of paths between two tables. The query log shows you the three that people actually use, and which direction they go.
What the conventional filters are. If 90% of queries against orders include a predicate excluding test accounts, that is not a coincidence. It is an unwritten rule, and it belongs in the knowledge base's business rules, where the model will see it.
What the vocabulary maps to. Queries that people named or commented, dashboards with titles, saved reports: these connect business language to specific SQL. "Monthly active" turns out to mean a particular window function against a particular table.
Which columns are dead. A column nobody has selected in two years is either deprecated or a trap. Either way it deserves a description that says so.
Turning queries into something reusable
Raw query logs are not directly usable: they are enormous, repetitive, full of one-off exploration, and studded with literal values. The processing that makes them usable is to filter out everything that is not a repeated SELECT over your own tables, cluster queries that differ only in constants, and replace the literals with parameters. What comes out is what we call a query capsule: a reusable template drawn from a query a person actually wrote and presumably checked. In 0.3 the most frequent capsules, up to five, go into the prompt as worked examples. The commands, the per-engine setup and the tips are in how to build a text-to-SQL knowledge base from query history.
Coverage is the number that matters
The obvious risk with any learned knowledge base is not knowing what it does not know. A system that has ingested query history and produces confident answers about the 15% of the warehouse it understands, and equally confident answers about the rest, is worse than one that has read nothing, because it has earned trust it cannot honour everywhere.
So the knowledge base has to report on itself: which tables and columns have descriptions and which are still just names, and how many capsules the history produced.
In NLQueries that is kb-stats: how many tables and columns have descriptions, table by table with --verbose, how many capsules there are and how many foreign-key joins appear in them. The intended use is unglamorous: you run it, you see that the finance tables have almost no column descriptions because the finance team uses a different tool, and you know that questions in that area need a human in the loop until you fix it.
Knowing where the map is blank is not a limitation to be apologised for. It is the feature that makes the rest of the map usable.
The obvious caveat
Query history encodes your team's mistakes as faithfully as its knowledge. If everyone has been joining two tables wrongly for three years, the history teaches that too.
This is a real limitation and we do not have a clever solution to it. What we have is the same answer as everywhere else in the system: the knowledge base is a plain YAML file you can read, and the generated SQL is shown with every answer. When a system inherits a bad habit, you find out by reading the query, not by discovering a discrepancy six months later.
The knowledge base is not meant to be an oracle. It is meant to be a written-down version of what the team already knows, in a form the system can use and a person can correct.