dbt-date for date spine

Introduction

A date spine is a list of all dates in a range. Using dbt_date helps create this list easily for analysis.

You want to analyze sales data by every day, even if some days have no sales.

You need a complete calendar table for joining with other data.

You want to fill gaps in time series data for reports.

You want to create monthly or weekly summaries based on daily data.

You want to compare data across consistent date ranges.

Syntax

dbt

{{
  dbt_date.spine(
    start_date='YYYY-MM-DD',
    end_date='YYYY-MM-DD',
    datepart='day'
  )
}}

start_date and end_date define the range of dates.

datepart can be 'day', 'week', 'month', etc., to control the granularity.

Examples

Creates a date spine with daily dates from January 1 to January 7, 2024.

dbt

{{ dbt_date.spine(start_date='2024-01-01', end_date='2024-01-07', datepart='day') }}

Creates a date spine with monthly dates from January to March 2024.

dbt

{{ dbt_date.spine(start_date='2024-01-01', end_date='2024-03-31', datepart='month') }}

Sample Program

This dbt model creates a date spine from January 1 to January 5, 2024, with daily dates. It outputs a table with one row per date.

dbt

{% set date_spine = dbt_date.spine(start_date='2024-01-01', end_date='2024-01-05', datepart='day') %}

select * from {{ date_spine }}

Important Notes

The date spine helps avoid missing dates in time series analysis.

You can join this spine with other tables to fill missing dates with zeros or nulls.

Make sure your start_date and end_date cover the full range you need.

Summary

dbt_date spine creates a list of dates for a range.

It helps analyze data with consistent date coverage.

Use start_date, end_date, and datepart to control the spine.