Creating metrics
Once you've created your semantic models, it's time to start adding metrics! Metrics can be defined in the same YAML files as your semantic models, or split into separate YAML files into any other subdirectories (provided that these subdirectories are also within the same dbt project repo)
The keys for metrics definitions are:
| Parameter | Description | Type |
|---|---|---|
name | Provide the reference name for the metric. This name must be unique amongst all metrics. | Required |
description | Describe your metric. | Optional |
type | Define the type of metric, which can be simple, ratio, cumulative, or derived. | Required |
type_params | Additional parameters used to configure metrics. type_params are different for each metric type. | Required |
config | Provide the specific configurations for your metric. | Optional |
label | The display name for your metric. This value will be shown in downstream tools. | Required |
filter | You can optionally add a filter string to any metric type, applying filters to dimensions, entities, or time dimensions during metric computation. Consider it as your WHERE clause. | Optional |
Here's a complete example of the metrics spec configuration:
metrics:
- name: metric name ## Required
description: same as always ## Optional
type: the type of the metric ## Required
type_params: ## Required
- specific properties for the metric type
config: here for `enabled` ## Optional
label: The display name for your metric. This value will be shown in downstream tools. ## Required
filter: | ## Optional
{{ Dimension('entity__name') }} > 0 and {{ Dimension(' entity__another_name') }} is not
null
This page explains the different supported metric types you can add to your dbt project.
📹 Learn about the dbt Semantic Layer with on-demand video courses!
Explore our dbt Semantic Layer on-demand course to learn how to define and query metrics in your dbt project.
Additionally, dive into mini-courses for querying the dbt Semantic Layer in your favorite tools: Tableau (beta), Hex, and Mode.
Conversion metrics​
Conversion metrics help you track when a base event and a subsequent conversion event occur for an entity within a set time period.
metrics:
- name: The metric name
description: The metric description
type: conversion
label: YOUR_LABEL
type_params: #
conversion_type_params:
entity: ENTITY
calculation: CALCULATION_TYPE
base_measure:
name: The name of the measure
fill_nulls_with: Set the value in your metric definition instead of null (such as zero)
join_to_timespine: true/false
conversion_measure:
name: The name of the measure
fill_nulls_with: Set the value in your metric definition instead of null (such as zero)
join_to_timespine: true/false
window: TIME_WINDOW
constant_properties:
- base_property: DIMENSION or ENTITY
conversion_property: DIMENSION or ENTITY
Cumulative metrics​
Cumulative metrics aggregate a measure over a given window. If no window is specified, the window will accumulate the measure over all of the recorded time period. Note that you will need to create the time spine model before you add cumulative metrics.
# Cumulative metrics aggregate a measure over a given window. The window is considered infinite if no window parameter is passed (accumulate the measure over all of time)
metrics:
- name: wau_rolling_7
owners:
- support@getdbt.com
type: cumulative
label: Weekly Active Users
type_params:
measure:
name: active_users
fill_nulls_with: 0
join_to_timespine: true
measure:
name: distinct_users
window: 7 days
Derived metrics​
Derived metrics are defined as an expression of other metrics. Derived metrics allow you to do calculations on top of metrics.
metrics:
- name: order_gross_profit
description: Gross profit from each order.
type: derived
label: Order Gross Profit
type_params:
expr: revenue - cost
metrics:
- name: order_total
alias: revenue
- name: order_cost
alias: cost
Ratio metrics​
Ratio metrics involve a numerator metric and a denominator metric. A constraint string can be applied to both the numerator and denominator or separately to the numerator or denominator.
metrics:
- name: cancellation_rate
owners:
- support@getdbt.com
type: ratio
label: Cancellation rate
type_params:
numerator: cancellations
denominator: transaction_amount
filter: |
{{ Dimension('customer__country') }} = 'MX'
- name: enterprise_cancellation_rate
owners:
- support@getdbt.com
type: ratio
type_params:
numerator:
name: cancellations
filter: {{ Dimension('company__tier' )}} = 'enterprise'
denominator: transaction_amount
filter: |
{{ Dimension('customer__country') }} = 'MX'
Simple metrics​
Simple metrics point directly to a measure. You may think of it as a function that takes only one measure as the input.
name— Use this parameter to define the reference name of the metric. The name must be unique amongst metrics and can include lowercase letters, numbers, and underscores. You can use this name to call the metric from the dbt Semantic Layer API.
metrics:
- name: cancellations
description: The number of cancellations
type: simple
label: Cancellations
type_params:
measure:
name: cancellations_usd # Specify the measure you are creating a proxy for.
fill_nulls_with: 0
filter: |
{{ Dimension('order__value')}} > 100 and {{Dimension('user__acquisition')}}
join_to_timespine: true
Filters​
A filter is configured using Jinja templating. Use the following syntax to reference entities, dimensions, and time dimensions in filters:
filter: |
{{ Entity('entity_name') }}
filter: |
{{ Dimension('primary_entity__dimension_name') }}
filter: |
{{ TimeDimension('time_dimension', 'granularity') }}
Further configuration​
You can set more metadata for your metrics, which can be used by other tools later on. The way this metadata is used will vary based on the specific integration partner
- Description — Write a detailed description of the metric.