Creating a Calendar Table Using Date Spine

Create a dim_calendar table in dbt and duckdb engine with the following columns and sample values:

1. date_id = 2022-01-01 (format: YYYY-MM-DD)
2. year = 2022
3. month = 1
4. month_name = January
5. day = 1
6. month_year = January 2022
7. month_first_day = 2022-01-01
8. quarter = Q1
9. quarter_index = 1
10. semester = S1
11. semester_index = 1
12. day_of_week = 6
13. day_of_week_name = Saturday
14. is_weekday = Weekend
15. week_of_year = 52
16. day_of_year = 1
17. month_year_sort = 202201

Using Date Spine, we can generate a series of dates.

WITH date_spine AS (
    SELECT date_series
    FROM 
        generate_series(DATE '2015-01-01', DATE '2035-12-31', INTERVAL 1 DAY) AS t(date_series)
),
 
calendar_table AS (
    SELECT
        date_series AS date_id,
        EXTRACT(YEAR FROM date_series) AS year,
        EXTRACT(MONTH FROM date_series) AS month,
        EXTRACT(DAY FROM date_series) AS day,
        STRFTIME(date_series, '%B') AS month_name,
        STRFTIME(date_series, '%B %Y') AS month_year,
        DATE_TRUNC('MONTH', date_series) AS month_first_day,
        'Q' || EXTRACT(QUARTER FROM date_series) AS quarter,
        EXTRACT(QUARTER FROM date_series) AS quarter_index,
        CASE
            WHEN EXTRACT(MONTH FROM date_series) <= 6 THEN 'S1'
            ELSE 'S2'
        END AS semester,
        CASE
            WHEN EXTRACT(MONTH FROM date_series) <= 6 THEN 1
            ELSE 2
        END AS semester_index,
        EXTRACT(DOW FROM date_series) AS day_of_week,
        STRFTIME(date_series, '%A') AS day_of_week_name,
        CASE 
            WHEN EXTRACT(DOW FROM date_series) IN (0, 6) THEN 'Weekend'
            ELSE 'Weekday'
        END AS is_weekday,
        EXTRACT(WEEK FROM date_series) AS week_of_year,
        EXTRACT(DOY FROM date_series) AS day_of_year,
        STRFTIME(date_series, '%Y%d')::INTEGER AS month_year_sort
    FROM date_spine
)
 
SELECT * FROM calendar_table