Metric Types#
A metric is simple, ratio, derived, cumulative or conversion, and its inputs, measures and dimensions change how it aggregates: an offset, a join to the time spine, a semi-additive measure, a dimension with a validity window, a filter on another metric's value. Each section on this page has the YAML, a query, and its rows. The examples extend the sales semantic project of Define metrics with three more feature groups:
| Feature group | Primary key | Event time | Other features |
|---|---|---|---|
visits | visit_id | visit_ts | customer_id |
balances | snapshot_id | snapshot_ts | customer_id, balance (double) |
customer_tiers | customer_id, valid_from | tier (string), valid_to (date) |
The orders feature group has eight orders from January to March 2026, of four customers in SE, DE and US. The definitions go in any YAML file under model-paths, such as models/metric_types.yml, next to models/semantic.yml.
Which metrics need metric_time and a time spine#
| Metric | Every query groups by metric_time | Needs a time spine |
|---|---|---|
simple | No | No |
ratio, derived | With an input at an offset_window or offset_to_grain | With an offset |
cumulative | With a window or a grain_to_date | Yes |
conversion | No | No |
A measure input with join_to_timespine: true | No | Yes |
A semi-additive measure, an SCD dimension, a Metric() filter | No | No |
A metric that reads such a metric, a derived metric over a cumulative one for example, needs metric_time too. SemanticMetric.requires_metric_time is set on these metrics, and the query workbench in the UI puts metric_time first in the group-bys when one is picked. The agg time dimension of the metric's measures, such as order__order_ts__day, counts as metric_time. A query without either is refused:
Error: Semantic layer request on 'sales' failed (HTTP 400, SEMANTIC_LAYER_INVALID_QUERY): metric revenue_7d accumulates over a window, so the query must group by metric_time or its agg time dimension
A metric that needs a time spine fails the compile with a TIME_SPINE error when none is bound, see Without a time spine.
Simple metrics#
A simple metric aggregates one measure, type_params.measure:
metrics:
- name: revenue
label: Revenue
type: simple
type_params:
measure: revenue
- name: orders
label: Orders
type: simple
type_params:
measure: order_count
hops semantic query sales --metrics revenue,orders --group-by metric_time__month \
--order-by metric_time__month -o csv
measure is a measure's name, or a mapping with its name and any of filter, alias, join_to_timespine and fill_nulls_with.
Metrics without a measure#
A simple metric can aggregate its own expr over a semantic model, with type_params.metric_aggregation_params in place of a measure:
metrics:
- name: large_orders
label: Orders of 50 or more
type: simple
type_params:
expr: CASE WHEN amount >= 50 THEN 1 ELSE 0 END
metric_aggregation_params:
semantic_model: orders
agg: sum
hops semantic query sales --metrics large_orders --group-by customer__country \
--order-by customer__country -o csv
metric_aggregation_params key | Value |
|---|---|
semantic_model | The semantic model to aggregate; required. |
agg | An aggregation of Define metrics, such as sum or count_distinct; required. |
agg_params | percentile, use_discrete_percentile and use_approximate_percentile, as on a measure. |
agg_time_dimension | The time dimension that is the metric's metric_time; the semantic model's defaults.agg_time_dimension without it. |
non_additive_dimension | As on a semi-additive measure. |
Without expr, the metric aggregates the feature of its own name. Cumulative and conversion metrics read such a metric with cumulative_type_params.metric, base_metric and conversion_metric in place of a measure.
Ratio metrics#
A ratio metric divides one metric by another:
metrics:
- name: revenue_per_order
label: Average order value
type: ratio
type_params:
numerator: revenue
denominator: orders
hops semantic query sales --metrics revenue_per_order --group-by metric_time__month \
--order-by metric_time__month -o csv
numerator and denominator are metric names, or mappings with a name and any of filter, alias and an offset. The two metrics are aggregated at the query's group-bys before the division, so a ratio is never an average of ratios. A ratio is a DOUBLE, whatever the types of the two metrics.
Derived metrics#
A derived metric is an expr over other metrics, listed in type_params.metrics, each with an optional alias and filter:
metrics:
- name: completed_revenue_per_order
label: Completed revenue per order
type: derived
type_params:
expr: completed_revenue / orders
metrics:
- name: revenue
alias: completed_revenue
filter: "{{ Dimension('order__status') }} = 'completed'"
- name: orders
hops semantic query sales --metrics completed_revenue_per_order --group-by customer__country \
--order-by customer__country -o csv
An expr that divides two integers, such as orders / customers over two counts, gives a fraction, a DOUBLE; a CAST the expr writes, such as CAST(orders / customers AS BIGINT), sets the type instead. A division of decimals follows Trino's rule for the type of a decimal quotient.
Offsets#
An input with offset_window reads the metric a window earlier, and one with offset_to_grain reads it at the start of the period of a grain:
metrics:
- name: revenue_last_month
label: Revenue a month earlier
type: derived
type_params:
expr: revenue_1_month_ago
metrics:
- name: revenue
alias: revenue_1_month_ago
offset_window: 1 month
- name: revenue_change_mom
label: Revenue change on the month before
type: derived
type_params:
expr: revenue - revenue_1_month_ago
metrics:
- name: revenue
- name: revenue
alias: revenue_1_month_ago
offset_window: 1 month
- name: revenue_first_month_of_quarter
label: Revenue in the first month of the quarter
type: derived
type_params:
expr: revenue_quarter_start
metrics:
- name: revenue
alias: revenue_quarter_start
offset_to_grain: quarter
hops semantic query sales --metrics revenue,revenue_last_month,revenue_change_mom \
--group-by metric_time__month --order-by metric_time__month -o csv
metric_time__month,revenue,revenue_last_month,revenue_change_mom
2026-01-01,220.75,,
2026-02-01,115.5,220.75,-105.25
2026-03-01,55.75,115.5,-59.75
2026-04-01,,55.75,
hops semantic query sales --metrics revenue,revenue_first_month_of_quarter \
--group-by metric_time__month --order-by metric_time__month -o csv
metric_time__month,revenue,revenue_first_month_of_quarter
2026-01-01,220.75,220.75
2026-02-01,115.5,220.75
2026-03-01,55.75,220.75
offset_windowis<count> <granularity>, such as1 monthor7 days;offset_to_grainis a granularity. An input takes one of them, not both.- The offset input reads the time spine: each period of the spine joins the value of the period the offset names, so April 2026 has the revenue of March and no revenue of its own.
- Metrics in one query are joined on their group-bys, and a metric without a value in a group is empty there:
revenue_change_momis empty in January and April, where one of its inputs is. - A derived or ratio metric with an offset input needs
metric_timein every query. - A filter on
metric_timealone, such as{{ TimeDimension('metric_time', 'day') }} >= '2026-02-01', filters the periods of the time spine, not the rows the offset reads: each queried period that holds a period the filter keeps has one value, read at the offset. A filter at a finer grain than the query's, such as a day filter on a monthly query, keeps the months that hold a kept day. - When the input at an offset is a cumulative metric, a query's time range also bounds the periods that metric is computed for, as in MetricFlow: the first offset's worth of the range has no rows, since the values it reads fall before the range. Start the range one offset earlier to get them.
Cumulative metrics#
A cumulative metric aggregates a measure over a window that ends at each period of metric_time:
metrics:
- name: revenue_7d
label: Revenue over 7 days
type: cumulative
type_params:
measure: revenue
cumulative_type_params:
window: 7 days
- name: revenue_mtd
label: Revenue month to date
type: cumulative
type_params:
measure: revenue
cumulative_type_params:
grain_to_date: month
period_agg: last
- name: revenue_all_time
label: Revenue to date
type: cumulative
type_params:
measure: revenue
hops semantic query sales --metrics revenue_mtd,revenue_7d --group-by metric_time__day \
--start-time 2026-02-10 --end-time 2026-02-18 --order-by metric_time__day -o csv
metric_time__day,revenue_mtd,revenue_7d
2026-02-10,15.5,15.5
2026-02-11,115.5,115.5
2026-02-12,115.5,115.5
2026-02-13,115.5,115.5
2026-02-14,115.5,115.5
2026-02-15,115.5,115.5
2026-02-16,115.5,115.5
2026-02-17,115.5,100.0
2026-02-18,115.5,
The order of 15.50 on February 10 leaves the 7-day window on February 17, and the orders of February 11 leave it on February 18. The month to date keeps them until the end of February.
cumulative_type_params key | Value |
|---|---|
window | <count> <granularity>, such as 7 days: each period aggregates the rows of the window that ends with it. |
grain_to_date | A granularity, such as month: each period aggregates the rows since the start of its period of that grain. |
period_agg | first (default), last or average: the value a query coarser than the metric's grain keeps for each of its periods. |
metric | A simple metric without a measure to accumulate, in place of type_params.measure. |
Without window and grain_to_date, the metric accumulates over all time: each period has the total up to it, and a query without metric_time has the total of every row. window and grain_to_date also read from type_params, where older YAML sets them.
A query at a coarser grain than the metric's keeps one value per period, the one period_agg picks:
hops semantic query sales --metrics revenue,revenue_mtd --group-by metric_time__month \
--order-by metric_time__month -o csv
metric_time__month,revenue,revenue_mtd
2026-01-01,220.75,220.75
2026-02-01,115.5,115.5
2026-03-01,55.75,55.75
With period_agg: last, the month to date of each month is its value on the month's last day. A cumulative metric has a row for each period of the time spine whose window holds rows, so period_agg: first keeps the value of the first such day, January 5 in January.
Filled gaps: join_to_timespine and fill_nulls_with#
A measure input with join_to_timespine: true has a row for every period of the time spine, and fill_nulls_with puts a number in place of an empty value:
metrics:
- name: orders_filled
label: Orders, zero without any
type: simple
type_params:
measure:
name: order_count
join_to_timespine: true
fill_nulls_with: 0
hops semantic query sales --metrics orders_filled --group-by metric_time__month \
--start-time 2026-01-01 --end-time 2026-04-30 --order-by metric_time__month -o csv
- The join to the time spine applies when the query groups by
metric_time; without a time range, the rows cover the whole spine, one per period. fill_nulls_withtakes an integer, and applies with or without the join, for example where another metric of the query has a group this one has not.- Both go on the measure input, or on
type_paramsnext tomeasureas dbt's newer YAML writes them, and on the inputs of a conversion metric.
Conversion metrics#
A conversion metric counts the events of one measure that follow an event of another, for the same entity, such as the orders that follow a visit. The examples add a semantic model over visits:
semantic_models:
- name: visits
model: ref('visits')
defaults:
agg_time_dimension: visit_ts
entities:
- name: visit
type: primary
expr: visit_id
- name: customer
type: foreign
expr: customer_id
dimensions:
- name: visit_ts
type: time
type_params:
time_granularity: day
measures:
- name: visit_count
agg: count
expr: visit_id
metrics:
- name: visit_to_order_rate
label: Visit to order rate
type: conversion
type_params:
conversion_type_params:
entity: customer
base_measure: visit_count
conversion_measure: order_count
calculation: conversion_rate
window: 7 days
- name: visit_to_order_conversions
label: Orders within a week of a visit
type: conversion
type_params:
conversion_type_params:
entity: customer
base_measure: visit_count
conversion_measure: order_count
calculation: conversions
window: 7 days
hops semantic query sales --metrics visit_to_order_rate,visit_to_order_conversions \
--group-by metric_time__month --order-by metric_time__month -o csv
metric_time__month,visit_to_order_rate,visit_to_order_conversions
2026-01-01,1.0,3.0
2026-02-01,0.6666666666666666,2.0
2026-03-01,,
Each order counts once, under the latest visit of its customer at or before it and within the window, and is grouped by that visit's group-bys: its month here. In January, the two orders of January 5 follow the visit of January 3, and the order of January 6 the visit that day, so 3 visits converted 3 orders. The only visit in March has no order within a week, so March has no conversions.
conversion_type_params key | Value |
|---|---|
entity | The entity both events share, an entity of both semantic models; required. |
base_measure, conversion_measure | The two events, each a measure that aggregates with count, count_distinct, or sum of 1. |
base_metric, conversion_metric | A simple metric without a measure, in place of each measure. |
calculation | conversion_rate (default), conversions over base events, or conversions, their count. |
window | <count> <granularity>: how long after the base event a conversion counts. Without it, any later conversion counts. |
constant_properties | A list of base_property and conversion_property, a dimension or entity of each semantic model that both events must agree on, such as the product of the visit and the order. |
The base events read the query's filters, the metric's filter, the filter of the base measure input and the time range, and the conversion events only the filter of the conversion measure input.
Semi-additive measures#
A semi-additive measure sums across entities but not across time, such as an account balance: a query keeps only the rows at the first, or last, value of its non_additive_dimension in each period. The examples add a semantic model over balances, one row per customer and snapshot:
semantic_models:
- name: balances
model: ref('balances')
defaults:
agg_time_dimension: snapshot_ts
entities:
- name: snapshot
type: primary
expr: snapshot_id
- name: customer
type: foreign
expr: customer_id
dimensions:
- name: snapshot_ts
type: time
type_params:
time_granularity: day
measures:
- name: customer_balance
agg: sum
expr: balance
non_additive_dimension:
name: snapshot_ts
window_choice: max
window_groupings:
- customer
- name: latest_balance
agg: sum
expr: balance
non_additive_dimension:
name: snapshot_ts
window_choice: max
metrics:
- name: customer_balance
label: Customer balance
type: simple
type_params:
measure: customer_balance
- name: latest_balance
label: Balance on the last snapshot day
type: simple
type_params:
measure: latest_balance
hops semantic query sales --metrics customer_balance,latest_balance \
--group-by metric_time__month --order-by metric_time__month -o csv
metric_time__month,customer_balance,latest_balance
2026-01-01,170.0,170.0
2026-02-01,200.0,170.0
2026-03-01,85.0,85.0
In February, one customer's last snapshot is on February 15 and the other three are on February 28. customer_balance takes each customer's last snapshot of the month, and latest_balance only the snapshots of the month's last snapshot day, February 28.
non_additive_dimension key | Value |
|---|---|
name | A time dimension of the semantic model, at the granularity of the measure's agg time dimension. |
window_choice | min (default) keeps the rows at the earliest value in each period, max those at the latest. |
window_groupings | Entities of the semantic model to pick the latest or earliest row of separately, such as each customer's. |
SCD Type II dimensions#
A semantic model over a slowly changing dimension table, one row per version of an entity with the time each version starts and ends, joins each measure row to the version valid at the row's time. The examples add customer_tiers, the loyalty tier of each customer over time:
customer_id | tier | valid_from | valid_to |
|---|---|---|---|
| 1 | silver | 2025-06-01 | 2026-01-01 |
| 1 | gold | 2026-01-01 | |
| 2 | silver | 2025-09-01 | |
| 3 | gold | 2025-01-01 | 2026-01-15 |
| 3 | silver | 2026-01-15 | |
| 4 | silver | 2026-02-01 |
semantic_models:
- name: customer_tiers
model: ref('customer_tiers')
primary_entity: customer
entities:
- name: customer
type: natural
expr: customer_id
dimensions:
- name: valid_from
type: time
type_params:
time_granularity: day
validity_params:
is_start: true
- name: valid_to
type: time
type_params:
time_granularity: day
validity_params:
is_end: true
- name: tier
type: categorical
hops semantic query sales --metrics revenue,orders --group-by customer__tier \
--order-by customer__tier -o csv
Customer 3's order of January 6 counts as gold and its order of February 10 as silver, since the tier changed on January 15.
- The key is an entity of
type: natural, and the model names it as itsprimary_entity. - Two time dimensions carry
validity_params: oneis_start: true, oneis_end: true. An empty end means the version is still valid. - A measure row joins the version whose start is at or before the row's agg time, and whose end is after it.
- The model has no measures, no other entity of type
primaryorunique, and one natural entity.
Metric filters#
Metric('<metric>', group_by=['<entity>']) in a filter is the value of a metric for each value of an entity, so a query can keep the rows of the customers whose revenue is over 100:
hops semantic query sales --metrics revenue,orders --group-by customer__country \
--where "{{ Metric('revenue', group_by=['customer']) }} > 100" \
--order-by customer__country -o csv
Of the two customers in SE, one has 120.50 of revenue and the other 70.25, so SE has the orders of the first only.
The same call goes in a metric's filter:
metrics:
- name: big_customer_revenue
label: Revenue of customers above 100
type: simple
type_params:
measure: revenue
filter: "{{ Metric('revenue', group_by=['customer']) }} > 100"
group_bynames exactly one entity, or an entity path such asorder__customer.- The metric in the call is computed per entity with its own filters only, not the query's, and joined to the rows on that entity.
Limits#
A query of one of these shapes is refused with 400 SEMANTIC_LAYER_INVALID_QUERY, and a message that names the shape. A metric or saved query whose definition has one compiles with an UNSUPPORTED warning, and the build serves its other metrics.
- A query without metrics of a dimension that an SCD model joins with its validity window.
- A cumulative metric read at an offset finer than the query's grain, such as an offset of 1 day in a monthly query.
- A semi-additive measure in a cumulative metric, or read at an offset finer than the query's grain.
- An input at an offset that is itself a ratio or derived metric, in a query grouped by a custom granularity, or by
metric_timeonly at a date part. - An input at an offset that is itself a ratio or derived metric, in a query whose grain cannot express the offset, such as an offset of 2 days in a monthly query.
- An input at an offset window in a custom granularity, such as
1 fiscal_quarter, that is itself a ratio or derived metric. - A
Metric()filter that applies to an input at an offset that is itself a ratio or derived metric. - A filter that combines
metric_timewith another group-by, one of them not a group-by of the query, on an input at an offset that is itself a ratio or derived metric. - Two custom granularities of the time spine in one query.
- A custom granularity as a metric's
time_granularity. - A window in microseconds or nanoseconds.
- A saved query that groups by a metric.
These definitions are compile errors:
- A custom granularity in a cumulative
windoworgrain_to_date, in a conversionwindow, or inoffset_to_grain. aggon the metric itself, dbt's newer YAML spec: a metric without a measure takestype_params.metric_aggregation_params.
The validation rules list every check of a compile.