Blog /
Text-to-SQL vs. Semantic Layers (dbt / MetricFlow / Cube): Do You Need Both for AI Analytics?
Published . Written for nlqueries-core 0.4.
semantic-layer dbt text-to-sql
Picture the first week after an AI assistant goes live on the analytics database. Someone in finance asks it for October revenue for one customer tenant, and it answers 40,000. The finance dashboard says 30,000. Both numbers came from SQL, both ran against the same tables, and both are labelled "revenue." Now you've got a meeting.
The assistant did nothing exotic. It joined to the order-items table because the question mentioned products somewhere upstream, and every order with two line items got counted twice. A semantic layer exists to stop exactly this, so it's tempting to conclude that semantic layers make text-to-SQL obsolete, or that a text-to-SQL tool that imports your dbt docs now "has" a semantic layer. Neither is quite right. This post works through where each approach earns its place, what actually happens when you import dbt descriptions into a text-to-SQL tool like NLQueries, and why the router between the two is the piece you can't hand-wave.
The product details come from vendor documentation and source code at pinned versions; the numbers come from small SQLite queries that check arithmetic, not any product's answers.
Two revenue answers, one innocent join
Here's a small fixture that reproduces the disagreement. It's synthetic: three customers across two tenants, six orders, three line items. Order 101 has two items, order 106 is paid but has a NULL amount, one order is cancelled and one falls on November 1.
-- Synthetic fixture (SQLite)
CREATE TABLE customers(id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, region TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
order_date TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE order_items(id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id), sku TEXT);
INSERT INTO customers VALUES (1,'A','East'), (2,'A','West'), (3,'B','North');
INSERT INTO orders VALUES
(101,1,'2026-10-01',10000,'PAID'), (102,1,'2026-10-02',5000,'CANCELLED'),
(103,2,'2026-10-31',20000,'PAID'), (104,3,'2026-10-02',70000,'PAID'),
(105,1,'2026-11-01',40000,'PAID'), (106,2,'2026-10-15',NULL,'PAID');
INSERT INTO order_items VALUES (1,101,'a'), (2,101,'b'), (3,103,'c');
-- Reference answer: tenant A, paid orders, October 2026
SELECT SUM(o.amount_cents) AS revenue_cents, COUNT(*) AS paid_order_count
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';
The question is "For tenant A, what were paid-order revenue and paid-order count in October 2026?" Against this fixture the reference query returns 30,000 cents across 3 orders. The NULL-amount order counts as an order but adds nothing to revenue. That's a business decision somebody had to make; SQL didn't make it for you.
Now the plausible-looking mistakes. Each variant below was run against the fixture with SQLite 3.45.3:
| Variant of the query | revenue_cents | order count |
|---|---|---|
| Reference (order grain) | 30000 | 3 |
Inner join to order_items |
40000 | 3 |
Left join to order_items |
40000 | 4 |
Left join, COUNT(DISTINCT o.id) |
40000 | 3 |
| Status filter forgotten | 35000 | 4 |
COUNT(amount_cents) instead of COUNT(*) |
30000 | 2 |
The inner-join row is the nasty one. Revenue is inflated by the duplicated order, yet the count comes out right by accident, because the NULL-amount order has no items and falls out of the join. Anyone sanity-checking "3 orders" against the dashboard would wave it through. The left join keeps order 106, so the count climbs to 4 while the sum stays wrong. Reaching for DISTINCT repairs the count and leaves the 40,000 untouched, because the sum is still taken over item rows. Forgetting the status filter pulls in the cancelled 5,000-cent order. And counting amount_cents instead of rows quietly drops the paid order that has no amount, so revenue is right and the order count isn't.
None of these queries is syntactically wrong, and none would trip a schema validator. They're wrong about grain (the level of detail one row represents), filters and NULL handling, which is business meaning, not SQL. Every column in every variant is real. The schema alone can't tell you which combination finance means.
What a semantic layer actually does
A semantic layer moves that meaning out of individual queries and into a shared definition. In dbt's version, the dbt Semantic Layer centralizes metric definitions above your dbt models, and MetricFlow constructs the SQL for a metric request from semantic models and metric definitions. You define "paid revenue" once, at order grain, with its filter. Every consumer that asks for that metric gets SQL built from the same definition rather than SQL someone wrote fresh.
Cube takes a similar stance from a different direction. You model cubes with measures, dimensions and joins, and Cube generates the query from that model when someone asks for a measure sliced by a dimension. Either way, the description of a metric and the execution of a metric live in the same place, and that's the property a description alone can't give you.
Two practical details matter before you plan around either one. First, dbt's documentation separates the code license from access to the hosted Semantic Layer. Current MetricFlow docs describe Apache 2.0 distribution, but the hosted Semantic Layer has its own plan requirements (dbt Semantic Layer, About MetricFlow). Don't carry older licensing assumptions forward without rechecking. Second, the metric specification is version-dependent. The live docs cover dbt 1.12+ and v2, so any metric YAML you copy from a blog (including this one, which deliberately doesn't include any) needs to match the version you actually run.
What a semantic layer doesn't do is answer questions nobody modeled. If operations wants to know which West-region customers had a paid order with no line items last month, and there's no metric or dimension for that, the governed path has nothing to offer. That isn't a flaw. It's the price of governance.
Where text-to-SQL still earns its keep
That operations question is the case text-to-SQL is built for. A natural-language-to-SQL (NL2SQL) tool reads the schema and whatever descriptive context you give it, then writes SQL for a question that wasn't anticipated. Coverage is the strength. Consistency is the weakness, as the table above shows: the model reconstructs meaning on every request, and it can reconstruct it slightly differently each time.
The fixture makes that concrete. A model that sees orders, order_items and a status column has everything it needs to write any of the six queries above, and nothing in the schema marks one of them as the approved answer. Good descriptions narrow the odds. A column note saying amounts exclude cancelled orders, or a model note saying revenue is measured at order grain, gives the model a real chance of choosing the right filter and the right grain. But a hint is still a hint. The model can follow it on Monday and ignore it on Tuesday, and nobody downstream can tell which happened unless the SQL is visible.
So the two approaches have different responsibilities, not competing ones. The semantic layer is the authority on what a certified metric means and how its SQL is built. The NL2SQL tool covers the long tail of questions outside that set, ideally with enough context to make sensible choices and enough transparency for a reviewer to see what it did. Most of the interesting engineering is in the seam between them.
What NLQueries takes from your dbt project, and what it leaves behind
This is where a lot of confusion lives, so precision helps. NLQueries Core 0.4.1 has a CLI command that reads dbt artifacts and merges their descriptions into an NLQueries knowledge base (KB), the YAML file of schema context the tool uses when generating SQL (CLI reference). The documented shape is:
# Documented form for NLQueries Core 0.4.1 -- not executed here.
# <dbt-target-directory> is the folder holding target/manifest.json from `dbt parse` or a build.
nlqueries import-dbt <dbt-target-directory> --kb <original-kb.yaml> --output <merged-kb.yaml>
Writing to a separate output file is the sensible habit, because you'll want to diff the result before promoting it. It also means the merge doesn't take effect by itself: the tool keeps reading the KB it already uses until you review the merged file and deliberately promote it. The manifest itself is the file dbt produces when it parses a project (manifest.json reference). Its schema version isn't the same thing as your dbt version. The current reference maps manifest v12 to dbt v2 and to 1.8–1.11, so record both.
Reading the importer source tells you what actually transfers:
- Model and column descriptions do. They come from model nodes, matched to physical relations through aliases and schema-qualified names, and they merge with a precedence of manual descriptions over dbt descriptions over schema comments over generated text. Hand-written or existing non-empty KB text is protected on import. A re-import can still update fields that came from schema comments or generated text, not only earlier dbt text, so diff every run.
- Sources and seeds don't. Only model nodes are covered. If revenue logic lives in a seed or on a source table, it won't come across.
- Long descriptions get trimmed. Each one is capped at 2,000 characters.
- Metrics become something to read, not something to run. The importer can surface dbt metric definitions, but derived metric expressions are review-only. They aren't written into the Core KB as executable MetricFlow plans.
That last point is the one to hold onto. After an import, NLQueries knows that orders.amount_cents is "order total in cents, excluding cancelled orders" if your dbt docs say so, and that context should help the model choose the right filter. It doesn't know how to execute your paid_revenue metric the way MetricFlow would, and it doesn't call MetricFlow or Cube on your behalf. Description import improves the inputs to ad-hoc SQL. It doesn't turn ad-hoc SQL into governed SQL. This article doesn't measure how much imported descriptions change answer quality, and you shouldn't assume a number until you've run it on your own questions.
One failure mode is worth knowing in advance: malformed artifacts can produce empty results rather than a loud error. If an import "succeeds" and nothing changes in the diff, check that the manifest parsed and that model names actually matched your relations before concluding there was nothing to import. Exported context also needs an explicit refresh (CLI reference), so a description changed in dbt last week isn't in the KB until someone re-runs the import.
Routing between the two without quietly changing the definition
If you want both, something has to decide which path a question takes. The NLQueries Core sources cited here show no API for executing certified metrics, and none of the documentation cited here describes a built-in bridge between NLQueries and a semantic layer, so the router is your application code. It's also the riskiest code in the design, because it's the part that decides whether an answer gets to wear the "certified" label.
The obvious first version is a keyword lookup. Map the phrase "paid revenue" to a metric ID, check whether that phrase appears in the lowercased question, and send anything containing a bare "revenue" or "sales" to a clarifying question. It reads well in a code review and fails in exactly the way that matters. "Unpaid revenue" contains the substring "paid revenue," so a question about money that hasn't been collected lands on the certified paid-revenue metric and comes back with the governed label attached. Split the question on whitespace to look for ambiguous words, and "Revenue?" becomes the token "revenue?", which doesn't match. The clarification branch never fires, and the question falls through to ad-hoc SQL. "Sales, please" does the same thing.
Treat any router like that as a deliberately incomplete lexical prototype. It must not designate an answer as certified: substring matches can select the wrong metric, and punctuation can bypass clarification. Keep the executable router out of the product until a resolver with explicit metric IDs, scope, dimensions, time range and ambiguity handling has passed an adversarial question set. Whether that resolver is a classifier, an explicit metric picker in the UI or a structured-output model call, its output should be a metric ID with every scope pinned, or a request for clarification. Never a best guess.
The adversarial set is the real deliverable here, and it doesn't need to be large to be useful. It should cover:
- paid and unpaid pairs, including negated phrasings, where the negated form must never resolve to the certified paid metric;
- punctuation and casing variants such as "Revenue?" and "sales, please", which must still reach clarification;
- mixed requests that name more than one metric, or a certified metric alongside something unmodeled, in the same sentence;
- tenant and time-scope cases, where the resolver has to fix both explicitly instead of inheriting whatever the question implied.
The pass condition is easy to state and easy to check: an ambiguous request never silently becomes a certified answer. Around that resolver, three rules keep the two paths honest.
First, never fall back silently. If a question names a certified metric and the semantic path fails, return the failure. Don't let the ad-hoc path improvise its own version of "paid revenue" and present it with the same label. That substitution is exactly how the 40,000 answer reaches a finance meeting.
Second, every answer says where it came from: a definition ID such as metric:paid_revenue@v3 on the governed path, the generated SQL on the ad-hoc path, and a visible route label on both. Users can only trust a number when they can tell which definition produced it.
Third, routing isn't access control. Sending a question to the semantic layer doesn't make it authorized, and the tenant filter in the reference query is just the arithmetic for the expected answer, not a security boundary. Both paths need the user's own permissions enforced at the database or semantic-layer level, and you should test a denied table on each route separately.
The NLQueries side of this has some useful properties for the ad-hoc path. Its Python entry points take an explicit execution parameter and return structured results (sync_runner source), and the CLI separates previewing generated SQL from executing it (CLI reference). That's what lets your router show the SQL before anything runs. Keep in mind that a preview step on its own doesn't guarantee zero database reads, so check what your configuration touches.
So, do you need both?
It depends on how many of your questions are about metrics someone has already agreed on.
| Raw-table NL2SQL only | Semantic layer only | Routed coexistence | |
|---|---|---|---|
| Metric consistency | Depends on context and model each time | Strong for modeled metrics | Strong when routing is correct |
| Ad-hoc coverage | Broad | Limited to what's modeled | Broad, labelled as ad hoc |
| Modeling and upkeep | Descriptions and KB refresh | Semantic models and metric specs | Both, plus the router |
| Provenance | Generated SQL | Metric definition | Route plus definition or SQL |
Notice the qualifier in the last column: "strong when routing is correct." Coexistence only inherits the semantic layer's consistency if the router never mislabels an answer, which is why the adversarial set above isn't optional.
If a handful of metrics drive real decisions (board revenue, billing, anything finance reconciles), those belong in a semantic layer, and an assistant should call it rather than reinvent it. If most questions are exploratory and nobody will reconcile them against a certified number, raw NL2SQL with good descriptions may be enough for now. Importing dbt docs into NLQueries is one way to give that path better context, with the limits described above. Teams with an established dbt project often land in the middle, where you get both coverage and consistency at the cost of owning a router and its tests.
If you want a starting weight for comparing these options, try metric consistency at about 35%, ad-hoc coverage at 25%, and modeling work and access/provenance at 20% each. These weights are an editorial proposal, not measurements, so adjust them for your organization.
Your next step is concrete. Take one metric your finance team actually reconciles, define it at order grain in whichever semantic tool you use, and run it alongside your NL2SQL tool on a fixture like the one above, including the item-join and NULL variants. If the two paths don't agree on 30,000 and 3, you've found the definition gap before your users do. Then write ten adversarial questions for that metric, starting with "unpaid revenue" and "Revenue?", and don't put any router in front of users until none of them comes back labelled certified by mistake. Finally, run import-dbt into a separate KB file and read the diff, so you know exactly what context crossed over and what didn't.
References
- dbt Labs. dbt Semantic Layer. Accessed 2026-11-05.
- dbt Labs. About MetricFlow. Accessed 2026-11-05.
- dbt Labs. Manifest JSON file. Accessed 2026-11-05.
- Cube. Getting started — Cube Data Modeling. Accessed 2026-11-05.
- NLQueries. CLI Command Reference, Core 0.4.1. Accessed 2026-11-04.
- NLQueries. nlqueries/knowledge/dbt_importer.py, Core 0.4.1. Accessed 2026-11-05.
- NLQueries. nlqueries/orchestrator/sync_runner.py, Core 0.4.1. Accessed 2026-11-04.
- NLQueries. Documentation (v0.4.1 banner). Accessed 2026-11-05.