BigQuery and Text-to-SQL
Explore why Text-to-SQL pilots often fail between demonstration and production, from ambiguous business terms to uncontrolled costs. Learn to apply three key safeguards: semantic layers for meaning, cost controls for spending, and permissions enforced in the data layer.
Many enterprise customers keep their most valuable data in BigQuery: sales, usage, billing, network events, and customer records. A natural request follows soon after any GenAI pilot begins: “Can business users just ask questions in plain English and get answers from our data?” Text-to-SQL, where a model translates a natural language question into a SQL query, appears to answer that request directly. Early demonstrations are often impressive, since a model can write a correct query against a small, clean sample schema within seconds.
Production is a different matter. Many Text-to-SQL pilots stall between demonstration and rollout, and the reasons are rarely about the model’s ability to write SQL. They involve ambiguous business definitions, unexpected query costs, and data access rules the agent was never designed to respect. FDE interviewers use Text-to-SQL scenarios frequently because they reveal whether a candidate thinks about data systems the way a customer’s data and security teams do.
Why Text-to-SQL pilots fail
The most serious failure in Text-to-SQL is not a query that crashes. It is a query that runs successfully and returns a plausible but wrong number. A sales director who receives an incorrect revenue figure has no obvious way to detect the error, and once one wrong number is discovered, trust in the whole system drops sharply.
Several causes account for most of these failures.
Cause | What happens |
Ambiguous business terms | “Active customer” or “revenue” has a specific internal definition that the model does not know |
Unclear schemas | Columns such as amt_2 or status_cd give the model little to work with |
Complex joins | The model joins tables on the wrong keys, duplicating or dropping rows |
Unbounded queries | A query scans an entire multi-terabyte table, creating unexpected cost |
Broad access | The agent runs under a powerful service account and returns data the user should not see |
No evaluation | The team has no set of known questions and correct answers to measure accuracy |
Each of these causes has a corresponding safeguard. Three safeguards matter most: a semantic layer for meaning, cost controls for spending, and permissions enforced in the data layer for access.
The first safeguard addresses the most common failure: the gap between business ...