Text-to-SQL for Enterprise Analytics: Schema Linking, Guardrails and Validating Against Governed Metrics

A text-to-SQL system that writes invalid SQL is a nuisance — the query errors and nobody is misled. The real danger is the query that is syntactically perfect, runs cleanly, and returns a number that is subtly wrong because the model guessed the wrong table, joined on the wrong key, or invented its own definition of a business term. No error is raised, a chart is produced, and a decision is made on it.

This is why enterprise text-to-SQL is a governance problem before it is a model problem. The question is not 'can the model write SQL' — modern models write SQL well — but 'can the organisation trust that the SQL means what the asker thinks it means'. The answer depends on three things the demo never shows: schema linking, guardrails, and validation against governed definitions.

Schema linking is the step where the model decides which tables and columns a question refers to. It is where most wrong-number failures originate. Point a model at a raw warehouse with columns called VAL1, DT_POST and ORG_CD and it will guess — sometimes correctly, sometimes not — and the guess is invisible in the output. Point it at curated metadata with clear names, descriptions, relationships and example values, and the mapping becomes reliable.

This is work you do once in the metadata layer, not per query: business-friendly table and column descriptions, documented relationships, synonyms for how people actually refer to things ('revenue', 'sales', 'turnover' resolving to the same measure), and sensible exclusion of columns that should never be queried. The quality of this metadata sets the ceiling on text-to-SQL accuracy far more than the choice of model.

A generated query must execute inside a cage. Non-negotiable guardrails: read-only credentials so no generated statement can ever write or drop; row-level security enforced at the data layer so the asker only sees rows they are entitled to, regardless of what the SQL requests; an allow-list of queryable objects so the model cannot reach into tables outside its remit; and cost and row limits so a careless cross-join cannot melt the warehouse or return ten million rows.

These controls live in the database and the serving layer, not in the prompt — a prompt instruction not to access the payroll table is a suggestion, while a permission that denies it is a control. Treat the language model as an untrusted query author whose output is always constrained by the same access rules you would apply to any user, because that is exactly what it is.

The fix for the two-executives-two-margins problem is to stop the model defining metrics at all. In a Microsoft estate the governed definitions already exist — as measures in a Power BI / Microsoft Fabric semantic model, where 'margin', 'OTIF' and 'fill rate' are defined once, version-controlled and agreed. Routing natural-language questions through that semantic model, rather than letting the model write raw aggregate SQL, means every answer uses the one sanctioned definition.

Where generated SQL is unavoidable, validate it against those definitions before it runs: check that the query's computation of a governed metric matches the certified measure, and reject or flag it if it does not. This turns the semantic layer into the source of truth and the language model into a translator from English to a question against that layer — which is the only version of text-to-SQL that survives contact with a board pack.

The pattern that holds up in production is narrow by design: natural language in, resolved against curated metadata and a governed semantic model, executed under read-only, row-level-secured, cost-limited access, with the generated query and its assumptions shown to the user for transparency. On the Microsoft stack this is Conversational BI over a Fabric/Power BI semantic model, which is why Microsoft built Copilot for Power BI to work against the model rather than the raw lakehouse.

Showing the user the query and the definition it used is not a nicety — it is how trust is built and errors are caught. An analyst who can see 'margin = [Gross Margin] measure, filtered to Region = EMEA, FY2026 Q2' can confirm or correct it; a black-box number cannot be audited. The best systems make the generated logic visible by default rather than hiding it behind the chart.

Text-to-SQL over a raw warehouse with poor metadata is where most enterprise attempts fail — the schema linking is unreliable and there is no governed definition to validate against, so the wrong-number risk is unmanaged. The capability belongs over a curated semantic layer; if that layer does not exist, building it is the real project, and text-to-SQL is the feature you add afterwards.

Ambiguous questions remain ambiguous. 'How are we doing in the north?' has no single correct SQL, and a system that silently picks an interpretation is back to inventing answers — the right behaviour is to ask a clarifying question, which is a product-design choice, not a model default. And highly complex analytical questions still exceed what any generator reliably produces, so text-to-SQL complements analysts on self-service questions; it does not replace them on hard ones.

Anchor the capability to governance before you expose it to users.

• Confirm a governed semantic model exists with the key metrics defined once (Power BI / Fabric measures) — build it if it does not; that is the real foundation
• Curate the metadata the model links against: business-friendly names, descriptions, relationships and synonyms
• Enforce read-only access, row-level security, object allow-lists and cost/row limits at the data layer, not the prompt
• Route questions through the semantic model, and validate any generated SQL against the certified definitions before it runs
• Show users the query and the definition used, and have the system ask rather than guess on ambiguous questions