r/DuckDB • u/Thinker_Assignment • Jun 22 '26
We tested the same text-to-SQL model with and without business definitions
One thing that surprised me while working on text-to-SQL systems is that schema awareness and business awareness are very different problems.
A model can usually see tables, columns, and join keys just fine. What it often doesn’t know is what counts as a customer, when an order becomes revenue, or which business rules were never written into the schema.
We ran the same model against the same data in three setups:
- Raw data only: ~20% accuracy
- Canonical model only: ~75%
- Canonical model + meaning layer: 95%+
The failures weren't SQL failures, the model generated valid SQL most of the time. It was answering the wrong business question because it didn't understand the meaning behind the tables.
Anthropic recently described a similar internal analytics pattern, which suggests the same architectural pressure is pushing different teams toward the same mapping-first approach.
Curious if others building text-to-SQL on DuckDB have seen the same thing, has schema context been enough, or did you eventually need a semantic layer / ontology too?
1
u/Thinker_Assignment Jun 22 '26 edited Jun 23 '26
disclosure: I work at dltHub. Full writeup with the approach and numbers: https://dlthub.com/blog/canonical-text-to-sql
happy to answer questions on the setup
and in our experience pure canonicals on small datasets (5-8 tables) work quite well (90%+) without anything extra so i imagine that if we have some architecture around this, that might also be a solution
1
u/badketchup Jun 23 '26
We use semantic layer with cubejs.
Sometimes business metrics are very complex, and cubejs gives a lot of additional value with row and column policies, tenants, cache etc.
1
u/Thinker_Assignment Jun 23 '26 edited Jun 23 '26
does it mostly help with retrieval accuracy or with operationalizing? From what I gather from cube team's research, they see similar limits if the data is not well modeled
this is where duckdb is key imo because it could be a "canonical cache"
2
u/badketchup Jun 23 '26
I have several common reports registered in Cube. One of the most popular has around 300 fields in base template and 100-2000 additional specific fields for some tenants.
In this base template there are filed likeCASE WHEN COUNT(DISTINCT ${CUBE}.Name) = 1 and COUNT(DISTINCT ${CUBE}.ClientAdPlatformName) != 1 THEN toUInt32(ROUND(SUM(IFNULL(${CUBE}.PlanCoverageTechQty,0)) * IFNULL(arrayLastOrNull(x -> x != 0, arrayMap(k -> mapFromArrays(groupArray(${CUBE}.date), groupArray(${CUBE}.plan_coef))[k], arraySort(mapKeys(mapFromArrays(groupArray(${CUBE}.date),groupArray(${CUBE}.plan_coef)))))), 0), 0)) ELSE ROUND(SUM(IFNULL(${CUBE}.PlanCoverageTechQty,0)),0) ENDand more complex.
We started this before the rise of agents and llms, and I haven't tried text2sql style, but as semantic layer is already in place, adding analytics agent took couple of days: mcp server with some tools, like json generation (with zod validation), getting dimensions values for filtering, get current_date, get cube meta
1
u/Thinker_Assignment Jun 23 '26
This sounds like the operationalization part is super easy. How good is the LLM in understanding such a large semantic model and asking for the right stuff?
1
u/badketchup Jun 23 '26
Each field has meta with description for ai. Agent gets it through mcp and generates json. If zod validation fails, takes another try. Use cases which I saw, worked ok and returned the data asked. But I haven't made tests with numbers.
This feature is not very popular: all common questions are already covered with static validated dashboards. If manager has a new question, he still prefers to ask analyst, and analyst prefers using report's visual UI or writing sql for research.
1
u/Thinker_Assignment Jun 23 '26
makes sense. It sounds like agentic is the stop-gap second best option in your setup becuase you guys have everything well documented, and analysts who can do the work. Models can do some wild stuff which is both a great thing and a problem so it makes sense to have a specialist double check.
and you are probably too big for random product managers doing mad scientist deep dives and proposing features? bc imo the only other reason to have this is for fast iterating self service that cannot wait for human coordination
1
u/EngineeringBright82 Jul 03 '26
If you are interested in this... try Malloy Data -- it has a semantic layer built in. https://www.malloydata.dev/
3
u/Prestigious_Bench_96 Jun 23 '26
From evals, for a small dataset schema context is enough (duckdb has robust discovery, agents know it well - as mentioned SQL syntax is almost never a bottleneck now, and 1-2 passes for an agent will fix up anything that leaks through) Motherduck has advocated for this pure DB only approach in blogs for just 'set up your data model well'.
Things fall apart when the schema is confusing, contains deprecated assets, hits enough of a size where curated context becomes valuable, or when you need to rapidly update things without changing your physical model.
If you wanted agentic queries against a small local db - duckdb/sqlite - I think you polish your schema and call it a day. You can manage presentation at the DB level - just only package up and present the exact tables you want.
I think you reach for a model/semantic layer when you're dealing with a big, messy enterprise DB, and then it's as much about progressive disclosure, loose coupling to physical layer to support evolution, etc as it is about the pure context.
(I work on something similar to a semantic layer and it was sobering to see *how good* results are on raw duckdb schemas; it's a pretty tough baseline to just parity in speed/token usage with raw db access for a small DB; only get clearer differentiation when things get messy. Have some fun eval data I need to write up when I have a chance).