Semantic Layers vs. Prompting: Where Business Definitions Should Live

"An active customer is someone who has ordered in the last 90 days."

You can put that sentence in a system prompt. You can also put it in a semantic layer. Both cause the AI to produce the right SQL most of the time. Only one of them is a definition; the other is a suggestion that usually gets followed.

The difference does not show up on day one. It shows up in month four, when someone asks a differently-phrased question, gets 88 days instead of 90, and quotes the number in a meeting.

What "in the prompt" actually means

A system prompt is text prepended to the model's context. The model reads it and generates SQL conditioned on it. This works — genuinely, most of the time. And it fails in four specific ways that share one root cause: the model is not obliged to follow it.

The four failure modes

It drifts with phrasing. "How many active customers?" and "How many customers are still active?" and "Show me our active user count" can produce three different queries. The definition was in context for all three; the generation was conditioned differently by each.

It degrades with context length. A definition on line 40 of a 200-line prompt competes with everything else in that prompt. Add a few more metric definitions and some schema, and adherence to any single instruction weakens. The failure is graceful and silent — which is the worst combination, because the query still runs and still returns a plausible number.

It cannot be reviewed. A change to a prompt definition is a change to prose. Nothing tests it, nothing diffs its behaviour, and nobody can tell from the change alone whether the SQL it produces is now different. Compare that to changing a SQL expression in a version-controlled file: the diff is the change.

It does not transfer. The prompt applies to the AI tool. Your dashboards, scheduled reports and hand-written analyst queries know nothing about it, so "active customer" means one thing in the AI and another everywhere else.

What a semantic layer is, concretely

Strip away the vendor framing and it is: a version-controlled file that maps a business concept to an exact SQL expression, plus a resolver that expands references to it.

Note what that comment block gives you that a prompt line cannot: a grain, an owner, and a changelog. When someone asks in six months why the number moved, the answer is in the file.

The critical property is where the boundary sits:

The model selects. The layer defines. That single sentence is the whole architecture.

Which definitions belong where

Not everything needs a semantic layer entry. Over-building one is a real failure mode — a layer with 300 entries that nobody maintains is worse than 12 that are correct.

Most businesses have somewhere between 10 and 30 definitions that pass this test. That is a tractable amount of work — a week, not a quarter.

The migration path

You do not need a framework to start, and starting without one is usually right.

Stage 1 — a metrics table in the warehouse

The cheapest possible version, and it captures most of the value:

Point the AI tool at semantic.* rather than at raw tables, and a whole class of ambiguity disappears — because the ambiguous columns are no longer reachable.

Stage 2 — column descriptions as the model's documentation

Warehouses store column descriptions, and schema APIs return them. DataLens reads BigQuery's description field when it pulls a table schema, and that description is often the only thing standing between the model and a wrong column choice:

ALTER TABLE analytics.orders
ALTER COLUMN gross_total
SET OPTIONS (description =
  'Order total INCLUDING tax and shipping. Do not use for revenue reporting — use net_revenue.');

That one line, in the warehouse where every tool can read it, is worth more than a paragraph in a prompt that only one tool can see.

Stage 3 — a proper semantic layer

Adopt a framework when you have more than a couple of dozen metrics, dependencies between them, or multiple teams changing them. The signal is the same one as adopting a transformation framework: dependency ordering has become something you have to think about.

What the prompt is still for

None of this makes prompting obsolete. The prompt handles everything that is genuinely about interpretation:

• Disambiguation policy — "when the user says 'sales', prefer net_revenue, and say which you used."
• Table selection guidance — "campaign-level questions use semantic.campaign_daily, not the raw events table."
• Behavioural rules — "always apply a date filter; never generate an unfiltered scan of events."
• Uncertainty handling — "if two metrics could match the question, ask rather than pick."

Notice these are all policies about how to behave, not facts that must be exactly right. That is the correct division: the prompt shapes judgement, the layer fixes values.

Frequently asked questions

Can't I just write a really good prompt?

You can get a long way, and for a small team with a handful of metrics it may genuinely be enough. What you cannot get is verifiability — no test tells you the prompt still produces the same SQL after you edited an unrelated line, and no review catches that it stopped. The prompt is fine until being wrong costs something.

Does a semantic layer make the AI less flexible?

It constrains metric definitions, not questions. The model can still filter, group, join and compose freely around a fixed metric expression — which is where the flexibility people actually want lives. In practice a layer makes exploratory questions more useful, because the answers are comparable to each other.

What if my metrics genuinely differ by context?

Then they are different metrics and should have different names. revenue_gross, revenue_net, revenue_delivered — three entries, three definitions, and the model picks. One name with three context-dependent meanings is the problem, not a requirement to accommodate.

Do I need dbt or a dedicated semantic layer tool?

Not to start. A set of views in a semantic schema plus column descriptions is a semantic layer in every way that matters for this purpose: one definition, version-controlled, readable by every consumer. Tools add dependency management, testing and lineage — worth having, and not the first step.

How does this interact with showing the SQL?

It makes showing the SQL much more useful. When a metric expands from a named definition, the reader can see both the composed query and which definition it used — so "is this the right number?" becomes "is this the right metric?", which is a question a non-technical stakeholder can actually answer.

The summary

• A definition in a prompt is probabilistic: it drifts with phrasing, degrades with context length, cannot be reviewed, and does not transfer to your other tools.
• A definition in a semantic layer is deterministic: the same SQL expression every time, version-controlled, diffable, and readable by every consumer.
• The model selects; the layer defines. Interpretation is the model's job and it is good at it. Authorship of financial definitions is not.
• Use the 5% test: if being slightly wrong would change a decision, it belongs in the layer. Presentation and behavioural policy stay in the prompt.
• Start with views in a semantic schema and column descriptions in the warehouse. That is a week of work and most of the benefit; adopt a framework when dependency ordering becomes a thing you think about.