Skip to main content

Metrics

β–ΆChangelog

About Metrics​

A metric is a timeseries aggregation over a table that supports zero or more dimensions. Some examples of metrics include:

  • active users
  • monthly recurring revenue (mrr)

In v1.0, dbt supports metric definitions as a new node type. Like exposures, metrics appear as nodes in the directed acyclic graph (DAG) and can be expressed in YAML files. Defining metrics in dbt projects encodes crucial business logic in tested, version-controlled code. Further, you can expose these metrics definitions to downstream tooling, which drives consistency and precision in metric reporting.

Benefits of defining metrics​

Use metric specifications in downstream tools
dbt's compilation context can access metrics via the graph.metrics variable. The manifest artifact includes metrics for downstream metadata consumption.

See and select dependencies
As with Exposures, you can see everything that rolls up into a metric (dbt ls -s +metric:*), and visualize them in dbt documentation. For more information, see "The metric: selection method."

Metrics appear as pink nodes in the DAG (for now)

Metrics appear as pink nodes in the DAG (for now)

Defining a metric​

You can define metrics in .yml files nested under a metrics: key.

Example definition​

models/<filename>.yml
caution
  • You cannot define metrics on ephemeral models. To define a metric, the materialization must have a representation in the data warehouse.

Available properties​

Metrics can have many declared properties, which define aspects of your metric. More information on properties and configs can be found here.

Available calculation methods​

Description
countThis metric type will apply the count aggregation to the specified field
count_distinctThis metric type will apply the count aggregation to the specified field, with an additional distinct statement inside the aggregation
sumThis metric type will apply the sum aggregation to the specified field
averageThis metric type will apply the average aggregation to the specified field
minThis metric type will apply the min aggregation to the specified field
maxThis metric type will apply the max aggregation to the specified field

Filters​

Filters should be defined as a list of dictionaries that define predicates for the metric. Filters are combined using AND clauses. For more control, users can (and should) include the complex logic in the model powering the metric.

All three properties (field, operator, value) are required for each defined filter.

Note that value must be defined as a string in YAML, because it will be compiled into queries as part of a string. If your filter's value needs to be surrounded in quotes inside the query (e.g. text or dates), use "'nested'" quotes:

    filters:
- field: is_paying
operator: 'is'
value: 'true'
- field: lifetime_value
operator: '>='
value: '100'
- field: company_name
operator: '!='
value: "'Acme, Inc'"
- field: signup_date
operator: '>='
value: "'2020-01-01'"

Querying Your Metric​

You can dynamically query metrics directly in dbt and verify them before running a job in the deployment environment. To query your defined metric, you must have the dbt_metrics package installed. Information on how to install packages can be found here.

Use the following metrics package installation code in your packages.yml file and run dbt deps to install the metrics package:

Once the package has been installed with dbt deps, make sure to run the dbt_metrics_calendar_model model as this is required for macros used to query metrics. More information on this, and additional calendar functionality, can be found in the project README.

Querying metrics with metrics.calculate​

Use the metrics.calculate macro along with defined metrics to generate a SQL statement that runs the metric aggregation to return the correct metric dataset. Example below:

Supported inputs​

The example above doesn't display all the potential inputs you can provide to the macro.

You may find some pieces of functionality, like secondary calculations, complicated to use. We recommend reviewing the package README for more in-depth information about each of the inputs that are not covered in the table below

InputExampleDescriptionRequired
Required
grainday, week, monthThe time grain that the metric will be aggregated to in the returned datasetRequired
dimensions[plan, country]The dimensions you want the metric to be aggregated by in the returned datasetOptional
start_date2022-01-01Limits the date range of data used in the metric calculation by not querying data before this dateOptional
end_date2022-12-31Limits the date range of data used in the metric claculation by not querying data after this dateOptional
whereplan='paying_customer'A sql statment, or series of sql statements, that alter the final CTE in the generated sql. Most often used to limit the data to specific values of dimensions providedOptional

Developing metrics with metrics.develop​