Dim Date

1-- Conformed date dimension: one row per calendar day with common attributes.
2-- Uses dbt_utils.date_spine; adjust the range and your warehouse's date functions as needed.
3{{ config(materialized='table') }}
4
5with spine as (
6    {{ dbt_utils.date_spine(
7        datepart="day",
8        start_date="cast('2020-01-01' as date)",
9        end_date="cast('2031-01-01' as date)"
10    ) }}
11)
12select
13    date_day::date                          as date,
14    extract(year   from date_day)           as year,
15    extract(month  from date_day)           as month,
16    extract(day    from date_day)           as day_of_month,
17    date_trunc('week',  date_day)::date      as week_start,
18    date_trunc('month', date_day)::date      as month_start,
19    extract(quarter from date_day)          as quarter,
20    extract(dow from date_day)              as day_of_week,
21    extract(dow from date_day) in (0, 6)    as is_weekend
22from spine