Semantic Layer: Under the Hood

Two dashboards disagree on last quarter's revenue. A wrong join inflates a metric by 30%. An AI assistant writes plausible SQL that silently drops half the data. These aren't edge cases. They're the default when business logic lives in scattered queries instead of a governed model.

This article is a walk-through of how I built a semantic layer from the ground up: the two-layer physical/model split, how entities and relationships are declared, how metrics are governed, how joins resolve automatically, how aggregates route safely, and how the whole thing is tested. A semantic layer is not middleware. It is the encoded knowledge of what your data means, and the engine that reads that knowledge to produce trustworthy SQL.

The examples throughout this article are drawn from SpeedDelivery, a food delivery platform connecting consumers, restaurant partners, and courier drivers across multiple cities. SpeedDelivery's data team serves three distinct audiences: an operations team tracking cancellation rates, delivery times, and zone performance; a finance team owning revenue, commission, and driver payout metrics; and a data platform team responsible for the warehouse schema and the semantic layer that sits on top of it. Their model is the thread that runs through every section here.

The two layers: physical truth and business meaning

Every table, column, and join path in the warehouse is declared in the physical layer. Every metric, dimension, and business concept is declared in the model layer. The model layer references the physical layer. The physical layer knows nothing about the model layer. This one-way dependency is what prevents warehouse schema changes from silently corrupting business metric definitions.

When an engineer renames a column in the physical layer, the model loader catches the broken reference during validation and fails immediately with a clear error. When an analyst changes a metric formula in the model layer, the warehouse schema files are untouched. Each layer evolves independently, owned by the people who understand it best.

What a physical table declaration looks like

Every table in the physical layer follows the same structure: a table block with database, schema, and name, followed by a columns array where each column declares its type, description, and optionally its primary key status or the set of valid categorical values.

table:
  database: warehouse
  schema:   analytics
  name:     delivery_fact
  description: "Delivery transaction fact table. Grain: one row per delivery."

columns:
  - name: delivery_id
    type: BIGINT
    is_primary_key: true
    description: "Unique delivery identifier"

  - name: consumer_id
    type: BIGINT
    description: "Foreign key to consumer_dim"

  - name: order_date
    type: DATE
    description: "Date the order was placed"

  - name: delivery_status
    type: VARCHAR(50)
    values: [pending, accepted, in_transit, delivered, cancelled]
    description: "Current delivery status"

  - name: total_amount
    type: DECIMAL(12,2)
    description: "Total amount charged for the delivery"

The description on consumer_id names the target dimension table explicitly. This is documentation, not a parsed reference. The actual join lives in a separate relationship file and is declared once there. The physical layer describes structure; the model layer describes meaning; they do not bleed into each other.

Defining an entity

An entity in the model layer represents a business domain: delivery, consumer, store, driver. It is the unit of meaning that analysts think in. A core model declaration answers three questions: where does the data live, what is the grain, and what is the primary time axis?

model: delivery
type: core
display_name: "Delivery"
icon: "local_shipping"
status: production                 # production | beta | deprecated
source_table: delivery_fact
date_field: order_date             # primary time axis for all date filtering
grain: [delivery_id]               # one row per delivery

dimensions:
  # Time dimensions
  - name: order_date
    column: order_date
    type: date
    description: "Date the order was placed"

  - name: delivery_dt
    column: delivery_dt
    type: date
    description: "Date the order was delivered"

  - name: cancelled_dt
    column: cancelled_dt
    type: date
    description: "Date the order was cancelled (NULL if not cancelled)"

  # Identifiers
  - name: delivery_id
    column: delivery_id
    type: integer
    description: "Unique identifier for the delivery"

  # Attributes
  - name: delivery_status
    column: delivery_status
    type: categorical
    description: "Current delivery status"
    aliases: ["order status"]

  - name: order_source
    column: order_source
    type: categorical
    description: "Platform where order was placed"
    aliases: ["channel", "platform"]

metrics:
  - name: total_orders
    display_name: "Total Orders"
    description: "Count of all deliveries"
    formula: "COUNT(delivery_id)"
    type: integer
    certification: verified

  - name: total_revenue
    display_name: "Total Revenue"
    description: "Sum of all order total amounts"
    formula: "SUM(total_amount)"
    type: currency
    unit: USD
    certification: verified
    aliases: ["gross revenue", "GMV"]

  - name: avg_delivery_time
    display_name: "Average Delivery Time"
    description: "Average delivery time in minutes"
    formula: "AVG(actual_delivery_minutes)"
    type: number
    unit: minutes
    semantic_type: duration

  - name: total_cancelled
    display_name: "Cancelled Orders"
    description: "Count of deliveries with status = 'cancelled'"
    formula: "COUNT(CASE WHEN delivery_status = 'cancelled' THEN 1 END)"
    type: integer
    # cancellation_rate is declared as a derived (expression) metric below.
    # See "Derived metrics" in the next section.

groups:
  - name: order_performance
    display_name: "Order Performance"
    description: "Order volume and per-order economics"
    metrics:
      - name: total_orders
        use_cases:
          - "Monitor order volume trends"
          - "Compare across segments"
      - name: total_revenue
        use_cases:
          - "Track overall revenue growth"

  - name: delivery_operations
    display_name: "Delivery Operations"
    description: "Delivery time and quality metrics"
    metrics:
      - name: avg_delivery_time
        use_cases:
          - "Monitor delivery speed trends"
      - name: cancellation_rate
        use_cases:
          - "Track order failure rate"

default_filters:
  - dimension: delivery_status
    operator: "!="
    value: "cancelled"

The grain field is the model's correctness contract: at this grain, one row represents one thing: [delivery_id] for a delivery, [order_id, line_item_id] for a line item. It matters most when joins are involved. Joining delivery_fact to a finer-grained table fans out rows; each delivery multiplies into as many rows as it has line items, and any SUM double-counts. The grain declaration makes this risk explicit: the model author's commitment that they've handled it, through pre-aggregation, a subquery, or a distinct count. A model without a declared grain cannot be reasoned about.

The date_field is the single time axis for every date range filter and time-period comparison applied to this entity. A delivery model uses order_date; a driver payout model uses payout_date. Declared once, it flows through every query.

The status field signals the model's lifecycle stage: production means the model is stable and ready for general use; beta means it is functional but under active development and may change; deprecated means it should no longer be used and will be removed in a future release. Status is separate from metric certification: a production model can contain a mix of verified and informal metrics. Status answers "is this model ready for use?" while certification answers "is this specific calculation correct?"

Perspectives: Purpose-built views with shared governance

A perspective inherits a core model and tailors it for a specific team or use case. SpeedDelivery's operations team needs delivery time, cancellation rate, and zone-level dimensions with a default filter excluding test orders. Their finance team needs revenue, commission, and margin with a default filter for completed transactions only. Perspectives can also scope data access: a regional perspective for China has region = 'CN' baked in, so users of that perspective only see China data. All three perspectives share the same core delivery model: when the core metric formula changes, every perspective updates automatically. No duplication, no drift.

model: delivery_operations
type: perspective
core: delivery                 # inherits source_table, date_field, all dims + metrics
display_name: "Delivery Operations"
icon: "speed"

inherits:                       # pull in dimensions from additional models
  - model: store
    join: delivery_fact__store_dim

groups:
  - name: operations_summary
    display_name: "Operations Summary"
    description: "Key operational health indicators"
    metrics:
      - name: total_orders
        use_cases:
          - "Monitor daily order volume"
      - name: avg_delivery_time
      - name: on_time_delivery_rate
      - name: cancellation_rate

sample_questions:
  - "Which stores have the highest cancellation rate this week?"
  - "How has average delivery time changed month over month?"

The resolver merges the core model's dimensions and metrics into the perspective, core items first, then the perspective's own additions. The perspective can add metrics the core doesn't have, override metric groups, append default filters, and pull in additional joined models. It cannot break the core model. Changes to the core automatically flow into every perspective that inherits it.

Metrics: formula, derived, and disambiguated

A metric is more than a SQL formula. It is a named, versioned, governed piece of business logic with enough metadata to be used correctly by both humans and AI systems. The metadata answers three questions that a formula cannot: what does this metric mean, how should it be aggregated, and when should it be used instead of a similar one?

Formula metrics

A formula metric is a direct SQL aggregate over the source table. The formula is written with bare column names; the model loader qualifies them to table.column form at load time so the query engine never sees an unqualified reference.

- name: total_revenue
  display_name: "Total Revenue"
  description: "Sum of all order total amounts including fees and surcharges"
  formula: "SUM(total_amount)"      # qualified to SUM(delivery_fact.total_amount) at load time
  type: currency
  unit: USD
  certification: verified
  aliases:
    - "gross revenue"
    - "GMV"
    - "total sales"
  preferred_for:
    - "revenue growth tracking"
    - "financial overview"
  disambiguation_hints: "Use for top-line gross revenue and GMV reporting.
    For earnings after costs and deductions, use net_revenue instead."

The certification level signals trust: informal means the metric was created by a team for their own use and hasn't been reviewed; verified means a data team member has confirmed the formula; gold means it is the company-wide standard for this concept. When two metrics match a question, certification breaks the tie.

The optional semantic type field encodes how a metric behaves mathematically: which aggregations are safe, how it should be displayed, and where it is likely to mislead. It is most important for non-additive metrics (rates, durations, semi-additive snapshots) where incorrect aggregation produces silent errors. Simple additive metrics like total_revenue typically omit this field. The types and what they imply:

Semi-additive metrics deserve extra attention because they are the most common source of silent errors in operations and finance models. If you have a metric that measures a state at a point in time (a balance, a count of active entities, an inventory level), it is semi-additive. The right query for "average daily active drivers last month" is AVG(daily_snapshot), not SUM(daily_snapshot). Declaring the semantic type makes this intent explicit and prevents the wrong aggregation from being applied by default.

The disambiguation hints field is operational guidance: free-text written by the metric author that explains when to use this metric and when to use a different one instead. Every disambiguation hint in the model was written to prevent a specific, observed confusion from recurring.

Derived metrics

Some business concepts are not raw aggregates but compositions of other metrics. Revenue per order is total revenue divided by total orders. Gross margin is net revenue divided by total revenue. These are declared as expression metrics: they reference other named metrics instead of writing out a formula.

# Base formula metrics
- name: total_revenue
  formula: "SUM(total_amount)"
  type: currency

- name: total_orders
  formula: "COUNT(delivery_id)"
  type: integer

# Derived metrics: each references formula metrics above
- name: revenue_per_order
  display_name: "Revenue Per Order"
  expression: "total_revenue / NULLIF(total_orders, 0)"
  type: currency
  semantic_type: ratio
  disambiguation_hints: "Average basket size. Do not sum across regions. Recompute from components."

- name: cancellation_rate
  display_name: "Cancellation Rate"
  expression: "total_cancelled / NULLIF(total_orders, 0)"
  type: percentage
  semantic_type: rate
  disambiguation_hints: "Share of orders cancelled before completion. Non-additive. Never sum across regions."

# total_cancelled is a formula metric: COUNT(CASE WHEN delivery_status = 'cancelled' THEN 1 END)
# At load time the resolver expands cancellation_rate to a flat formula:
# "(COUNT(CASE WHEN delivery_fact.delivery_status = 'cancelled' THEN 1 END))
#  / NULLIF((COUNT(delivery_fact.delivery_id)), 0)"

At load time the resolver substitutes each referenced metric name with its fully-qualified formula, producing a single flat SQL expression. The substitution is longest-name-first to prevent partial matches. Chaining is not allowed: an expression metric cannot reference another expression metric. This is validated at load time with a single forward pass, so circular or deep chains are caught before they can cause confusion at query time.

Canonical filter values

For every enumerated column in the physical layer, the exact valid values are declared alongside the column definition, as shown in the delivery_status column above. These canonical values serve three purposes: filter validation (programmatic filters match against exact strings, eliminating case-sensitivity bugs and empty results from typos); UI dropdowns (analysts pick from a controlled list rather than typing free text); and AI agent grounding (values are extracted at load time and injected into the agent's prompt context, so when a user asks "show me cancelled deliveries," the LLM writes cancelled (the declared value), not a guess).

Dimensions, model filters, and metric groups

Dimensions

A dimension is any attribute you can slice or filter by: order date, delivery status, store category, consumer region, zone name. Dimensions that come directly from the source fact table are declared inline on the model. Dimensions that come from joined tables are pulled in through the inherits list (more on that in the inheritance section). Either way, the analyst sees a flat list of available dimensions and picks one; the join resolution is invisible.

A special case worth knowing: degenerate dimensions: identifiers stored directly on the fact table with no corresponding dimension table (order_number, trip_code). In a delivery model, delivery_id is the canonical example: it is the grain column, always available for filtering and grouping, requiring no join. Degenerate dimensions are declared as regular inline dimensions and emit no JOIN clause. The most common mistake is omitting them from aggregate table declarations. If an aggregate is built at daily grain without a delivery_id column, any query filtering by a specific delivery must fall back to the full fact table.

Model filters

A model's default_filters silently scope every query run against it. They are applied before any user filter and cannot be overridden by user input unless the perspective explicitly sets override_default_filters: true.

default_filters:
  - dimension: delivery_status
    operator: "!="
    value: "cancelled"
  - dimension: delivery_status
    operator: "!="
    value: "refunded"

This is how a team-specific perspective stays clean by default: at SpeedDelivery, the finance perspective excludes test orders and the operations perspective excludes archived zones. The filters live in the model definition, reviewed in a pull request, visible to everyone. When a query arrives, the engine prepends the default filters before evaluating any user-supplied conditions.

Default filters also participate in aggregate routing. Before an aggregate table can be used, the engine verifies that the user's filters, plus the model's defaults, are compatible with the content restrictions built into the aggregate. A mismatch forces a fallback to the full fact table, every time.

Metric groups

Metrics are organized into named groups for navigation and context. A group has a display name, a description, an ordered list of metrics, and use-case hints for each metric that explain when it is most relevant. Groups can nest up to four levels deep. A finance perspective might have a top-level Financial group containing Revenue and Margin subgroups, each with their own ordered metrics and use-case annotations.

Star and snowflake schema joins

Every join between tables is declared once in the physical layer as a named relationship. The model layer references join names, not join conditions. The query engine resolves the condition at query time from the physical layer declaration. This means a join condition is defined in exactly one place, reviewed in one PR, and changes in one place. A model that needs the same join three times still references the same relationship name three times.

The star schema is the default pattern: a central fact table with direct foreign key joins to surrounding dimension tables. Each relationship is a named many_to_one declaration in the physical layer.

name: delivery_fact__consumer_dim
type: many_to_one
join_type: LEFT JOIN
left:
  table: delivery_fact
  column: consumer_id
right:
  table: consumer_dim
  column: consumer_id
# join_condition is auto-derived from left/right columns

The type field (many_to_one, one_to_one, one_to_many) is advisory, not runtime-enforced. It documents the expected cardinality and is used by the query engine to reason about aggregate routing (a metric that requires a one_to_many join cannot safely use a pre-aggregated table, for example). It does not validate row counts at query time. If the actual data in your warehouse violates the declared cardinality (a many_to_one join that is secretly many_to_many at runtime), the engine will not detect it, and the result will silently double-count. The cardinality declaration is your data contract with future model users. Make it accurate. Test it with a COUNT vs COUNT DISTINCT check on the join key before declaring it.

Snowflake joins extend the star by chaining through intermediate dimension tables, for example from a delivery to its consumer, then from the consumer to their historical address. Multi-hop paths are declared using a double-underscore convention in the join name: delivery_fact__consumer_dim__consumer_address. The resolver validates chain continuity at load time: each hop's right table must match the next hop's left table. A broken chain raises an error before any query can be served.

Slowly changing dimension joins

Some dimension tables maintain a history of how entity attributes changed over time. A consumer's region may have changed three times in two years. Joining without accounting for the time dimension produces incorrect or multiplied results: the query picks up multiple historical rows per consumer. An SCD join declaration adds temporal predicates automatically.

name: delivery_fact__consumer_address_scd
type: scd                          # signals: apply temporal predicate at query time
join_type: LEFT JOIN
left:
  table: delivery_fact
  column: consumer_id
right:
  table: consumer_address
  column: consumer_id
  start_date: scd_start_dt         # column: when this record became valid
  end_date: scd_end_dt             # column: when it stopped being valid
join_condition: delivery_fact.consumer_id = consumer_address.consumer_id

# At query time, the engine adds temporal predicates automatically:
# AND consumer_address.scd_start_dt <= delivery_fact.order_date
# AND consumer_address.scd_end_dt   >  delivery_fact.order_date

The SCD relationship is declared once. Every query that uses it gets the correct temporal predicate applied automatically. The model author writes the join name; the engine handles the time logic. SCD relationships are validated at load time: a missing start_date or end_date raises an error immediately rather than producing silently incorrect results.

Inheritance and multi-role dimensions

Two mechanisms handle reuse in the model layer. The core field handles vertical inheritance (a perspective extending a core model). The inherits list handles horizontal composition, pulling dimensions and metrics from an entirely different entity model through a declared join.

Models compose through three levels. Dimension entities (L1) are the reusable building blocks: consumer, store, driver, market. Core entities (L2) inherit from them and add fact-specific metrics. Perspectives (L3) inherit from a core entity and shape it for a specific team or use case.

Three rules that keep inheritance manageable

Inheritance without guardrails creates the same problems it solves. Implicit transitive dependencies, unbounded depth, and duplicate names from multi-path inheritance are how object-oriented hierarchies collapse in application code. Three strict constraints prevent this in the model layer.

Rule 1: Explicit, not transitive. If entity A inherits B and C, and entity D inherits A, then D does not automatically gain B and C. D must list them explicitly in its own inherits block. Every model's complete dependency graph is readable from its own file. You never need to trace a chain of files to understand what a model includes. The cost is some declaration repetition. The benefit is that no model has invisible dependencies.

Rule 2: Maximum depth of three levels. Dimension (L1) → Core (L2) → Perspective (L3). No deeper nesting. The model loader enforces this with a hard depth limit: any chain exceeding three levels raises a ValueError at load time. Deeper hierarchies create resolution ambiguity and make the model harder to reason about. Three levels cover every real composition pattern encountered in practice.

Rule 3: Circular dependency detection. The model loader tracks a visited set during recursive resolution. If a model appears twice in the resolution chain, the loader raises a ValueError with the full cycle path (A → B → C → A) before any query can be generated. This is caught at load time, not at query time. You cannot publish a model with a circular dependency.

Cross-model composition

A delivery model that needs to expose consumer attributes (tier, region, signup date) does not redeclare those attributes. It inherits them from the consumer model through a declared join path. The resolver pulls all consumer dimensions into the delivery model's namespace, tagging each with the join name it requires. At query time, when a user selects a consumer dimension, the engine adds that join to the query automatically.

inherits:
  - model: consumer
    join: delivery_fact__consumer_dim
    # → consumer_id, consumer_tier, consumer_region, ... all added to namespace
    # → each tagged with _join: "delivery_fact__consumer_dim"

  - model: zone
    join: delivery_fact__zone_dim
    # → zone_name, zone_market, ... all added to namespace

Cross-fact composition: the Customer 360 pattern

The most powerful form of composition combines metrics from multiple fact tables into a single model. A Customer 360 view needs order metrics (revenue, order count), support metrics (ticket count, resolution time), and engagement metrics (login frequency, feature usage). Each lives in a different fact table with its own grain. The semantic layer composes them through a shared dimension anchor.

model: customer_360
type: core
source_table: customer_dim          # the shared anchor
date_field: created_at

dimensions:
  - name: customer_id
    column: customer_id
    type: integer
  - name: customer_tier
    column: tier
    type: categorical

metrics:
  - name: total_customers
    display_name: "Total Customers"
    formula: "COUNT(customer_id)"
    type: integer

inherits:
  - model: order                    # pulls total_revenue, total_orders
    join: customer_dim__order_fact

  - model: support                  # pulls total_tickets, avg_resolution_time
    join: customer_dim__support_fact

  - model: engagement               # pulls login_count, feature_usage_score
    join: customer_dim__engagement_fact

When an analyst queries total_revenue and total_tickets together, the query engine automatically includes both JOINs. When they query only total_revenue, only the order_fact JOIN is added. The model author declares the composition once; the engine handles join optimization per query. This is how a single model serves questions that would otherwise require manual SQL across three different fact tables.

Conformed dimensions

Cross-model inheritance enables conformed dimensions: dimension tables that carry the same meaning regardless of which fact table you reach them through. At SpeedDelivery, consumer_dim means the same thing whether you join to it from delivery_fact, payment_fact, or support_ticket_fact. A report comparing revenue per consumer tier against support ticket rate per consumer tier is only meaningful if both models join the same consumer_dim with the same definition of "tier"; conformed dimensions guarantee this. When the consumer model is updated, every domain that inherits from it picks up the change automatically. The practical implication: dimension models should be designed to be shared, not owned by a single fact team. A conformed dimension belongs to the data platform.

Multi-role dimensions

Some fact tables join to the same dimension table more than once through different foreign keys. A delivery has both an ordering consumer and a receiving consumer. Both are rows in consumer_dim. Both are referenced by separate foreign keys in delivery_fact. Without role separation, the second join produces a SQL alias collision and "show me revenue by consumer region" is ambiguous: which consumer?

inherits:
  - model: consumer
    join: delivery_fact__consumer_dim
    # standard role: consumer_region, consumer_segment, consumer_tier ...

  - model: consumer
    join: delivery_fact__recipient_consumer_dim
    role: recipient
    # role-prefixed: recipient_consumer_region, recipient_consumer_segment ...

Without role qualifiers, inheriting consumer twice would produce duplicate dimension names, ambiguous SQL, and a broken UI. The role is validated at load time: a duplicate inheritance without a role raises an error before any query is generated.

Multi-hop formula resolution

Sometimes a metric needs a column that is two or more joins away from the source table. A delivery metric might need a market-level attribute from market_dim, reachable only through delivery_fact → store_dim → market_dim. Requiring the model author to flatten this into a single-hop reference would mean duplicating columns across physical tables.

Metric formulas support multi-hop relationship paths using the same double-underscore naming convention as join names:

# Single-hop: one join (delivery_fact → driver_payout)
formula: "SUM(delivery_fact__driver_payout.driver_payout_amount)"

# Multi-hop: two chained joins (delivery_fact → store_dim → market_dim)
formula: "SUM(delivery_fact__store_dim__market_dim.region_revenue)"

The model loader splits the path on double-underscores into consecutive hops and validates chain continuity: the right table of hop N must equal the left table of hop N+1. If any hop's relationship is undefined or the chain is discontinuous, the loader raises an error at load time. All intermediate joins are collected and passed to the query engine, which emits them in the correct order. The model author writes a single path string. The resolution machinery is entirely hidden.

Aggregate-aware context switching

Pre-aggregated tables trade flexibility for speed. SpeedDelivery's daily delivery aggregate pre-computes totals by date, zone, store, and status, reducing a 100-million-row scan to 10,000 rows for common queries. But an aggregate is not a general-purpose replacement for the fact table. It was built at a specific grain, under specific assumptions, and possibly covering only a subset of rows. Using it for the wrong query produces a number that looks right and is wrong.

The semantic model declares aggregate sources directly on the core model. Every alternative is explicit, versioned, and reviewed.

aggregate_sources:
  - table: daily_delivery_agg
    date_field: order_date

    column_mappings:              # metric name → aggregate table column
      total_revenue:    total_amount
      total_orders:     delivery_count
      total_cancelled:  cancelled_count
      total_commission: commission_amount
      total_discounts:  discount_amount

    content_filters:              # restrictions this aggregate was built with
      - dimension: delivery_status
        operator: "!="
        value: "cancelled"

The column_mappings field tells the engine how to translate metric names into the aggregate table's column names. The content_filters field declares what subset of data the aggregate was built over. These two fields together are what make the routing decision safe.

The routing decision

Before every query, the engine evaluates whether the aggregate can safely serve the entire request. The check is binary: the aggregate answers the whole query, or it answers none of it. No partial routing. The rules are evaluated in order, and the first failure short-circuits the check immediately.

The routing decision is also irreversible within a query. An early prototype tried pulling some metrics from the aggregate and others from the fact table in a single response. The results were subtly wrong when the two sources interacted: different grains, different implicit filters, combined totals that didn't reconcile. Eliminating partial routing eliminated an entire class of silent errors and reduced the routing logic to a single, auditable decision point.

Query generation: the engine that reads the model

Everything defined in the model (entities, joins, metrics, filters, aggregate sources) is consumed by a single pure function. Given a perspective ID, a list of metrics, a list of dimensions, a filter tree, and a date range, it returns SQL. No side effects, no randomness. The same inputs always produce the same output.

The engine does not make decisions. All decisions are encoded in the model: which joins are valid, which aggregates are eligible, which filters apply by default. The engine executes those decisions deterministically in eight steps. This is what makes the output traceable: every clause in the generated SQL points back to a specific declaration in the model.

• 01
  Load and flatten the model

  Resolves the perspective ID to a model name, loads the fully resolved model with all inheritance chains flattened into a single namespace. Inherited dimensions are tagged with their required join name.
• 02
  Build dimension and metric lookup maps

  Constructs two dictionaries keyed by name, one for dimensions and one for metrics, covering the base model and all inherited models. Role-prefixed names are indexed under their aliased names. These maps are the lookup table for every subsequent step.
• 03
  Collect required joins

  Walks the requested dimensions, each metric's requiredJoins list, the user's filter tree, and the model's default filters. Every dependency goes into a set; duplicates collapse automatically. This set is the sole input to step five.
• 04
  Check aggregate eligibility

  Evaluates each declared aggregate source against eligibility rules. The first one that passes is selected. If none passes, the full fact table is used. This step determines which table name appears in FROM and which formula strategy SELECT uses; nothing else changes downstream.
• 05
  Build JOIN clauses

  Emits a clause for each relationship in the required join set. Standard joins get a simple equality condition. SCD joins get temporal predicates matching event date against the dimension's validity window. Role-based joins get an AS alias clause with the alias recorded for downstream column references.
• 06
  Build SELECT clause

  Processes dimensions first, applying DATE_TRUNC wrapping when a granularity suffix like __month is present, then metrics. In aggregate mode, formulas are rebuilt from the aggregate's mapped columns. Time-period comparisons expand one metric into three expressions: current period, prior period, and percentage change.
• 07
  Build WHERE clause

  Applies the model's default filters first, then the user's filter tree. Validates every operator against an explicit allowlist (equality, comparison, IN/NOT IN, LIKE/NOT LIKE, null checks, and a between pair). Handles IN/NOT IN values in list, JSON string, and comma-separated formats. Appends the date range condition last, on the model's effective date field.
• 08
  Assemble final SQL

  Joins all clauses in order: SELECT, FROM, JOINs, WHERE, GROUP BY, LIMIT. The output is readable, not optimized: no reordering, no subquery folding, no optimizer hints. Every clause must be traceable by a human back to a specific model definition.

# SpeedDelivery - operations team querying 2025 annual delivery performance
perspective: "delivery_operations"
metrics:     ["total_revenue", "cancellation_rate"]
dimensions:  ["order_date__month", "zone_name"]
filters:     [{dimension: "delivery_status", op: "!=", value: "cancelled"}]
date_range:  ["2025-01-01", "2025-12-31"]

# Generated SQL  (daily_delivery_agg selected - user filter satisfies content restriction)
SELECT
  DATE_TRUNC('MONTH', daily_delivery_agg.order_date)  AS order_date__month,
  zone_dim.zone_name                                    AS zone_name,
  SUM(daily_delivery_agg.total_amount)                  AS total_revenue,
  SUM(daily_delivery_agg.cancelled_count)
    / NULLIF(SUM(daily_delivery_agg.delivery_count), 0) AS cancellation_rate
FROM analytics.daily_delivery_agg AS daily_delivery_agg
LEFT JOIN zone_dim
  ON daily_delivery_agg.zone_id = zone_dim.zone_id
WHERE
  daily_delivery_agg.delivery_status != 'cancelled'   # user filter - always emitted to WHERE
  # note: the aggregate already excludes cancelled rows by construction;
  # this filter is a no-op at runtime but is still written out for traceability
  AND daily_delivery_agg.order_date
        BETWEEN DATE '2025-01-01' AND DATE '2025-12-31'
GROUP BY
  DATE_TRUNC('MONTH', daily_delivery_agg.order_date),
  zone_dim.zone_name
LIMIT 1000

The SELECT builds dimension expressions first, applying DATE_TRUNC wrapping for granularity suffixes like __month, then metrics, using aggregate column formulas when the aggregate was selected or original fact formulas otherwise. The WHERE applies default model filters, then user filters, then the date range. Every step is mechanical: no inference, no optimization, no black box.

Tests: enforcing correctness at every path

A semantic layer with a bug in a formula is more dangerous than no semantic layer at all. Without a governed model, analysts know they are on their own and verify everything. With one, they trust the output and stop checking. The test suite is what earns that trust: not by checking that queries run, but by asserting the exact SQL produced for every scenario.

def test_aggregate_content_filter_fallback(self):
    # No status filter → aggregate cannot guarantee it excludes cancellations
    # → must fall back to the full fact table
    result = generate_sql(
        perspective="p_delivery_economics",
        metrics=["total_revenue"],
        dimensions=["zone_name"],
        filters=[],
        model_data=self.model_data,
    )
    expected = normalize("""
        SELECT
          zone_dim.zone_name AS zone_name,
          SUM(delivery_fact.total_amount) AS total_revenue
        FROM analytics.delivery_fact AS delivery_fact
        LEFT JOIN zone_dim ON delivery_fact.zone_id = zone_dim.zone_id
        GROUP BY zone_dim.zone_name
        LIMIT 1000
    """)
    self.assertEqual(normalize(result["sql"]), expected)

The tests do not assert that output "contains" a keyword. They assert the exact SQL string after whitespace normalization. A test that asserts partial output can pass while the generated SQL is subtly wrong: wrong join order, wrong alias, wrong filter placement. Exact output forces every clause to be correct. When a test fails, the diff between expected and actual shows precisely what changed.

The test suite covers: every filter operator tested independently; nested AND/OR/NOT filter groups including deep nesting; every aggregate eligibility rule including boundary cases; every date granularity level; SCD temporal predicates; role-based join aliasing; expression metric expansion; inheritance merging; formula qualification with string literal preservation; model filter propagation; and time-period comparison expressions (YoY, MoM, QoQ).

Serving AI agents

An LLM asked to "show revenue by region" will generate SQL. Without grounding, that SQL is a plausible guess: it might pick the wrong revenue column, join to the wrong region table, or filter on a status value that doesn't exist. The semantic layer exists to prevent exactly this failure mode. It provides the structured context that turns a language model from a guess generator into a reliable query author.

What the agent receives

The planner, the agent that decomposes a user's question into sub-tasks, does not see the full model. It sees a lean YAML summary produced by a server-side function that flattens inherited models and strips everything the planner doesn't need to make a plan. Formulas, join paths, physical column names, and SQL aliases are deliberately omitted: the planner picks metrics and dimensions by name; the query worker (a separate agent, downstream) resolves them to SQL. This keeps the planner's prompt small and prevents it from inventing SQL-level decisions that belong to the engine.

The summary is rendered as YAML and cached into the planner's system prompt. Here is the exact shape served to the planner for SpeedDelivery's delivery-operations perspective:

perspective_id: p_delivery_operations
name: Delivery Operations
description: Ops view of deliveries: volume, speed, cancellation.
source_table: analytics.delivery_fact
date_field: order_date
requires_time_window: true
models: [delivery, store, zone]

metrics:
  - name: total_revenue
    description: Sum of all order total amounts.
    aliases: ["gross revenue", "GMV"]
  - name: total_orders
    description: Count of all deliveries.
  - name: cancellation_rate
    description: Share of orders cancelled before completion.

dimensions:
  - name: order_date
    type: date
    description: Date the order was placed.
  - name: delivery_status
    description: Current delivery status.
    values: [pending, accepted, in_transit, delivered, cancelled]
  - name: zone_name
    description: Delivery zone the order was fulfilled in.

Every field in this payload is load-bearing:

• requires_time_window tells the planner whether it must emit an explicit date range on every query sub-task. If true and no window can be inferred from the user's question, the planner emits a clarifier; it does not invent a silent default.
• aliases let the planner resolve user phrasing: "show me GMV" resolves to total_revenue because "GMV" is declared as an alias.
• values on a dimension is the contract against hallucinated filter values. When the user says "cancelled deliveries", the planner's sub-task metadata uses the string cancelled verbatim, the exact value in the list, rather than guessing case or spelling.
• type: date on a dimension marks it as a valid time axis for granularity suffixes (order_date__month, order_date__week) and for the perspective's date field.

What's deliberately not in the payload: SQL formulas, physical column names, table aliases, join paths, and default filters. The planner doesn't need them. It names what it wants, and the downstream query worker + engine resolve the rest. This separation is why the planner prompt stays under a few thousand tokens even on perspectives with dozens of metrics and joined entities.

Disambiguation in practice

When a user asks "what's our revenue?", the planner sees two candidates in its YAML: total_revenue with description "Sum of all order total amounts" and aliases [gross revenue, GMV], and net_revenue with description "Revenue after platform fees and driver payouts" and aliases [take rate revenue]. The description plus alias list is what resolves the ambiguity: "GMV" routes cleanly to total_revenue, "take rate" to net_revenue. When the user's wording matches neither well, the planner emits a clarifier rather than guessing. The same pattern applies across every ambiguous concept in the model: active_users vs engaged_users, order_date vs delivery_date, consumer_region vs store_region.

Richer governance metadata, semantic types and free-text disambiguation hints, lives in the full model layer and is available to downstream agents and UI surfaces (e.g., a model-browser tooltip). It is intentionally kept out of the planner's prompt context to keep that prompt lean; the planner's job is to pick which metric, not to reason about aggregation semantics.

Canonical values prevent hallucination

When the user says "show me cancelled orders", the planner needs to produce a filter. Without canonical values, it might emit 'Cancelled', 'CANCELLED', or 'canceled', any of which would return zero rows with no error. With canonical values declared on the dimension ([pending, accepted, in_transit, delivered, cancelled]) and included in the YAML summary above, the planner's sub-task metadata carries cancelled verbatim. The values are not suggestions; they are the contract between the model and every consumer of it, human or machine.

Common anti-patterns

Building a semantic layer is straightforward. Building one that stays maintainable as it grows is harder. These are the patterns that cause the most pain, observed across multiple implementations.

How this compares to alternatives

Several tools occupy the semantic layer space. Each makes different trade-offs. Understanding where they differ helps clarify what this implementation prioritizes.

What this implementation prioritizes

Two-layer separation. Physical schema and business model are explicitly decoupled. Schema changes break at load time, not silently at query time. Most alternatives blur this boundary.

AI-native metadata. Disambiguation hints, semantic types, and canonical values are first-class fields, not afterthoughts. The model is designed to be consumed by LLMs as much as by humans.

Aggregate routing with safety guarantees. Pre-aggregated tables are declared with content filters and eligibility rules. The engine proves safety before routing; it never guesses. Most alternatives either skip aggregates entirely or require manual routing decisions.

Exact-output testing. The test suite asserts precise SQL for every scenario. Regressions are caught as diffs, not as user-reported bugs weeks later.

No vendor lock-in. The model is YAML files. The engine is a pure function. You can swap the query executor, change your warehouse, or integrate with any BI tool without rewriting definitions.

Getting started

You don't need to model your entire warehouse before the semantic layer provides value. Start small, prove the pattern, then expand. Here's the path that works.

• 01
  Pick one entity

  Choose a single fact table that your team queries frequently. Orders, transactions, events: something with clear business meaning and enough complexity to be interesting. Don't start with a dimension table; start with the thing people actually ask questions about.
• 02
  Define the physical layer first

  Declare the table, its columns, types, and primary key. For any enumerated column (status, category, tier), list the canonical values. This is the foundation everything else builds on. Get it right before moving to the model layer.
• 03
  Add three metrics

  Not thirty. Three. Pick the metrics your team asks about most: a count, a sum, and a ratio. Define each with a formula, semantic type, and disambiguation hint. Write exact-output tests for each. Ship it.
• 04
  Add one relationship

  Pick the most common dimension join, probably a customer, store, or product table. Declare the relationship in the physical layer. Add an inherits entry to the model. Verify the join resolves correctly in generated SQL.
• 05
  Use it for a week

  Run real queries against the model. Note what's missing: metrics you need, dimensions you expected, filters that don't exist. This feedback is more valuable than any upfront design session. Let usage drive expansion.
• 06
  Expand incrementally

  Add metrics as teams request them. Add relationships as queries require them. Build aggregates only after you've identified slow query patterns. Every addition goes through PR review and the test suite. The model grows with demand, not ahead of it.

File structure that scales

semantic_layer/
  physical/                   # warehouse structure
    tables/
      delivery_fact.yaml
      consumer_dim.yaml
      store_dim.yaml
    relationships/
      delivery_fact__consumer_dim.yaml
      delivery_fact__store_dim.yaml

  models/                     # business meaning
    dimensions/               # L1: reusable dimension entities
      consumer.yaml
      store.yaml
    core/                     # L2: fact-based entities
      delivery.yaml
    perspectives/             # L3: team-specific views
      delivery_operations.yaml
      delivery_finance.yaml

  tests/                      # exact-output test cases
    test_delivery_metrics.py
    test_aggregate_routing.py
    test_inheritance.py

Physical and model layers live in separate directories. Within models, the three inheritance levels (dimensions, core, perspectives) are explicit in the folder structure. Tests live alongside the definitions they validate. When someone asks "where is the revenue metric defined?", the answer is discoverable from the directory layout alone.

A model worth trusting

Every piece described here (entity definitions, relationship declarations, disambiguation hints, model filters, aggregate sources, inheritance chains) exists to answer the same question: why did this query produce this number?

The physical layer says which columns the data came from. The model layer says what they mean. The formula qualification pass shows how metric formulas map to table columns. The aggregate content filters document which rows are present. The SCD declarations show how historical joins are resolved. The test suite defines what "correct" looks like for every combination of these behaviors.

An analytics system that can answer "why?" is one that people will actually use to make decisions, and come back to investigate when a number surprises them. That is what a semantic layer is for.

Next: How SpeedDelivery's semantic layer detects schema drift and keeps itself current →