dbt Semantic Layer and Metrics

Semantic Layer Architecture

Semantic Layer Pipeline

Formal Definitions

Detailed Explanation

The dbt Semantic Layer provides a centralized system for defining business metrics, ensuring consistent definitions across all analytics consumers. It eliminates metric inconsistencies by defining calculations once and serving them to multiple downstream tools.

Metric Types

Code Examples

Semantic Model Definition

# models/marts/fct_orders.yml
version: 2

semantic_models:
  - name: orders
    defaults:
      agg_time_dimension: order_date
    description: "Order transactions fact table"
    model: ref('fct_orders')
    
    entities:
      - name: order_id
        type: primary
      - name: customer_id
        type: foreign
      - name: product_id
        type: foreign
    
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: order_status
        type: categorical
      - name: customer_segment
        type: categorical
      - name: product_category
        type: categorical
    
    measures:
      - name: order_count
        agg: count
        expr: order_id
        description: "Count of orders"
      
      - name: revenue
        agg: sum
        expr: amount
        description: "Total order amount"
      
      - name: unique_customers
        agg: count_distinct
        expr: customer_id
        description: "Distinct customers who placed orders"

Simple Metric

# metrics/order_metrics.yml
version: 2

metrics:
  - name: total_revenue
    type: simple
    type_params:
      measure: revenue
    description: "Total revenue from all orders"
    filter: |
      {{ Dimension('order__order_status') }} != 'cancelled'
    label: "Total Revenue"
    round: 2
  
  - name: total_orders
    type: simple
    type_params:
      measure: order_count
    description: "Total number of orders"
    label: "Total Orders"
  
  - name: unique_customers
    type: simple
    type_params:
      measure: unique_customers
    description: "Number of unique customers"
    label: "Unique Customers"

Derived Metric

# metrics/order_metrics.yml
metrics:
  - name: average_order_value
    type: derived
    type_params:
      expr: total_revenue / total_orders
      metrics:
        - name: total_revenue
          filter: |
            {{ Dimension('order__order_status') }} != 'cancelled'
        - name: total_orders
          filter: |
            {{ Dimension('order__order_status') }} != 'cancelled'
    description: "Average revenue per order"
    label: "Average Order Value"
    round: 2
  
  - name: revenue_per_customer
    type: derived
    type_params:
      expr: total_revenue / unique_customers
      metrics:
        - name: total_revenue
        - name: unique_customers
    description: "Revenue per unique customer"
    label: "Revenue per Customer"
    round: 2

Cumulative Metric

# metrics/cumulative_metrics.yml
metrics:
  - name: cumulative_revenue
    type: cumulative
    type_params:
      measure: revenue
      window: 7 day
      grain_to_date: month
    description: "7-day rolling cumulative revenue"
    label: "Cumulative Revenue (7-day)"
    round: 2
  
  - name: cumulative_orders
    type: cumulative
    type_params:
      measure: order_count
      window: 30 day
    description: "30-day rolling cumulative orders"
    label: "Cumulative Orders (30-day)"

Conversion Metric

# metrics/conversion_metrics.yml
metrics:
  - name: conversion_rate
    type: conversion
    type_params:
      numerator: completed_orders
      denominator: total_orders
      window: 7 day
    description: "Order completion rate within 7 days"
    label: "Conversion Rate"
    round: 4
  
  - name: completed_orders
    type: simple
    type_params:
      measure: order_count
    filter: |
      {{ Dimension('order__order_status') }} = 'completed'
  
  - name: total_orders
    type: simple
    type_params:
      measure: order_count

Querying Metrics

-- Query via MetricFlow SQL
SELECT
    order_date,
    customer_segment,
    {{ metric('total_revenue') }},
    {{ metric('total_orders') }},
    {{ metric('average_order_value') }}
FROM {{ metric('total_revenue') }}
GROUP BY 1, 2
ORDER BY 1, 2

Using the Semantic Layer API

# Query via Python API
from dbt_semantic_interfaces.protocols.semantic_manifest import SemanticManifest

# Connect to the Semantic Layer
semantic_layer = SemanticLayerClient(
    host="https://semantic-layer.getdbt.com",
    auth_token="your_token",
    environment_id="123"
)

# Query metrics
result = semantic_layer.query(
    metrics=["total_revenue", "total_orders"],
    group_by=["order_date", "customer_segment"],
    where="order_status != 'cancelled'",
    order_by=["order_date"]
)

# Process results
for row in result.data:
    print(f"Date: {row['order_date']}, Segment: {row['customer_segment']}, "
          f"Revenue: ${row['total_revenue']:,.2f}, Orders: {row['total_orders']}")

Semantic Model with Multiple Measures

# models/marts/fct_events.yml
semantic_models:
  - name: events
    model: ref('fct_events')
    
    entities:
      - name: event_id
        type: primary
      - name: user_id
        type: foreign
      - name: session_id
        type: foreign
    
    dimensions:
      - name: event_date
        type: time
        type_params:
          time_granularity: day
      - name: event_type
        type: categorical
      - name: platform
        type: categorical
    
    measures:
      - name: event_count
        agg: count
        expr: event_id
      
      - name: unique_users
        agg: count_distinct
        expr: user_id
      
      - name: session_count
        agg: count_distinct
        expr: session_id
      
      - name: avg_session_duration
        agg: average
        expr: session_duration_seconds
      
      - name: total_page_views
        agg: sum
        expr: page_views

Comparison: Metrics Approaches

Performance Metrics

Best Practices

1. Define semantic models first - Map all dbt models to semantic models
2. Use consistent naming - snake_case for all metric names
3. Document everything - Descriptions for all metrics and dimensions
4. Start simple - Begin with simple metrics, build complexity gradually
5. Validate definitions - Use dbt ls --resource-type metric to verify
6. Version control - Track metric changes in Git
7. Test metrics - Validate metric values against known baselines
8. Monitor performance - Track query latency and optimize as needed

See Also

• Exposures and Semantic Layer — Defining downstream consumers
• dbt Projects — Project configuration
• dbt Best Practices — Documentation patterns
• dbt Cloud — Semantic Layer in dbt Cloud