How to give LLMs access to large warehouse schemas: embedding table and column metadata, retrieving relevant schema context, and the schema linking problem.
Schema linking is the hardest sub-problem in text-to-SQL at scale. At five tables, you can put the entire schema in context. At fifty tables, you are at the edge. At five hundred tables -- the reality of most enterprise data warehouses -- you cannot. Schema linking is the process of identifying which tables and columns are relevant to a user's question before generating SQL. Get it wrong and the LLM generates SQL against the wrong tables. Get it right and the rest of the problem becomes tractable.
The standard approach is RAG over schema metadata: embed descriptions of every table and column, index them in a vector store, and retrieve the most relevant ones at query time. This is straightforward to build but fragile to maintain. The quality of schema linking is bounded by the quality of the metadata -- if your column descriptions are empty or wrong, retrieval fails and SQL generation fails downstream. Schema documentation is not a nice-to-have; it is the infrastructure that makes LLM warehouse access work.
Beyond simple semantic retrieval, production schema linking systems add structural signals: table relationship graphs (joins available between tables), query history (which tables appear together in past queries for similar questions), and schema popularity (frequently queried tables and columns get higher retrieval weight). These signals require the data engineer to build and maintain them as infrastructure -- they do not come from the warehouse for free. The teams getting the best results on schema linking are the ones treating it as a data product problem, not an LLM problem.
Schema linking breaks in predictable ways as warehouse scale grows. The first failure mode is semantic ambiguity: if your embedding model maps "customer acquisition cost" to a marketing table when the relevant table is named "cac_breakdown_by_channel", semantic search fails and the LLM generates SQL against the wrong table. The fix is better column and table descriptions -- but descriptions must be written in the vocabulary users actually use when asking questions, not in the internal naming conventions the original developer used.
The second failure mode is disambiguation at the same abstraction level. Two tables named "orders" -- one for e-commerce, one for food delivery -- look identical in embedding space unless the metadata explicitly differentiates them. The LLM will pick one and be wrong half the time. Production schema linking systems solve this with business context metadata: the description should say "e-commerce order records for the US storefront, excludes marketplace orders" not just "order data". The data catalog is where this context lives; schema linking quality is a direct function of catalog quality.
The third failure mode is structural: the user's question requires a join between two tables, but schema linking only retrieved one of them. Structural retrieval -- using the foreign key graph to expand the retrieved table set to include all join partners -- catches this systematically. This requires building and maintaining a join graph as infrastructure, not just embedding table descriptions. The teams that have solved schema linking at scale treat join topology as a first-class signal alongside semantic similarity.
The minimum schema metadata record that enables reliable linking contains four elements. First, table purpose: a plain-language description of what business process this table captures, what it does NOT contain, and which team owns it. Second, column descriptions: what each column means in business terms, including units (dollars vs. cents, UTC vs. local time), encoding conventions (status codes and their meanings, flag fields), and what a null value means for that column. Third, example values for categorical and low-cardinality columns, showing the actual values the column takes in production -- not just the data type. Fourth, relationship metadata: which columns are foreign keys, what they join to, and whether the join is guaranteed to be unique.
Schema metadata should be versioned and treated as a data product with an owner and a freshness SLA. Every time a column is renamed, a table is deprecated, or a new business concept is modeled in the warehouse, the schema metadata must be updated within the same sprint. A schema metadata store that lags behind the warehouse by two weeks has effectively zero coverage for recently changed tables. This is the same discipline applied to data contracts: the schema description is a contract between the warehouse and its consumers, including LLM agents.
The most comprehensive recent survey of the field. The schema linking section (section 4) is the clearest technical treatment of the core problem: how do you identify which tables and columns are relevant to a question before generating SQL?
Shows how an enterprise warehouse vendor solves schema linking at scale: a semantic model layer sits between the LLM and raw tables, reducing the schema search space to pre-approved, documented entities.
If you are building schema linking on your own infrastructure, embedding table and column descriptions and indexing them in pgvector is the lightest-weight path. No additional vector database required.
Some course links above are affiliate links. If you enroll, we may earn a small commission at no extra cost to you.
New resources and perspective on building AI-ready data systems, a few times a month. No spam.