dbt macro

Generate Date Dimension

Generate a complete date dimension for Snowflake, including fiscal periods and Dutch public holidays.

Goal

A date dimension is the backbone of every data warehouse. This macro generates a complete dim_datum table for Snowflake in one step, with all calendar and fiscal attributes you need for time-based analyses. No more manual SQL, no missing holidays.

The macro calculates Dutch public holidays automatically, including moveable holidays like Easter and Pentecost (via the Computus algorithm), and accounts for the Sunday shift rule for King's Day.

Parameters

Parameter	Default	Description
start_date	'2000-01-01'	Start date of the dimension
end_date	'2030-12-31'	End date of the dimension
fiscal_year_start_month	1	Start month of the fiscal year (1–12)
dim_datum_taal	'nl'	Language for names: 'nl' or 'en'
schoolvakanties	none	Optional table with school holiday data (van_datum, tot_datum, vakantie_naam, land, regio).
schoolvakantie_land	none	Filter by country, e.g. 'NL'. Requires schoolvakanties.
schoolvakantie_regio	none	Filter by region, e.g. 'Noord'. Requires schoolvakanties.

What to expect

The generated table contains columns across five dimensions:

Day

dag_nummerdag_van_het_jaardag_van_de_weeknaam_van_de_dagweekend_vlagwerkdag_vlag

Week

kalenderweekiso_week_nummeriso_week_jaariso_week_label

Month

maandnummermaandnaammaandafkortingmaand_label

Quarter & Year

kwartaalkwartaal_labelkalenderjaarfiscaal_jaarfiscale_maandfiscaal_kwartaal

Holidays

is_feestdagnaam_feestdag

School holidays (optional)

is_schoolvakantieschoolvakantie_naam

Usage

1. Create a dbt model

Create a file called dim_datum.sql in your dbt models folder and call the macro in it:

{{ generate_date_dimension(
    start_date = '2000-01-01',
    end_date   = '2035-12-31'
) }}

2. Set fiscal year

Does your fiscal year start in April? Pass the start month:

{{ generate_date_dimension(
    fiscal_year_start_month = 4
) }}

3. Add school holidays

Connect a table with school holiday data and optionally filter by country and region. The macro adds is_schoolvakantie and schoolvakantie_naam to the output:

{{ generate_date_dimension(
    schoolvakanties      = ref('schoolvakanties'),
    schoolvakantie_land  = 'NL',
    schoolvakantie_regio = 'Noord'
) }}

Column	Type	Description
van_datum	DATE	First day of the holiday
tot_datum	DATE	Last day of the holiday
vakantie_naam	TEXT	Name, e.g. "Summer holiday"
land	TEXT	Country code, e.g. 'NL'
regio	TEXT	Region or null for the whole country

Supported countries and data sources:

Country	Regions	Source
NL	Noord, Midden, Zuid	rijksoverheid.nl
BE	NL, FR, DE gemeenschappen	onderwijs.vlaanderen.be / enseignement.be
DE	16 Bundesländer	kmk.org
GB	Per local authority	gov.uk
FR	Zones A, B, C	education.gouv.fr
US	School district niveau	Geen centrale bron

4. Configure language

Set the language via a dbt variable in dbt_project.yml:

vars:
  dim_datum_taal: 'nl'   # or 'en' for English column values

View on GitHub All utils