{"id":112805,"date":"2026-11-09T12:00:00","date_gmt":"2026-11-09T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=112805"},"modified":"2026-10-22T13:33:50","modified_gmt":"2026-10-22T13:33:50","slug":"how-to-build-an-scd-type-2-dimension-using-dbt-snapshot","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/data-analytics\/how-to-build-an-scd-type-2-dimension-using-dbt-snapshot\/","title":{"rendered":"How to build an SCD Type 2 dimension using dbt snapshot"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><strong>An SCD Type 2 dimension preserves history by inserting a new record every time a tracked column changes, rather than overwriting the old value. Building one by hand usually means writing custom merge logic, but dbt&#8217;s built-in <code>dbt snapshot<\/code> feature can do this for you <em>without<\/em> any extra code.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This article picks up where my <a href=\"https:\/\/www.red-gate.com\/simple-talk\/data-analytics\/general-analytics\/how-to-script-dimensions-with-data-build-tool-dbt-macros\/\" target=\"_blank\" rel=\"noreferrer noopener\">previous guide<\/a> on loading a Type 1 slowly changing dimension with macros left off. This time, we&#8217;ll use dbt snapshots to build a full SCD Type 2 customer dimension in a Microsoft Fabric warehouse: from the underlying view, through the snapshot configuration, to the final dimension model.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">First &#8211; a refresher, if you need one. Feel free to skip this section.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>This article is part of <strong>Koen Verbeeck&#8217;s complete guide to dbt (data build tool)<\/strong> &#8211; <a href=\"https:\/\/www.red-gate.com\/simple-talk\/collections\/complete-guide-to-dbt-data-build-tool\/\" target=\"_blank\" rel=\"noreferrer noopener\">click here<\/a> for the full series.<\/em><\/p>\n\n\n\n<h2 id=\"h-what-is-dbt\" class=\"wp-block-heading\">What is dbt?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>In recent years,\u00a0<a href=\"https:\/\/www.getdbt.com\/\" target=\"_blank\" rel=\"noreferrer noopener\">dbt\u00a0(data build tool)<\/a> has become popular in the data engineering world for handling <a href=\"https:\/\/aws.amazon.com\/what-is\/etl\/\" target=\"_blank\" rel=\"noreferrer noopener\">ELT (extract-transform-load)<\/a> processes. <\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">dbt takes on the role of the \u201cT\u201d, meaning it\u2019s responsible for transforming the data in a data store. You can learn a lot more in my introduction article <a href=\"https:\/\/www.red-gate.com\/simple-talk\/data-analytics\/general-analytics\/a-gentle-introduction-to-dbt\/\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a>.<\/p>\n\n\n\n<h2 id=\"h-what-are-slowly-changing-dimensions\" class=\"wp-block-heading\">What are slowly changing dimensions?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"h-what-are-slowly-changing-dimensions-lowly-changing-dimension-scd-is-typically-used-in-dimensional-modelling-for-data-warehouses\"><strong>Slowly changing dimensions (SCDs) are typically used in dimensional modelling for data warehouses. There are two types: Type 1 and Type 2. <\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s the breakdown:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li><strong>Type 1<\/strong>: no history is kept. When a row has a field that changes value, the row is updated in the dimensions. For example, when a customer modifies their e-mail address, it&#8217;s updated in the customer dimension, and the old address is no longer available.<br><br><\/li>\n\n\n\n<li><strong>Type 2<\/strong>: history is kept by inserting a new record every time a column changes a value. When a customer moves to a new address, instead of updating the address fields like in a type 1 dimension, we insert a new record for that customer, along with timestamps that indicate when the record is valid. This enables accurate time-variant analysis.<\/li>\n<\/ul>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\">Let\u2019s illustrate with an example. In the following table, we have a couple of customer records:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"952\" height=\"145\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image.png\" alt=\"an image showing a table of 3 unique customer records.\" class=\"wp-image-112806\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image.png 952w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-300x46.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-768x117.png 768w\" sizes=\"auto, (max-width: 952px) 100vw, 952px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">At some point in time, Sarah moves to a new address, which will lead to a new record in the dimension (and with a new surrogate key):<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"945\" height=\"178\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-1.png\" alt=\"an image showing a graph of customer records.\" class=\"wp-image-112807\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-1.png 945w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-1-300x57.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-1-768x145.png 768w\" sizes=\"auto, (max-width: 945px) 100vw, 945px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The old record is \u201cclosed\u201d by putting a date in the <em>Valid To<\/em> field, and the current flag is updated as well. It\u2019s important to note that both the <em>Valid To<\/em> and the <em>Current Flag<\/em> fields are optional. Both can be derived from the <em>Valid From<\/em> field.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There are actually more types of slowly changing dimensions, but those are out of scope for this article. If you want to learn more, check out the Kimball book <a href=\"https:\/\/amzn.to\/3UAPJiK\" target=\"_blank\" rel=\"noreferrer noopener\">The Data Warehouse Toolkit<\/a>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Thus far, we have only created dbt models that are essentially a <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-basic-t-sql-select-statement\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL <code>SELECT<\/code><\/a> statement, but SCD Type 2 dimensions seem much more complicated. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>However,<\/strong> <strong>in this guide, I&#8217;ll show how to use the <a href=\"https:\/\/docs.getdbt.com\/docs\/build\/snapshots?version=2\" target=\"_blank\" rel=\"noreferrer noopener\">dbt snapshot feature<\/a> to cut away this complication. With it, SCD Type 2 dimensions can be loaded far more elegantly.<\/strong><\/p>\n\n\n\n<h2 id=\"h-how-to-write-the-business-logic-for-an-scd-type-2-dimension\" class=\"wp-block-heading\">How to write the business logic for an SCD Type 2 dimension<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">We&#8217;ll use the customer dimension as an example of how to use dbt snapshots. Just like in our <a href=\"https:\/\/www.red-gate.com\/simple-talk\/data-analytics\/general-analytics\/how-to-script-dimensions-with-data-build-tool-dbt-macros\/\" target=\"_blank\" rel=\"noreferrer noopener\">type 1 dimension<\/a>, we&#8217;ll first create a view that contains all the business logic that we need for our customer dimension. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Rename the <code>DimCustomer.sql<\/code> model to <code>vw_DimCustomer.sql<\/code> and enter the following SQL <code>SELECT<\/code> statement:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">{{\n  config(\n    materialized = 'view'\n    )\n}}\nWITH cte_mainoffices AS\n(\n    SELECT\n         ca.CustomerID\n        ,a.City\n        ,a.StateProvince\n        ,a.CountryRegion\n        ,a.PostalCode\n    FROM {{ source('adventureworkslt', 'CustomerAddress') }} ca\n    JOIN {{ source('adventureworkslt', 'Address') }}       a \n          ON ca.AddressID = a.AddressID\n    WHERE ca.AddressType = 'Main Office'\n)\n, cte_cols AS\n(\n    SELECT\n        c.CustomerID\n        ,CustomerName           = CONCAT_WS(' ',c.Title, c.FirstName, c.MiddleName, \n                                                                c.LastName, c.Suffix)\n        ,CustomerCompanyName    = ISNULL(c.CompanyName,'N\/A')\n        ,CustomerSalesPerson    = ISNULL(c.SalesPerson,'N\/A')\n        ,CustomerEmail          = ISNULL(c.EmailAddress,'N\/A')\n        ,CustomerPhone          = ISNULL(c.Phone,'N\/A')\n        ,CustomerPostalCode     = ISNULL(m.PostalCode,'N\/A')\n        ,CustomerCity           = ISNULL(m.City,'N\/A')\n        ,CustomerStateProvince  = ISNULL(m.StateProvince,'N\/A')\n        ,CustomerCountryRegion  = ISNULL(m.CountryRegion,'N\/A')\n    FROM {{ source('adventureworkslt', 'Customer') }}       c\n    LEFT JOIN cte_mainoffices   m ON c.CustomerID = m.CustomerID\n)\nSELECT\n     *\n    ,SCD2_Hash = CONVERT(CHAR(66),\n                    HASHBYTES('SHA2_256'\n                             ,CONCAT(\n                                      CustomerName\n                                ,'||',CustomerSalesPerson\n                                ,'||',CustomerCompanyName\n                                ,'||',CustomerSalesPerson\n                                ,'||',CustomerEmail\n                                ,'||',CustomerPhone\n                                ,'||',CustomerPostalCode\n                                ,'||',CustomerCity\n                                ,'||',CustomerStateProvince\n                                ,'||',CustomerCountryRegion\n                                ))\n                    ,1)\nFROM cte_cols<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Marked in bold is an extra column added to the view that doesn\u2019t exist in the original <code>DimCustomer<\/code> model: <strong><code>SCD2_Hash<\/code><\/strong>. This column contains a hash (converted to text) calculated from the concatenation of all columns. We&#8217;ll use this hash to efficiently determine if any of the columns\u2019 values have changed.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When we now build the model, we can see a new view will be created in our Fabric warehouse:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"396\" height=\"238\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image.jpg\" alt=\"an image showing a new view created in the Fabric warehouse: vw_DimCustomer\" class=\"wp-image-112808\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image.jpg 396w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-300x180.jpg 300w\" sizes=\"auto, (max-width: 396px) 100vw, 396px\" \/><\/figure>\n\n\n\n<h2 id=\"h-how-to-create-an-scd-type-2-table-with-dbt-snapshot\" class=\"wp-block-heading\">How to create an SCD Type 2 table with dbt snapshot<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Now that the groundwork for our SCD Type 2 dimension has been laid, the view will calculate any necessary business logic and will return the latest state of each dimension member as currently known in the source system.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For this, <strong>dbt has a built-in feature to keep track of history for a model: the <a href=\"https:\/\/docs.getdbt.com\/docs\/build\/snapshots?version=2\" target=\"_blank\" rel=\"noreferrer noopener\">snapshot<\/a><\/strong>. Creating a snapshot of a model will create a SCD Type 2-like table, with added metadata fields, such as a <em>valid from<\/em> and a <em>valid to<\/em>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Don\u2019t mistake the concept of snapshots in dbt with <a href=\"https:\/\/www.kimballgroup.com\/data-warehouse-business-intelligence-resources\/kimball-techniques\/dimensional-modeling-techniques\/periodic-snapshot-fact-table\/\" target=\"_blank\" rel=\"noreferrer noopener\">snapshot fact tables<\/a> from dimensional modelling &#8211; they\u2019re not the same.<\/em><\/p>\n\n\n\n<h3 id=\"h-create-a-new-file\" class=\"wp-block-heading\">Create a new file<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Let\u2019s create a snapshot using the <code>DimCustomer<\/code> view as a source. In the dbt project, go to the <em>snapshots <\/em>folder and create a new file:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"408\" height=\"521\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-1.jpg\" alt=\"an image showing where the snapshots folder is located within the dbt project.\" class=\"wp-image-112809\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-1.jpg 408w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-1-235x300.jpg 235w\" sizes=\"auto, (max-width: 408px) 100vw, 408px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Name the file <em>customer_scd2.yml<\/em>:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"582\" height=\"244\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-2.jpg\" alt=\"an image showing naming the file customer_scd2.yml\" class=\"wp-image-112810\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-2.jpg 582w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-2-300x126.jpg 300w\" sizes=\"auto, (max-width: 582px) 100vw, 582px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">A snapshot is <em>not<\/em> a SQL <code>SELECT<\/code> statement like the other models we created earlier, but rather a specification. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In the YAML file, add the following:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">snapshots:\n  - name: customer_scd2\n    relation: ref('vw_DimCustomer')\n    config:\n      schema: snapshots\n      unique_key: CustomerID\n      strategy: check\n      check_cols: [\"SCD2_Hash\"]\n      hard_deletes: \"new_record\"\n      dbt_valid_to_current: \"CONVERT(DATE,'9999-12-31')\" # \nSpecifies that current records should have `dbt_valid_to` set \nto `'9999-12-31'` instead of `NULL`.<\/pre><\/div>\n\n\n\n<h4 id=\"h-let-s-go-over-this-config-line-by-line\" class=\"wp-block-heading\">Let\u2019s go over this config line-by-line:<\/h4>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>We will add a snapshot named <code>customer_scd2<\/code>.<br><br><\/li>\n\n\n\n<li>The source for this snapshot is the <code>vw_DimCustomer<\/code> view. We&#8217;ll reference it using the built-in <a href=\"https:\/\/docs.getdbt.com\/reference\/dbt-jinja-functions\/ref\" target=\"_blank\" rel=\"noreferrer noopener\">ref<\/a> function (keeping lineage intact). You can also reference source tables, if you want, by using the <a href=\"https:\/\/docs.getdbt.com\/reference\/dbt-jinja-functions\/source?version=2\" target=\"_blank\" rel=\"noreferrer noopener\">source<\/a> function.<br>(<em>See <a href=\"https:\/\/www.red-gate.com\/simple-talk\/data-analytics\/general-analytics\/loading-models-from-source-data-with-dbt\/\" target=\"_blank\" rel=\"noreferrer noopener\">Loading Models from Source Data with dbt<\/a> to learn more about how you can define source tables in dbt<\/em>.)<br><br><\/li>\n\n\n\n<li>The snapshot will be put in a database schema named <em>snapshots<\/em>.<br><br><\/li>\n\n\n\n<li>The <code>CustomerID <\/code>column will be used to identify unique dimension members. This is the business key of your dimension.<br><br><\/li>\n\n\n\n<li>We&#8217;ll use the <strong>check<\/strong> strategy to determine if a row needs to be updated or inserted. This strategy uses the columns defined in <code>check_cols<\/code> to verify if a row has changed. There\u2019s also the <strong>timestamp<\/strong> strategy, which uses a <strong><code><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/data-types\/datetime-transact-sql?view=sql-server-ver17\" target=\"_blank\" rel=\"noreferrer noopener\">datetime<\/a><\/code> column<\/strong> indicating the last time a row was changed (i.e. a <em>last modified<\/em> date).<br><br><\/li>\n\n\n\n<li>Since we don\u2019t have such a column in our source data, the check strategy is the preferred option.<br><br><\/li>\n\n\n\n<li>The column used for the check strategy is the <code>SCD2_Hash<\/code> column we defined in our view. &nbsp;If you use the timestamp strategy instead, you need to specify the name of the <code>datetime<\/code> column.<br><br><\/li>\n\n\n\n<li>The <code>hard_deletes<\/code> config specifies how deletes should be handled. The default is <code>ignore<\/code>, which means the last known value of the record will remain the active one in the snapshot. <br><br><\/li>\n\n\n\n<li>Another option is <code>invalidate<\/code>, where the <code>valid to<\/code> field will be set to the current time (indicating there\u2019s no longer a \u201ccurrent\u201d record). <br><br><\/li>\n\n\n\n<li>The last option is <code>new record<\/code>, where a new record is inserted with the <code>dbt_is_deleted<\/code> metadata set to <em>True<\/em>.<br><br><\/li>\n\n\n\n<li>The <code>dbt_valid_to_current<\/code> is an optional configuration. The most current record for a dimension member doesn\u2019t have a <code>valid to<\/code> value, since it has not yet been closed off. With this config, you can specify a dummy value instead of having a <code>NULL<\/code> value. Here, we choose for the date 9999-12-31.<\/li>\n<\/ul>\n<\/div>\n\n\n<section id=\"my-first-block-block_82da4d85dc96989c32fed8eb986b3f81\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Subscribe to the Simple Talk newsletter<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            All the latest articles, guides, podcasts, and more &#8211; delivered straight to your inbox.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/simple-talk\/subscribe\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Subscribe now: Subscribe to the Simple Talk newsletter\">Subscribe now<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<h3 id=\"h-creating-the-snapshot-table\" class=\"wp-block-heading\">Creating the snapshot table<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">We can create the snapshot table itself using the command <a href=\"https:\/\/docs.getdbt.com\/reference\/commands\/snapshot?version=2\" target=\"_blank\" rel=\"noreferrer noopener\"><code>dbt snapshot<\/code><\/a>. Running this command will refresh any snapshots created in the project. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If the snapshot table doesn\u2019t yet exist, it&#8217;ll be created in the configured schema:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"903\" height=\"377\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-3.jpg\" alt=\"an image showing the snapshot table in dbt.\" class=\"wp-image-112811\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-3.jpg 903w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-3-300x125.jpg 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-3-768x321.jpg 768w\" sizes=\"auto, (max-width: 903px) 100vw, 903px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In the Fabric warehouse, we can see the following table has been created:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"443\" height=\"413\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-2.png\" alt=\"an image showing the table created in the Fabric warehouse.\" class=\"wp-image-112812\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-2.png 443w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-2-300x280.png 300w\" sizes=\"auto, (max-width: 443px) 100vw, 443px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The columns in the red square are metadata columns added by dbt:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li><code>dbt_scd_id<\/code>: a unique id for each dimensions member. This can act as a surrogate key.<\/li>\n\n\n\n<li><code>dbt_updated_at<\/code>: a timestamp from when the row was last modified (as a result of running dbt snapshot).<\/li>\n\n\n\n<li><code>dbt_valid_from<\/code><em> <\/em>and <code>dbt_valid_to<\/code>: <code>datetime<\/code> columns marking the period a certain row was active.<\/li>\n\n\n\n<li><code>dbt_is_deleted<\/code>: flag for marking soft deletes<\/li>\n<\/ul>\n<\/div>\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"903\" height=\"391\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-4.jpg\" alt=\"an image showing a table with the metadata columns added by dbt.\" class=\"wp-image-112813\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-4.jpg 903w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-4-300x130.jpg 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-4-768x333.jpg 768w\" sizes=\"auto, (max-width: 903px) 100vw, 903px\" \/><\/figure>\n\n\n\n<h2 id=\"h-how-to-add-history-to-the-snapshot-table\" class=\"wp-block-heading\">How to add history to the snapshot table<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let\u2019s now create some history by first inserting a new customer record, updating it by <code>change a value<\/code>, and then deleting it. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">After each SQL <a href=\"https:\/\/www.techtarget.com\/whatis\/definition\/Data-Definition-Language-DDL\" target=\"_blank\" rel=\"noreferrer noopener\">DDL (data definition language)<\/a> statement, we can run <strong><code>dbt snapshot<\/code><\/strong> to capture the current state:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- insert original record\nINSERT INTO [SalesLT].[Customer]\n    ([FirstName]\n    ,[LastName]\n    ,[CompanyName]\n    ,[PasswordHash]\n    ,[PasswordSalt])\nVALUES\n    ('Peter'\n    ,'Parker'\n    ,'Student'\n    ,'N\/A'\n    ,'N\/A');\n-- run dbt snapshot!\n\nUPDATE [SalesLT].[Customer]\nSET CompanyName = 'Parker Industries'\nWHERE CustomerID = 30120; -- check which ID is used in your database\n\n-- run dbt snapshot!\n\nDELETE FROM SalesLT.Customer\nWHERE CustomerID = 30120;\n\n-- run dbt snapshot!<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This should create 3 records in the snapshot table:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"902\" height=\"321\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-5.jpg\" alt=\"\" class=\"wp-image-112814\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-5.jpg 902w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-5-300x107.jpg 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-5-768x273.jpg 768w\" sizes=\"auto, (max-width: 902px) 100vw, 902px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In your dbt projects, you can run <code>dbt snapshot<\/code> as often as needed to capture the level of historical granularity you want for your SCD Type 2 table. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you run <code>dbt build<\/code>, meanwhile, this also includes running the snapshots of your project (but dbt run on the other <em>does not<\/em>.) See <a href=\"https:\/\/docs.getdbt.com\/reference\/commands\/snapshot?version=2\" target=\"_blank\" rel=\"noreferrer noopener\">the docs<\/a> for more info.<\/p>\n\n\n\n<h2 id=\"h-how-to-build-the-dimension-model\" class=\"wp-block-heading\">How to build the dimension model<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">With our snapshot history table finished, we can build our final <code>DimCustomer<\/code> model. We will use a similar macro as the one we used in <a href=\"https:\/\/www.red-gate.com\/simple-talk\/data-analytics\/general-analytics\/how-to-script-dimensions-with-data-build-tool-dbt-macros\/\" target=\"_blank\" rel=\"noreferrer noopener\">the previous article<\/a> to load the model. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Duplicate the <code>load_dimension<\/code> macro and rename it to <code>load_dimension_scd2<\/code>. Change its definition to the following code:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">{% macro load_dimension_scd2(dimension_name) %}\n{%- set relname = dimension_name.lower() + '_scd2' -%}\n{%- set columns = adapter.get_columns_in_relation(ref(relname)) -%}\nSELECT\n    {{ 'SK_' + dimension_name }} = dbt_scd_id\n    {%- for col in columns  if not col.column.lower().startswith('dbt_') -%}\n    ,{{col.column}}\n    {% endfor -%}\n    ,ValidFrom  = dbt_valid_from\n    ,ValidTo    = dbt_valid_to\n    ,IsDeletedf = dbt_is_deleted\n    ,InsertDate = dbt_updated_at\n    ,UpdateDate = dbt_updated_at\nFROM {{ ref(relname) }}\n{% endmacro %}<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">We slightly changed the naming convention for <code>relname<\/code>. This is because the source for our dimension isn\u2019t a view &#8211; it is, in fact, the snapshot table we created earlier. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For the surrogate key, we can just reuse the <code>dbt_scd_id<\/code> metadata column. We again loop over all columns of the relation, but exclude the <code>dbt_*<\/code> metadata columns. Instead, those are added manually to the end of the <code>SELECT<\/code> statement with the appropriate aliases.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The model for the <em>Customer<\/em> dimension then becomes <code>{{ load_dimension_scd2('Customer') }}<\/code>. Add this to a file named <code>DimCustomer.sql<\/code>:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"902\" height=\"519\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-6.jpg\" alt=\"an image showing the customer dimension model being added to a file.\" class=\"wp-image-112815\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-6.jpg 902w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-6-300x173.jpg 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/09\/image-6-768x442.jpg 768w\" sizes=\"auto, (max-width: 902px) 100vw, 902px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Running this model will recreate the dimension each time but, since we&#8217;re using the <code>dbt_scd_id <\/code>column from the snapshot table, we get an idempotent result.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It\u2019s also possible to combine the <code>load_dimension<\/code> and <code>load_dimension_scd2<\/code> macros into a single one &#8211; it just requires some additional parameters (for example, SCD type) and code, if you want to centralize the logic.<\/p>\n\n\n\n<h2 id=\"h-alternatives-to-using-dbt-snapshot-for-scd-type-2\" class=\"wp-block-heading\">Alternatives to using dbt snapshot for SCD Type 2<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The way we have set up our SCD Type 2 dimension using snapshots in dbt means that history will be tracked for <em>all <\/em>columns of the dimension. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">However, if you want a mix of SCD Type 1 <em>and <\/em>Type 2 columns in one dimension \u2013 for example, if you only need history on a handful of columns \u2013 you can create a snapshot for just the SCD Type 2 columns. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Then, create a regular view for the Type 1 columns (as we did for the Product dimension), join the two together, and you&#8217;ve got a single combined dimension.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You also don\u2019t <em>have<\/em> to use snapshots to create your Type 2 dimensions. You can create custom code to do this yourself if you want more control, or if you have a specific set of requirements. Learn more in <a href=\"https:\/\/www.linkedin.com\/pulse\/custom-scd-type-2-model-dbt-alwyn-d-souza-hgtmc\/\" target=\"_blank\" rel=\"noreferrer noopener\">this blog post<\/a>.<\/p>\n\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. What&#039;s the difference between SCD Type 1 and SCD Type 2?<\/h3>\n            <div class=\"faq-answer\">\n                <p dir=\"ltr\">An SCD Type 1 dimension overwrites a column&#8217;s old value when it changes, so no history is kept. An SCD Type 2 dimension inserts a new record instead, with valid-from and valid-to dates marking when each version was active. Type 2 is the right choice when you need to analyze data as it looked at a specific point in time.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. What does dbt snapshot actually do?<\/h3>\n            <div class=\"faq-answer\">\n                <p dir=\"ltr\"><code>dbt snapshot<\/code> turns a regular dbt model into an SCD Type 2-style table. It compares the current source data against the existing snapshot, and inserts a new row whenever tracked columns change. dbt adds metadata columns automatically, including <code>dbt_valid_from<\/code>, <code>dbt_valid_to<\/code>, and a surrogate key, so you don&#8217;t have to build that logic yourself.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. Can I combine SCD Type 1 and Type 2 columns in one dimension?<\/h3>\n            <div class=\"faq-answer\">\n                <p dir=\"ltr\">Yes. Create a snapshot for just the columns that need history, then build a regular view for the Type 1 columns that don&#8217;t. Join the two together on the business key, and you get a single dimension with a mix of tracked and untracked columns.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">4. Does dbt snapshot work with Microsoft Fabric?<\/h3>\n            <div class=\"faq-answer\">\n                <p dir=\"ltr\">Yes. <code>dbt snapshot<\/code> runs against any warehouse dbt supports, including Microsoft Fabric. The snapshot table is created in your configured schema, with the same metadata columns you&#8217;d get on other platforms, so the workflow doesn&#8217;t change based on the destination warehouse.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">5. What happens when a source row is deleted?<\/h3>\n            <div class=\"faq-answer\">\n                <p dir=\"ltr\">That depends on the <code>hard_deletes<\/code> config. The default, <code>ignore<\/code>, leaves the last known record active. <code>invalidate<\/code> closes the record by setting its valid-to date. <code>new_record<\/code> inserts an extra row flagged with <code>dbt_is_deleted<\/code>, so you can see exactly when the delete happened.<\/p>\n            <\/div>\n            <\/section>\n\n\n\n<h2 id=\"h-conclusion\" class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><code>dbt snapshot<\/code> gives you an SCD Type 2 dimension without writing custom merge logic: it handles change detection, valid-from and valid-to dates, and deletes, all from a short YAML config. <\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you need more control, or want to mix Type 1 and Type 2 columns in the same dimension, you can still combine snapshots with regular views or write custom code. For most use cases, however, the built-in feature is the fastest path to reliable history tracking.<\/p>\n\n\n\n<section id=\"my-first-block-block_274cf4a0b57da6bc5ba3862f29d0f563\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Simple Talk is brought to you by Redgate Software<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/solutions\/overview\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Discover how Redgate can help you: Simple Talk is brought to you by Redgate Software\">Discover how Redgate can help you<\/a>\n                    <\/div>\n    <\/div>\n<\/section>","protected":false},"excerpt":{"rendered":"<p>Learn how to build an SCD Type 2 customer dimension in dbt using the built-in snapshot feature, with a full walkthrough for Microsoft Fabric.&hellip;<\/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,143523,53,159164],"tags":[4168,159230,158997,4150,4183],"coauthors":[159081],"class_list":["post-112805","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-analytics","category-databases","category-featured","category-microsoft-fabric","tag-database","tag-dbt","tag-microsoft-fabric","tag-sql","tag-t-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112805","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=112805"}],"version-history":[{"count":16,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112805\/revisions"}],"predecessor-version":[{"id":113990,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112805\/revisions\/113990"}],"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=112805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=112805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=112805"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=112805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}