{"id":106113,"date":"2025-04-23T19:28:28","date_gmt":"2025-04-23T19:28:28","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=106113"},"modified":"2025-04-25T14:38:27","modified_gmt":"2025-04-25T14:38:27","slug":"how-to-load-a-date-dimension-in-microsoft-fabric-using-macros-in-dbt","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/business-intelligence\/general-analytics\/how-to-load-a-date-dimension-in-microsoft-fabric-using-macros-in-dbt\/","title":{"rendered":"Loading a Date Dimension in Microsoft Fabric Warehouse with dbt Macros: Complete Tutorial with Generated Calendar Table"},"content":{"rendered":"\n

<\/h1>\n\n\n\n

With the popular data transformation tool dbt<\/a> (data build tool), we have a bunch of interesting features at our disposal to write SQL more efficiently. One of those features is macros<\/strong>, which we\u2019ll introduce in this article (don\u2019t worry; they\u2019re not related to the Excel VBA macros of your nightmares).<\/p>\n\n\n\n

The article A gentle introduction to dbt<\/a> explains how you can get dbt in the cloud version, how you can set up a free account, and how to create a connection to a Microsoft Fabric warehouse. The article Loading Models from Source Data with dbt<\/a> expands this setup further by showing how you can define your source tables (loading source tables is not possible in dbt, the tool can only do transformations using SQL) and how to load some sample dimension and fact tables. It\u2019s recommended to go through these articles first if you haven\u2019t already because we will build upon them.<\/p>\n\n\n\n

Using Macros to Create a Date Dimension<\/h2>\n\n\n\n

Every data warehouse typically has a date dimension. Since almost all data analysis involves time in some manner, it\u2019s probably the most important dimension in your data warehouse project. There are plenty of online resources on how to create an SQL SELECT<\/code> statement that will generate a date table. In this article, we\u2019ll go one step further and create one using reusable logic. In dbt, we can accomplish this with packages and macros.<\/p>\n\n\n\n

Installing a Package into your dbt Project<\/h3>\n\n\n\n

There are numerous packages available for dbt. Packages<\/a> are extensible logic that you can import into your dbt project, very similar to extensions in Visual Studio Code for example, or modules\/packages in Python. You can find a list of available packages at hub.getdbt.com<\/a>, and like dbt they are open-source. One widely used package is dbt-util<\/a>, which offers a range of useful macros. To install a package, we need to add a packages.yml<\/em> file to the project.<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

Then create a new packages.yml<\/code> file:<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

<\/p>\n\n\n\n

Inside this file, we can specify the following configuration:<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

<\/p>\n\n\n\n

This will include the dbt_utils package with the specific version 1.3.0. If you don\u2019t care about a specific version and rather want the latest version of a specific release, you can use the following YAML code:<\/p>\n\n\n\n

packages: \n- package: dbt-labs\/dbt_utils \n#version: 1.3.0 #specific version \nversion: [\">=1.3.0\", \"<1.4.0\"] # get latest patch level of specific minor release<\/pre><\/div>\n\n\n\n
In the command line at the bottom of the screen, we now need to run the command dbt deps<\/code>. This command<\/a> will install the most recent versions of the dependencies listed in the packages.yml<\/code> file.<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The package is now installed and its macros can be used in your dbt models.<\/p>\n\n\n\n
Creating the Date Dimension<\/h3>\n\n\n\n
The package dbt_util<\/code> has a useful macro called date_spine<\/a>. It will take a start and end date and a time interval such as day (equivalent to datepart<\/a> parameters in T-SQL). The macro will generate a list of dates between the start and end date. It\u2019s quite similar to how we use tally tables in SQL Server<\/a>.<\/p>\n\n\n\n
Let\u2019s add a new file to the dbt project containing the model for our DimDate<\/em>.<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n

  
Right click and create a new file:<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
We can generate the list of dates using the date_spine macro with the following code (which is SQL embedded with Jinja):<\/p>\n\n\n\n
WITH date_spine AS\n( -- 103 = dd\/mm\/yyyy\n  {{ dbt_utils.date_spine(\n      start_date=\" CONVERT(DATE,'01\/01\/2020', 103)\",\n      datepart=\"day\",\n      end_date=\" DATEADD(YEAR,5,CONVERT(DATE,GETDATE(), 103))\"\n     )\n  }}\n)\nSELECT * \nFROM date_spine\n<\/pre><\/div>\n\n\n\n
However, when we build our model, we get the following error:<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n

 
Turns out the date_spine<\/code> macro from the dbt_utils<\/code> package uses ORDER BY<\/code> inside another function called GENERATE_SERIES<\/a>, which is used by date_spine<\/code>. This might work on other data platforms, but T-SQL doesn\u2019t allow it. This brings us to a problem that can occur when working with 3rd<\/sup> party packages: not all of them will be compliant with Microsoft Fabric.<\/p>\n\n\n\n
You can implement platform-specific code within a macro (the database adaptor within dbt will then use the correct implementation). Still, for Fabric, there are no such implementations yet in the dbt-utils package, which is inconvenient. dbt-utils is widely used within the dbt community.<\/p>\n\n\n\n
There are Fabric-specific utility macros within the dbt-fabric<\/a> package, but at the time of writing there\u2019s no date_spine equivalent. However, not to worry; we can write our own macro!<\/p>\n\n\n\n
Creating our First Macro<\/strong><\/p>\n\n\n\n
A macro in dbt is a piece of reusable SQL code. It\u2019s one of the most powerful features in dbt, and it\u2019s basically \u201cdynamic SQL on steroids\u201d. Let\u2019s illustrate with an example. In the macros folder, we create a new file called my_date_spine.sql<\/code>:<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Inside the file, we put the following code, which is again a combination of SQL and Jinja:<\/p>\n\n\n\n
{% macro my_date_spine(start_date, end_date) %}\n    SELECT dates = DATEADD(DAY,[value] - 1,{{ start_date }})\n    FROM GENERATE_SERIES(\n           1\n          ,DATEDIFF(DAY\n                   ,{{ start_date }}\n                   ,{{ end_date }}) + 1\n          ,1)\n{% endmacro %}<\/pre><\/div>\n\n\n\n
We use the built-in T-SQL functions GENERATE_SERIES<\/a> and DATEDIFF<\/a> to generate our list of dates. In the first Jinja line, we define the name of the macro and its input parameters (start_date and end_date). Inside the SQL code, we can reference these parameters by using the double curly brackets.<\/p>\n\n\n\n
When we go back to the date dimension, we can call the macro like this:<\/p>\n\n\n\n
WITH date_spine AS\n( -- 103 = dd\/mm\/yyyy\n{{ my_date_spine(\n     start_date=\"CONVERT(DATE,'01\/01\/2020', 103)\"\n    ,end_date=\"DATEADD(YEAR,5,CONVERT(DATE,GETDATE(), 103))\"\n   )\n}}\n)\nselect * from date_spine<\/pre><\/div>\n\n\n\n
When we compile the code, we can see dbt has put the SQL code from the macro inside our model, and any parameter references are replaced as well:<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
This is the power of macros within dbt. They finally make SQL code actually reusable without the performance hits<\/a> that we tend to get with SQL Server user-defined functions.<\/p>\n\n\n\n
If we hit preview, we can see we get the list of dates we need for our date dimension:<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Now, we can finish our SELECT statement by using date functions to calculate the typical columns of a date table: year, quarter, month, week, and so on.<\/p>\n\n\n\n
WITH date_spine AS\n( -- 103 = dd\/mm\/yyyy\n{{ my_date_spine(\n     start_date=\"CONVERT(DATE,'01\/01\/2020', 103)\"\n    ,end_date=\"DATEADD(YEAR,5,CONVERT(DATE,GETDATE(), 103))\"\n   )\n}}\n)\nSELECT\n   SK_Date        = dates\n  ,[DateDesc]\t= CONVERT(CHAR(11),dates,120)\n  ,[WeekNbr]      = DATEPART(ISO_WEEK,dates)\n  ,[MonthNbr]\t= DATEPART(MONTH,dates)\n  ,[QuarterNbr]\t= DATEPART(QUARTER,dates)\n  ,[Year]\t\t= DATEPART(YEAR, dates)\n  ,[DayName]\t\t= DATENAME(WEEKDAY, dates)\n  ,[WeekName]\t= 'W' + CAST(DATEPART(ISO_WEEK,dates) AS VARCHAR(2))\n  ,[MonthName]\t= DATENAME(MONTH,dates)\n  ,[QuarterName]\t= 'Q' + CAST(DATEPART(QUARTER,dates) AS CHAR(1))\n  ,[YearWeek]\t= CAST(DATEPART(YEAR, dates) AS CHAR(4)) + \n                       RIGHT('0' + CAST(DATEPART(ISO_WEEK, dates) AS VARCHAR(2)),2)\n  ,[YearWeekDesc] = CAST(DATEPART(YEAR, dates) AS CHAR(4)) + ' W' + CAST(DATEPART(ISO_WEEK,dates) AS VARCHAR(2))\n  ,[YearMonth]\t= CAST(DATEPART(YEAR, dates) AS CHAR(4)) + \n                        SUBSTRING(CONVERT(VARCHAR(6),dates,112),5,2)\n  ,[YearMonthDesc] = CAST(DATEPART(YEAR, dates) AS CHAR(4)) + '-' + \n                         RIGHT('0' + CAST(DATEPART(MONTH, dates) AS VARCHAR(2)),2)\n  ,[YearMonthDescFull]\t= DATENAME(MONTH,dates) + ' ' + CAST(DATEPART(YEAR, dates) AS CHAR(4))\n  ,[YearQuarter]\t= CAST(DATEPART(YEAR, dates) AS CHAR(4)) + CAST(DATEPART(QUARTER, dates) AS CHAR(1))\n  ,[YearQuarterDesc] = CAST(DATEPART(YEAR, dates) AS CHAR(4)) + ' ' +\n                             'Q' + CAST(DATEPART(QUARTER,dates) AS CHAR(1))    \n  ,[IsMonthFirstDay] = IIF(dates = DATEADD(MONTH, DATEDIFF(MONTH, 0, dates), 0) THEN 'yes' ELSE 'no' END\n  ,[IsMonthLastDay] = IIF(dates = EOMONTH(dates,0),'yes','no')\n  ,[MonthFirstDay] = DATEADD(DAY,1,EOMONTH(dates,-1))\n  ,[MonthLastDay]\t = EOMONTH(dates,0)\n  ,[DayOfWeek]\t = DATEPART(WEEKDAY, dates)\n  ,[DayOfMonth]\t = DATEPART(DAY, dates)\n  ,[DayOfQuarter]\t = DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,dates),0),dates) + 1\n  ,[DayOfYear]\t = DATEPART(DAYOFYEAR, dates)\nFROM date_spine d;<\/pre><\/div>\n\n\n\n
You could join against reference tables that contain the holidays for each year to expand the date dimension or add extra columns for fiscal dates.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
In this article, we saw how installing packages can expand the functionality of debt. These give us access to additional macros, which are useful tools for reusing SQL functionality. You can always write your own macro, which we did to generate a list of dates for our date dimension.<\/p>\n","protected":false},"excerpt":{"rendered":"
Building a date dimension table in Microsoft Fabric Warehouse using dbt macros – generating date rows with dbt’s sequence utilities, adding common date attributes (day of week, quarter, fiscal period), and materialising the result as a table. Complete tutorial with runnable dbt project structure.…<\/p>\n","protected":false},"author":110905,"featured_media":106126,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,159165],"tags":[159230,159047,158997],"coauthors":[159081],"class_list":["post-106113","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-general-analytics","tag-dbt","tag-fabric","tag-microsoft-fabric"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106113","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/110905"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=106113"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106113\/revisions"}],"predecessor-version":[{"id":106139,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106113\/revisions\/106139"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/106126"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=106113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=106113"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=106113"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=106113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}