building-dbt-semantic-layer

• [Time Spine Setup](references/time-spine.md) - Required for time-based metrics and aggregations
• [Best Practices](references/best-practices.md) - 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](https://docs.getdbt.com/docs/build/latest-metrics-spec) 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.

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"):

1. Search project models or existing semantic models by name, description, and column names for relevant candidates
2. Present top matches with brief context (model name, description, key columns)
3. User confirms which model(s) / semantic models to build on / extend / update
4. 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"):

1. Read the model SQL and existing YAML config
2. Identify the grain (primary key / entity)
3. Suggest dimensions based on column types and names
4. 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")

1. Identify high importance models in the project
2. Suggest some metrics and dimensions for those models
3. 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

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: avg

expr: amount

```

After writing YAML, validate in two stages:

1. Parse Validation: Run dbt parse to confirm YAML syntax and references
2. 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.

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

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"

```

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

• 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)

• 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)

| 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 |