Skip to main content

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

This article explains the different supported metric types you can add to your dbt project. The keys for metrics definitions are:

📹 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, Excel, Hex, and Mode.

Default granularity for metrics

It's possible to define a default time granularity for metrics if it's different from the granularity of the default aggregation time dimensions (metric_time). This is useful if your time dimension has a very fine grain, like second or hour, but you typically query metrics rolled up at a coarser grain. The granularity can be set using the time_granularity parameter on the metric, and defaults to day. If day is not available because the dimension is defined at a coarser granularity, it will default to the defined granularity for the dimension.

Example

You have a semantic model called orders with a time dimension called order_time. You want the orders metric to roll up to monthly by default; however, you want the option to look at these metrics hourly. You can set the time_granularity parameter on the order_time dimension to hour, and then set the time_granularity parameter in the metric to month.

semantic_models:
...
dimensions:
- name: order_time
type: time
type_params:
time_granularity: hour
measures:
- name: orders
expr: 1
agg: sum
metrics:
- name: orders
type: simple
label: Count of Orders
type_params:
measure:
name: orders
time_granularity: month -- Optional, defaults to day

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.

models/metrics/file_name.yml
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.

models/metrics/file_name.yml
# 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
type: cumulative
label: Weekly active users
type_params:
measure:
name: active_users
fill_nulls_with: 0
join_to_timespine: true
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.

models/metrics/file_name.yml
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 filter string can be applied to both the numerator and denominator or separately to the numerator or denominator.

models/metrics/file_name.yml
metrics:
- name: cancellation_rate
type: ratio
label: Cancellation rate
type_params:
numerator: cancellations
denominator: transaction_amount
filter: |
{{ Dimension('customer__country') }} = 'MX'
- name: enterprise_cancellation_rate
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.

Note: If you've already defined the measure using the create_metric: True parameter, you don't need to create simple metrics. However, if you would like to include a constraint on top of the measure, you will need to create a simple type metric.

models/metrics/file_name.yml
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')}} is not null
join_to_timespine: true

Filters

A filter is configured using Jinja templating. Use the following syntax to reference entities, dimensions, time dimensions, or metrics in filters.

Refer to Metrics as dimensions for details on how to use metrics as dimensions with metric filters:

models/metrics/file_name.yml
filter: | 
{{ Entity('entity_name') }}

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

filter: |
{{ TimeDimension('time_dimension', 'granularity') }}

filter: |
{{ Metric('metric_name', group_by=['entity_name']) }} # Available in v1.8 or with versionless dbt Cloud.

For example, if you want to filter for the order date dimension grouped by month, use the following syntax:

filter: |  
{{ TimeDimension('order_date', 'month') }}

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