Metrics are currently in a prototype phase and not meant for production use at the moment.
SQLMesh provides a framework for defining and working with metrics (also known as a semantic layer). Metrics are arbitrary SQL functions that perform aggregations for use in analytics, data science, or machine learning.
A semantic layer is valuable because it provides a consistent definition and language for metrics. For example, if an executive asks "How many active users are there?" the answer could differ depending on who they ask or what dashboard they look at. The tables, aggregations, and joins needed to correctly calculate the answer could be complex and implemented differently (or incorrectly) by different people.
Metrics are defined in a SQLMesh project metrics directory, and they are selected by name in a model query. SQLMesh uses its semantic understanding of the query to determine the metric's role in the query, determine the appropriate SQL operations to calculate the metric, and add them to the query code submitted to the SQL engine.
Metrics are defined using SQLMesh's SQL-based metrics definition language.
The following is an example metric definition. Note the following aspects:
- The metrics
expressioncan be any aggregate SQL function (
COUNTin this example)
- The columns referenced in
expressionare fully qualified (
- The metric can reference multiple models as long as their grains/references are properly specified (
expressionuses both the
SQLMesh models access metrics in their query's
SELECT clause with the
METRIC function and the metric name.
For example, this model query selects the
total_orders_from_active_customer metric. Because it is a simple query that solely selects a metric and its grouping column, it can select from the special table
When that model query is run, SQLMesh uses its semantic understanding of the query and metrics definitions to generate the code that is actually executed by the SQL engine:
SQLMesh automatically generates the correct join to use values from both the