<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Kin Lee on Medium]]></title>
        <description><![CDATA[Stories by Kin Lee on Medium]]></description>
        <link>https://medium.com/@wengkinlee?source=rss-80f43af45509------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*MLNvvH8JRY6uq5rw</url>
            <title>Stories by Kin Lee on Medium</title>
            <link>https://medium.com/@wengkinlee?source=rss-80f43af45509------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 10:07:13 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@wengkinlee/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Modelling Type 1 + 2 Slowly Changing Dimensions with dbt]]></title>
            <link>https://medium.com/weareservian/modelling-type-1-2-slowly-changing-dimensions-with-dbt-1b80078f290a?source=rss-80f43af45509------2</link>
            <guid isPermaLink="false">https://medium.com/p/1b80078f290a</guid>
            <category><![CDATA[cloud]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[slowly-changing-dimension]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Kin Lee]]></dc:creator>
            <pubDate>Mon, 08 Nov 2021 22:45:51 GMT</pubDate>
            <atom:updated>2021-11-08T22:45:51.512Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*y5C1zti8SUI7Rpwn" /><figcaption>Photo by <a href="https://unsplash.com/@pickawood?utm_source=medium&amp;utm_medium=referral">Pickawood</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>If you ever had the trouble of trying to model different types of Slowly Changing Dimensions on a single dimension with dbt, continue reading on. If not, check out these resources to get an idea on what <a href="https://www.getdbt.com/product/what-is-dbt/"><strong>dbt</strong></a> and <a href="https://en.wikipedia.org/wiki/Slowly_changing_dimension"><strong>SCD’s</strong></a> are.</p><p>In short, dbt (data build tool) is a very useful tool that handles data transformations in data warehouses with just SQL code. Often dubbed as the “T” in ELT, dbt is particularly useful in modelling SCD (Slowly Changing Dimensions) with its <a href="https://docs.getdbt.com/docs/building-a-dbt-project/snapshots"><strong>snapshot</strong></a> feature.</p><p>Although dbt snapshots are able to natively capture Type 2 changes for a single dimension, they aren’t particularly useful if you want to capture Type 1 changes concurrently as well. This is due to how snapshots work, where any changes captured by a snapshot strategy are treated as a Type 2 change.</p><p>You might ask yourself, why would we go through the challenge to capture Type 1 changes if we’re already capturing Type 2 changes? Why not just treat everything as Type 2? Well, the simple answer to that is as your dimension gets larger, more changes and inserts are going to be added to your dimension, increasing the size of your dimensions to a point where querying data might become slow. Capturing less essential changes as Type 1 would help in query performance in the long run.</p><p>In this article, we will discuss how we can add extra capabilities to dbt’s snapshots to handle Type 1 changes for a single dimension (table) by layering macros on top of the snapshot using an incremental model.</p><h4><strong>The Setup</strong></h4><p>As part of our setup, we will be using AWS Redshift as our warehouse and the <a href="https://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html">Tickit sample data</a> that comes with it. In particular, we will be capturing Type 1 and Type 2 changes for the <strong>listing</strong> dimension.</p><p>Here is a sample of the raw listing table that we will create a dimension out of.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/395e498e94f7a382cf38f82d06fdf605/href">https://medium.com/media/395e498e94f7a382cf38f82d06fdf605/href</a></iframe><p>For our dimension, the column <strong>listid</strong> is going to be used to create our dimension key.</p><p>We will treat these columns as Type 1 changes:</p><p><strong>numtickets, priceperticket, totalprice, listtime</strong></p><p>These are the Type 2 columns:</p><p><strong>sellerid, eventid, dateid</strong></p><p>Once we connect our data warehouse to the dbt project we are good to go!</p><h4>The Plan</h4><p>There are three components to building our final dimension table. The first component being the dbt snapshot that will handle any incoming Type 2 changes, while the second component will be a reusable macro that accounts for Type 1 changes. The final component is another macro used to record any dimension metadata (record start and end date, update timestamp etc.). It is also worth noting that the final dimension table is an <a href="https://docs.getdbt.com/docs/building-a-dbt-project/building-models/configuring-incremental-models">incremental</a> model. The diagram below highlights what this looks like.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/511/1*U4ejrmxw2FR5RBoidLPM4w.png" /><figcaption>Architecture For Dimension Table</figcaption></figure><p>In terms of joins, the dimension model is initially created from the Type 2 layer, which we then stack the Type 1 layer and any incremental runs through left joins.</p><h4>Capturing Type 2 Changes</h4><p>Out of the two components, Type 2 is the easier one to implement in dbt, hence our starting point. We will be making use of dbt snapshots to capture all Type 2 changes.</p><p>First thing we should do is create a dbt snapshot file, the code below shows what the file should look like.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6d395c0ceed9798fc93bc559c897858f/href">https://medium.com/media/6d395c0ceed9798fc93bc559c897858f/href</a></iframe><blockquote>Note: In this snapshot and other models, we define our source in the schema.yml located in our models directory</blockquote><p>In the config block, specify the schema where you want to create your snapshot and the unique key the snapshot will use to make changes to (in our case, <strong>listid</strong>). As we have a mix of Type 1 and 2 columns in our dimensions, we will use the check strategy to track Type 2 changes in the columns specified as Type 2.</p><p>To construct our snapshot, we first define what the dimension key is going to be (<strong>listing_key in this case)</strong>. For this example, the dimension key follows a definition of “<strong>listid-current_timestamp”</strong>. We then include all the columns from the source table (listing) and using the <strong>dbt_utils</strong> library, we create a surrogate key for all the Type 1 columns to be able to keep track of any Type 1 changes for a given record. This will prove useful in the Type 1 component. Below is a snippet of what records would look like.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f44a839e7075949e622d4adec81fba80/href">https://medium.com/media/f44a839e7075949e622d4adec81fba80/href</a></iframe><p>By default, dbt generates 4 metadata columns, dbt_scd_id, dbt_updated_at, dbt_valid_from and dbt_valid_to. For our dimension model, we will only be using the last 3 columns, which represent the timestamp for when the record was updated and the active record dates respectively.</p><h4>Capturing Type 1 Changes</h4><p>Now that we have our Type 2 changes handled, let us create our Type 1 component. As part of the final design, Type 1 changes are handled at the final dimension model level. To separate out the logic for handling Type 1 changes, let us create a macro to handle just that.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0bbeacaa0fec1358d8d574ce75cb56d5/href">https://medium.com/media/0bbeacaa0fec1358d8d574ce75cb56d5/href</a></iframe><p>Our macro takes in several argument, the first being a list of columns we want to track changes for (Type 1 columns), the name of the dimension we’re building and prefixes for the models that we will refer to when building the macro.</p><p>Going through the prefixes, <strong>inc</strong> represents the dimension model we refer to when running incremental runs, <strong>t2</strong> represents the snapshot we created to capture type 2 changes and <strong>source</strong> represents the source data we are building our dimension for (listing)</p><p>When handling Type 1 changes, there are two scenarios that we need to handle, the first being the initial run for the dimension incremental model to load all the current data (Line 11–15) and the second being all subsequent incremental runs after the initial run for incremental data (Line 3- 9). For both scenarios, we are using the <strong>t2</strong> records as our base for comparison to the <strong>source </strong>and <strong>inc</strong> records for Type 1 changes.</p><p>In the initial run case, we first need to check whether a record in <strong>t2</strong> is an active record. This is done by checking the dbt generated column “<strong>dbt_valid_to</strong>” value is null, indicating that it is active. We also need to check whether there has been a change in the <strong>t1_key</strong> for the record, indicating a Type 1 change occuring. If these two conditions are met, then the record gets updated with the latest value.</p><p>Similarly for the incremental runs, the same logic is used here, but instead of comparing the <strong>t2</strong> records, we will use the <strong>inc </strong>records as we already have our dimension model created in the initial run. This implies that if the record is current and there is a difference in <strong>t1_key</strong>, a Type 1 change occurs. However, one additional case we need to account for is for new entries in the snapshot (line 7–8). This is handled by checking when a record does not exist in <strong>inc</strong>, we then use the incoming data from <strong>t2</strong>, otherwise we persist its previous value from the <strong>inc </strong>table.</p><h3>Dimension Metadata (Timestamps)</h3><p>Now that we are capturing both Type 1 and Type 2 changes for our listing dimension, the last thing we need to do is capture all timestamp metadata for our dimension records. To be exact, we will capture the dates that the record is active and an update timestamp whenever a Type 1 change occurs or if a record is marked as inactive.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c5f136002902720f780dc3c11efd6163/href">https://medium.com/media/c5f136002902720f780dc3c11efd6163/href</a></iframe><p>Using the same logic and macro arguments as the Type 1 macro above, there are two scenarios that we need to cover (initial and incremental runs) and the way that values are handled is the same, with the only difference being that if Type 1 changes occur, we will insert the current timestamp in the <strong>dbt_updated_at</strong> timestamp, with the default being the <strong>inc</strong> table value and the <strong>t2</strong> value respectively for the incremental and initial case if no Type 1 change has occurred.</p><h3>Putting Everything Together</h3><p>Now that we have all pieces for our final dimension model created, let’s combine them to a single incremental model.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0278a1b1a6e7bdb4bb3de2d11066405f/href">https://medium.com/media/0278a1b1a6e7bdb4bb3de2d11066405f/href</a></iframe><p>With the two views v_listing and v_this, these views correspond to the prefix of <strong>source</strong> and <strong>inc</strong> used in the macros above. Both these views are created to insert the surrogate key used to track changes for all Type 1 columns using the <strong>dbt_utils</strong> surrogate_key function.</p><p>Now onto the main chunk of the model. Reflecting the Type 2 changes is simply just taking the values from the snapshot. Capturing Type 1 changes and updating the timestamp involves using the macros above. As the snapshot also records the active dates for each record, we simply convert the dates created in the snapshot to your local timezone as dbt’s default timezone is UTC.</p><p>For the initial run of this model, we will only use the snapshot and v_listing to construct our dimension table. The v_this view only gets used during incremental runs, since it is only defined after the dimension is created.</p><p>As a result this is what the final table looks like.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/33cff00a87888b9c299bc35b5b3b0844/href">https://medium.com/media/33cff00a87888b9c299bc35b5b3b0844/href</a></iframe><p>Let’s put our model to the test. We will make each Type change once in the source table. This is what our dimension table looks like before the change.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2f7a33f658e23e5a8bb251ee81a73e7d/href">https://medium.com/media/2f7a33f658e23e5a8bb251ee81a73e7d/href</a></iframe><p>We shall change list id 483’s <strong>sellerid</strong> to 2000 (Type 2 change) and list id 724 ‘s <strong>numticket </strong>and <strong>totalprice</strong> columns to 11 and 440 respectively (Type 1 Change) in the source table.</p><p>After running the snapshot and the incremental model, our dimension table looks like this now.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ef24e839b8f035d6a9df66f77030f7a4/href">https://medium.com/media/ef24e839b8f035d6a9df66f77030f7a4/href</a></iframe><h3>Conclusion</h3><p>By following this pattern to create dimension models, it is easy to incorporate both Type 1 and Type 2 changes into the same dimension. Using dbt macros, we have modularised the dimension’s functionality for reusability and provide capacity to add more functionality to the dimension.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1b80078f290a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/weareservian/modelling-type-1-2-slowly-changing-dimensions-with-dbt-1b80078f290a">Modelling Type 1 + 2 Slowly Changing Dimensions with dbt</a> was originally published in <a href="https://medium.com/weareservian">Cognizant Servian</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>