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
expression keys. The
name is case insensitive and must be unique, and the
expression contains the SQL code used to calculate the metric.
The expression field can be any SQL statement that contains an aggregation function (e.g.,
SUM). This example uses the
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
The example above refers to the
account_id column in model
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.searches models with the following
prod.users model has a grain of
user_id, meaning that its rows are uniquely identified by the
prod.searches model specifies
user_id in its
references key, signaling that other models may join to its
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:
prod.searches.num_searches models have specified their grains/references, SQLMesh can automatically do the correct join between them.
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
METRIC definition supports the following keys. The
expression keys are required.
- The name of the metric. This name is case insensitive and must be unique in a project.
- A SQL expression consisting of an aggregation, a formula consisting of other metrics, or a combination of both.
- The description of the metric.
- The owner of the metric. Used for organizational and governance purposes.
- The dialect that the expression is written in. It is recommended to leave this empty and rely on the project's default dialect.