Semantic Layer Fundamentals

The Mental Model

A semantic layer centralizes business meaning on top of trusted models. It lets tools ask for metrics and dimensions without each tool rewriting SQL logic.

The semantic layer is a dictionary plus a map. It says what business words mean and how those words connect to warehouse tables.

Dataset reference: ecommerce tables and grain assumptions.

Resolve a question into joins and aggregation

The question is "net revenue by customer country." Identify the measure, dimension, join cardinality, and time interpretation before generating SQL.

with orders(customer_id, net_amount) as (values (7, 80), (8, 50)),
customers(customer_id, country) as (values (7, 'FR'), (8, 'DE'))
select c.country, sum(o.net_amount) as net_revenue
from orders o join customers c on c.customer_id = o.customer_id
group by c.country order by c.country;

Expected rows: DE = 50 and FR = 80. This example assumes one current customer row per customer_id and no missing customer keys. Duplicating a customer row changes the result, so the semantic layer still depends on tested entity relationships. For historical countries, use the validity-aware design from the facts and dimensions lesson.

Interactive Check

Question: For "revenue by customer country", what are the metric and dimension?

Practice: Map Questions to Semantics

Translate five business questions into entities, measures, dimensions, and metrics.

Use the guided lab below to record your result, assumptions, and the check that would catch an incorrect result.