Quickstart with MetricFlow time spine

Quickstart

Semantic Layer

Intermediate

Introduction

This guide explains how to configure a time spine using the Semantic Layer Jaffle shop example project as a reference.

What is a time spine model?

A time spine is essential for time-based joins and aggregations in MetricFlow, the engine that powers the Semantic Layer.

To use MetricFlow with time-based metrics and dimensions, you must provide a time spine. This serves as the foundation for time-based joins and aggregations. You can either:

Create a time spine SQL model from scratch, or
Use an existing model in your project, like dim_date.

And once you have a time spine, you need to configure it in YAML to tell MetricFlow how to use it. This guide will show you how to do both!

Prerequisites

Before you start, make sure you have:

A dbt project set up. If you don't have one, follow the Semantic Layer quickstart guide or the dbt Cloud quickstart guides guide to help you get started.

Add a time spine SQL model

Let's get started by assuming you're creating a time spine from scratch. If you have a dbt project set up already and have your own time spine (like a dim_date type model), you can skip this step and go to Use an existing dim_date model.

The time spine is a dbt model that generates a series of dates (or timestamps) at a specific granularity. In this example, let's create a daily time spine — time_spine_daily.sql.

Navigate to the models/marts directory in your dbt project.

Add a new SQL file named time_spine_daily.sql with the following content:

models/marts/time_spine_daily.sql

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

with

base_dates as (
    {{
        dbt.date_spine(
            'day',
            "DATE('2000-01-01')",
            "DATE('2030-01-01')"
        )
    }}
),

final as (
    select
        cast(date_day as date) as date_day
    from base_dates
)

select *
from final
where date_day > dateadd(year, -5, current_date())  -- Keep recent dates only
  and date_day < dateadd(day, 30, current_date());

This generates a model of daily dates ranging from 5 years in the past to 30 days into the future.

Run and preview the model to create the model:

dbt run --select time_spine_daily 
dbt show --select time_spine_daily # Use this command to preview the model if developing locally

If developing in the Cloud IDE, you can preview the model by clicking the Preview button:

Preview the time spine model in dbt Cloud IDE

Add YAML configuration for the time spine

Now that you've created the SQL file, configure it in YAML so MetricFlow can recognize and use it.

Navigate to the models/marts directory.

Add a new YAML file named _models.yml with the following content:

models/marts/_models.yml

models:
  - name: time_spine_daily
    description: A time spine with one row per day, ranging from 5 years in the past to 30 days into the future.
    time_spine:
      standard_granularity_column: date_day  # The base column used for time joins
    columns:
      - name: date_day
        description: The base date column for daily granularity
        granularity: day

This time spine YAML file:

Defines date_day as the base column for daily granularity.
Configures time_spine properties so MetricFlow can use the model.

Using an existing dim_date model

This optional approach reuses an existing model, saving you the effort of creating a new one. However if you created a time spine from scratch, you can skip this section.

If your project already includes a dim_date or similar model, you can configure it as a time spine:

Locate the existing model (dim_date).

Update _models.yml file to configure it as a time spine:

_models.yml

models:
  - name: dim_date
    description: An existing date dimension model used as a time spine.
    time_spine:
      standard_granularity_column: date_day
    columns:
      - name: date_day
        granularity: day
      - name: day_of_week
        granularity: day
      - name: full_date
        granularity: day

This time spine YAML file configures the time_spine property so MetricFlow can use the model.

Run and preview the time spine

For the time spine you created, let's run it and preview the output if you haven't already done this. If you have already run the model, you can skip this step.

Run the following command:

dbt run --select time_spine_daily
dbt show --select time_spine_daily # Use this command to preview the model if developing locally

If developing in the Cloud IDE, you can preview the model by clicking the Preview button:

Preview the time spine model in dbt Cloud IDE
Check that the model:
- Contains one row per day.
- Covers the date range you want (5 years back to 30 days forward).
(Optional) If you have metrics already defined in your project, you can query the model/metrics using Semantic Layer commands to validate the time spine.

Let's say you have a revenue metric defined. You can query the model/metrics using the following command:
```
dbt sl query --metrics revenue --group-by metric_time
```
This will output results similar to the following in the Cloud IDE:

Validate the metrics and time spine output in dbt Cloud IDE
Double check that the results are correct and returning the expected data.

Add additional granularities

This section is optional and will show you how to add additional granularities to your time spine:

Yearly
Custom calendars

Yearly time spine

To support multiple granularities (like hourly, yearly, monthly), create additional time spine models and configure them in YAML.

Add a new SQL file named time_spine_yearly.sql with the following content:

models/marts/time_spine_yearly.sql

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

with years as (

    {{
        dbt.date_spine(
            'year',
            "to_date('01/01/2000','mm/dd/yyyy')",
            "to_date('01/01/2025','mm/dd/yyyy')"
        )
    }}

),

final as (
    select cast(date_year as date) as date_year
    from years
)

select * from final
-- filter the time spine to a specific range
where date_year >= date_trunc('year', dateadd(year, -4, current_timestamp())) 
  and date_year < date_trunc('year', dateadd(year, 1, current_timestamp()))

Then update the _models.yml file and add the yearly time spine (below the daily time spine config):

_models.yml

models:
  - name: time_spine_daily
    ... rest of the daily time spine config ...

  - name: time_spine_yearly
    description: time spine one row per house
    time_spine:
      standard_granularity_column: date_year
    columns:
      - name: date_year
        granularity: year

Run or preview the model to create the model:

dbt run --select time_spine_yearly
dbt show --select time_spine_yearly # Use this command to preview the model if developing locally

Validate the output by querying the generated model:

dbt sl query --metrics orders --group-by metric_time__year

If you're developing in the Cloud IDE, you can preview the model by clicking the Preview button.

Validate the metrics and time spine output in dbt Cloud IDE

Extra credit!

For some extra practice, try one of the following exercises:

Order the dbt sl query --metrics orders --group-by metric_time__year command output by ascending order of metric_time__year. Check out the dbt Semantic Layer commands docs for more information on how to do this.
Filter to this year and last year only to limit data returned.
Try creating a monthly time spine — duplicate your daily time spine model, adjust it to generate one row per month, and update the YAML file to include granularity: month. Give it a try!

Custom calendars

To support custom calendars (like fiscal years, fiscal quarters, and so on), create an additional time spine and configure it in YAML. This feature is available in the dbt Cloud's Latest release track or dbt Core 1.9 and later.

Add a new SQL file named fiscal_calendar.sql with the following content (or use your own custom calendar and configure it in YAML):

models/marts/fiscal_calendar.sql

    with date_spine as (

    select 
        date_day,
        extract(year from date_day) as calendar_year,
        extract(week from date_day) as calendar_week

    from {{ ref('time_spine_daily') }}

),

fiscal_calendar as (

    select
        date_day,
        -- Define custom fiscal year starting in October
        case 
            when extract(month from date_day) >= 10 
                then extract(year from date_day) + 1
            else extract(year from date_day) 
        end as fiscal_year,

        -- Define fiscal weeks (e.g., shift by 1 week)
        extract(week from date_day) + 1 as fiscal_week

    from date_spine

)

select * from fiscal_calendar

Then update _models.yml file and add the fiscal calendar time spine (below the yearly time spine config):

_models.yml

models:
  - name: time_spine_yearly
    ... rest of the yearly time spine config ...  
    
  - name: fiscal_calendar
    description: A custom fiscal calendar with fiscal year and fiscal week granularities.
    time_spine:
      standard_granularity_column: date_day
      custom_granularities:
        - name: fiscal_year
          column_name: fiscal_year
        - name: fiscal_week
          column_name: fiscal_week
    columns:
      - name: date_day
        granularity: day
      - name: fiscal_year
        description: "Custom fiscal year starting in October"
      - name: fiscal_week
        description: "Fiscal week, shifted by 1 week from standard calendar"

Run or preview the model to create the model:
```
dbt run --select fiscal_calendar
dbt show --select fiscal_calendar # Use this command to preview the model if developing locally
```
If you're developing in the Cloud IDE, you can preview the model by clicking the Preview button.
Validate the output by querying the generated model along with your metrics:
```
dbt sl query --metrics orders --group-by metric_time__fiscal_year
```
Validate the custom calendar metrics and time spine output in dbt Cloud IDE

What's next

Congratulations 🎉! You've set up a time spine and are ready to bring the benefits of MetricFlow and the Semantic Layer to your organization. You've learned:

How to create a time spine or use an existing model.
How to configure a time spine in YAML.
How to add additional granularities to your time spine.

Here are some additional resources to help you continue your journey:

Introduction​

What is a time spine model?​

Prerequisites​

Add a time spine SQL model​

Add YAML configuration for the time spine​

Using an existing dim_date model​

Run and preview the time spine​

Add additional granularities​

Yearly time spine​

Custom calendars​

What's next​

Introduction

What is a time spine model?

Prerequisites

Add a time spine SQL model

Add YAML configuration for the time spine

Using an existing dim_date model

Run and preview the time spine

Add additional granularities

Yearly time spine

Custom calendars

What's next