How to Generate a Time Spine in dbt

What Is a Time Spine and Why Do You Need One?

A time spine (also called a date spine) is a utility table that contains one row for every time period in a defined range. Most commonly, it has one row per day, but it can be hourly, weekly, or monthly depending on the grain you need.

The purpose is simple: event-based data has gaps. If no orders were placed on January 15th, your orders table has no row for that date. When you aggregate by day, January 15th disappears from the results. A chart built on that query shows a misleading jump from January 14th to January 16th instead of a zero on the 15th.

A time spine fixes this. By LEFT JOINing from the spine to your event data, every period gets a row — periods with events get actual values, periods without events get zero or null. This is essential for accurate time series reporting, dashboard visualizations, and metric calculations.

What Goes Wrong Without a Time Spine?

Consider a simple query that counts daily orders:

SELECT order_date, COUNT(*) AS order_count
FROM orders
GROUP BY order_date
ORDER BY order_date

This returns rows only for dates that have orders. The gaps cause three problems:

1. Charts mislead — line charts connect adjacent points, so a gap between Tuesday and Thursday looks like Wednesday had the same trend, when in reality there were zero orders.
2. Aggregations break — a rolling 7-day average that skips days produces incorrect results because it averages over fewer data points than expected.
3. Metrics layer errors — MetricFlow (dbt’s semantic layer) requires a time spine to correctly calculate cumulative and derived metrics. Without one, metric queries fail or return incorrect values.

How Do You Generate a Time Spine with dbt-utils?

The most portable approach uses the date_spine macro from dbt-utils. Create a model in your project:

-- models/utilities/time_spine.sql
{{ config(materialized='table') }}

WITH spine AS (
    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="cast('2020-01-01' as date)",
        end_date="cast('2026-12-31' as date)"
    ) }}
)

SELECT
    date_day
FROM spine

This generates one row per day from January 1, 2020 through December 31, 2026. The date_spine macro works across all dbt-supported warehouses because it uses cross-joins or recursive logic internally rather than database-specific functions.

Can You Use generate_series Instead?

For databases that support it, the native generate_series function is more performant:

-- Postgres / DuckDB
SELECT generate_series(
    '2020-01-01'::date,
    '2026-12-31'::date,
    '1 day'::interval
)::date AS date_day

generate_series is a SQL function that produces a set of values from a start to an end at a specified interval. It runs natively in the database engine, making it faster than the macro approach. The tradeoff is portability — this syntax does not work on Snowflake, BigQuery, or Databricks.

What About Databases Without Native Series Generation?

For databases that lack both generate_series and efficient cross-join support, a recursive CTE approach works:

WITH RECURSIVE date_series AS (
    SELECT CAST('2020-01-01' AS DATE) AS date_day
    UNION ALL
    SELECT DATEADD(day, 1, date_day)
    FROM date_series
    WHERE date_day < '2026-12-31'
)

SELECT date_day FROM date_series

This starts with a seed date and adds one day at a time until it reaches the end date. It is the least performant approach for large ranges but works on virtually every SQL database.

Which Approach Works Best for Each Warehouse?

Warehouse	Recommended Method	Notes
Snowflake	dbt_utils.date_spine	No native generate_series; macro uses GENERATOR table function internally
BigQuery	GENERATE_DATE_ARRAY or dbt_utils.date_spine	BigQuery has GENERATE_DATE_ARRAY which is native and performant
Postgres	generate_series	Native function, most performant option
DuckDB	generate_series	Same syntax as Postgres
Redshift	generate_series or dbt_utils.date_spine	Redshift supports generate_series but with some limitations on large ranges
Databricks	SEQUENCE or dbt_utils.date_spine	SEQUENCE function generates arrays that can be exploded into rows

If your project runs on a single warehouse, use the native approach for that platform. If you need portability across warehouses, dbt_utils.date_spine is the safest choice.

What Does MetricFlow Require for the Time Spine?

dbt’s semantic layer (powered by MetricFlow) requires a time spine table to calculate cumulative metrics, derived metrics, and fill-values. The required format is specific:

-- models/utilities/metricflow_time_spine.sql
{{ config(materialized='table') }}

WITH spine AS (
    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="cast('2020-01-01' as date)",
        end_date="cast('2026-12-31' as date)"
    ) }}
)

SELECT
    date_day,
    DATE_TRUNC('week', date_day) AS date_week,
    DATE_TRUNC('month', date_day) AS date_month,
    DATE_TRUNC('quarter', date_day) AS date_quarter,
    DATE_TRUNC('year', date_day) AS date_year
FROM spine

MetricFlow looks for a model registered as a time spine in your dbt_project.yml or semantic manifest. The model must have date_day as the primary time column. Adding truncated columns for other grains allows MetricFlow to aggregate metrics at weekly, monthly, or quarterly levels without additional models.

Where Should the Time Spine Live in Your Project?

Most teams place the time spine in a utilities or staging directory:

models/
  staging/
    ...
  utilities/
    time_spine.sql
  intermediate/
    ...
  marts/
    ...

The time spine is not a staging model (it does not clean raw data) and not a mart (it is not business-facing). It is infrastructure — a utility that other models reference. Placing it in a dedicated utilities folder makes its purpose clear in your DAG.

Grain selection: daily is the most common grain because it balances storage and utility. Hourly spines are large but necessary for real-time dashboards. Weekly or monthly spines are rare as standalone models — it is simpler to truncate a daily spine.

Range management: hardcoded start and end dates are simple but require manual updates. Dynamic ranges using MIN(date) from source data and CURRENT_DATE are more maintainable but add a dependency on source freshness.

How Do You Validate Time Spine Changes?

Modifying a time spine — changing the date range, switching from daily to hourly grain, or adjusting the generation method — affects every model that joins to it. This makes time spine changes high-impact despite their apparent simplicity.

Before merging a time spine change, validate:

• Row count — does the new spine have the expected number of rows? A daily spine from 2020-01-01 to 2026-12-31 should have exactly 2,557 rows.
• Date range — does MIN(date_day) and MAX(date_day) match your intended range?
• No duplicates — is date_day unique? A duplicate date in the spine will double-count events when joined.
• Downstream impact — run data diffs on models that reference the time spine to confirm they still produce correct results. Tools like Recce can compare the output of downstream models between your PR branch and production, surfacing differences caused by the spine change.

These checks are especially important because time spine errors are silent — they do not cause query failures, they cause subtly wrong numbers.

Summary

A time spine is a utility table with one row per time period, used to fill gaps in event-based data and enable accurate metrics. The most portable generation method is the dbt_utils.date_spine macro; databases like Postgres and DuckDB can use native generate_series for better performance; recursive CTEs work as a universal fallback. MetricFlow requires a specific time spine format with date_day as the primary column. Place the time spine in a utilities directory, default to daily grain, and validate changes carefully — especially row counts, date ranges, and downstream data diffs — because time spine errors produce silently wrong results rather than failures.