Skip to main content

Measure properties

Measures are the business metrics that calculate a value for a model. Here are a few examples of measures:

  • total_rows compiles to count(*),
  • unique_users compiles count(distinct user_id) in SQL.

dimension: | column: | sql:#

You should set either dimension, column, or sql in order to define the measures under model.meta.metriql.measures. If it's not set, the measure simply counts the all rows (i.e. count(*)). Here are a few examples:

- name: events
aggregation: count
aggregation: count_unique
dimension: user_id
sql: {measure.total_rows}/{measure.unique_users}

Please note that these fields are not required under column.meta as they point to the relevant column.

column: references the column in the model target.

dimension: references the dimensions within the same model.

sql: lets you define complex expressions that column type measure is not capable of.

For referencing other entities inside a measure checkout the SQL Context.


The aggregation function of the measure value dimension | column | sql.

Here are the valid values:

count, count_unique, sum, minimum, maximum, average, approximate_unique

dimension: 'user_id'
aggregation: approximate_unique

aggregation is not required if the sql is defined. Here is an example:

sql: "{measure.total_rows} / {measure.total_users}"


You can restrict a measure to aggregate only specific dimension values without applying a filter to an entire query. Here is an example:

aggregation: count
filters: [{dimension: occurred_at, operator: between, value: '1 week'}]

The measure above compiles to the following SQL expression:

COUNT(CASE WHEN WEEK(now(), occurred_at) < 1 THEN NULL ELSE 1)

Learn more about the filter operators here.


If you use WINDOW operations in your sql expressions, you must set this property to true so that metriql constructs the SQL queries using a subquery.

sql: '{measure.revenue} - LAG({measure.revenue}) OVER ({TABLE}.month)'
window: true

Please note that you need to use the referenced measures in your query, otherwise the queries will complain that the measures not available.