Creating Date Dimensions using dbt in Azure Synapse: Custom Macros

Some dbt packages come equipped with useful macros for generating date dimension tables. However, these macros often face limitations when used with Azure Synapse or similar Microsoft data warehousing (DWH) solutions.

For instance, the dbt_utils package includes the date_spine macro, but it doesn’t work as expected within Azure Synapse. This macro relies on a nested Common Table Expression (CTE), which isn’t compatible with this environment.¹

Similarly, the dbt_date package provides the get_date_dimension macro, which seems very practical. Unfortunately, this package lacks support for Azure Synapse.²

We can avoid these issues by writing our own macro. Below, you’ll find a dbt macro that helps us create date dimension tables when using Azure Synapse and the likes.

the dim_date_azure macro

Let’s begin by creating the file dim_date_azure.sql in the macros/ folder. The file will contain the following query, which allows us to generate a basic date spine between two dates:

The above macro uses the internal system tables of our DWH to generate a table with multiple rows, which is then transformed into dates.

the dim_date model

After saving the macro, we can use it to create a model in our models/ folder. Since we anticipate using these dates alongside other production tables, it makes sense to materialize the model as a table among our other production tables.

For example, let’s create a date dimension table spanning ten years, from 2015 to 2025:

After materialization, the resulting table will have a d column with a series of dates between the start_date and end_date.

using it dynamically

We can also use the above dim_date_azure macro dynamically by using the run_query macro in the same model. For example, we can set end_date to be today’s date plus six months:

In this way, every time we materialize dim_date, the table will be updated with new dates accordingly. Happy querying.