Quickstart with MetricFlow time spine

Quickstart

Semantic Layer

Intermediate

Introduction

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

What is a time spine table?

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

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

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

And once you have a time spine table, 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 table from scratch. If you have a dbt project set up already and have your own time spine table (like a dim_date type model), you can skip this step and go to Use an existing dim_date table.

The time spine table 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 table — 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 table of daily dates ranging from 5 years in the past to 30 days into the future.

Run and preview the model to create the table:

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

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

Preview the time spine table 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 table

Using an existing dim_date table

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

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

Locate the existing table (dim_date).

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

_models.yml

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

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

Run and preview the time spine

For the time spine table you created, let's run it and preview the output.

Run the following command:

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

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

Preview the time spine table in dbt Cloud IDE
Check that the table:
- 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 table/metrics using Semantic Layer commands to validate the time spine.

Let's say you have a revenue metric defined. You can query the table/metrics using the following command:
```
dbt sl query --metrics revenue --group-by metric_time
```
This will output results similar to the following in the dbt 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 tables 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 _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 table:

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

Validate the output by querying the generated table:
```
dbt sl query --metrics orders --group-by metric_time__year
```
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.
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 table and configure it in YAML.

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 table:
```
dbt run --select fiscal_calendar
dbt show --select fiscal_calendar # Use this command to preview the table if developing locally
```
If you're developing in the dbt Cloud IDE, you can preview the table by clicking the Preview button in the model page.
Validate the output by querying the generated table 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 dbt Semantic Layer to your organization. You've learned:

How to create a time spine table or use an existing table.
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 table?​

Prerequisites​

Add a time spine SQL model​

Add YAML configuration for the time spine​

Using an existing dim_date table​

Run and preview the time spine​

Add additional granularities​

Yearly time spine​

Custom calendars​

What's next​

Introduction

What is a time spine table?

Prerequisites

Add a time spine SQL model

Add YAML configuration for the time spine

Using an existing dim_date table

Run and preview the time spine

Add additional granularities

Yearly time spine

Custom calendars

What's next