MetricFlow time spine

The MetricFlow time spine is a dbt model that contains one row per day across a date range. MetricFlow uses it as a reliable date anchor for cumulative metrics and for filling gaps in time series output.

It is not optional if you plan to use cumulative metrics or the join_to_timespine parameter. MetricFlow expects the table to exist, and queries that require it will fail without it.

Creating the time spine model

Create a dbt model called metricflow_time_spine.sql:

{{ config(materialized='table') }}

{{ dbt.date_spine(
    datepart="day",
    start_date="CAST('2020-01-01' AS DATE)",
    end_date="CAST('2030-12-31' AS DATE)"
) }}

The dbt.date_spine macro is part of dbt Core’s cross-database macro library. It generates one row per datepart unit between start_date and end_date. The result is a single column date_day with a continuous sequence of dates.

Materialize it as a table, not a view. MetricFlow joins against it frequently, and a view re-executes the date generation logic on every query. The table materialization is cheaper and faster.

Run it explicitly when first setting up the semantic layer:

dbt build -s metricflow_time_spine

Configuring it in dbt_project.yml

After creating the model, tell MetricFlow which column to use:

semantic-layer:
  time_spine:
    standard_granularity_column: date_day

The standard_granularity_column must match the column name produced by your time spine model. With dbt.date_spine, that column is date_day.

When MetricFlow uses the time spine

MetricFlow reaches for the time spine in two situations.

The first is cumulative metrics with a window parameter. A metric like “rolling 7-day active users” needs a continuous sequence of dates to define what “the past 7 days” means for each data point. Without a time spine, MetricFlow has no anchor to perform the rolling calculation against. Queries will error.

metrics:
  - name: weekly_active_users
    type: cumulative
    type_params:
      measure: active_users
      window: 7 days

The second is the join_to_timespine parameter on measures. When a metric has days with no underlying data, the default behavior is to return no row for that day. If you want a zero instead, you need both join_to_timespine and fill_nulls_with:

measures:
  - name: daily_revenue
    agg: sum
    expr: order__amount
    join_to_timespine: true
    fill_nulls_with: 0

Without join_to_timespine, a chart of daily revenue will have gaps for days with no orders. Those gaps often look like missing data rather than zero-activity days, which confuses downstream users and breaks automated reports that expect a row for every date.

The distinction between join_to_timespine: true with and without fill_nulls_with matters. The timespine join ensures every date appears. fill_nulls_with sets what value to show for dates with no data. For revenue, zero is typically correct. For a metric where null and zero have different meanings (say, a sensor reading where no measurement is genuinely different from a measurement of zero), you might want join_to_timespine without fill_nulls_with.

Date range and maintenance

Set your start date to before your earliest data and your end date far enough in the future that you won’t need to update it constantly. Starting at 2020-01-01 and ending at 2030-12-31 covers most projects for several years.

When you do need to extend the range, update the macro call and re-run dbt build -s metricflow_time_spine. The model is small — a decade of daily dates is around 3,650 rows — so rebuilding it is fast.

The time spine does not need to cover exactly the same range as your data. If your oldest order is from 2022, a time spine starting in 2020 works fine. MetricFlow only joins against dates that appear in query results.

Using a custom time spine column

If you already have a date dimension table in your project, you can point MetricFlow at it instead of generating a new model. The requirement is a column with one row per day in your configured granularity. The column must be named exactly as specified in standard_granularity_column.

For projects that need sub-day granularity (hourly metrics), MetricFlow supports configuring a higher-granularity time spine with an hour or minute datepart. You configure this alongside the standard daily spine.

Most projects only need the daily spine. If you’re building operational dashboards with hour-level granularity, you’d configure an additional time spine model at the appropriate granularity and reference it separately in your dbt_project.yml.