dbt_metrics

The dbt_metrics package uses a structured macro flow to ensure consistent SQL generation. The process begins with one of two primary entry-point macros:

1. calculate: The primary macro used by end-users to generate metric SQL.
2. develop: A macro used for testing and simulating metrics by providing metric YAML files without including them in the full project.

Both macros follow a two-step execution lifecycle:

1. Validation

The macros validate that inputs are correct and match expected schemas. They also validate inputs against the existing metrics object in the dbt manifest to ensure dimensions are correct, time grains are permitted, and other constraints are met.

2. Variable Creation

Both macros generate two critical internal variables required for downstream SQL generation processes: metric_tree and metrics_dictionary.

When defining metrics, you specify a calculation_method to determine how the expression is aggregated:

Secondary calculations are window functions that act on primary metrics to enable comparisons like year-to-date sums, rolling averages, or period-over-period changes. Using secondary calculations requires providing a grain input in the macro call.

You can create these using convenience constructor macros. While you can manually create a list of dictionary entries, it is not recommended as it lacks input validation.

Column aliases are automatically generated but can be overridden using the alias parameter.

The metrics_dictionary is a dictionary containing the attributes for every metric identified in the full_set. It provides the metadata necessary for the SQL generator to build queries. For each metric, it includes:

• Metric name
• Metric calculation method
• Metric expression
• Metric timestamp
• Metric time grains
• Metric dimensions
• Metric filters
• If not derived: Metric model name
• If not derived: Metric model object

The metric_tree is a dictionary used during the SQL generation process to manage dependencies and nesting. It contains five key-value pairs:

• full_set: A list of all metric names required to construct the SQL (includes all parent and expression metrics).
• base_set: A list of metric names explicitly provided to the macro.
• parent_set: A list of first-level (non-derived) metrics that downstream metrics depend upon.
• derived_set: A list of derived metric names.
• ordered_derived_set: A list of dictionaries containing derived metrics and their depth from the parent. This is used to construct the nested Common Table Expressions (CTEs) in the generated SQL.

To implement a custom primary aggregation that can be used via the calculation_method configuration in a metric, follow these steps:

1. Create a macro named metric_<your_aggregate_name>(expression).
2. Override the gen_primary_metric_aggregate() macro to include your new macro in the dispatch list.
3. Update the get_metric_allowlist() macro to ensure your new aggregation is valid for both existing aggregations and itself (to prevent nonsensical secondary calculations like an average of an average).

{% do return ({
    "average": ['max', 'min'],
    "count": ['max', 'min', 'average', 'my_new_aggregate'],
    "my_new_aggregate": ['max', 'min', 'sum', 'average', 'my_new_aggregate']
}) %}

To use dbt_metrics, add the package to your packages.yml file. Ensure you use a version compatible with your project (e.g., [ ">=1.5.0", "<1.6.0" ]).

packages:
  - package: dbt-labs/metrics
    version: [ ">=1.5.0", "<1.6.0" ]

To aggregate metrics by a dimension in your custom calendar table (e.g., is_weekend) without defining it in the metric itself, add the dimension name to the custom_calendar_dimension_list variable in your project configuration.

# dbt_project.yml
vars:
  custom_calendar_dimension_list: ["is_weekend"]

The package includes a default calendar table. To use your own, ensure it contains a date_day column and time grain columns (e.g., date_week, date_month). Additional custom columns must be prefixed with date_ (e.g., date_4_5_4_month).

Set the dbt_metrics_calendar_model variable in dbt_project.yml to point to your model.

# dbt_project.yml
vars:
    dbt_metrics_calendar_model: my_custom_calendar

To create a custom secondary aggregation (accessible via the secondary_calculations input in the metric macro), follow these steps:

1. Create a macro following the pattern secondary_calculation_<your_calculation_name>(metric_name, dimensions, calc_config).
2. Override the perform_secondary_calculations() macro to register and execute your custom logic.

Metric nodes accept config dictionaries. You can specify configurations directly in the metric's YAML file or globally in dbt_project.yml.

Accepted Metric Configurations:

# in metrics.yml
metrics:
  - name: config_metric
    config:
      enabled: True
      treat_null_values_as_zero: False

# in dbt_project.yml
metrics:
  your_project_name:
    +enabled: true