Text-to-SQL That Survives Real Enterprise Schemas

Most text-to-SQL demos work on a 5-table SQLite database with clean column names. Real enterprise schemas look nothing like that. They have 500+ tables, cryptic names like tbl_emp_comp_v2_fix, views that depend on other views, and stored procedures nobody documented.

The problem with existing text-to-SQL approaches

OpenAI's GPT-4 and Anthropic's Claude can write SQL. But when you point them at a real enterprise schema, they hallucinate column names, join on wrong keys, and produce queries that look correct but return garbage. We've seen accuracy rates as low as 30% on production schemas.

The issue isn't the model — it's the context. A 500-table schema with 2,000 columns doesn't fit in a single prompt. Even with function calling, the model needs to know which tables are relevant before it can write a query.

Our approach: schema-aware retrieval + multi-step generation

Our text-to-SQL API at mazzagrp.com uses a three-stage pipeline:

1. Schema retrieval: We embed every table name, column name, and foreign key relationship. When a user asks a question, we retrieve the top 20 most relevant tables — not the entire schema.
2. Query planning: The model generates a query plan (which tables to join, what aggregations) before writing SQL. This catches hallucinated joins.
3. SQL generation + validation: The final SQL is validated against the actual schema. If a column doesn't exist, we regenerate.

Example: natural language to SQL on a real schema

curl -X POST https://api.mazzagrp.com/v1/sql-generate \
  -H "Content-Type: application/json" \
  -d '{
    "question": "Show me top 10 customers by revenue in Q3 2024",
    "schema": "postgresql://connection_string"
  }'

The response includes the SQL query, the tables it joins, and an explanation of each step. You get a query you can audit before running it.

Handling enterprise complexity

Enterprise schemas have challenges that toy databases don't:

• Window functions: "Show running total by month" requires SUM() OVER (ORDER BY month) — not a simple GROUP BY.
• Dialect portability: The same question generates different SQL on PostgreSQL vs SQL Server vs Oracle. We detect the dialect and adjust.
• Multi-join queries: "Customers who bought X but not Y" requires 3+ joins with EXCEPT/NOT EXISTS. Our planner handles this.
• Schema aliases: Enterprise tables often have views and aliases. We resolve to the underlying physical tables.

Benchmark results

On the Spider benchmark (the standard text-to-SQL eval), our pipeline achieves 87% execution accuracy. On real enterprise schemas (not benchmark data), we measure 78-92% depending on schema complexity. The key insight: schema retrieval matters more than model size.

Try it at mazzagrp.com/api-docs/sql-generate — $0.12 per query, payable in USDC on Base.