How to create a time spine model in dbt (Snowflake)

The Semantic Layer needs a table with one row per day, the time spine. It uses it for running totals, for comparisons with earlier periods and to fill days that have no data. You build it as an ordinary dbt model and mark it with the time_spine property.

Copy and paste

The model (Snowflake SQL):

-- models/marts/time_spine_daily.sql
{{ config(materialized='table') }}

with days as (
    {{ dbt.date_spine('day', "to_date('2020-01-01')", "dateadd(day, 30, current_date)") }}
)

select cast(date_day as date) as date_day
from days

The configuration:

# models/marts/_time_spine.yml
version: 2
models:
  - name: time_spine_daily
    time_spine:
      standard_granularity_column: date_day
    columns:
      - name: date_day
        granularity: day

dbt build -s time_spine_daily

How it works

dbt.date_spine generates one row per day between the two dates. The end date is not included.
time_spine: tells the Semantic Layer that this model is the calendar. standard_granularity_column names the date column, and that column needs granularity: day.
The model name is free. Older projects used a model called metricflow_time_spine; once the YAML above is in place you can delete it.

Which metrics need it

cumulative metrics, such as running totals and month to date;
derived metrics with offset_window or offset_to_grain, such as growth on the previous month;
conversion metrics;
any metric with join_to_timespine: true, which returns a row for days without data.

Simple metrics grouped by month do not read it, but the Semantic Layer expects a daily time spine in every project.

How far it should run

Start before your oldest data: a date in 2020 covers most projects.
End a little after today. Cumulative metrics continue until the last day of the spine: with a spine running to the end of the year, a running total repeats the same value for every future month. dateadd(day, 30, current_date) keeps the spine just ahead of the data, and rebuilding the model daily moves it forward.

The table stays small, a few thousand rows, so table is the right materialization.

Other databases

dbt.date_spine works on every adapter; only the date expressions change. On DuckDB, for example: "cast('2020-01-01' as date)". For metrics at an hourly grain, add a second spine with an hourly column and granularity: hour.

The model and the configuration were tested with dbt Core 1.12 and MetricFlow 0.213 on DuckDB; the Snowflake date expressions follow the dbt documentation.

References

MetricFlow time spine