How we stopped a local LLM from inventing JOINs across a 900-table schema

Вадим Мамаев — Mon, 27 Jul 2026 14:08:00 +0000

Practical notes from building an internal text-to-SQL agent. Everything runs on-prem: a local model behind a LiteLLM proxy, bge-m3 embeddings, Postgres with pgvector for retrieval, and ClickHouse as the target warehouse.

Disclosure: English is not my native language. I wrote the original draft in Russian and used an LLM to help edit and translate it. The system, implementation details, and conclusions described below are my own.

TL;DR

Reliable text-to-SQL over a large schema is not a prompting problem alone.
Finding the right tables and joining them safely are different problems, so we separated them.
We retrieve tables with vector search plus pg_trgm, fused with Reciprocal Rank Fusion.
We store known relationships in a trust-weighted join graph and choose an allowed path by minimizing an internal risk penalty.

The problem

We have an internal agent that answers questions against a large ClickHouse warehouse with roughly 900 tables. A user asks in Russian, “show me unused storage volumes” or “what does this system cost,” and the agent is expected to find the data, write SQL, run it, and return an answer.

The entire pipeline stays inside our network. Questions, schema, and data never leave the perimeter.

This looks easy in a demo: connect an LLM to the database, put the schema in the prompt, and let it work. On a real warehouse, that setup breaks in two predictable places:

The model selects the wrong tables.
Even when it finds the right ones, it does not know how to join them safely.

The second failure mode hurt more. The agent would start probing the schema: DESCRIBE one table, SELECT from another, retry with a neighboring column, then change one WHERE clause and try again. A single question could generate dozens of near-identical queries before timing out.

We were not paying a cloud token bill—the model is local—but we were paying in latency, occupied GPU time, and pointless load on ClickHouse.

The key lesson was simple:

“Which tables do I need?” and “How should I join them?” are two different kinds of knowledge. They should not be buried in one giant prompt.

The pipeline now looks like this:

By “correct SQL,” I do not just mean SQL that parses. It must only touch allowed tables and columns, answer the actual question, and avoid corrupting the result through a wrong join or mismatched grain.

This post focuses on table selection and joins. SQL validation and safe execution are a separate layer—and probably a separate post.

Keeping schema knowledge in Markdown

The first decision was where to keep what the agent knows about the warehouse.

A mega-prompt was hard to maintain, review, and diff. We also did not want to invent a custom configuration language. We settled on plain Markdown, one file per domain.

A simplified example:

---
domain: storage
keywords_ru: [СХД, том, LDEV, RAID-группа, пул, задержка]
keywords_en: [storage, volume, ldev, raid group, pool, latency]
question_examples:
  - "show unused volumes by array"
  - "which volume is growing fastest"
tables_primary:
  - warehouse.array_ldev_config
  - warehouse.array_iops_stats
---

# What the fields mean
...

# How the tables join
...

# Easy ways to get this domain wrong
...

The indexer compiles each domain file into small table cards:

Table card: warehouse.array_ldev_config

Domain: storage

Synonyms: СХД, том, storage, volume, LDEV

Purpose: storage volume configuration

Keys: serial, ldev_id

Grain: one row per volume

Join: stats → config on (serial, ldev_id), N:1

Constraint: filter stats to the relevant load_date

The format works for very ordinary reasons:

a domain expert can fix a description without changing code;
every edit is a normal Git diff;
adding a domain means adding a file;
hashing lets us re-embed only cards that changed.

One small addition made a noticeable difference. Users ask questions in Russian, while table and column names are in English. Before embedding a card, we explicitly prepend Russian and English synonyms.

bge-m3 is multilingual, but multilingual embeddings alone were not enough for our corpus. The Russian acronym “СХД,” for example, does not necessarily map cleanly to a table literally named array_ldev_config. Explicit bridges such as “СХД ↔ storage array” improved retrieval recall in practice.

Each card is embedded with bge-m3 and stored in Postgres with pgvector. If the Markdown hash has not changed, we do not recompute the embedding.

Finding tables: two searches and RRF

Vector search alone was not enough.

It was good at meaning, but it could blur exact identifiers such as serial numbers, ticket codes, and literal column names. We therefore run two searches in parallel:

vector search over embeddings for semantic similarity;
trigram search with pg_trgm for exact and near-exact strings.

Their raw scores are not directly comparable. Cosine distance and trigram similarity live on different scales, so adding them is arbitrary. Instead, we use Reciprocal Rank Fusion (RRF): discard the raw scores and combine the ranks.

A document benefits from ranking highly in either list and gets an extra boost when it performs well in both.

We use k = 60, the value the authors fixed during a pilot investigation in the original RRF paper. We have not tuned it for our corpus, so I would not claim it is optimal. Plain RRF has usually been sufficient for us. An LLM reranker can be added on top, but it is disabled by default because the gain has not yet justified the extra latency.

At this point, we have a list of relevant tables. We still have not answered the more dangerous question: how should they be joined?

The join graph

We model the schema as a graph:

nodes are tables;
edges are allowed relationships;
edge metadata contains keys, direction, cardinality, grain, and temporal conditions;
edge weight is an internal risk penalty.

Lower weight means we have more reason to trust the relationship:

Weight	Level	Basis
1	confirmed	a foreign key or a verified data-catalog relationship
2	validated on data	the join was checked and has appeared in successful queries
3	unconfirmed	key names and types match, but the semantics are unverified
4	weak hypothesis	the relationship is inferred from meaning
5	model guess	the relationship has not been verified

These are not probabilities or calibrated confidence scores. They are engineering penalties.

Because lower means safer, we can look for a suitable chain with a standard shortest-path algorithm:

Path A → D → B wins because it has the lower total penalty.

We use Dijkstra, cap the maximum weight of any individual edge, and limit the number of hops. This is a heuristic, not a proof of correctness. The minimum-penalty path is simply the path our system has the most reason to prefer.

Join keys are not enough

An edge that only says table_a.id = table_b.id is not safe.

For every trusted relationship, we also want to know:

cardinality: 1:1, 1:N, or N:M;
direction;
the grain of both tables;
temporal rules such as load_date or valid_from/valid_to;
the risk of row multiplication.

Otherwise, a syntactically valid join can fan rows out and silently corrupt every downstream SUM.

A non-empty result does not prove that a join is correct. You can join two tables incorrectly and still get millions of rows. Successful execution is therefore only one signal. High-trust edges also need confirmed semantics, cardinality checks, and controls for unexpected row amplification.

The inverse is also true: an empty INNER JOIN does not prove that the relationship is fake. Filters, mismatched time windows, or incomplete data can all produce an empty result. We treat it as a reason to lower confidence and inspect the edge, not as an automatic verdict.

A background job reads the audit log and records joins that appear in successfully executed queries. This is only a usage signal: “the query ran” is not the same as “the answer was semantically correct.”

What the model receives

When retrieval returns two or more candidate tables, we find an allowed path in the graph and add it to the model context.

The wording depends on the trust level:

Confirmed relationship:
  Use warehouse.array_iops_stats AS s
  JOIN warehouse.array_ldev_config AS c
    ON s.serial = c.serial AND s.ldev_id = c.ldev_id
  Cardinality: many-to-one.
  Select the relevant load_date before aggregating.

Medium confidence:
  Likely relationship: (...)
  Validate cardinality and row amplification first.

Weak hypothesis:
  Do not present it to the model as a recommended join.

This is what reduced the blind column probing. The model no longer receives the whole schema plus “figure it out.” It gets a small set of relevant tables and explicit constraints for joining them.

What improved—and what we have not proved yet

In one illustrative storage query, the agent used to reach 51 SQL attempts. After we started pinning joins from the graph, it dropped to 18. Moving schema knowledge out of the system prompt and into cards and edges also reduced the prompt by roughly 70%.

Those are useful observations, not a benchmark. One before/after example does not answer the questions that actually matter:

What percentage of generated queries execute?
What percentage of answers are semantically correct?
How good is table retrieval (Recall@k)?
What are the median and p95 SQL-attempt counts?
What is the end-to-end latency?
How often does a join run successfully but corrupt an aggregation?

We are still building that evaluation harness. The honest claim today is narrower: this approach substantially reduced probing in our working scenarios and made failures easier to diagnose, but we still need a fixed evaluation set to measure the overall quality.

Why two systems are easier to operate than one prompt

Table retrieval and join selection do not just require different methods; they fail differently.

If the agent misses a table, we inspect its card, synonyms, and retrieval results. If it finds the right tables but joins them incorrectly, we inspect the edge, cardinality, and grain. We do not have to swap the model or rewrite the entire system prompt after every failure.

Most of the system is also just data:

domain descriptions live in Markdown under Git;
cards and embeddings live in Postgres/pgvector;
relationships are Postgres rows editable through a small UI;
adding a domain means adding descriptions and a handful of verified edges, without fine-tuning the model.

Numerical answers should come only from SQL the agent actually executed, and the query should be retained for inspection. That does not make an answer automatically true, but it does make it reproducible and auditable.

Where this approach falls short

The main limitation is coverage.

If a table has not been described, retrieval may miss it. If a relationship is absent from the graph, the system cannot safely recommend that join. A new domain still requires expert work: document the tables, record their grain, and verify the main relationships.

There are other failure modes:

heuristic weights do not replace semantic validation;
old successful queries can reinforce old mistakes;
schema or business-logic changes can make an edge stale;
a long chain of individually valid joins can still distort the result;
we have not tuned RRF's k = 60 for our corpus;
the LLM reranker is disabled by default.

The graph does not eliminate metadata management. It turns expert knowledge from prose “somewhere in the wiki” into a structure the agent can use while generating SQL.

The part I would reuse

I would not start by copying a particular model or writing a larger prompt.

I would copy the separation of responsibilities:

Retrieve the smallest useful set of tables.
Independently find an allowed, low-risk way to join them.
Give the model only that constrained slice of the schema.
Retain the executed SQL so the answer can be inspected.

For us, this was much more practical than expecting the model to reconstruct the warehouse from column names.

If there is interest, I can follow up with the edge-table schema, the SQL for RRF, or the evaluation harness. I would also be curious to hear how others handle cardinality and stale joins in large warehouses.

DEV Community: Вадим Мамаев