The semantic layer: why text-to-SQL needs one

Text-to-SQL fails in production for a reason that has nothing to do with the model: nobody agreed what "revenue" means. A semantic layer for text-to-SQL is the fix. It is a machine-readable definition of your metrics, dimensions, joins and time conventions, kept in one place, that the model queries instead of the raw schema. This article explains what it is, why raw-schema text-to-SQL cannot be trusted, how to build a semantic layer incrementally, and what it costs to put one in front of your warehouse.

What a semantic layer is and why text-to-SQL needs it

A semantic layer sits between the database and anything that asks questions of it. It says: "net revenue is the sum of invoice_amount minus credits, from the invoices table joined to customers on customer_id, excluding test accounts, in the customer's currency converted at month-end rate." It says what a "month" is for your fiscal calendar, what "active customer" means, and which table is the source of truth when three tables disagree.

Given a raw schema, a language model has to infer all of that from column names. It will produce plausible SQL that is wrong in ways only an analyst notices. Given a semantic layer, the model's job shrinks to selecting a metric, some dimensions, a filter and a time grain, and a compiler turns that into SQL from definitions that have already been checked. The accuracy difference on real business questions is the difference between a demo and a product, which is why every natural-language data querying build we ship starts with one.

Raw-schema versus semantic-layer generation

The four things a semantic layer must define

Metrics

Each metric has a name, a business description, an aggregation, a base expression and its filters. "Gross margin" is not a column; it is revenue minus cost of goods, both of which are themselves metrics with their own exclusions. The layer should support derived metrics and ratios so the model never has to compose arithmetic itself.

Dimensions and joins

Dimensions are the things you slice by: region, product line, plan, sales rep. Each is attached to an entity, and joins between entities are declared with their cardinality. A declared many-to-one join lets the compiler avoid the fan-out that inflates sums, the single most common silent error in hand-written and model-written SQL alike.

Time

Which date matters for a metric (order date, ship date, invoice date), the fiscal calendar, the time zone, and the grains that make sense. "Last quarter" must resolve the same way for the copilot as for the CFO's spreadsheet. Comparisons such as "versus the same period last year" should be a declared operation on the time spine, not arithmetic the model improvises.

Access

Which roles may see which metrics and dimensions, and which row filters apply to each role. Sales managers see their region; finance sees everything; a partner sees their own accounts. This belongs in the layer so it is enforced at compile time for every query, as described in row-level security for AI analytics.

Tools: dbt, Cube, LookML and a YAML file

You do not need a new platform to have a semantic layer. If you already model in dbt, the dbt Semantic Layer and MetricFlow give you metrics, entities and time spines in the same repository as your models, which is the right place for them. Cube and Looker's LookML solve the same problem with different trade-offs around caching and BI integration. For a smaller warehouse, a reviewed YAML file of metrics and joins, compiled by a few hundred lines of code, is enough to start and can be migrated later. The choice matters less than the discipline: definitions in version control, reviewed by the people who own the numbers.

How the model uses the layer

The prompt does not contain the schema. It contains the catalogue of metrics and dimensions, with descriptions and synonyms ("turnover" means revenue; "AOV" means average order value), and a small number of worked examples. The model produces a structured request, not SQL: metric, dimensions, filters, grain, ordering, limit. The compiler validates that request against the layer, rejects anything undefined, applies the user's row filters, and generates SQL. The SQL is shown to the user along with the plain-English reading of the request. If the request cannot be expressed in the layer, the model says so and suggests the nearest metric, which is far safer than a creative join.

Building it incrementally

The mistake is to try to model the whole warehouse. Start from the questions. Collect a golden question set of one to three hundred real questions with verified answers, and define only the metrics and dimensions those questions need. That is typically twenty to forty metrics and a dozen dimensions for a first release, covering most of what people actually ask. Every unanswerable question in production becomes a candidate definition for the next sprint. The layer grows with demand and stays reviewed.

A worked example

A subscription software company had three definitions of monthly recurring revenue: one in the billing system, one in the finance model and one in the BI tool. A text-to-SQL pilot over the raw warehouse produced a fourth. The fix was not a better prompt. We built a semantic layer in their existing dbt project covering MRR, churn, expansion and the dimensions finance actually reported on, with the finance lead approving each definition in a pull request. The copilot was rebuilt to select from the layer rather than write SQL, and the golden set was rerun. The remaining failures were almost entirely questions the layer did not yet cover, which is the right kind of failure: visible and fixable. The BI dashboards were then pointed at the same definitions, so the copilot and the board pack finally agreed. This is the shape of most natural-language data querying engagements we take on.

Team and timeline

A semantic layer plus text-to-SQL copilot is an analytics engineer, an AI engineer and a data owner on your side, over six to eight weeks. The first two weeks build the golden set and define the first metrics with finance; weeks three to five build the compiler, the model integration and the access rules; the last weeks run the copilot in shadow mode beside the analyst team and tune against the set. Pricing for natural-language data querying starts at $12,500 or ₹8L, and the pricing page lists the programs; a three-week ProofRun is the right first step if you want the accuracy number on your own data before committing. Ongoing definition changes are handled under a Care Plan.

Before you start: a checklist

• List the ten metrics the business argues about and who owns each definition
• Collect 100–300 real questions with answers verified by an analyst
• Decide where definitions live: dbt, Cube, LookML or a reviewed YAML file
• Declare joins with cardinality; find the fan-out risks before the model does
• Write down the fiscal calendar, time zone and which date each metric uses
• Map roles to metric visibility and row filters
• Agree that dashboards will move to the same definitions
• Plan a review process: a pull request per definition, approved by the owner

Glossary

• Semantic layer: a governed definition of metrics, dimensions, joins and time that sits between data and consumers
• Metric: a named, aggregated business number with its filters, such as net revenue
• Dimension: an attribute to group or filter by, such as region or plan
• Fan-out: row multiplication from a join that inflates sums; prevented by declared cardinality
• Time spine: a calendar table that makes period comparisons consistent
• Compiler: the component that turns a validated metric request into SQL

Questions clients ask

• Will the semantic layer slow queries down? No. The compiler emits ordinary SQL against your warehouse; caching in tools like Cube can make it faster than ad-hoc queries.
• What if analysts still want raw SQL? They keep it. The layer governs what the copilot and dashboards produce; analysts can bypass it with their own credentials and their own accountability.
• Can the model add a metric on the fly? It should not. A missing metric becomes a proposal for the data owner to review, not a query the model invents.
• Does this work across two databases? Yes, if the layer knows which entity lives where; federating within one question is harder and usually deferred.
• How do we keep it current? Definitions live in version control with an owner per metric, and every change reruns the golden set before merge.

Related reading

Read text-to-SQL accuracy: what 95 percent really means, ask your database: building a safe natural-language analyst and natural-language reporting for the product layer on top.

Define the numbers once, let the model choose among them, and text-to-SQL becomes something finance will sign off.