Semantic layers vs. prompting: where business definitions should live (definitions, not suggestions)

"A repeat customer is anyone with two or more paid orders in the last 12 months."

You can write that sentence into an AI tool's system prompt, or you can encode it in a semantic layer. Both will get the AI to produce the right SQL most of the time. Only one of them is actually a definition. The other is a suggestion the model usually follows.

You won't see the difference in week one. You'll see it a few months in, when someone asks the question a new way, gets "the last 365 days" instead of "the last 12 calendar months" — or worse, "this calendar year" — and three people bring three repeat-customer numbers to one meeting.

What "it's in the prompt" really means

A system prompt is text the model reads before it writes SQL. It works, most of the time. And it fails in four specific ways that share one cause: nothing obliges the model to follow it.

1. It drifts with phrasing."How many repeat customers?", "Who bought again this year?" and "Show returning buyers" can produce three different queries — the definition was in context each time, but each wording nudged the generation differently.
2. It weakens as the prompt grows.A definition on line 40 of a 200-line prompt competes with every other instruction, metric and table description. Adherence fades quietly — the query still runs and still looks plausible.
3. It can't be reviewed.Editing a prompt is editing prose. Nothing tests it, and nobody can tell from the change whether the SQL it produces moved. Change a SQL expression in version control and the diff is the change.
4. It doesn't travel.Dashboards, scheduled reports and analysts' hand-written queries never read the AI's prompt, so the metric means one thing in chat and another everywhere else.

What a semantic layer actually is

Without the vendor gloss: a version-controlled file that maps a business term to one exact SQL expression, plus something that expands references to it.

-- name:        repeat_customers
-- grain:       customer
-- definition:  customers with 2+ paid, non-refunded orders in the
--              12 calendar months before the reference month
-- owner:       retention team
-- changelog:   2026-09-12 — stopped counting fully refunded orders
SELECT COUNT(*) AS repeat_customers
FROM (
  SELECT customer_id
  FROM orders
  WHERE payment_status IN ('paid', 'partially_refunded')
    AND refund_amount < order_total
    AND DATE(created_at, 'Asia/Kolkata')
        BETWEEN DATE_SUB(@month_start, INTERVAL 12 MONTH)
            AND DATE_SUB(@month_start, INTERVAL 1 DAY)
  GROUP BY customer_id
  HAVING COUNT(*) >= 2
);

That header gives you three things a prompt line never can: a grain, an owner and a changelog. When someone asks in six months why the number moved, the answer is in the file.

The important part is where the line between the model and the layer sits:

1. The model reads the intent."Repeat customers in Pune last quarter?" becomes: metric = repeat_customers, filters = city and period. Genuinely hard, and models are good at it.
2. The layer supplies the metric.repeat_customers expands to its exact SQL. The model doesn't write it and can't vary it.
3. The model adds filters and grouping.A WHERE on city and a GROUP BY on month around a fixed expression — mechanical and easy to check.
4. The query is validated before it runs.A dry run catches invalid columns and reports the cost, for free.

The model selects; the layer defines. That sentence is the whole design.

Which definitions belong where?

Not everything needs a layer entry — 300 unmaintained entries are worse than 12 correct ones. The test I use: what happens if this is 5% wrong?

Most businesses have somewhere between 10 and 30 definitions that fail the 5% test. That's a week of work, not a quarter.

How to start without buying anything

The cheapest version captures most of the value: a schema of views that every consumer reads.

-- The AI tool, dashboards, analysts and scheduled reports all read this.
-- One definition, changed only through code review.
CREATE OR REPLACE VIEW semantic.monthly_customers AS
SELECT
  DATE_TRUNC(DATE(o.created_at, 'Asia/Kolkata'), MONTH)           AS month,
  o.sales_channel,
  o.state,
  COUNT(DISTINCT o.customer_id)                                   AS customers,
  COUNT(DISTINCT IF(o.is_first_order, o.customer_id, NULL))       AS new_customers,
  SUM(o.net_revenue)                                              AS net_revenue
FROM semantic.orders_clean o
WHERE o.payment_status IN ('paid', 'partially_refunded')
GROUP BY month, o.sales_channel, o.state;

Point the AI at semantic.* instead of raw tables and a whole class of ambiguity disappears — the confusing columns simply aren't reachable.

Then add column descriptions in the warehouse itself, where every tool that reads the schema picks them up:

ALTER TABLE analytics.orders
ALTER COLUMN order_total
SET OPTIONS (description =
  'Includes tax and shipping. NOT revenue — use net_revenue for reporting.');

Move to a dedicated semantic-layer framework when you have more than a couple of dozen metrics, metrics built on other metrics, or several teams changing them. Until then, views plus descriptions are the right size.

• Point the AI at curated views, not raw tables.
• Describe your trap columns on the 20 most-queried tables — lead with which column not to use.
• Record synonyms once ("sales" means net_revenue) where every tool can read it.
• Adopt a framework later, when dependencies between metrics become the problem.

What the prompt is still for

Prompts aren't obsolete. They handle everything that's about behaviour rather than values:

• Disambiguation — "when someone says 'sales', use net revenue and say so".
• Table guidance — "campaign questions use semantic.campaign_daily".
• Guardrails — "always filter by date; never scan the raw events table".
• Uncertainty — "if two metrics could fit, ask instead of picking".

Policies about how to act go in the prompt. Facts that must be exactly right go in the layer.

Quick gut-check