Skip to main content

MetricFlow time spine

It's common in analytics engineering to have a date dimension or "time spine" table as a base table for different types of time-based joins and aggregations. The structure of this table is typically a base column of daily or hourly dates, with additional columns for other time grains, like fiscal quarters, defined based on the base column. You can join other tables to the time spine on the base column to calculate metrics like revenue at a point in time, or to aggregate to a specific time grain.

MetricFlow requires you to define at least one dbt model which provides a time-spine, and then specify (in YAML) the columns to be used for time-based joins. MetricFlow will join against the time-spine model for the following types of metrics and dimensions:

To see the generated SQL for the metric and dimension types that use time spine joins, refer to the respective documentation or add the compile=True flag when querying the Semantic Layer to return the compiled SQL.

Configuring time spine in YAML

Time spine models are normal dbt models with extra configurations that tell dbt and MetricFlow how to use specific columns by defining their properties. Add the models key for the time spine in your models/ directory. If your project already includes a calendar table or date dimension, you can configure that table as a time spine. Otherwise, review the example time-spine tables to create one.

Some things to note when configuring time spine models:

  • Add the configurations under the time_spine key for that model's properties, just as you would add a description or tests.
  • You only need to configure time-spine models that the Semantic Layer should recognize.
  • At a minimum, define a time-spine table for a daily grain.
  • You can optionally define additional time-spine tables for different granularities, like hourly. Review the granularity considerations when deciding which tables to create.
  • If you're looking to specify the grain of a time dimension so that MetricFlow can transform the underlying column to the required granularity, refer to the Time granularity documentation
tip

If you previously used a model called metricflow_time_spine, you no longer need to create this specific model. You can now configure MetricFlow to use any date dimension or time spine table already in your project by updating the model setting in the Semantic Layer.

If you don’t have a date dimension table, you can still create one by using the code snippet in the next section to build your time spine model.

Creating a time spine table

MetricFlow supports granularities ranging from milliseconds to years. Refer to the Dimensions page (time_granularity tab) to find the full list of supported granularities.

To create a time spine table from scratch, you can do so by adding the following code to your dbt project. This example creates a time spine at an hourly grain and a daily grain: time_spine_hourly and time_spine_daily.

models/_models.yml
models: 
# Hourly time spine
- name: time_spine_hourly
description: my favorite time spine
time_spine:
standard_granularity_column: date_hour # column for the standard grain of your table, must be date time type.
custom_granularities:
- name: fiscal_year
column_name: fiscal_year_column
columns:
- name: date_hour
granularity: hour # set granularity at column-level for standard_granularity_column

# Daily time spine
- name: time_spine_daily
time_spine:
standard_granularity_column: date_day # column for the standard grain of your table
columns:
- name: date_day
granularity: day # set granularity at column-level for standard_granularity_column
Time spine directory structureTime spine directory structure
  • This example configuration shows a time spine model called time_spine_hourly and time_spine_daily. It sets the time spine configurations under the time_spine key.
  • The standard_granularity_column is the column that maps to one of our standard granularities. This column must be set under the columns key and should have a grain that is finer or equal to any custom granularity columns defined in the same model.
    • It needs to reference a column defined under the columns key, in this case, date_hour and date_day, respectively.
    • It sets the granularity at the column-level using the granularity key, in this case, hour and day, respectively.
  • MetricFlow will use the standard_granularity_column as the join key when joining the time spine table to another source table.
  • The custom_granularities field, (available in dbt Cloud Latest and dbt Core v1.9 and higher) lets you specify non-standard time periods like fiscal_year or retail_month that your organization may use.

For an example project, refer to our Jaffle shop example.

Considerations when choosing which granularities to create

  • MetricFlow will use the time spine with the largest compatible granularity for a given query to ensure the most efficient query possible. For example, if you have a time spine at a monthly grain, and query a dimension at a monthly grain, MetricFlow will use the monthly time spine. If you only have a daily time spine, MetricFlow will use the daily time spine and date_trunc to month.
  • You can add a time spine for each granularity you intend to use if query efficiency is more important to you than configuration time, or storage constraints. For most engines, the query performance difference should be minimal and transforming your time spine to a coarser grain at query time shouldn't add significant overhead to your queries.
  • We recommend having a time spine at the finest grain used in any of your dimensions to avoid unexpected errors. For example, if you have dimensions at an hourly grain, you should have a time spine at an hourly grain.

Example time spine tables

Daily

metricflow_time_spine.sql
{{
config(
materialized = 'table',
)
}}

with days as (

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

),

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

select * from final
where date_day > dateadd(year, -4, current_timestamp())
and date_day < dateadd(day, 30, current_timestamp())

Daily (BigQuery)

Use this model if you're using BigQuery. BigQuery supports DATE() instead of TO_DATE():

metricflow_time_spine.sql

{{config(materialized='table')}}
with days as (
{{dbt.date_spine(
'day',
"DATE(2000,01,01)",
"DATE(2025,01,01)"
)
}}
),

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

select *
from final
-- filter the time spine to a specific range
where date_day > date_add(DATE(current_timestamp()), INTERVAL -4 YEAR)
and date_day < date_add(DATE(current_timestamp()), INTERVAL 30 DAY)

Hourly

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_day > dateadd(year, -4, current_timestamp())
and date_hour < dateadd(day, 30, current_timestamp())

Custom calendar Preview

Custom date transformations can be complex, and organizations often have unique needs that can’t be easily generalized. Creating a custom calendar model allows you to define these transformations in SQL, offering more flexibility than native transformations in MetricFlow. This approach lets you map custom columns back to MetricFlow granularities, ensuring consistency while giving you control over the transformations.

For example, if you use a custom calendar in your organization, such as a fiscal calendar, you can configure it in MetricFlow using its date and time operations.

  • This is useful for calculating metrics based on a custom calendar, such as fiscal quarters or weeks.
  • Use the custom_granularities key to define a non-standard time period for querying data, such as a retail_month or fiscal_week, instead of standard options like day, month, or year.
  • This feature provides more control over how time-based metrics are calculated.
 Data types and time zone considerations

Add custom granularities

To add custom granularities, the Semantic Layer supports custom calendar configurations that allow users to query data using non-standard time periods like fiscal_year or retail_month. You can define these custom granularities (all lowercased) by modifying your model's YAML configuration like this:

models/_models.yml
models:
- name: my_time_spine
description: my favorite time spine
time_spine:
standard_granularity_column: date_day
custom_granularities:
- name: fiscal_year
column_name: fiscal_year_column

Coming soon

Note that features like calculating offsets and period-over-period will be supported soon!

0