Quickstart with MetricFlow time spine

Updated

dbt Cloud

Quickstart

Intermediate

Introduction

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

Prerequisites

Before you start, make sure you have:

A dbt project set up. If you don't have one, follow the Quickstart guides guide to help you get started.

What is a time spine?

A time spine is essential for time-based joins and aggregations in MetricFlow, the engine that powers the dbt Semantic Layer. It can be created from scratch or configured using an existing table (like a dim_date table).

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!

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',
        schema = 'marts',
        alias = 'time_spine_daily'
    )
}}

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.

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 2000-01-01 to 2030-01-01.
    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
        tests:
          - not_null
          - unique
        granularity: day

This time spine YAML file:

Defines date_day as the base column for daily granularity.
Adds tests for uniqueness and non-null values.
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 test the time spine

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

Run the time spine model to create the table:
```
dbt run --select time_spine_daily
```

Validate the output by querying the generated table:

select * from your_project_schema.time_spine_daily

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 time spine output in dbt Cloud IDE
Double check that the results are correct and returning the expected data.

Add additional granularities (optional)

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

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

models/marts/time_spine_hourly.sql

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

with hours as (

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

),

final as (
    select cast(date_hour as timestamp) as date_hour
    from hours
)

select * from final
-- filter the time spine to a specific range
where date_hour > dateadd(year, -4, current_timestamp()) 
  and date_hour < dateadd(day, 30, current_timestamp())

Then update _models.yml file and add the hourly 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_hourly
    description: A time spine with one row per hour, ranging from 2000-01-01 to 2030-01-01.
    time_spine:
      standard_granularity_column: date_hour
    columns:
      - name: date_hour
        granularity: hour

Run the model to create the table:
```
dbt run --select time_spine_hourly
```

Validate the output by querying the generated table:

dbt sl query --metrics revenue --group-by metric_time__hour

tip

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!

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​

Prerequisites​

What is a time spine?​

Add a time spine SQL model​

Add YAML configuration for the time spine​

Using an existing dim_date table​

Run and test the time spine​

Add additional granularities (optional)​

What's next​