Adrienne Vermorel

Defining Metrics in dbt: Best Practices and Patterns

February 13, 2026

You’ve set up your semantic models in dbt with entities, dimensions, and measures. Now comes the part where most teams get stuck: writing metrics that actually work.

The good news? Metric design follows predictable patterns. Once you understand the five metric types and a few organizational principles, you can express almost any business calculation. This tutorial walks through each metric type with practical examples, then covers the naming conventions and organizational patterns that keep metrics maintainable as your project grows.

The Five Metric Types

MetricFlow supports five metric types. Each serves a specific purpose, and choosing the right one determines whether your calculation is correct.

Simple Metrics

Simple metrics reference a single measure with one aggregation. They’re the building block for everything else.

metrics:
  - name: order_total
    label: "Total Order Value"
    description: "Sum of all order values"
    type: simple
    type_params:
      measure: order_value

That’s it. The measure order_value already defines its aggregation (likely sum), so the metric just references it. Use simple metrics for straightforward counts and sums: total revenue, order count, active users.

Cumulative Metrics

Cumulative metrics aggregate over time windows. Think weekly active users, month-to-date revenue, or rolling 30-day averages.

metrics:
  - name: weekly_active_users
    label: "Weekly Active Users"
    description: "Unique users active in the past 7 days"
    type: cumulative
    type_params:
      measure: active_users
      window: 7 days

Two parameters control the behavior:

window creates a sliding window (7 days, 30 days)
grain_to_date resets at period boundaries (month-to-date, year-to-date)

metrics:
  - name: mtd_revenue
    label: "Month-to-Date Revenue"
    type: cumulative
    type_params:
      measure: revenue
      grain_to_date: month

One requirement catches people off guard: queries using cumulative metrics with window must include metric_time as a dimension. Without a time dimension, the sliding window has no anchor point.

Derived Metrics

Derived metrics perform calculations using other metrics. They’re essential for profit margins, growth rates, and period-over-period comparisons.

metrics:
  - name: gross_profit
    label: "Gross Profit"
    type: derived
    type_params:
      expr: revenue - cost_of_goods_sold
      metrics:
        - name: revenue
        - name: cost_of_goods_sold

The expr parameter accepts any valid SQL expression using the referenced metric names. For period-over-period calculations, use offset_window with an alias:

metrics:
  - name: revenue_growth_wow
    label: "Revenue Growth % W/W"
    type: derived
    type_params:
      expr: (revenue - revenue_last_week) / revenue_last_week * 100
      metrics:
        - name: revenue
        - name: revenue
          offset_window: 7 days
          alias: revenue_last_week

The alias lets you reference the same metric at different time offsets within one expression.

Ratio Metrics

Ratio metrics divide a numerator by a denominator. Why not use a derived metric? Because ratios have a mathematical trap: the sum of ratios isn’t the ratio of sums.

If Store A has 50% conversion rate and Store B has 25% conversion rate, the combined rate isn’t 37.5%. It depends on the volume at each store. Ratio metrics handle this correctly by summing numerator and denominator separately before dividing.

metrics:
  - name: conversion_rate
    label: "Conversion Rate"
    type: ratio
    type_params:
      numerator: conversions
      denominator: sessions

You can apply filters to just the numerator or denominator:

metrics:
  - name: mobile_conversion_rate
    label: "Mobile Conversion Rate"
    type: ratio
    type_params:
      numerator:
        name: conversions
        filter:
          - "{{ Dimension('session__device_type') }} = 'mobile'"
      denominator: sessions

Conversion Metrics

Conversion metrics track when a base event leads to a conversion event within a time window. Think funnel analysis: visits to purchases, signups to activations.

metrics:
  - name: visit_to_purchase_rate
    label: "Visit to Purchase Rate"
    type: conversion
    type_params:
      entity: user
      calculation: conversion_rate
      base_measure: visits
      conversion_measure: purchases
      window: 7 days

The entity parameter defines the join key linking base and conversion events. The calculation parameter can be conversion_rate (percentage) or conversions (count).

For stricter matching, constant_properties ensures attributes match between events:

type_params:
  entity: user
  calculation: conversion_rate
  base_measure: visits
  conversion_measure: purchases
  window: 7 days
  constant_properties:
    - base_property: "{{ Dimension('visit__device_type') }}"
      conversion_property: "{{ Dimension('purchase__device_type') }}"

This counts a conversion only if the user’s device type matches between the visit and purchase.

Naming Conventions That Scale

Consistent naming makes metrics discoverable.

Names vs Labels

The name field is for code. The label field is for humans. Keep them distinct:

name: revenue_growth_mom
label: "Revenue Growth % M/M"

Names use snake_case, all lowercase. Labels use proper capitalization and can include symbols like %.

Patterns by Metric Type

Different metric types benefit from different naming patterns:

Type	Pattern	Example
Simple	`{noun}_{aggregation}`	`order_count`, `revenue_sum`
Cumulative	`{time_period}_{metric}`	`weekly_active_users`, `mtd_revenue`
Derived	`{metric}_growth_{period}`	`revenue_growth_mom`, `orders_growth_yoy`
Ratio	`{numerator}_per_{denominator}`	`revenue_per_customer`, `orders_per_session`
Conversion	`{action}_to_{action}_rate`	`visit_to_buy_rate`, `signup_to_activate_rate`

Be Specific

Vague names cause confusion. revenue could mean gross revenue, net revenue, or adjusted revenue. response_time could be milliseconds or seconds.

Better:

gross_revenue
net_revenue_after_refunds
response_time_seconds
response_time_p95_ms

Use consistent prefixes to group related metrics:

# Revenue family
revenue_total
revenue_per_order
revenue_growth_mom
revenue_mtd

# Customer family
customer_count
customer_lifetime_value
customer_acquisition_cost
customer_retention_rate

When someone searches for “revenue,” they find all revenue-related metrics together.

Organizing Metrics in Large Projects

Small projects can define semantic models and metrics in the same file. Large projects need more structure.

Co-located Structure

For projects with fewer than 20 metrics, keep everything together:

models/
  marts/
    mrt__finance__orders.sql
    mrt__finance__orders.yml    # semantic model + metrics
    mrt__sales__customers.sql
    mrt__sales__customers.yml   # semantic model + metrics

The YAML file contains both the semantic model definition and any metrics built from it.

Parallel Sub-folder Structure

For larger projects, separate semantic models from metrics and organize by domain:

models/
  marts/
    mrt__finance__orders.sql
    mrt__sales__customers.sql
  semantic_models/
    orders.yml
    customers.yml
  metrics/
    revenue_metrics.yml
    customer_metrics.yml
    conversion_metrics.yml

This structure scales because metrics often span multiple semantic models. A customer_lifetime_value metric might reference measures from both orders and customers semantic models. Putting it in a dedicated metrics folder avoids arbitrary placement decisions.

One Primary Entity Per Semantic Model

Each semantic model should have exactly one primary entity. This constraint keeps the semantic graph navigable.

semantic_models:
  - name: orders
    defaults:
      agg_time_dimension: ordered_at
    model: ref('mrt__finance__orders')
    entities:
      - name: order
        type: primary
      - name: customer
        type: foreign
      - name: product
        type: foreign

The primary entity (order) identifies what each row represents. Foreign entities (customer, product) enable joins to other semantic models.

Complex Metric Patterns

Real-world metrics require more than simple aggregations.

Period-Over-Period Comparisons

The offset_window parameter shifts a metric back in time:

metrics:
  - name: bookings_vs_last_week
    label: "Bookings Change vs Last Week"
    type: derived
    type_params:
      expr: bookings - bookings_7_days_ago
      metrics:
        - name: bookings
        - name: bookings
          offset_window: 7 days
          alias: bookings_7_days_ago

For percentage change:

expr: (bookings - bookings_7_days_ago) / NULLIF(bookings_7_days_ago, 0) * 100

The NULLIF prevents division by zero when last week had no bookings.

Filtered Metrics

Apply filters using Jinja templating:

metrics:
  - name: enterprise_revenue
    type: simple
    type_params:
      measure: revenue
    filter:
      - "{{ Dimension('customer__segment') }} = 'enterprise'"

For time dimensions with specific granularity:

filter:
  - "{{ TimeDimension('order__ordered_at', 'month') }} >= '2024-01-01'"

Handling Nulls in Time Series

Metrics with no data for a time period return null, which creates gaps in charts. Two parameters fix this:

type_params:
  measure:
    name: revenue
    fill_nulls_with: 0
    join_to_timespine: true

join_to_timespine: true ensures every date appears in results. fill_nulls_with: 0 replaces nulls with zeros. Together, they produce complete time series without gaps.

Testing and Validation

MetricFlow validates configurations at three levels:

Parsing validation: Does the YAML follow the schema?
Semantic validation: Are names unique? Do references exist? Is there exactly one primary entity?
Data platform validation: Do the referenced columns exist in physical tables?

Run all validations with:

# dbt Cloud
dbt sl validate

# dbt Core
mf validate-configs

Add --verbose-issues --show-all for detailed output when debugging failures.

CI Integration

Add validation to your CI pipeline to catch breaking changes:

- name: Validate semantic layer
  run: dbt sl validate

This prevents merging PRs that break metric definitions.

Anti-Patterns to Avoid

One-Off Models for Metrics

Don’t create a new dbt model just to define a metric:

-- Bad: models/metrics/monthly_revenue.sql
SELECT
  DATE_TRUNC('month', ordered_at) AS month,
  SUM(amount) AS monthly_revenue
FROM {{ ref('mrt__finance__orders') }}
GROUP BY 1

This duplicates transformation logic. Instead, define the metric on your existing orders mart model and let MetricFlow handle the aggregation.

Sum of Ratios

Never average percentages or rates:

# Wrong: will give mathematically incorrect results
- name: avg_conversion_rate
  type: simple
  type_params:
    measure: conversion_rate  # This is already a percentage per row

Use a ratio metric with separate numerator and denominator measures. The semantic layer computes the ratio after aggregating the components.

Hardcoded Filters in Measures

Measures with embedded filters reduce flexibility:

# Inflexible
measures:
  - name: enterprise_revenue
    expr: CASE WHEN segment = 'enterprise' THEN amount END
    agg: sum

Better to define a general measure and apply filters at the metric level:

measures:
  - name: revenue
    expr: amount
    agg: sum

metrics:
  - name: enterprise_revenue
    type: simple
    type_params:
      measure: revenue
    filter:
      - "{{ Dimension('customer__segment') }} = 'enterprise'"

Now you can create smb_revenue, startup_revenue, or any other segment without defining new measures.

Missing Descriptions

Metrics without descriptions become mysteries:

# What does this measure?
- name: arr
  type: simple
  type_params:
    measure: arr_value

Always include description and label:

- name: arr
  label: "Annual Recurring Revenue"
  description: "Sum of annualized contract values for active subscriptions, excluding one-time fees"
  type: simple
  type_params:
    measure: arr_value

Six months from now, someone will thank you.

What to Do Next

Simple metrics for your core business measures (revenue, orders, users) are the right starting point. Get those working in your BI tools before adding complexity.

From there, a single derived metric like a period-over-period comparison or growth rate will exercise the offset_window pattern that handles most reporting needs.

Your existing dbt models likely contain embedded business logic worth migrating: hardcoded filters, calculated percentages, and pre-aggregated tables are all candidates for proper semantic layer metrics.

You don’t need every possible metric defined upfront. A few well-structured examples give your team patterns to follow as new requirements emerge.