Building the dbt Semantic Layer

This skill guides the creation and modification of dbt Semantic Layer components: semantic models, entities, dimensions, and metrics.

Semantic models - Metadata configurations that define how dbt models map to business concepts
Entities - Keys that identify the grain of your data and enable joins between semantic models
Dimensions - Attributes used to filter or group metrics (categorical or time-based)
Metrics - Business calculations defined on top of semantic models (e.g., revenue, order count)

Additional Resources

Time Spine Setup - Required for time-based metrics and aggregations
Best Practices - Design patterns and recommendations for semantic models and metrics

[!NOTE] This skill contains guidance for the new dbt semantic layer YAML spec, valid for dbt 1.12.0 and above. If the user is using a different version of dbt, you can use the migration guide to help them migrate to the new spec and add new components to their semantic layer. Ask the user if they want to migrate to the new spec before proceeding.

Entry Points

Users may ask questions related to building metrics with the semantic layer in a few different ways. Here are the common entry points to look out for:

Business Question First

When the user describes a metric or analysis need (e.g., "I need to track customer lifetime value by segment"):

Search project models or existing semantic models by name, description, and column names for relevant candidates
Present top matches with brief context (model name, description, key columns)
User confirms which model(s) / semantic models to build on / extend / update
Work backwards from users need to define entities, dimensions, and metrics

Model First

When the user specifies a model to expose (e.g., "Add semantic layer to

customers

model"):

Read the model SQL and existing YAML config
Identify the grain (primary key / entity)
Suggest dimensions based on column types and names
Ask what metrics the user wants to define

Both paths converge on the same implementation workflow.

Open Ended

User asks to build the semantic layer for a project or models that are not specified. ("Build the semantic layer for my project")

Identify high importance models in the project
Suggest some metrics and dimensions for those models
Ask the user if they want to create more metrics and dimensions or if there are any other models they want to build the semantic layer on

Implementation Workflow

Step 1: Enable Semantic Model

Decide which dbt model to build the semantic model on. Add

semantic_model:

block to the model's YAML with

enabled: true

. Set

agg_time_dimension

to the primary time column. If the model does not have a time column, warn user that the model cannot contain metrics that are time-based. Ask the user if they want to create a derived time dimension.

Example YAML:

yaml

models:
  - name: orders
    semantic_model:
      enabled: true # enable the semantic model
    agg_time_dimension: ordered_at # set the primary time column (this is a column in the dbt model)

Step 2: Define Entities

Identify the primary key column (check for

_id

suffix, uniqueness tests, or explicit config). Add

entity: \n\t type: primary

block to that column's entry. If the model has foreign keys, define those as

entity: type: foreign

yaml

models:
  - name: orders
    semantic_model:
      enabled: true # enable the semantic model
    agg_time_dimension: ordered_at # set the primary time column (this is a column in the dbt model)
    columns:
      - name: order_id # this is the primary key column of the model
        entity: 
          type: primary 
          name: order
      - name: customer_id # this is a foreign key column of the model
        entity: 
          type: foreign 
          name: customer

Step 3: Define Dimensions

Scan columns for dimension candidates. These would be useful columns to group by when querying a metrics:

Time columns →
```
dimension: type: time
```
with appropriate
```
granularity
```
(set at the column level)
Categorical columns (strings, booleans) →
```
dimension: type: categorical
```

Present suggested dimensions to user for confirmation.

Example YAML:

yaml

models:
  - name: orders
    semantic_model:
      enabled: true # enable the semantic model
    agg_time_dimension: ordered_at # set the primary time column (this is a column in the dbt model)
    columns:
      
      - name: order_id
        entity: 
          type: primary 
          name: order
      
      - name: customer_id
        entity: 
          type: foreign 
          name: customer
      
      - name: ordered_at
        granularity: day # set the granularity of the time column
        dimension:
          type: time

      - name: order_status
        dimension:
          type: categorical

Step 4: Define Metrics

Create some simple metrics for the model. For each metric, collect: name, description, label, aggregation type, and expression. Support metric types:

simple

derived

cumulative

conversion

ratio

yaml

models:
  - name: orders
    semantic_model:
      enabled: true # enable the semantic model
    agg_time_dimension: ordered_at # set the primary time column (this is a column in the dbt model)
    columns:
      
      - name: order_id
        entity: 
          type: primary 
          name: order
      
      - name: customer_id
        entity: 
          type: foreign 
          name: customer
      
      - name: ordered_at
        granularity: day # set the granularity of the time column
        dimension:
          type: time

      - name: order_status
        dimension:
          type: categorical

    metrics:
      - name: order_count
        type: simple
        agg: count
        expr: 1

      - name: total_revenue
        type: simple
        agg: sum
        expr: amount

      - name: average_order_value
        type: simple
        agg: average
        expr: amount

Validation

After writing YAML, validate in two stages:

Parse Validation: Run
```
dbt parse
```
to confirm YAML syntax and references
Semantic Layer Validation:
- ```
dbt sl validate
```
  (dbt Cloud CLI / dbt Fusion)
- ```
mf validate-configs
```
  (MetricFlow CLI)

Do not consider work complete until both validations pass.

Editing Existing Components

When modifying existing semantic layer config:

Check if the model's YAML already has
```
semantic_model:
```
block
Read existing entities, dimensions, and metrics before making changes
Preserve all existing YAML content not being modified
After edits, run full validation to ensure nothing broke

YAML Format Reference

Derived Dimensions and Entities

if the user wants to create a derived dimension or entity that is not a column within the dbt model, then we can use the

derived_semantics

block.

yaml

    derived_semantics:
      dimensions:
        - name: order_size_bucket
          type: categorical
          expr: case when amount > 100 then 'large' else 'small' end
          label: "Order Size"

      entities:
        - name: order_customer_key
          type: foreign
          expr: "order_id || '-' || customer_id"

Advanced Metric Examples

All simple metrics are defined at the model level under the

metrics

key. Advanced metrics that refer to simple metrics within the same model are defined within a model's YAML entry the

models.metrics

key. Advanced metrics that refer to simple metrics across different models are defined at the top level under the

metrics

key.

Derived Metrics

yaml

      - name: revenue_per_order
        type: derived
        description: Average revenue per order
        label: Revenue per Order
        expr: total_revenue / total_orders
        input_metrics:
          - name: total_revenue
          - name: total_orders

      # With offset window
      - name: revenue_growth
        type: derived
        expr: total_revenue - revenue_last_week
        input_metrics:
          - name: total_revenue
          - name: total_revenue
            alias: revenue_last_week
            offset_window: 1 week
            filter: "{{ Dimension('order__status') }} = 'completed'"

Cumulative Metrics

yaml

      - name: cumulative_revenue
        type: cumulative
        description: Running total of revenue
        label: Cumulative Revenue
        input_metric: total_revenue
        grain_to_date: week
        period_agg: first

      # With window
      - name: trailing_7d_revenue
        type: cumulative
        input_metric: total_revenue
        window: 7 days

Ratio Metrics

yaml

      - name: conversion_rate
        type: ratio
        description: Orders divided by visits
        label: Conversion Rate
        numerator: total_orders
        denominator: total_visits

      # With filters
      - name: premium_conversion_rate
        type: ratio
        numerator:
          name: total_orders
          filter: "{{ Dimension('order__customer_segment') }} = 'premium'"
          alias: premium_orders
        denominator: total_visits

Conversion Metrics

yaml

      - name: signup_to_purchase
        type: conversion
        description: Rate of signups converting to purchase
        label: Signup to Purchase
        entity: customer
        calculation: conversion_rate
        base_metric: signups
        conversion_metric: purchases
        window: 7 days
        constant_properties:
          - base_property: signup_channel
            conversion_property: purchase_channel

Top-level Metrics (Cross-model)

yaml

# For metrics depending on multiple semantic models
metrics:
  - name: cross_model_ratio
    type: ratio
    numerator:
      name: metric_from_model_a
      filter: "{{ Dimension('entity__dim') }} > 10"
    denominator:
      name: metric_from_model_b
    config:
      group: example_group
      tags:
        - example_tag
      meta:
        owner: "@someone"

Filtering Metrics

Filters can be added to simple metrics or metric inputs to advanced metrics. The format of a filters is a Jinja template that can reference entities, dimensions, and metrics, a boolean operator, and a value. Ensure the value matches the type of the column being filtered.

Examples

filter: | {{ Entity('entity_name') }} = 'value'

filter: |
{{ Dimension('primary_entity__dimension_name') }} > 100

filter: |
{{ TimeDimension('time_dimension', 'granularity') }} > '2026-01-01'

filter: |
{{ Metric('metric_name', group_by=['entity_name']) }} > 100

Key Formatting Rules

```
semantic_model:
```
block at model level with
```
enabled: true
```
```
agg_time_dimension:
```
at model level (not nested under
```
semantic_model
```
)
```
entity:
```
and
```
dimension:
```
on columns (can use shorthand or full form)
```
granularity:
```
required at column level for time dimensions
```
metrics:
```
array at model level for single-model metrics
Top-level
```
metrics:
```
key for cross-model metrics (derived, ratio, cumulative, conversion only)

Best Practices

Start with entities - Identify the grain before defining dimensions or metrics
Use shorthand where possible -
```
entity: primary
```
instead of full nested form for simple cases
Name metrics for business users - Use clear
```
label
```
values for non-technical users
Keep metrics close to their data - Simple metrics on their semantic model; top-level only for cross-model
Set appropriate granularity - Match the actual data grain (usually
```
day
```
)

Common Pitfalls

Pitfall	Fix
Missing `agg_time_dimension`	Every semantic model needs a default time dimension
`granularity` inside `dimension:` block	Must be at column level
Defining a column as both an entity and a dimension	A column can only be one or the other
Simple metrics in top-level `metrics:`	Top-level is only for cross-model metrics
Using `window` and `grain_to_date` together	Cumulative metrics can only have one
Missing `input_metrics` on derived metrics	Must list metrics used in `expr`

building-dbt-semantic-layer

NPX Install

Tags

SKILL.md Content

Building the dbt Semantic Layer

Additional Resources

Entry Points

Business Question First

Model First

Open Ended

Implementation Workflow

Step 1: Enable Semantic Model

Step 2: Define Entities

Step 3: Define Dimensions

Step 4: Define Metrics

Validation

Editing Existing Components

YAML Format Reference

Derived Dimensions and Entities

Advanced Metric Examples

Derived Metrics

Cumulative Metrics

Ratio Metrics

Conversion Metrics

Top-level Metrics (Cross-model)

Filtering Metrics

Key Formatting Rules

Best Practices

Common Pitfalls