How PyDough Reached 100% Accuracy on dbt’s Semantic Layer Benchmark

dbt Labs recently published an 11-question benchmark comparing its Semantic Layer with direct text-to-SQL over the ACME Insurance schema. Their best result was 98.2% accuracy using a modeled Semantic Layer. Direct text-to-SQL over the raw schema reached 64.5%.

We ran the same 11 questions through our PyDough-based pipeline and scored the results with dbt's own comparator. Across 20 independent runs per question, our pipeline answered 220 out of 220 correctly: 100% accuracy.

We agree with the underlying conclusion of dbt's benchmark: raw DDL is not enough for reliable AI analytics. But hand-modeling that information is not the only way to provide it.

Our pipeline profiles the schema and data, captures what it learns in a PyDough metadata graph, and has the LLM generate PyDough rather than SQL. Relationships such as joins are defined once in that graph instead of being reconstructed by the model for every question.

The result is an interesting alternative: derive much of the semantic context automatically, encode it in a structured and executable representation, and remove low-level SQL decisions from the LLM's job.

In this post, we'll walk through the benchmark, how the pipeline works, and why moving more of the problem into structured metadata and an executable query layer produced more accurate results.

The Benchmark

We followed the same basic protocol: the same 11 questions, 20 independent runs per question, and dbt's comparator as the definition of a correct answer.

There was one component we could not reproduce directly. dbt's harness executes both the gold query and generated query through an ADBC Flight SQL client connected to a dbt platform Semantic Layer environment. That requires an account, service token, and environment ID, and there is no local execution path.

So we replaced the execution layer. We built the ACME schema as a local SQLite database from dbt's DDL and seed CSVs, then swapped the Flight SQL client for a read-only SQLite executor.

Four of the 11 questions need the relationship from Agreement_Party_Role to Policy. That relationship normally passes through the missing Agreement table, so the DDL cannot describe the full path. Our pipeline has to recover it from the schema, column names, and data.

Results

Across all 220 runs, the PyDough pipeline returned the correct result.

A standard deviation of zero deserves some scrutiny. A caching bug could produce exactly that result. That is not what happened here. Each of the 20 iterations ran in its own artifact directory with its own response cache, made fresh API calls, and recorded zero cache hits. The generated outputs also varied. Across the benchmark, the system produced an average of 11 distinct queries per question, with different aliases, join orders, and, in some cases, different traversal paths. Only the two SELECT COUNT(*) questions were byte-identical across all 20 runs.

So why does this work? To understand that, it helps to look at what makes ACME difficult in the first place.

Why the model generates PyDough instead of SQL

The LLM in our pipeline does not write SQL. It writes PyDough.

PyDough is our open-source Python DSL for analytics. Instead of expressing an answer directly in terms of physical tables and joins, a query operates against a logical graph of collections, properties, and relationships.

The PyDough compiler then lowers that expression into relational operations and ultimately SQL in the dialect required by the target engine.

For example, a premium belongs to a policy, and a policy has parties. In PyDough, the generator traverses those relationships as properties. It does not reconstruct the corresponding JOIN ... ON clauses from the database schema.

That changes the generation problem in a few important ways.

• The model cannot get a join wrong, because it never writes one. Join paths live in the metadata graph, declared once. The single largest error class in text-to-SQL over a normalized schema — a missing table in the middle of a path, a fan-out that silently double-counts, a join key that happens to be the wrong one of two similarly named columns — is not an error the generator is in a position to make. On a schema like ACME that is most of the difficulty.
• The surface area is smaller. No dialect quirks, no identifier quoting rules, no window-function boilerplate, no correlated-subquery patterns to get right. The same PyDough runs against SQLite here and against a warehouse in production; the compiler handles the difference. A smaller output language means fewer ways for a sampled token to be wrong.
• It fails loudly. A property that doesn't exist, or a traversal the graph doesn't permit, is a compile error with a specific message and a location — not a query that runs and returns plausible-looking wrong rows. Some fraction of what an LLM would otherwise emit as silently-wrong SQL becomes a deterministic, checkable failure before anything touches the database. That is the failure mode dbt Labs correctly identify as text-to-SQL's worst property, and moving the query language up a level takes a bite out of it.
• The generated expression is closer to the business question. “Total premiums per policy holder” is a filter, a traversal and an aggregation in PyDough, in roughly that order. The corresponding SQL is a four-table join with a GROUP BY whose grouping key sits three hops from the amount being summed. Shorter distance between the English and the code means less for the model to invent.

The benchmark still ultimately executes and scores SQL. The difference is that SQL is compiler output rather than model output. But changing the output language only works if the graph contains the information the model needs. That is where most of the pipeline's work happens.

How the pipeline works

The system deliberately does as much work as possible once per database rather than rediscovering the same information for every question. It is split into an offline metadata-building stage and an online query-generation stage.

Once per database, offline

1. Build the initial PyDough metadata graph: collections, scalar properties, and relationships derived from the schema.
2. Profile every column using ordinary SQL: null counts, distinct counts, min/max values, common values, value shapes, and lengths.
3. Render those statistics into an English description. No LLM is involved in the profiling itself.
4. Summarize each column with one inexpensive LLM call using its profile and table context. The system produces a short description for retrieval and a longer description for generation.
5. Attach those descriptions to the metadata graph.
6. Index values so literals in a user's question can be mapped back to the properties that actually contain them.

Per question, online

~11 calls · 1.5¢

1. Perform schema linking by generating a query five times under five different metadata renderings and taking the union of the properties referenced.
2. Generate three final candidates at different temperatures.
3. Compile and execute each candidate.
4. Group candidates by result set.
5. When they agree, use the consensus result.
6. When they disagree, make one judge call using each candidate's query, vote count, and a preview of its returned rows.

The asymmetry is intentional. A database schema is relatively fixed. The questions users may ask against it are effectively unbounded. Anything the system can learn about the database once can be amortized across every future question.

Prompt instructions do not have that property. They have to be supplied and interpreted on every request.

That is also why the prompt itself is deliberately uninteresting. The system prompt is roughly 30 lines of output conventions: use only properties the graph defines, don't add columns the question didn't ask for, prefer a top-k operation over a max subquery, and similar rules.

There is no chain-of-thought scaffolding, self-critique loop, or tool-calling agent exploring the database at question time. The core of the system is the metadata it has available before generation starts.

Semantics matter, but hand-modeling isn't the only option

dbt's benchmark makes a convincing case that raw text-to-SQL over DDL is not enough. Accurate analytical queries depend on information the physical schema often doesn't provide: how tables relate, what columns and values mean, and how business concepts map onto the underlying data.

In our pipeline, much of that context is derived from the existing schema and data, captured in a PyDough metadata graph, and reused across questions. The LLM generates against that graph rather than reconstructing joins and column meaning from raw DDL on every request. The same graph is then used to compile the query to SQL.

On this benchmark, that approach produced 100% accuracy across 220 runs, without the additional hand-built models used in dbt's modeled configuration.

The result reinforces dbt's broader point that semantic context matters, while showing a different way to provide it: derive more of that context from the underlying data and make it part of the query system itself.

You can try the same approach on your own data. PyDough-CE is fully open source and available on GitHub. Check out the repository, run the quick start, and let us know how it performs.

‍