Why LLMs invent joins (and how to stop them)

Ask an LLM to answer a business question from a database schema and it will often invent a join. Not always a wrong join - sometimes a plausible one. Plausible is the problem. Agents optimize for fluent SQL, not for the definition your finance team certified last quarter.

Why inventing joins feels natural to models

Language models are trained on mountains of SQL examples. Schemas look like puzzles: table names, column names, foreign-key-shaped columns. The model pattern-matches. orders.customer_id meets customers.id. Revenue looks like SUM(amount). Filters look like WHERE status = 'paid'.

None of that encoding includes your grain rules, soft deletes, late-arriving facts, or the fact that marketing and finance disagree on what “active customer” means. The model fills gaps with statistical guesses. Humans review. Agents often do not - or they review with another model that shares the same bias.

Schema-only context also invites over-joining. More tables feel more complete. The agent stitches facts through dimensions it was never meant to traverse, doubles rows, and reports a number that no dashboard would show.

Why “just give it better docs” is not enough

Documentation helps humans. For agents, prose is soft. A paragraph that says “always join through the dim_customer bridge” competes with every other token in the prompt. Tools that accept free-form SQL invite the model to ignore the paragraph and write what looks right.

Paste a catalog of tables into the context window and you have not governed meaning. You have given the model more surface area to invent from.

Governed metrics remove join invention from the loop

The fix is not smarter prompting. The fix is a different request shape. Instead of “write SQL against these tables,” the agent asks for a certified metric by name, with dimensions and filters the metric allows.

datahashi publishes metrics from a YAML model as an immutable artifact. Humans and agents use the same metric without hallucinated SQL. Consumers talk to REST, the console, or MCP with the same request shape. MCP exposes list_catalog, describe_metric, and run_semantic_query. None of those tools accept or return SQL. The agent discovers what exists, reads the contract, and runs a semantic query. Joins stay inside the governed definition, not in the model’s improvisation.

The warehouse still holds transforms. datahashi is a thin serving layer on top - not a second place to reimplement business logic. Metering exists so you can audit which consumers asked for which metrics.

What changes in the agent loop

Before: schema dump, natural-language question, generated SQL, hope.

After: list catalog, describe the metric you need, run a semantic query with named dimensions and filters, return the result.

That loop is boring on purpose. Boring is how two consumers return the same number. A dashboard panel and an agent answer can both target one metric request. You can prove alignment instead of debating whose SQL was closer.

What not to wait for

You do not need every serving optimization shipped before you stop join invention. Rollups and p99-style latency targets are serving concerns; do not treat them as prerequisites for governance. The prerequisite is a certified metric contract that agents cannot bypass with creative SQL.

If your agents still see raw schemas as the primary interface, they will keep inventing joins. Point them at named metrics instead, and invention has nowhere to go.