Text-to-SQL in production: 5 errors that kill accuracy

94% accuracy doesn’t come from a magic prompt

When we sell D4ta Oracle, we don’t promise that an LLM “understands your business.” We promise that an operator can ask “how many units of SKU X are in the Barcelona warehouse?” and get the correct figure in seconds.

That requires engineering. These are the five errors that most often break Text-to-SQL projects in production.

1. Raw schema without business context

Sending the full CREATE TABLE to the model is insufficient. Column names (qty_avail, wh_loc_cd) mean nothing to the LLM or the user.

Solution: semantic layer with business descriptions, synonyms (“stock” = qty_avail, “Barcelona” = warehouse_id = 3), and indexed examples of frequent questions with pgvector.

2. No role-based permissions

A chat that can run SELECT * on cost or payroll tables is a security incident waiting to happen.

Solution: role policies before SQL generation. The model only sees tables and columns allowed for that user. Validation happens after generation and before execution.

3. Confusing natural language with legal language

“Pending shipment” can mean different things depending on the ERP: confirmed order, picking started, or delivery note generated without warehouse dispatch.

Solution: explicit business definitions in the RAG. If there’s ambiguity, the system should ask, not guess.

4. Not validating SQL before execution

A misgenerated DELETE or a cartesian JOIN can crash performance or expose data.

Solution: statement allowlist (SELECT only), default LIMIT, query timeout, and execution plan review for expensive queries.

5. Measuring “nice answers” instead of “correct answers”

A fluent answer in plain language that cites a made-up number is worse than a visible error.

Solution: golden set of questions with expected answers. Regression tests on every schema or model change. Primary metric: numerical accuracy, not perplexity.

Reference stack (D4ta Oracle)

Question → Embedding → Context retrieval (pgvector)
        → SQL generation with scoped schema
        → Validation + permissions
        → PostgreSQL execution
        → Natural language response + traceability

When does Oracle make sense for your company?

• You have data in ERP/PostgreSQL but the team queries in Excel
• More than 10h/month on repetitive stock, order, or turnover questions
• You need self-service without training every user in SQL

If you don’t have the data digitized yet, Oracle isn’t the first step — you first need to build the data pipeline.

Try Oracle demo → · Full Chat-to-SQL guide →