Why Text-to-SQL Fails in Enterprise

Most Text-to-SQL demos are too clean.

They use simple schemas and obvious table names. They assume one or two tables link perfectly. This works for a demo. It fails in a real company.

In enterprise databases, the hard part is not the SELECT clause. The hard part is the join path.

A model might write a valid query for revenue by customer. The SQL runs. The dashboard shows numbers. But the answer is wrong.

Why does this happen?

  • The customer table is not the official master list.
  • The revenue field is not the finance-approved version.
  • Records are duplicated across regions.
  • The join creates fanout errors.
  • Fiscal calendars differ from calendar years.

A syntactically valid query is not a trusted query.

Real production systems are messy. You face missing foreign keys, legacy tables, and one-to-many relationships that inflate numbers. Even humans need to check old reports or ask finance before they trust a join.

An AI model only sees names and columns. It guesses. Sometimes the guess is dangerous.

Consider fanout. If you join orders to order lines and then to shipments, one order might count multiple times. The SQL is correct. The result is wrong.

Metadata helps, but it is not enough. Column names do not tell you if a relationship is safe for financial reporting. Foreign keys do not explain historical exceptions.

Text-to-SQL needs relationship intelligence.

A reliable system must know:

  • Approved join paths for specific business questions.
  • Relationship cardinality.
  • Known fanout risks.
  • Which paths are deprecated.

Instead of just writing code, the system should reason about safety. It should choose the trusted path or ask for help when paths are unclear.

The gap in current systems is the leap from mapping names to generating SQL. You must add a layer of relationship context.

Text-to-SQL is not just a language problem. It is a context problem.

Until models understand which joins are safe, they will work in demos but fail in production.

Source: https://dev.to/arisyndata/why-text-to-sql-breaks-when-the-join-path-is-not-obvious-3bk0

Optional learning community: https://t.me/GyaanSetuAi