Solving Text-to-SQL Join Hallucinations in Large Database Schemas

Building a production-grade Text-to-SQL agent is deceptively simple in a demo but notoriously difficult in a real-world enterprise environment. When your data warehouse scales to 900+ tables, such as a massive ClickHouse deployment, the standard RAG (Retrieval-Augmented Generation) approach of stuffing the schema into a prompt fails spectacularly. The most common failure? The LLM begins to hallucinate complex JOIN operations across tables that have no physical or logical relationship.

In this tutorial, we will explore how we successfully constrained a local LLM—integrated via n1n.ai for high-speed inference—to handle a massive schema without inventing joins. By separating table retrieval from relationship logic and implementing a trust-weighted join graph, we reduced query failures by over 60%.

The Core Challenge: Schema Overload

When dealing with a schema of this magnitude, you face two primary hurdles:

1. Table Selection: The model selects the wrong tables because semantic similarity is too broad.
2. Join Hallucination: Even if the correct tables are found, the model doesn't know the exact keys or grains, leading it to guess (and fail).

Naive implementations often try to solve this with a "Mega-Prompt." However, even advanced models like Claude 3.5 Sonnet or OpenAI o3 (accessible via the n1n.ai API aggregator) have context limits and attention decay when processing thousands of lines of DDL. The solution is to move the intelligence out of the prompt and into the architecture.

Step 1: Hybrid Retrieval with Reciprocal Rank Fusion (RRF)

Finding the right table among 900 is a retrieval problem. We found that vector search alone (using bge-m3 embeddings) often misses specific technical identifiers. To solve this, we implemented a hybrid search strategy in Postgres using pgvector and pg_trgm.

Why Vector Search Isn't Enough

Vector search is great for conceptual matches (e.g., "storage volumes" matching array_ldev_config). However, it struggles with exact matches for acronyms or specific column names. We run two searches in parallel:

1. Semantic Search: Using cosine distance on embeddings.
2. Trigram Search: Using pg_trgm for fuzzy string matching on table and column names.

To combine these results without arbitrary weight scaling, we use Reciprocal Rank Fusion (RRF). The formula for the RRF score of a document $d$ is:

RRFScore(d) = \sum_{r \in R} \frac{1}{k + r(d)}

Where $R$ is the set of rankers and $k$ is a constant (usually 60). This ensures that a table appearing high in either list gets a significant boost.

Step 2: The Trust-Weighted Join Graph

This is the "secret sauce." Instead of letting the LLM decide how to join tables, we provide it with a pre-validated path. We model the entire warehouse as a directed graph where nodes are tables and edges are allowed relationships.

Edge Metadata and Risk Penalties

Each edge in our graph isn't just a connection; it's a weighted relationship based on "Trust Levels":

Implementation with Dijkstra’s Algorithm

When the retrieval step returns a set of candidate tables (e.g., Table A and Table B), we don't just ask the LLM to join them. We run Dijkstra’s algorithm on our graph to find the path with the lowest total penalty.

If the user asks about "storage costs," and our retrieval finds billing_table and storage_config, the graph might find that they can only be joined through a bridge table asset_mapping. We then inject this specific JOIN logic into the LLM prompt as a constraint.

Step 3: Domain Documentation as Code

To make this maintainable, we avoid hardcoding the graph. Instead, we use Markdown-based "Domain Cards." These files live in Git and serve as the source of truth for both the indexer and the developers.

Example storage.md:

---
domain: storage
tables_primary:
  - warehouse.array_ldev_config
  - warehouse.array_iops_stats
---

# How the tables join

- warehouse.array_iops_stats JOIN warehouse.array_ldev_config
  ON serial AND ldev_id
  Grain: 1:N

Pro Tip: Selecting the Right LLM for Text-to-SQL

For complex reasoning like SQL generation, the model's ability to follow strict constraints is vital. While local models are great for privacy, they often lack the "reasoning depth" of top-tier models. We recommend using n1n.ai to benchmark your local model against DeepSeek-V3 or GPT-4o. Often, using a high-tier model via API for the "SQL Planning" phase while using local models for simpler tasks provides the best cost-to-performance ratio.

Handling Cardinality and Grain

A major pitfall in Text-to-SQL is the "Fan-out" problem, where a join incorrectly multiplies rows, leading to wrong sums. In our join graph, we explicitly store the Grain of the table. If the LLM attempts a join that would result in row multiplication, our validator (a separate layer) flags it before execution.

SQL Validation Logic

Before the query hits ClickHouse, we perform a dry run or use an LLM-based validator (like Claude 3.5 Sonnet via n1n.ai) to check:

1. Are all JOINs following the allowed graph paths?
2. Is the aggregation level (GROUP BY) consistent with the table grains?
3. Does the query include mandatory filters (e.g., load_date) to prevent full table scans?

Conclusion

Stopping an LLM from inventing JOINs isn't about writing a better prompt; it's about building a better scaffold. By implementing hybrid retrieval with RRF and a trust-weighted join graph, you turn a chaotic guessing game into a deterministic search process.

For developers looking to implement this at scale, using a robust LLM API aggregator like n1n.ai allows you to swap models instantly to find the one that best understands your specific schema constraints.

Get a free API key at n1n.ai