Why Text-to-SQL Fails on Enterprise Data (and How to Fix It)

1. Ambiguity at the metric level. 'Revenue', 'active customer', 'margin' mean different things across teams. The warehouse does not tell the model which one to use.

2. Hidden joins. Production schemas have bridge tables, soft deletes, effective-dated dimensions, and multi-hop relationships no schema introspection exposes.

3. Business rule exceptions. 'Exclude returns for this category, except in EU where returns are counted differently' — no LLM can infer this from tables.

4. Time semantics. Fiscal calendars, week-starts, retail 4-4-5, as-of vs transaction dates — all of these quietly break naive SQL generation.

5. Silent failure. Unlike a compiler error, wrong SQL still runs and returns a number. The operator has no way to know whether to trust it.

A grounded context layer. Instead of asking the LLM to invent a join, route its question through a governed layer that already knows the answer to 'what does revenue mean here' and 'how do we compute retention in food delivery'.

Human-in-the-loop validation. Promoted, reusable answer patterns reviewed by analysts become trusted artifacts the organization can lean on.

Traceability. Every answer must be able to explain which definitions and rules produced it, so operators can trust or reject it quickly.