{"id":107177,"date":"2025-07-16T14:00:00","date_gmt":"2025-07-16T14:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107177"},"modified":"2025-07-11T11:20:58","modified_gmt":"2025-07-11T11:20:58","slug":"how-to-script-dimensions-with-data-build-tool-dbt-macros","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/business-intelligence\/general-analytics\/how-to-script-dimensions-with-data-build-tool-dbt-macros\/","title":{"rendered":"Scripting Dimension Tables with dbt Macros: Type 1 and Type 2 SCD Patterns for Data Warehouses"},"content":{"rendered":"\n

In previous articles we introduced the popular data engineering tool dbt<\/a> (data build tool). We showed you how you can transform data in a Microsoft Fabric<\/a> warehouse and how you can create tables with dbt models<\/a>. We also demonstrated how you can use macros and Jinja<\/a> to mimic the behavior of dynamic SQL (which we all know and love to use for our scripting), and apply this to create a date dimension<\/a>.<\/p>\n\n\n\n

In this article, we\u2019ll revisit the dimension models we created<\/a>. We wrote the entire SQL statement for the dimension by hand, and the dimensions themselves were very rudimentary; they lacked a surrogate key<\/a> and there were no audit columns (such as insert date and update date). We\u2019ll show you how we can expand the dimensions using Jinja, but also how we can minimize development effort by baking reusable patterns into the Jinja code. We\u2019ll focus on loading dimensions of type Slowly Changing Dimension Type 1, which means no history is kept. Type 2 will be the focus of another article.<\/em><\/p>\n\n\n\n

Load a Type 1 Dimension<\/h3>\n\n\n\n

Writing the Business Logic<\/h4>\n\n\n\n

There\u2019s only so much you can abstract away. Every dimension or fact table will have some SQL code that is truly unique for that table: the business logic. Loading a dimension is always the same: check if a row exists by using the business key columns (which define the uniqueness of a dimension member). If it doesn\u2019t exist yet, insert a new row. If it does exist, check if the row needs to be updated or not; if it does, update it. However, defining the expressions that define each column is something we cannot generate. No matter which tool, framework or scripting you use, there will always be work that you need to do for each table. We are going to put that unique part into a view.<\/p>\n\n\n\n

Let\u2019s look at the Product dimension we created in the previous article<\/a>:<\/p>\n\n\n\n

WITH cte_categories AS\n(\n    SELECT\n         ProductCategoryID\n        ,ProductCategoryName    = [Name]\n    FROM {{ source('adventureworkslt', 'ProductCategory') }}\n    WHERE ParentProductCategoryID IS NULL\n)\n,   cte_subcategories AS\n(\n    SELECT\n         ProductSubCategoryID   = sc.ProductCategoryID\n        ,ProductSubCategoryName = sc.[Name]\n        ,c.ProductCategoryName\n    FROM {{ source('adventureworkslt', 'ProductCategory') }} sc\n    LEFT JOIN cte_categories     c ON sc.ParentProductCategoryID = c.ProductCategoryID\n    WHERE ParentProductCategoryID IS NOT NULL\n)\n,   cte_models AS\n(\n    SELECT\n        m.ProductModelID\n       ,ProductModelName    = m.[Name]\n       ,ProductModelDesc    = ISNULL(d.[Description],'Description missing...')\n    FROM {{ source('adventureworkslt', 'ProductModel') }}                        m\n    LEFT JOIN {{ source('adventureworkslt', 'ProductModelProductDescription') }} md ON m.ProductModelID = md.ProductModelID AND md.Culture = 'en'\n    LEFT JOIN {{ source('adventureworkslt', 'ProductDescription') }}      d  ON md.ProductDescriptionID = d.ProductDescriptionID\n)\nSELECT\n     p.ProductID\n    ,ProductName            = p.[Name]\n    ,p.ProductNumber\n    ,ProductColor           = ISNULL(p.Color,'N\/A')\n    ,p.StandardCost\n    ,p.ListPrice\n    ,ProductSize            = ISNULL(p.Size,'N\/A')\n    ,ProductWeight          = p.[Weight]\n    ,p.SellStartDate\n    ,p.SellEndDate\n    ,p.DiscontinuedDate\n    ,ProductSubCategoryName = ISNULL(pc.ProductSubCategoryName,'N\/A')\n    ,ProductCategoryName    = ISNULL(pc.ProductCategoryName,'N\/A')\n    ,ProductModelName       = ISNULL(m.ProductModelName,'N\/A')\n    ,ProductModelDesc       = ISNULL(m.ProductModelDesc,'N\/A')\nFROM {{ source('adventureworkslt', 'Product') }} p\nLEFT JOIN cte_subcategories pc ON p.ProductCategoryID = pc.ProductSubCategoryID\nLEFT JOIN cte_models        m  ON p.ProductModelID = m.ProductModelID;<\/pre><\/div>\n\n\n\n
This query defines the business logic used for this dimension. When we run this model, it will create a table called DimProduct, but as mentioned before we are missing some columns like the surrogate key. We can add them to this model, or we can create a reusable template for a dimension to minimize development time and especially reduce the risk for mistakes. Another advantage is that all our dimensions will be standardized in format and naming conventions.<\/p>\n\n\n\n
The first step is to transform this model into a view. We can do this by simply adding the following Jinja configuration at the top of the model:<\/p>\n\n\n\n
{{\n  config(\n    materialized = 'view'\n    )\n}}<\/pre><\/div>\n\n\n\n
We also rename the model to vw_Dimproduct.sql<\/em>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
When we now build the model, we can see a view will be created in our Fabric warehouse:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Create the Dimension Model with Jinja<\/h4>\n\n\n\n
With the following model, we can load our dimension table:<\/p>\n\n\n\n
{%- set columns = adapter.get_columns_in_relation(ref('vw_DimProduct')) -%}\n{#- Define the business key columns -#}\n{%- set bk_cols = ['ProductID'] -%}\nSELECT\n    SK_Product = {{ dbt_utils.generate_surrogate_key(bk_cols) }}\n    {% for col in columns -%}\n    ,{{col.column}}\n    {% endfor -%}\n    ,InsertDate = SYSDATETIME()\n    ,UpdateDate = SYSDATETIME()\nFROM {{ ref('vw_DimProduct') }}<\/pre><\/div>\n\n\n\n
It generates the following SQL code:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Let\u2019s take a closer look at what happens in this script. We don\u2019t want to write out all of the different columns, so the macro get_columns_in_relation<\/a> from the dbt adapter object is used to fetch all the columns from the view we reference. Using a for loop<\/em>, we can loop over this collection and write out all the column names in the SELECT<\/code> statement.<\/p>\n\n\n\n
As mentioned before, we also want to generate a surrogate key. Traditionally, this is done by using an IDENTITY<\/code> constraint in SQL Server<\/a>, since surrogate keys are defined as meaningless integers. In dbt however, there are two problems with this approach:<\/p>\n\n\n
\n
    \n
  1. Since you don\u2019t specify an IDENTITY <\/code>column in your SELECT<\/code> statement (because it is auto-populated), it\u2019s not supported in dbt. If it isn\u2019t specified in the model, the column doesn\u2019t exist for dbt, since dbt creates tables by wrapping the SELECT<\/code> statement in a CREATE TABLE AS SELECT<\/code> statement. Furthermore, at the time of writing, the Fabric Warehouse doesn\u2019t support IDENTITY<\/code> constraints yet.
    <\/li>\n\n\n\n
  2. We could work around this by generating the surrogate keys ourselves (for example by using the ROW_NUMBER<\/code> function), but this goes against the principle of dbt, where models should be idempotent; if you drop a table and run the model again, you should get the same result.<\/li>\n<\/ol>\n<\/div>\n\n\n
    To solve these issues, typically hashes<\/strong> are used in dbt for the surrogate keys. There are interesting discussions online of the benefits here<\/a> and here<\/a>. One of the advantages of using a hash is that you can drop and reload your dimensions without having the reload your fact tables. Coming from a SQL Server world, the approach of using hashes might seem strange and unusual and it can take some time to get used to.<\/p>\n\n\n\n
    We can generate a hash-based surrogate key using the generate_surrogate_key<\/a> macro from the dbt_utils<\/em> package. It needs the business keys of the dimension as input, so they are defined at the start of the model as an array variable:<\/p>\n\n\n\n
    {#- Define the business key columns -#}\n{%- set bk_cols = ['ProductID'] -%}<\/pre><\/div>\n\n\n\n
    The first line of the script above shows how you can put comments in your Jinja code. You might\u2019ve also noticed the minus signs here and there at the start or end of the Jinja code. These are used to control white space<\/a>. If for example we remove them from this block:<\/p>\n\n\n\n
    {% for col in columns -%}<\/pre><\/div>\n\n\n\n
    …we get the following result:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    It can be trial & error to get the correct white space control, but the focus should be on writing readable model code, not necessarily nicely formatted generated SQL.<\/p>\n\n\n\n
    If we now build the model, we get the dimension we need:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    This model will drop the dimension table and recreate it fully when the model is run. Because of the hashed surrogate key and because we don\u2019t need to track history, this shouldn\u2019t be an issue.<\/p>\n\n\n\n
    Optimizing the Jinja<\/h4>\n\n\n\n
    We still must specify the reference to the vw_DimProduct<\/em> view twice, and specify a custom name for the surrogate key. It\u2019s not that big of a deal, but when you copy-paste this code for another dimension and forget to adapt something, you might end up with a customer dimension with SK_Product for its surrogate key name. So let\u2019s modify the Jinja so we only need to specify the dimension name once, and the Jinja code takes care of the rest:<\/p>\n\n\n\n
    {%- set dimname = 'Product' -%}\n{%- set relname = 'vw_Dim' + dimname -%}\n{%- set columns = adapter.get_columns_in_relation(ref(relname)) -%}\n{#- Define the business key columns -#}\n{%- set bk_cols = ['ProductID'] -%}\nSELECT\n    {{ 'SK_' + dimname }} = {{ dbt_utils.generate_surrogate_key(bk_cols) }}\n    {%- for col in columns -%}\n    ,{{col.column}}\n    {% endfor -%}\n    ,InsertDate = SYSDATETIME()\n    ,UpdateDate = SYSDATETIME()\nFROM {{ ref(relname) }}<\/pre><\/div>\n\n\n\n
    By using a naming convention for the view and the surrogate key (vw_DimMyDimension<\/em> and SK_MyDimension<\/em>), we only need to write the dimension name once and insert it where needed. Keep in mind we still need to specify the business key columns.<\/p>\n\n\n\n
    We can go one step further, and turn the script into a macro, maximizing the reusability. Let\u2019s add a new file to the macros folder:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    We\u2019re going to name the macro load_dimension<\/code>.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The macro itself contains the following code:<\/p>\n\n\n\n
    {% macro load_dimension(dimension_name, bk_columns) %}\n{%- set relname = 'vw_Dim' + dimension_name -%}\n{%- set columns = adapter.get_columns_in_relation(ref(relname)) -%}\nSELECT\n    {{ 'SK_' + dimension_name }} = {{ dbt_utils.generate_surrogate_key(bk_columns) }}\n    {%- for col in columns -%}\n    ,{{col.column}}\n    {% endfor -%}\n    ,InsertDate = SYSDATETIME()\n    ,UpdateDate = SYSDATETIME()\nFROM {{ ref(relname) }}\n{% endmacro %}<\/pre><\/div>\n\n\n\n
    The SQL code in the DimProduct.sql<\/em> file simply becomes:<\/p>\n\n\n\n
    {{ load_dimension('Product',['ProductID']) }}<\/pre><\/div>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    If your dimension has multiple columns for the business key, then you can pass them along in array-format:<\/p>\n\n\n\n
    {{ load_dimension(MyDimension,['BK_Column1', 'BK_Column2']) }}<\/pre><\/div>\n\n\n\n
    As an exercise for the reader, try to convert the model for the customer dimension to a model where you only use this macro.<\/p>\n\n\n\n
    Conclusion<\/h4>\n\n\n\n
    In this article, we put the business logic \u2013 which is unique for every table \u2013 in a view and then used Jinja to abstract all the other logic, such as creating a surrogate key and audit columns, away. dbt and macros are all about reusability of code and trying to implement patterns. In the end, we went as far as creating a macro for our pattern and the dimension model only consists of one single line of Jinja code that calls the macro.<\/p>\n\n\n\n
    By using macros and Jinja, we can standardize the models in our dbt project and minimize the risk of errors.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Building dimension tables with dbt macros – Type 1 (overwriting changes) and Type 2 (preserving history with effective dates) slowly changing dimensions, parameterised dbt macros for repeatable dimensional patterns, and generating consistent surrogate keys across multiple dimensions.…<\/p>\n","protected":false},"author":110905,"featured_media":107188,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[159160,159165],"tags":[159336,159230,159337,159338,159339,158997,48496,4150,4151],"coauthors":[159081],"class_list":["post-107177","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business-intelligence","category-general-analytics","tag-data-build-tool","tag-dbt","tag-dimensions","tag-jinja","tag-macros","tag-microsoft-fabric","tag-script","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107177","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=107177"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107177\/revisions"}],"predecessor-version":[{"id":107193,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107177\/revisions\/107193"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/107188"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=107177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=107177"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=107177"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=107177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}