Measures

Measures are numerical values that mathematical functions can be applied to and are the foundation for creating metrics. An example of a measure is value of transactions - using this measure, you can create metrics like total transaction value or average transaction value. In Transform, measures are created in data sources and become the building blocks of your metrics.

Measures have a few inputs in the data source configuration:

Name (Required)#

The name of the measure can be chosen by the user or optionally reference a database column directly. If the name of the measure is different than the column in the table, you must add an expr which denotes the column name. The name of the measure will be referenced when creating a metric. Measure names must be unique across all data sources in a project.

Description (Required)#

The description section describes what measure is calculated. It is strongly recommended to create verbose and human-readable descriptions in this field.

Aggregation (Required)#

The aggregation determines how the field will be aggregated. For example, a sum aggregation type over a granularity of day would sum the values across a given day.

Aggregation Types	Description
sum	Sum across the values
min	Minimum across values
max	Maximum across values
average	Average across values
sum_boolean	A sum for a boolean type
count_distinct	Distinct count of values

tip

All of the metrics above, except for count_distinct, are additive measures. The framework can aggregate these agg types sequentially to reuse datasets. As an example, transactions by day could be aggregated to transactions by week. Non-additive measures like count_distinct don't have this property and must be calculated from the source every time. This can be more costly and slower, but the resulting metrics are worth the wait in many cases.

Expr (Optional)#

Use an expr with any valid SQL to manipulate the output of the value of the measure. This could be a SQL statement or it could simply be the name of the column in the database. Using an expr is required if you've specified a name that does not match the name of the measure column in the database. In this case, the expr column should reflect the column name.

caution

If you are using Snowflake and use any week-level function inside the expr parameter, the function will now return the ISO-standard default of MONDAY.

Examples:

You are running Snowflake warehouse instances with account or session level overrides for the WEEK_START parameter that fix it to a value other than 0 or 1: You will now see MONDAY as the week start in all cases.
You are using the DAYOFWEEK function inside the expr parameter with the legacy Snowflake default of WEEK_START = 0: DAYOFWEEK will now return the ISO-standard values of 1 (Monday) through 7 (Sunday) instead of Snowflake’s legacy default values of 0 (Monday) through 6 (Sunday).

Create Metric#

You can optionally choose to create a metric directly from a measure. This is a shortcut for creating a metric. By default, the description and owners will default to the description and owner of the data source. The tier of the metric will default to Tier 3.

You can currently specify the create metric and what the display name of the metric is:

create_metric: true create_metric_display_name: "Your Metric Display Name"

Note: If you prefer to have a single file location or a single mechanism for storing all your metric definitions, we do not recommend you use this shortcut because this allows metric definitions to exist in multiple places across your model.

Examples#

Using various Aggregation Types

measures:  - name: transaction_amount_usd    description: The total USD value of transactions daily    agg: sum

measures:  - name: transaction_amount_usd_avg    description: The average USD value of the transaction.    expr: transaction_amount_usd    agg: average

measures:  - name: transaction_amount_usd_max    description: The maximum USD value of the transaction.    expr: transaction_amount_usd    agg: max

measures:  - name: transaction_amount_usd_min    description: The minimum USD value of the transaction.    expr: transaction_amount_usd    agg: min

measures:  - name: quick_buy_transactions    description: The total transactions bought as quick buy.    expr: quick_buy_flag    agg: sum_boolean

measures:  - name: distinct_transactions_count    description: Distinct count of transactions    expr: transaction_id    agg: count_distinct

Using expr to compute the aggregation based on a condition. This will sum the values for when is_valid = true.

measures:  - name: transactions_amount_usd    description: The sum USD value of valid transactions    agg: sum    expr: CASE WHEN is_valid = true THEN 1 ELSE 0;

Using Create Metric to create a metric from the measure

measures:  - name: transactions    description: The average value of transactions.    expr: transaction_amount_usd    agg: average    create_metric: true     create_metric_display_name: Avg Value of Transactions (Daily)