Fill null values for metrics

Understanding and implementing strategies to fill null values in metrics is key for accurate analytics. This guide explains fill_nulls_with and join_to_timespine to ensure data completeness, helping end users make more informed decisions and enhancing your dbt workflows.

About null values

You can use fill_nulls_with to replace null values in metrics with a value like zero (or your chosen integer). This ensures every data row shows a numeric value.

This guide explains how to ensure there are no null values in your metrics:

• Use fill_nulls_with for simple, cumulative, and conversion metrics
• Use join_to_timespine and fill_nulls_with together for derived and ratio metrics to avoid null values appearing.

Fill null values for simple metrics

For example, if you'd like to handle days with site visits but no leads, you can use fill_nulls_with to set the value for leads to zero on days when there are no conversions.

Let's say you have three metrics:

• website_visits and leads
• and a derived metric called leads_to_website_visit that calculates the ratio of leads to site visits.

(Applies to dbt v1.12 and later)

On the days when there are no conversions, you can set the value for leads to zero by adding the fill_nulls_with parameter to the leads metric:

models/website_visits.yml

# Define simple metrics within a semantic model
models:
  - name: website_visits_model
    semantic_model:
      enabled: true
    # ... other configs ...
    metrics:
      - name: website_visits
        type: simple
        agg: count
      - name: leads
        type: simple
        agg: count
        fill_nulls_with: 0 # This fills null values with zero
      - name: leads_to_website_visit
        type: derived
        expr: leads/website_visits
        input_metrics:
          - name: leads
          - name: website_visits

The website_visits and leads metrics have the following data:

metric_time	website_visits
2024-01-01	50
2024-01-02	37
2024-01-03	79

metric_time	leads
2024-01-01	5
2024-01-03	8

• Note that there is no data for 2024-01-02 in the leads metric.

Although there are no days without visits, there are days without leads. After applying fill_nulls_with: 0 to the leads metric, querying these metrics together shows zero for leads on days with no conversions:

metric_time	website_visits	leads
2024-01-01	50	5
2024-01-02	37	0
2024-01-03	79	8

Use join_to_timespine for derived and ratio metrics

(Applies to dbt v1.12 and later)

To ensure you have a complete set of data for every and daily coverage for metrics calculated from other metrics, you can use join_to_timespine to fill null values for derived and ratio metrics. These metrics are built from other metrics (other calculations), not direct aggregations, requiring MetricFlow to have an extra subquery layer to render the metric. The subquery nesting is as follows:

• For derived and ratio metrics, there are three levels of subquery nesting — derived or ratio metric → input metrics → simple metrics with aggregations.
• For simple and cumulative metrics, there are only two levels of subquery nesting — simple or cumulative metric → aggregation.

Because coalesce isn't applied to the third, subquery layer for derived or ratio metrics, this means you could still have nulls in the final result set.

Note you can use join_to_timespine with simple metrics as well if you want to include a row for every date, even if there is no data.

Fill null values for derived and ratio metrics

To fill null values for derived and ratio metrics, you can link them with a time spine to ensure daily data coverage. As mentioned in the previous section, this is because derived and ratio metrics take metrics as inputs.

For example, the following structure leaves nulls in the final results (leads_to_website_visit column) because COALESCE isn't applied at the third outer rendering layer for the final metric calculation in derived metrics:

metric_time	website_visits	leads	leads_to_website_visit
2024-01-01	50	5	.1
2024-01-02	37	0	null
2024-01-03	79	8	.1

To display a zero value for leads_to_website_visit for 2024-01-02, you would join the leads metric to a time spine model to ensure a value for each day. You can do this by adding join_to_timespine to the (Applies to dbt v1.12 and later) simple metric in the leads metric configuration:

(Applies to dbt v1.12 and later)

models/leads.yml

models:
  - name: leads_model
    semantic_model:
      enabled: true
    # ... other configs ...
    metrics:
      - name: leads
        type: simple
        agg: count
        fill_nulls_with: 0
        join_to_timespine: true

Once you do this, if you query the leads metric after the timespine join, there will be a record for each day and any null values will get filled with zero.

metric_time	leads	leads_to_website_visit
2024-01-01	5	.1
2024-01-02	0	0
2024-01-03	8	.1

Now, if you combine the metrics in a derived metric, there will be a zero value for leads_to_website_visit on 2024-01-02 and the final result set will not have any null values.

FAQs

 How to handle null values in derived metrics defined on top of multiple tables

For additional examples and discussion on how to handle null values in derived metrics that use data from multiple tables, check out MetricFlow issue #1031.

Was this page helpful?

Privacy policyCreate a GitHub issue

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.