DEV Community: ArisynData

Why Text-to-SQL Breaks When the Join Path Is Not Obvious

ArisynData — Fri, 26 Jun 2026 13:05:00 +0000

Most Text-to-SQL examples are too clean.

They usually assume a simple schema, obvious table names, clear foreign keys, and a question that maps neatly to one or two tables. In that environment, generating SQL from natural language looks impressive.

Enterprise databases are not like that.

In real analytics work, the hard part is often not the SELECT clause. It is the join path.

The SQL Can Be Valid and Still Wrong

Imagine a user asks:

“Show revenue by customer for the last quarter.”

A model may generate something like:

SELECT
  c.customer_name,
  SUM(o.revenue) AS total_revenue
FROM customers c
JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.order_date < '2026-04-01'
GROUP BY c.customer_name;

Technically, this looks fine.

But in an enterprise environment, several things may be wrong:

customers may not be the approved customer master.
orders.revenue may not be the finance-approved revenue field.
Customer records may be duplicated across regions.
Some orders may need to be excluded because they were adjusted later.
The join may create duplication if there are multiple customer records per account.
Last quarter may follow fiscal, not calendar, logic.

The database accepts the query.

The dashboard renders.

The answer is wrong.

That is the uncomfortable part of enterprise Text-to-SQL.

A syntactically valid query is not the same as a trusted query.

Why Join Paths Are Hard

Join paths are obvious only when the data model is clean.

In production systems, they are usually messy.

You may have:

Missing foreign keys
Legacy tables
Similar columns with different meanings
One-to-many relationships that create fanout
Historical snapshots
Slowly changing dimensions
Department-specific marts
Fields reused for different purposes
Business rules that exist only in old SQL reports

Even experienced engineers often need time to inspect the schema, check existing reports, ask someone in finance, and run sample queries before trusting a join.

Now ask an AI model to do the same thing with only table names and column names.

It will guess.

Sometimes the guess will be right.

Sometimes it will be dangerously plausible.

The Fanout Problem

One of the most common issues is fanout.

Suppose you join orders to order_lines and then to shipments.

SELECT
  o.customer_id,
  SUM(o.order_amount) AS revenue
FROM orders o
JOIN order_lines l
  ON o.order_id = l.order_id
JOIN shipments s
  ON l.line_id = s.line_id
GROUP BY o.customer_id;

If one order line can have multiple shipments, order revenue may be counted multiple times.

The SQL is valid.

The join is valid.

The result is not valid for revenue reporting.

A human analyst may know to aggregate at the order level first, or use a shipment-adjusted revenue table, or avoid this path altogether.

A model needs that knowledge in context.

Why Metadata Alone Is Not Enough

Metadata helps, but it does not solve the full problem.

Column names can tell you that customer_id relates to customers.

They do not tell you whether this is the right customer relationship for financial reporting.

Foreign keys can tell you that a relationship exists.

They do not tell you whether the relationship is safe for aggregation.

Descriptions can tell you what a table contains.

They do not always explain historical exceptions.

That is why enterprise Text-to-SQL needs more than schemas.

It needs relationship context.

What Relationship Context Should Include

At minimum, an AI query system should know:

Candidate join paths between tables
Approved join paths for common business questions
Relationship cardinality
Known fanout risks
Join confidence
Source of relationship evidence
Whether the relationship came from constraints, SQL history, naming patterns, dbt models, BI datasets, or human approval
Which paths are rejected or deprecated

This changes the behavior of the system.

Instead of simply generating SQL, it can reason about query safety.

It can choose the trusted path.

It can warn when no approved path exists.

It can ask for clarification when multiple paths are possible.

That is far more useful than blindly producing a query.

A Better Pattern

A more reliable Text-to-SQL system should work like this:

Parse the user question.
Identify business entities and metrics.
Resolve semantic definitions.
Retrieve candidate tables.
Retrieve trusted relationship paths.
Check join risks.
Generate SQL using approved paths.
Validate the SQL against semantic and relationship rules.
Explain the assumptions behind the result.

The key difference is step 5 and step 6.

Many systems jump from semantic mapping directly to SQL generation.

That is where errors enter.

The missing layer is relationship intelligence.

Final Thought

Text-to-SQL is not just a language translation problem.

It is a context problem.

Models are getting better at writing SQL.

But enterprise analytics requires more than syntactically correct SQL.

It requires knowing which joins are safe, which paths are trusted, and which assumptions should be checked before the query runs.

Until that context exists, Text-to-SQL will continue to work well in demos and struggle in real companies.

Why AI Analytics Has a Knowledge Problem

ArisynData — Wed, 24 Jun 2026 13:38:00 +0000

One thing I’ve noticed while working with enterprise analytics systems:

The hardest problems are rarely technical.

Most modern models can generate SQL.

Most warehouses are well documented.

Most organizations have catalogs and governance programs.

Yet teams still depend heavily on a handful of experienced engineers.

Why?

Because analytics depends on knowledge, not just data.

For example:

A schema may tell you that three customer tables exist.

It doesn’t tell you:

· which one is authoritative

· which one is historical

· which one executive reporting relies on

Experienced engineers know the difference.

AI doesn’t.

That’s why many enterprise analytics failures aren’t caused by bad SQL generation.

They’re caused by missing organizational knowledge.

As AI adoption accelerates, I think we’re going to spend less time talking about prompts and more time talking about knowledge infrastructure.

Because models can’t use knowledge that organizations never captured.

Why AI Keeps Generating Bad SQL Even When The Schema Is Correct

ArisynData — Mon, 22 Jun 2026 13:32:00 +0000

One thing I've noticed while testing Text-to-SQL systems:
The schema is often fine.
The model is often fine.
The SQL is often syntactically correct.
The answer is still wrong.
Why?
Because SQL generation isn't the hard part.
Join selection is.
Imagine a warehouse containing:
orders
customers
subscriptions
invoices
accounts
A model may know all five tables exist.
The challenge is deciding:
Which relationship path should be used?
That's where many systems fail.
Most Text-to-SQL architectures focus on:
Schema → Prompt → SQL
But production environments usually require:
Schema
↓
Relationship Discovery
↓
Trusted Join Path
↓
Prompt
↓
SQL
Without relationship context, the model is forced to guess.
And enterprise analytics is a terrible place for guessing.

Why AI Analytics Is Becoming a Data Infrastructure Problem

ArisynData — Wed, 17 Jun 2026 13:44:00 +0000

Most AI analytics discussions focus on models.

In practice, many failures come from the surrounding data environment.

A model can understand natural language and generate SQL.

That doesn't mean it understands your business.

Enterprise environments contain:

disconnected systems
undocumented relationships
conflicting metric definitions
hidden business logic
What AI actually needs is context.

Business Question
↓
AI Agent
↓
Semantic Definitions
↓
Metadata
↓
Lineage
↓
Relationship Discovery
↓
Enterprise Data

The question is no longer:

"Can the model generate SQL?"

It's:

"Can the model understand the environment behind the SQL?"

Why Text-to-SQL Needs Join Path Context, Not Just Schema

ArisynData — Mon, 15 Jun 2026 13:25:00 +0000

A lot of Text-to-SQL examples start with something like this:

Give the model the schema.
Ask the user for a question.
Generate SQL.
Run it.

That can work for demos.

It gets much harder in real enterprise data.

The problem is not always SQL syntax. The model may generate perfectly valid SQL. The query may run. The output may even look reasonable.

The issue is whether the query used the right path through the data.

Schema does not tell the whole story

A schema gives you tables, columns, types, and sometimes constraints.

But it usually does not tell you enough about analytical intent.

For example, imagine a question like:

Show monthly revenue by customer segment.
A model may see tables like:

1）customers
2）orders
3）order_items
4）payments
5）invoices
6）customer_segments
It can probably generate a join.

But which one?

Should revenue come from orders, payments, invoices, or finance postings?

Should customer segment be current segment or segment at the time of order?

Should the query join customer directly to orders, or through account, membership, or billing entity?

Will joining order items duplicate revenue?

These are not syntax questions.

They are relationship and grain questions.

Valid SQL is not the same as correct SQL

This is where enterprise Text-to-SQL gets dangerous.

A bad query does not always fail.

Sometimes it returns a clean table and a nice chart.

The model may choose a join path that looks obvious from column names but is not the business-approved path. Or it may join two fact tables directly and inflate the result. Or it may use a dimension table that is current-state when the metric needs historical context.

No exception is thrown.

The number is just wrong.

What join path context should include

A more reliable Text-to-SQL system needs relationship context before SQL generation.

At minimum, join path context should include:

candidate tables for the question
approved table relationships
join keys
cardinality
table grain
preferred paths
risky paths
duplication risks
validation checks
This turns table relationships into something the model can actually use.

Instead of asking the model to infer everything from names, the system gives it a query plan.

A better workflow

A schema-only workflow looks like this:

User question
→ schema prompt
→ SQL generation
→ execution

A relationship-aware workflow looks more like this:

User question
→ intent detection
→ semantic matching
→ candidate table selection
→ trusted join path retrieval
→ query plan
→ SQL generation
→ validation
→ answer

That extra planning step is not just overhead.

It is what prevents the system from producing plausible but wrong SQL.

The analyst version of this

Good analysts already think this way.

Before writing a join, they ask:

What is the grain of this table?
Is this the source of truth?
Will this join duplicate the metric?
Is this relationship current-state or historical?
Does this path match the business definition?
Text-to-SQL systems need to capture more of that reasoning.

Not as a long prompt full of vague instructions, but as structured context the system can retrieve and apply.

Final thought

Text-to-SQL is not just a translation problem.

It is a data modeling problem, a governance problem, and a relationship-context problem.

Models are getting better at writing SQL.

But if the system does not know how the underlying data connects, better SQL generation just gives us wrong answers faster.

Why Text-to-SQL Needs Table Relationship Discovery Before SQL Generation

ArisynData — Thu, 11 Jun 2026 16:12:00 +0000

Most Text-to-SQL discussions start with language.

Can the model understand the question?
Can it generate valid SQL?
Can it explain the result?

Those are important.

But in enterprise systems, the harder problem is often not language.

It is table relationships.

A model may understand the user’s question and still choose the wrong tables, the wrong join keys, or the wrong join path.

The SQL may run.

The answer may look reasonable.

The result may still be wrong.

A simple example

Suppose a user asks:

What was revenue by customer segment last quarter?
A model may retrieve tables that look relevant:

customers
customer_profiles
customer_segments
orders
contracts
invoices
invoice_items
revenue_snapshot

Now the difficult part begins.

Which customer table is authoritative?

Should revenue come from invoices, contracts, orders, or a finance snapshot?

Should customer segment be current or historical?

How should invoice data connect to customer segment?

Should the join go directly from invoices to customers, or through contracts, accounts, or a bridge table?

These are not just semantic questions.

They are relationship questions.

Schema retrieval is not enough

A common Text-to-SQL flow looks like this:

User question
→ retrieve relevant schemas
→ send schemas to LLM
→ generate SQL
→ execute query
This can work for simple demos.

It breaks down when the schema is large, old, inconsistent, or distributed across systems.

Schema retrieval can tell the model that a column exists.

It does not always tell the model whether that column should be used.

For example:

customer.customer_id
crm_account.account_id
billing_customer.customer_ref
customer_snapshot.customer_id
customer_profile.customer_id
All of these may look relevant.

Only one may be right for the question.

Or the correct answer may require more than one of them.

Foreign keys are helpful, but incomplete

Declared foreign keys are useful when they exist.

But many enterprise systems do not fully declare relationships in the database.

Some relationships are enforced in application code. Some come from ETL logic. Some exist across systems. Some live only in BI models or analyst knowledge.

Even when foreign keys exist, they may not define the right analytical path.

A join can be technically valid and analytically wrong.

That is why Text-to-SQL systems need a relationship-aware layer before SQL generation.

What relationship discovery should provide

Before generating SQL, the system should be able to build context like this:

{
  "question": "revenue by customer segment last quarter",
  "candidate_tables": [
    "invoices",
    "contracts",
    "customers",
    "customer_segments"
  ],
  "preferred_join_path": [
    "invoices.contract_id -> contracts.contract_id",
    "contracts.customer_id -> customers.customer_id",
    "customers.segment_id -> customer_segments.segment_id"
  ],
  "grain_warning": "invoice detail level may duplicate revenue unless aggregated before joining",
  "time_logic": "use historical customer segment for period-based reporting",
  "confidence": "high"
}

This kind of context gives the LLM a much better foundation.

Instead of guessing from names, the model receives evidence about how the data connects.

A better Text-to-SQL workflow

A relationship-aware workflow looks more like this:

User question
→ intent understanding
→ semantic mapping
→ metadata retrieval
→ table relationship discovery
→ join path selection
→ SQL generation
→ SQL validation
→ execution
→ explanation
The relationship discovery step should answer:

Which tables are relevant?
Which joins are possible?
Which join path is preferred?
What is the relationship strength?
Could the join duplicate rows?
Is there a bridge table?
Are there multiple valid paths?
Should the system ask a clarification question?
This step reduces the chance that the model produces SQL that is syntactically correct but structurally wrong.

What can go wrong without relationship context?

Here are common failure modes.

Duplicate rows

A one-to-many join multiplies records because the model does not understand data grain.

SELECT
c.segment,
SUM(i.amount) AS revenue
FROM invoices i
JOIN customers c
ON i.customer_id = c.customer_id
JOIN customer_contacts cc
ON c.customer_id = cc.customer_id
GROUP BY c.segment;
If each customer has multiple contacts, revenue may be over-counted.

Missing records

An inner join removes valid records because not all entities have a matching dimension row.

Wrong table

The model chooses customer_profile when the approved reporting path requires customer_snapshot.

Wrong time logic

The model uses the customer’s current segment instead of the segment at the time of transaction.

False confidence

The worst case: the query runs, the chart looks good, and nobody notices the join path was wrong.

Relationship discovery is not just for AI

This is useful beyond Text-to-SQL.

Table relationship discovery also helps with:
1）data warehouse modeling
2）ETL and ELT design
3）BI dataset preparation
4）migration planning
5）impact analysis
6）data quality checks
7）metadata documentation
8）lineage and governance
In many companies, relationship knowledge is scattered across SQL scripts, notebooks, dashboards, and senior analysts’ memory.

AI systems need that knowledge to become structured and reusable.

Final thought

Text-to-SQL is not only a translation problem.

It is a data context problem.

A model needs to know what the user means.

It also needs to know how the data connects.

That is why table relationship discovery should happen before SQL generation, not after the query fails.

Better prompts help.

Better relationship context matters more.

Designing Relationship Context for Text-to-SQL Systems

ArisynData — Mon, 08 Jun 2026 15:00:00 +0000

A common Text-to-SQL architecture looks like this:

user question -> schema context -> prompt -> SQL

This works well enough for demos.

It is not enough for enterprise databases.

In real production environments, the missing piece is often relationship
context: structured information about how tables and fields connect,
which join paths are trusted, and where ambiguity exists.

A semantic layer can define what a business term means.

But the SQL generator still needs to know how the physical data objects
connect.

The problem with schema-only context

Many Text-to-SQL systems start by passing table names, column names, and
a user question into a model.

For example:

Question:

Which customers have not placed an order in the last 90 days?

Schema:

customers(id, name, email, signup_date)

orders(id, customer_id, order_date, status, total_amount)

For a simple schema, this is fine.

The relationship is obvious:

customers.id = orders.customer_id

But enterprise schemas are rarely that simple.

You may have:

crm_account

customer_profile

customer_profile_v2

sales_contract

invoice_header

invoice_line

payment_record

finance_revenue_snapshot

region_mapping

Some tables are authoritative.

Some are historical.

Some are replicated.

Some are partially deprecated.

Some relationships are declared as foreign keys.

Many are not.

A model can still generate SQL from this environment, but without the
right relationship context, it may generate SQL that looks correct and
returns the wrong answer.

That is worse than an obvious error.

Semantic context is different from relationship context

It is useful to separate two kinds of context.

Semantic context answers:

What does this business term mean?

Relationship context answers:

How do the relevant data objects connect?

For example, semantic context may define:

{

"business_term": "revenue",

"approved_metric": "recognized_revenue",

"definition": "Revenue recognized according to finance reporting rules",

"owner": "Finance",

"version": "2026.1"

}

That helps the model understand the business meaning.

But it does not fully answer which tables to join.

For that, the system needs relationship context.

What relationship context might look like

Relationship context can be represented as structured input before SQL
generation.

For example:

{

"business_intent": "customers_without_recent_orders",

"semantic_context": {

"entity": "customer",

"activity": "order",

"time_window": "last_90_days"

"relationship_context": {

"candidate_tables": [

"customers",

"orders",

"order_items"

"preferred_path": [

{

"from": "customers.customer_id",

"to": "orders.customer_id",

"relationship_type": "customer_to_order",

"confidence": 0.97

}

"risk_flags": [

"orders table contains cancelled orders",

"order_date should be used instead of created_at"

"clarification_required": false

}

Now the model is not guessing from raw table names alone.

It has a constrained context.

It knows which tables matter.

It knows how they connect.

It knows which risks to consider.

A more reliable workflow

A relationship-aware Text-to-SQL workflow might look like this:

Natural language question

↓

Intent and entity extraction

↓

Business semantic mapping

↓

Metadata retrieval

↓

Relationship context assembly

↓

Join path selection

↓

SQL generation

↓

Validation

↓

Execution

↓

Explanation and feedback

The important point is that SQL generation happens after semantic and
relationship context are assembled.

This reduces the burden on the model.

The model is still useful, but it is no longer responsible for inventing
the entire data environment from scratch.

Why prompts are not enough

Prompt engineering helps.

You can tell the model:

Use the correct tables.

Avoid ambiguous joins.

Prefer approved metrics.

Ask a clarifying question when necessary.

These are good instructions.

But they are not a substitute for missing data knowledge.

If the system does not know which table is authoritative, the
instruction "use the correct table" is not enough.

If the system does not know which join path is trusted, the instruction
"avoid ambiguous joins" is not enough.

The system needs a governed source of truth for metadata, business
definitions, and table relationships.

Otherwise, the model is forced to make a probabilistic decision in a
place where the business expects determinism.

What should be included in relationship context?

A practical relationship context layer may include:

source table and target table
source field and target field
relationship type
confidence score
coverage ratio
uniqueness ratio
preferred join path
deprecated relationships
business meaning of the relationship
validation rules
known duplication risks

For example:

{

"relationship": {

"source": "sales_contract.customer_id",

"target": "customer_master.customer_id",

"type": "many_to_one",

"confidence": 0.94,

"coverage": 0.98,

"preferred": true,

"notes": "Preferred customer relationship for sales reporting"

}

This kind of context gives the SQL generation layer a much better
starting point.

Validation matters

Even with relationship context, generated SQL should not be executed
blindly.

Validation should check:

whether referenced tables exist
whether join fields are resolvable
whether the query may duplicate records
whether the metric definition matches the approved semantic layer
whether the user has permission to access the requested data
whether ambiguity should trigger clarification

For example, if there are two possible revenue definitions, the system
should not guess.

It should ask:

Do you mean booked revenue or recognized revenue?

A good Text-to-SQL system should know when not to answer.

The practical takeaway

If you are building an enterprise Text-to-SQL system, do not start with
the model alone.

Start with these questions:

What business terms does the user use?
Which metrics and dimensions are governed?
Which tables are authoritative?
Which join paths are trusted?
What should the system do when the question is ambiguous?
How will generated SQL be validated before execution?
How will user feedback improve future answers?

Only then should the model generate SQL.

At Arisyn, this is one of the problems we are exploring: how to make
enterprise Text-to-SQL more relationship-aware, governed, and
explainable by design.

The direction is simple:

Better prompts help.

Better context matters more.

Why Text-to-SQL Needs Relationship Context, Not Just Better Prompts

ArisynData — Fri, 05 Jun 2026 15:00:00 +0000

Text-to-SQL systems are often explained as a language problem:

User asks a question. Model generates SQL. Database returns results.
That mental model is too simple for real enterprise data.
In production, the hard part is rarely whether a model can write a SELECT statement. The hard part is whether the system has enough context to choose the right tables, the right metrics, the right filters, and the right join path.
This is where many demos break.

The clean demo problem

A demo dataset usually
has a small number of well-named tables:

customers
orders
order_items
products

The relationships are obvious. The naming is consistent. The business logic is easy to infer.
Enterprise databases are different.
You may see:

cust_master
customer_profile_v2
crm_account
sales_contract
invoice_record
invoice_detail
region_mapping
finance_revenue_snapshot

Some relationships are declared. Many are not. Some columns look similar but mean different things. Some tables are current; others are historical, replicated, or partially deprecated.
A language model can still generate SQL, but without the right context, it may generate SQL that looks reasonable and returns the wrong answer.
That is more dangerous than an obvious error.

The missing input: relationship context

A better Text-to-SQL pipeline should not ask the LLM to infer everything from raw table names.
It should provide structured context such as:

{
  "business_term": "customer",
  "candidate_tables": ["crm_account", "customer_profile_v2"],
  "preferred_table": "crm_account",
  "relationships": [
    {
      "from": "crm_account.account_id",
      "to": "sales_contract.account_id",
      "confidence": 0.96
    },
    {
      "from": "sales_contract.contract_id",
      "to": "invoice_record.contract_id",
      "confidence": 0.93
    }
  ]
}

Now the model is no longer guessing from names alone. It is generating SQL with context about semantics and relationships.

A more reliable architecture

A relationship-aware Text-to-SQL workflow might look like this:

Natural language question
        ↓
Intent and entity extraction
        ↓
Business semantic mapping
        ↓
Candidate table discovery
        ↓
Relationship / join path discovery
        ↓
SQL generation
        ↓
Validation and execution
        ↓
Explanation and feedback

The important point is that SQL generation happens after semantic and relationship context is assembled.

Why prompts are not enough

Prompt engineering helps, but prompts cannot reliably solve missing data context.
You can tell a model:

Use the correct tables and avoid ambiguous joins.
But that instruction does not tell it which table is correct.
The system needs a governed source of truth for metadata, business definitions, and table relationships. Otherwise, the model is forced to make a probabilistic decision in a place where the business expects determinism.

Where relationship discovery fits

Relationship discovery is the process of finding and managing how tables and fields connect, even when explicit foreign keys are missing or incomplete.
Signals may include:

database constraints
naming patterns
value overlap
uniqueness and coverage
co-occurrence
business semantic mappings
historical query patterns No single signal is perfect. But combining them gives the SQL generation layer a much better starting point.

The practical takeaway

If you are building an enterprise Text-to-SQL system, do not start with the model alone.
Start with these questions:

What business terms does the user use?
Which metrics and dimensions are governed?
Which tables are authoritative?
Which join paths are trusted?
What should the system do when the question is ambiguous? Only then should the model generate SQL. At Arisyn, we are exploring this problem through a semantic query engine backed by data relationship discovery. The direction we believe in is simple: enterprise Text-to-SQL should be relationship-aware, governed, and explainable by design.