Definition
Metrics are defined in SQL files in the metrics/
directory of your SQLMesh project. A single file can contain multiple metric definitions.
A metric is defined with the function METRIC()
and must include the name
and expression
keys. The name
is case insensitive and must be unique, and the expression
contains the SQL code used to calculate the metric.
SQL expression
The expression field can be any SQL statement that contains an aggregation function (e.g., MAX
, SUM
). This example uses the COUNT
function:
All columns referenced in the expression should be fully qualified with the model name. For example, if a model name is a.b
, a metric referencing column c
in that model should refer to it as a.b.c
.
The example above refers to the account_id
column in model silver.accounts
using silver.accounts.account_id
.
Automatic joins
Metrics can refer to multiple tables and will use the model grains and references to automatically join them together. grains
specify a model's key column(s) that uniquely identify the model's rows and references
specify column(s) that other tables may join to.
For example, a SQLMesh project might contain the prod.users
and prod.searches
models with the following MODEL
DDLs.
The prod.users
model has a grain of user_id
, meaning that its rows are uniquely identified by the user_id
column:
The prod.searches
model specifies user_id
in its references
key, signaling that other models may join to its user_id
column:
Because those models specify their grain and references, SQLMesh can correctly generate code for a metric that uses columns from both models.
In this example, canadian_searchers
sums searches from users located in Canada:
Because the prod.users.country
and prod.searches.num_searches
models have specified their grains/references, SQLMesh can automatically do the correct join between them.
Derived metrics
Metrics can perform additional operations/calculations with other metrics.
In this example, the third metric clicks_per_search
is calculated by dividing the first metric total_searches
by the second metric total_clicks
:
Properties
The METRIC
definition supports the following keys. The name
and expression
keys are required.
name
- The name of the metric. This name is case insensitive and must be unique in a project.
expression
- A SQL expression consisting of an aggregation, a formula consisting of other metrics, or a combination of both.
description
- The description of the metric.
owner
- The owner of the metric. Used for organizational and governance purposes.
dialect
- The dialect that the expression is written in. It is recommended to leave this empty and rely on the project's default dialect.