10  Calendar Date Dimension

A date dimension is a fancy synonym for “calendar”. I think having a calendar table is essential for any data warehouse. If you don’t have one yet, drop everything that you’re doing and go create one. Just trust me, you can thank me later.

10.1 Building it with DuckDB

There’s no one right way to build a calendar table. If you want to maintain an Excel file that gets regularly ingested, that’s fine. If you want to use a Python or R, that’s great too because they both have expressive ways to manipulate dates1. If you want to use SQL, that’s also fine. In DuckDB, it’s extremely easy because generate_series returns a list of dates that you can manipulate. Here’s some code that creates the calendar:

SELECT
    CAST(strftime(dates, '%Y%m%d') AS INTEGER) AS date_key,
    dates AS date,
    YEAR(dates) AS year,
    QUARTER(dates) AS quarter,
    MONTH(dates) AS month,
    monthname(dates) AS month_name,
    WEEK(dates) AS week,
    DAY(dates) AS day_of_month,
    DAYOFWEEK(dates) AS day_of_week,
    dayname(dates) AS day_name,
    DAYOFYEAR(dates) AS day_of_year,
    CASE WHEN DAYOFWEEK(dates) IN (0, 6) THEN true ELSE false END AS is_weekend,
    false AS is_holiday,  -- Can be updated with actual holidays
    CASE 
        WHEN MONTH(dates) >= 7 THEN YEAR(dates) + 1 
        ELSE YEAR(dates) 
    END AS fiscal_year,  -- Assuming fiscal year starts July 1
    CASE 
        WHEN MONTH(dates) >= 7 THEN QUARTER(dates) - 2
        ELSE QUARTER(dates) + 2
    END AS fiscal_quarter
FROM generate_series(
    DATE '2020-01-01',
    DATE '2025-12-31',
    INTERVAL '1 day'
) AS t(dates);

10.2 Adding Holidays

A good calendar table also needs a column whether a date is a holiday. Generally, there is no way to create a holiday field without some sort of dependency, so there is no best way of creating it. The simplest way is to create an Excel file with some years in advance that you would ingest on a yearly basis. In that case your dependency is someone on the team remembering to expand the table and updating the holidays.

A different way of handling holidays is to automate the creation of the holiday field. Basically, it would be a case when statement that checks whether the date is December 24th, December 25th, January 1st etc. The first wrinkle is that someone will still have to keep up with adding or removing holidays. In fact, in 2019, Lithuania declared November 2nd, All Souls’ day, a holiday. Holidays do change over time!

The second wrinkle is that calculating your own holidays can be tricky. For example, Easter Sunday is a holiday in many countries but it doesn’t have a fixed date. It falls on the first Sunday after the first full moon following the vernal equinox. In other words, it’s a moving target and there’s a whole Wikipedia article about the it. In SQL, you could create a macro or a function. Based on this great 2020 blog post, here’s an implementation of an algorithm published in New Scientist in 1961:

CREATE OR REPLACE MACRO easter_sunday(y) AS TABLE
SELECT easter_sunday_date
FROM (VALUES (y % 19, (y / 100)::int, y % 100))              AS e1(a, b, c)
CROSS JOIN LATERAL (VALUES (b / 4, b % 4))                            AS e2(d, e)
CROSS JOIN LATERAL (VALUES (FLOOR((8 * b + 13) / 25)))                 AS e3(g)
CROSS JOIN LATERAL (VALUES ((19*a + b - d - g + 15) % 30, FLOOR(c/4), c%4)) AS e4(h, i, k)
CROSS JOIN LATERAL (VALUES (FLOOR((32 + 2*e + 2*i - h - k) % 7)))           AS e5(l)
CROSS JOIN LATERAL (VALUES (FLOOR((a + 11*h + 19*l) / 433)))                 AS e6(m)
CROSS JOIN LATERAL (VALUES (FLOOR((h + l - 7*m + 90) / 25)))                 AS e7(n)
CROSS JOIN LATERAL (VALUES (FLOOR((h + l - 7*m + 33*n + 19) % 32)))         AS e8(p)
CROSS JOIN LATERAL (VALUES (make_date(y, n::int, p::int)))        AS e9(easter_sunday_date);

To join the dates to our date dimension, we generate a series of years to pass into our macro and then join the resulting table to the date dimension:

create or replace table dim_date_with_easter as 
with easter_sundays as (
SELECT 
  e.*
FROM generate_series(2020, 2025) AS t(y),
LATERAL easter_sunday(t.y) AS e
)
select 
  dim_date.*,
  case when easter_sunday_date is not null then 1 else 0 end as is_easter_sunday
from dim_date
left join easter_sundays
  on easter_sunday_date = date

select *
from dim_date_with_easter
where is_easter_sunday = 1
6 records
date_key date year quarter month month_name week day_of_month day_of_week day_name day_of_year is_weekend is_holiday fiscal_year fiscal_quarter is_easter_sunday is_easter_sunday_1
20200412 2020-04-12 2020 2 4 April 15 12 0 Sunday 103 TRUE FALSE 2020 4 1 1
20210404 2021-04-04 2021 2 4 April 13 4 0 Sunday 94 TRUE FALSE 2021 4 1 1
20220417 2022-04-17 2022 2 4 April 15 17 0 Sunday 107 TRUE FALSE 2022 4 1 1
20230409 2023-04-09 2023 2 4 April 14 9 0 Sunday 99 TRUE FALSE 2023 4 1 1
20240331 2024-03-31 2024 1 3 March 13 31 0 Sunday 91 TRUE FALSE 2024 3 1 1
20250420 2025-04-20 2025 2 4 April 16 20 0 Sunday 110 TRUE FALSE 2025 4 1 1

The final way to handle holidays is to use a service provider. There are paid providers but Nager.Date has a free API that you can use to get holidays for a given country and year. Here’s an R function that wraps it:

library(dplyr)
library(tidyr)
library(httr)

get_holidays <- function(country_code = "lt", year = "2023") {

  rs <- GET(glue::glue("https://date.nager.at/api/v3/publicholidays/{year}/{country_code}"))

  out <- content(rs) %>%
    tibble::enframe() %>%
    select(value) %>%
    unnest_wider(value) %>%
    transmute(date = as.Date(date),
           holiday_name_local = localName,
           holiday_name = name)

  return(out)

}

get_holidays(country_code = "lt", year = "2026")
# A tibble: 14 × 3
   date       holiday_name_local                       holiday_name             
   <date>     <chr>                                    <chr>                    
 1 2026-01-01 Naujieji metai                           New Year's Day           
 2 2026-02-16 Lietuvos valstybės atkūrimo diena        The Day of Restoration o…
 3 2026-03-11 Lietuvos nepriklausomybės atkūrimo diena Day of Restoration of In…
 4 2026-04-05 Velykos                                  Easter Sunday            
 5 2026-04-06 Antroji Velykų diena                     Easter Monday            
 6 2026-05-01 Tarptautinė darbo diena                  International Working Day
 7 2026-06-24 Joninės, Rasos                           St. John's Day           
 8 2026-07-06 Valstybės diena                          Statehood Day            
 9 2026-08-15 Žolinė                                   Assumption Day           
10 2026-11-01 Visų šventųjų diena                      All Saints' Day          
11 2026-11-02 Vėlinės                                  All Souls' Day           
12 2026-12-24 Šv. Kūčios                               Christmas Eve            
13 2026-12-25 Šv. Kalėdos                              Christmas Day            
14 2026-12-26 Šv. Kalėdos                              St. Stephen's Day

Of course, now you are dependent on an external provider but an external provider might make more sense if you are handling holidays across different countries. In all of the ways provided, you need someone or something to handle holiday changes.

10.3 Smart keys in the date dimension

In Kimball dimensional modelling every dimension and fact has a primary key, the date dimension is not an exception. In fact, you could use a smart key as the primary key and use 20260101 for a row with the date 2026-01-01. While this does make your warehouse consistent and let’s you create a reference to unknown values, I prefer joining directly on date columns. I feel like the added overhead of creating primary keys isn’t worth it considering that dates are stored as integers anyway and most databases handle NULL values in joins and aggregations just fine.

  1. Full disclosure, I used R to generate the date dimension for this book.↩︎