A semantic layer gives business metrics shared definitions so different reports and applications can request the same calculation. It connects business language such as “net sales” to data, aggregation rules and permitted dimensions. It does not automatically correct bad source records.

Why two accurate queries can disagree

Consider a synthetic dataset with three orders. Order 1 is completed for $100 with a $20 refund. Order 2 is completed for $50 with no refund. Order 3 is cancelled for $80. One dashboard sums every order and shows $230. Another counts only completed orders and shows $150. A third subtracts refunds and shows $130.

The SQL can be syntactically correct in all three cases. The disagreement is about the metric definition. Before choosing a tool, agree on which question each number answers.

Write a metric contract in plain language

• Name: completed-order net sales, USD.
• Grain: one row per order before aggregation.
• Population: orders whose current status is completed.
• Calculation: gross order amount minus refunded amount.
• Time rule: attribute the amount to the order date in this learning example.
• Missing values: zero refunds are explicit; unknown refunds are investigated.
• Owner: a named business owner approves the definition in a real implementation.

This is an operational sales metric, not a complete accounting revenue-recognition policy. Using current status and refunds can restate historical periods. If a business reports refunds on their processing date, it needs a different time rule and possibly a separate refund-event model.

Implement the definition on a tiny dataset

WITH orders(order_id, status, gross_cents, refund_cents) AS (
 VALUES (1, 'completed', 10000, 2000),
        (2, 'completed', 5000, 0),
        (3, 'cancelled', 8000, 0)
)
SELECT SUM(gross_cents - refund_cents) / 100.0 AS net_sales_usd
FROM orders
WHERE status = 'completed';

Expected result: $130. This VALUES query works in PostgreSQL and DuckDB. It illustrates the business definition; it is not a dbt Semantic Layer configuration file.

Where a semantic layer helps

In a larger system, a shared metric definition can be exposed to supported reporting tools or applications. The dbt Semantic Layer is one implementation that manages metrics over models and handles joins through MetricFlow. Its exact configuration and integrations depend on the supported version and environment.

A useful implementation defines allowed dimensions, valid join paths and aggregation behaviour. For example, joining order-level net sales to order-line categories requires an allocation rule. Repeating the full order amount in every category creates double counting even if the metric has a clear name.

What about AI questions?

If someone asks an assistant “What were sales last month?”, it still needs a metric, timezone and period definition. A governed metric gives the application a more explicit target. Validate the generated answer against a known query; do not treat the presence of a semantic layer as proof that every answer is correct.

A practical acceptance test

Create a small fixture containing a completed order, a cancellation, a partial refund and a missing value. Write expected results before implementing the metric. Ask two reporting paths for the same metric and filters, then reconcile differences. Document intentional differences instead of forcing distinct business measures into one label.

Read the business case studies and SQL aggregation guide next.

Sources and further reading

• dbt Semantic Layer overview

Found an error? Send a correction.