Define Metrics over Feature Groups#
Metrics are defined in dbt semantic YAML: semantic models that read feature groups, and metrics over the measures of those models. The examples on this page use two feature groups:
| Feature group | Primary key | Event time | Other features |
|---|---|---|---|
orders | order_id | order_ts | customer_id, status (string), amount (double) |
customers | customer_id | country (string) |
Project layout#
A dbt project has a dbt_project.yml at its root, and the semantic layer reads semantic models and metrics from the YAML files under its model-paths, models when none is given. Register the folder that holds dbt_project.yml as the semantic project's root path.
dbt_project.yml:
A compile reads every .yml, .yaml and .json file under the root path, except in hidden folders and in the root's target, dbt_packages and logs folders, where dbt writes its output and packages. It reads at most 1000 such files, each of at most 1 MiB and 32 MiB in all; past that, the build is INVALID with a TOO_MANY_FILES, FILE_TOO_LARGE or FILES_TOO_LARGE error.
Semantic models#
models/semantic.yml starts with one semantic model per feature group:
version: 2
semantic_models:
- name: orders
description: One row per order.
model: ref('orders')
defaults:
agg_time_dimension: order_ts
entities:
- name: order
type: primary
expr: order_id
- name: customer
type: foreign
expr: customer_id
dimensions:
- name: order_ts
type: time
type_params:
time_granularity: day
- name: status
type: categorical
measures:
- name: revenue
agg: sum
expr: amount
- name: order_count
agg: count
expr: order_id
- name: customers
description: One row per customer.
model: ref('customers')
entities:
- name: customer
type: primary
expr: customer_id
dimensions:
- name: country
type: categorical
- Entities are the keys of a semantic model. Semantic models join on an entity they share:
ordersreachescustomersthroughcustomer, the primary entity ofcustomers. A query joins at most two hops away, and never along a join that would repeat the rows of a measure. An entity isprimary,unique,foreign, ornatural, the key of an SCD Type II semantic model. - Dimensions are
categoricalortime. A time dimension needstype_params.time_granularity, and its column must be a date or a timestamp. - Measures aggregate an
expr, a feature or a SQL expression over the features of the feature group. Every measure needs a time dimension to aggregate over, from its ownagg_time_dimension, fromdefaults.agg_time_dimension, or else from the event time of its feature group; it becomes the measure'smetric_time.create_metric: trueon a measure also defines a simple metric with the measure's name, andnon_additive_dimensionmakes it semi-additive.
Each expr is checked against the feature group's features at compile time, so a typo in a feature name is an error at its line instead of a failed query. Names are lower-cased when compiled.
agg | Result type | Notes |
|---|---|---|
sum | BIGINT for an integer expr, DECIMAL(38, s) for a DECIMAL(p, s), else the type of expr | |
min, max | The type of expr | |
average | DOUBLE for an integer expr, else the type of expr | |
count | BIGINT | Counts the rows where expr is not null. |
count_distinct | BIGINT | |
sum_boolean | BIGINT | Counts the rows where expr is true. |
percentile | DOUBLE | Needs agg_params.percentile, strictly between 0 and 1, and agg_params.use_approximate_percentile: true. |
A decimal keeps up to 38 digits. The result type is the type of the metric's column in every answer: in Arrow, in JSON and in the BI tables.
Trino computes only approximate continuous percentiles, so a metric over a median measure, or over an exact percentile, is a METRIC error at compile, and one over a percentile with both use_approximate_percentile and use_discrete_percentile compiles with an UNSUPPORTED warning and its queries are refused. Write a median as a percentile measure with percentile: 0.5 and use_approximate_percentile: true.
Bind a semantic model to a feature group#
model: ref('orders') reads the feature group named orders, at its latest version. A new version of the feature group then changes what the metrics read at the next compile.
config.meta.hopsworks names the feature group explicitly, and takes precedence over ref:
semantic_models:
- name: customers
model: ref('customers')
config:
meta:
hopsworks:
feature_group: customer_profile
version: 2
| Key | Value |
|---|---|
feature_group | The feature group to read, when its name differs from the dbt model's. |
version | The version of feature_group to read; without it, the latest version. |
feature_store | The feature store that holds the feature group, such as marketing_featurestore, for a feature store or feature group shared with the project. Without it, the project's own feature store. |
In the NATIVE format, a semantic model names its feature group table with node_relation, as schema_name and alias, such as sales_featurestore and orders_1.
A semantic model reads a feature group that has an offline table: the project's own, or one shared with the project. Any other relation is a RELATION error.
Event time#
The compiler links the event_time of each feature group to a time dimension of every semantic model that reads it. The linked dimension is the model's time dimension whose expr, or name when it has no expr, is the event time feature, ignoring case. A model without one gets a time dimension named after the feature, at day granularity, so orders can be grouped by order__order_ts without declaring it. Declare the time dimension yourself for a finer granularity, such as hour; the compiler never changes a declared dimension.
The linked dimension is the default agg time dimension: a measure without its own agg_time_dimension, in a semantic model without defaults.agg_time_dimension, aggregates over it, and it becomes the measure's metric_time. With the orders feature group, this semantic model compiles, and revenue by metric_time__month groups by the month of order_ts:
semantic_models:
- name: orders
model: ref('orders')
entities:
- name: order
type: primary
expr: order_id
measures:
- name: revenue
agg: sum
expr: amount
To aggregate over another time, set defaults.agg_time_dimension on the semantic model or agg_time_dimension on the measure; the event time stays linked and queryable as a dimension. A time spine declared without a column reads the event time of its feature group. The dimension listing of the REST API marks the linked dimension with "eventTime": true.
A feature group without an event time links nothing, and a measure without an agg time dimension is an AGG_TIME_DIMENSION error that says the feature group has no event_time. When the event time is not a date or a timestamp, or the name of the dimension to add is already an element of the semantic model, a non-time element of another one, or a dimension of a semantic model with the same primary entity, the compiler adds nothing and reports an EVENT_TIME warning that names the clash. Declare a time dimension on the event time feature under another name to link it.
Metrics#
Metrics follow the semantic models in the same file, or in any other YAML file under model-paths:
metrics:
- name: revenue
label: Revenue
type: simple
type_params:
measure: revenue
- name: orders
label: Orders
type: simple
type_params:
measure: order_count
- name: revenue_per_order
label: Average order value
description: Revenue divided by orders, the basket size.
type: ratio
type_params:
numerator: revenue
denominator: orders
- 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
- name: cancelled_orders
label: Cancelled orders
type: simple
type_params:
measure: order_count
filter: "{{ Dimension('order__status') }} = 'cancelled'"
| Type | Definition |
|---|---|
simple | One measure, type_params.measure, or an expr aggregated over a semantic model. |
ratio | One metric divided by another, type_params.numerator and type_params.denominator. |
derived | An expr over other metrics, listed in type_params.metrics, each with an optional alias, filter and offset. |
cumulative | A measure aggregated over a window, or since the start of a period, that ends at each period of metric_time. |
conversion | The rate or count of the events of one measure that follow an event of another, for the same entity. |
Metric types has the YAML of each, and of offsets, join_to_timespine, semi-additive measures, SCD Type II dimensions and metric filters.
A filter applies to every query of a metric, or to one input of a derived metric. Filters use the call syntax of queries: {{ Dimension('order__status') }}, {{ TimeDimension('metric_time', 'month') }}, {{ Entity('customer') }}, and {{ Metric('revenue', group_by=['customer']) }} for the value of a metric per entity. The label and description show in the metric catalog of the UI and in search.
A semantic project also defines saved queries, named metric queries, under saved_queries:. The next steps are the time spine, which cumulative metrics and offsets need, and compiling the definitions.