How to Build a Semantic Layer for AI: Metrics, Relationships, and Business Rules

Build a semantic layer for AI by starting with one agreed business calculation, implementing it at the correct data grain, and exposing it through a controlled query interface. Expand after the first metric is demonstrably correct.

This walkthrough uses an illustrative retailer. It is a small implementation pattern, not a complete semantic-layer product. If the concept is new, start with what a semantic layer does for AI.

1. Write the metric contract

Ask the business owner to approve these decisions before choosing model syntax:

Decision	Example definition
Metric	Net merchandise sales
Eligible orders	Paid, non-test orders
Amount	Merchandise after discounts; excluding tax and shipping
Refunds	Completed merchandise refunds attributed to the original order
Currency	USD only in this example
Time	Order payment date in the agreed reporting timezone
History	Earlier periods restate when later refunds arrive
Owner	Named commercial or finance owner

This is an operational sales measure, not a claim about accounting revenue recognition. If the business needs a fixed historical snapshot or a cash-flow view, define a separate metric.

2. Establish the input grain

Assume orders has one row per order and refunds has one row per refund event. Order amounts and refund amounts are integer cents. The upstream pipeline has already converted payment timestamps into the reporting date and separated merchandise refunds from tax or shipping refunds.

Several refund events can belong to one order. Aggregate them before joining. The following SQL creates one metric row per eligible order:

CREATE VIEW order_net_sales AS
WITH completed_refunds AS (
  SELECT order_id, SUM(merchandise_refund_cents) AS refund_cents
  FROM refunds
  WHERE status = 'completed'
  GROUP BY order_id
)
SELECT
  o.order_id,
  o.reporting_date,
  o.country,
  o.merchandise_cents - COALESCE(r.refund_cents, 0) AS net_sales_cents
FROM orders o
LEFT JOIN completed_refunds r ON o.order_id = r.order_id
WHERE o.status = 'paid' AND o.is_test = 0;

The left join preserves orders with no refunds. It does not validate the upstream data: duplicate order IDs or incorrectly classified refunds would still produce bad results. Reject or investigate those conditions in data-quality checks.

3. Map the model to business terms

In your semantic-layer tool, register order_id as the unique key, net_sales_cents as a sum metric, and reporting_date and country as supported dimensions. Document the refund and currency conventions next to the metric.

dbt's semantic-model documentation describes its configuration structure. Use the documentation for your installed version; YAML copied from a different release may not be valid. In another tool, express the same business contract using that tool's model syntax.

Expose the approved metric to the agent through a constrained request such as this illustrative application interface:

{
  "metric": "net_merchandise_sales",
  "group_by": ["country"],
  "start_date": "2026-08-01",
  "end_date_exclusive": "2026-09-01"
}

This JSON is not a vendor API. Your query service must validate its fields, bind values safely, enforce authorized scope, and reject unsupported combinations. User identity should come from authentication, not a model-supplied customer ID.

4. Check the calculation independently

For this fixture, paid non-test order A100 contains 10,000 cents and a completed refund of 2,000 cents. Order A101 contains 6,000 cents with no refund. Both have an August reporting date.

SELECT SUM(net_sales_cents) / 100.0 AS net_merchandise_sales_usd
FROM order_net_sales
WHERE reporting_date >= '2026-08-01'
  AND reporting_date < '2026-09-01';

The expected result is 140.00. Add a test order worth 500.00 and a cancelled order worth 70.00; the result must remain 140.00. Split the completed refund into two events totaling 20.00 and check that the result remains unchanged.

Also test a pending refund, an order without refunds, the period boundaries, and an empty period. Decide explicitly whether no data should display as zero or as unavailable. Neither behavior should be an accidental consequence of a null value.

5. Test the agent separately from the metric

Ask “net sales for August,” “August merchandise after refunds,” and an ambiguous question such as “how much revenue did we make?” Check that the first two use the approved definition and that the third gets appropriate clarification.

Try a request outside the user's permitted scope. Confirm that enforcement happens in the execution system even if the agent generates an inappropriate request. Then check that the explanation preserves units, dates, and caveats from the result.

6. Operate the definition as a maintained asset

Version the model, require review for definition changes, and re-run the fixtures when changing joins, source systems, or agent tools. Give each metric an owner and make data freshness visible.

The aim is a small set of dependable operations the agent can use. Add more metrics after the team can explain and reproduce the first answers.

For failures along the way, use our AI answer diagnosis guide. Labs4Change can help design and implement the data foundation for your first AI agent.