How to show zero instead of NULL for a metric in dbt (fill_nulls_with, join_to_timespine)

A month without sales should show 0, not a gap in the chart, and a region without returns should show a 0% return rate, not an empty cell. The Semantic Layer has two settings for this, and they solve different halves of the problem.

Copy and paste

    metrics:
      - name: revenue
        type: simple
        agg: sum
        expr: amount
        fill_nulls_with: 0          # empty values become 0
        join_to_timespine: true     # a row for every period, with or without orders

A version that also works on dbt Core 1.12:

metrics:
  - name: revenue_or_zero
    type: derived
    expr: "coalesce(revenue_spine, 0)"
    input_metrics:
      - name: revenue_spine          # a simple metric with join_to_timespine: true

What each setting does

The orders have no sales in August 2025. Queried by month from July to September:

• join_to_timespine joins the metric to the time spine, so every period gets a row. Without a value, the row is empty.
• fill_nulls_with replaces empty values with a number, but only in rows that exist. Alone, it changes nothing for August, because August has no row.
• Both together give a row for August with 0.

fill_nulls_with alone is useful when the row exists for another reason: in a query of order_count and returned_orders by month, every month has orders, and returned_orders shows 0 instead of an empty cell in the months without returns. A ratio built on a filled numerator then returns 0 too.

dbt Core 1.12 drops fill_nulls_with

With the latest YAML spec, dbt Core 1.12.6 parses fill_nulls_with without complaint and then leaves it out of semantic_manifest.json, where MetricFlow reads it. In the manifest every metric showed "fill_nulls_with": null, and no query filled anything. join_to_timespine was kept.

The same project parsed with dbt v2 2.0.9 kept fill_nulls_with: 0 on the simple metrics, and MetricFlow then returned the zeros in the table above. So:

• dbt platform on dbt v2, or local dbt v2: use the two settings.
• dbt Core 1.12, or anywhere you need certainty: check the manifest, and fall back to a derived metric with coalesce, as in the second snippet. It returned 80, 0 and 120 on both engines.

python -c "import json; m=json.load(open('target/semantic_manifest.json')); \
print([(x['name'], x['type_params']['fill_nulls_with']) for x in m['metrics']])"

Derived, ratio and conversion metrics

• Derived and ratio metrics take their values from their inputs. Put fill_nulls_with and join_to_timespine on the simple input metrics, or wrap the result in coalesce in the derived expr.
• Conversion metrics are listed in the documentation as supporting fill_nulls_with, but neither engine kept it in the manifest in my tests. Use a derived metric with coalesce; see how to define a conversion metric.

Zero or nothing

A filled zero claims that the value is known to be zero. That is right for sales in a month with no orders, and wrong for a day the pipeline failed to load. When missing data is possible, keep the gap, or fill it only in metrics where an absence of rows really means none.

Tested with dbt Core 1.12.6, dbt v2 2.0.9 and MetricFlow 0.213 on DuckDB.