{"id":340,"date":"2023-12-12T11:04:35","date_gmt":"2023-12-12T10:04:35","guid":{"rendered":"https:\/\/bitwise.exposed\/?p=340"},"modified":"2026-03-16T09:38:47","modified_gmt":"2026-03-16T08:38:47","slug":"scd2-pattern","status":"publish","type":"post","link":"https:\/\/bitwise.exposed\/index.php\/2023\/12\/12\/scd2-pattern\/","title":{"rendered":"SCD2 simplified example"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">When dealing with <a href=\"https:\/\/bitwise.exposed\/index.php\/2023\/12\/10\/types-of-dimension-tables\/#DimTablesSCD2\" data-type=\"post\" data-id=\"328\">Slowly Changing Dimension Type 2 (SCD2)<\/a> in a data warehouse, a common practice is to use a staging pattern to handle the loading and processing of data before it is inserted into the final dimension table. This staging pattern helps manage historical changes and ensures that only the necessary updates are applied to the dimension table. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s a simplified example of a staging pattern in SQL for handling SCD2:<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Step 1 <\/strong>&#8211; <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">DimCustomer <\/mark>table, that will maintain the history of customer changes:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>CREATE TABLE DimCustomer(\n    Customer_SNK INT IDENTITY PRIMARY KEY,        -- SuperNaturalKey\n    Customer_BK INT,                              -- BusinessKey\n    FirstName VARCHAR(100),\n    LastName VARCHAR(100),\n    EffectiveDate DATE,\n    EndDate DATE DEFAULT '9999-12-31',\n    IsCurrent CHAR(1)\n);<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0000FF\">CREATE<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">TABLE<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #795E26\">DimCustomer<\/span><span style=\"color: #000000\">(<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    Customer_SNK <\/span><span style=\"color: #0000FF\">INT<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">IDENTITY<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">PRIMARY KEY<\/span><span style=\"color: #000000\">,        <\/span><span style=\"color: #008000\">-- SuperNaturalKey<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    Customer_BK <\/span><span style=\"color: #0000FF\">INT<\/span><span style=\"color: #000000\">,                              <\/span><span style=\"color: #008000\">-- BusinessKey<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    FirstName <\/span><span style=\"color: #0000FF\">VARCHAR<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">100<\/span><span style=\"color: #000000\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    LastName <\/span><span style=\"color: #0000FF\">VARCHAR<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">100<\/span><span style=\"color: #000000\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    EffectiveDate <\/span><span style=\"color: #0000FF\">DATE<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    EndDate <\/span><span style=\"color: #0000FF\">DATE<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">DEFAULT<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #A31515\">&#39;9999-12-31&#39;<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    IsCurrent <\/span><span style=\"color: #0000FF\">CHAR<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">1<\/span><span style=\"color: #000000\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Step 2<\/strong> &#8211; <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">FactSalesTransaction <\/mark>table, with records of sales transactions:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>CREATE TABLE FactSalesTransaction(\n    TransactionID INT PRIMARY KEY,\n    Customer_SNK INT,\n    SalesDate DATE,\n    SalesAmount MONEY\n);<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0000FF\">CREATE<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">TABLE<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #795E26\">FactSalesTransaction<\/span><span style=\"color: #000000\">(<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    TransactionID <\/span><span style=\"color: #0000FF\">INT<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">PRIMARY KEY<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    Customer_SNK <\/span><span style=\"color: #0000FF\">INT<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    SalesDate <\/span><span style=\"color: #0000FF\">DATE<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    SalesAmount <\/span><span style=\"color: #0000FF\">MONEY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Step 3<\/strong> &#8211; <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">Staging_Customer <\/mark>table to fetch updates from the source system:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>CREATE TABLE Staging_Customer(\n    CustomerID INT,\n    FirstName VARCHAR(100),\n    LastName VARCHAR(100),\n    LoadDate DATE,\n);<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0000FF\">CREATE<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">TABLE<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #795E26\">Staging_Customer<\/span><span style=\"color: #000000\">(<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    CustomerID <\/span><span style=\"color: #0000FF\">INT<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    FirstName <\/span><span style=\"color: #0000FF\">VARCHAR<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">100<\/span><span style=\"color: #000000\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    LastName <\/span><span style=\"color: #0000FF\">VARCHAR<\/span><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">100<\/span><span style=\"color: #000000\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    LoadDate <\/span><span style=\"color: #0000FF\">DATE<\/span><span style=\"color: #000000\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Step 4<\/strong> &#8211; Data <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">load <\/mark>into the <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">staging table<\/mark>  from source system.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(2 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Truncate the staging table\nTRUNCATE TABLE Staging_Customer;\n\n-- Insert data into the staging table\nINSERT INTO Staging_Customer (\n    CustomerID,\n    FirstName,\n    LastName,\n    LoadDate\n)\nSELECT \n    CustomerID,\n    FirstName,\n    LastName,\n    GETDATE()     -- get the current date and time\nFROM source_table;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #008000\">-- Truncate the staging table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">TRUNCATE<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">TABLE<\/span><span style=\"color: #000000\"> Staging_Customer;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #008000\">-- Insert data into the staging table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">INSERT INTO<\/span><span style=\"color: #000000\"> Staging_Customer (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    CustomerID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    FirstName,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    LastName,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    LoadDate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">SELECT<\/span><span style=\"color: #000000\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    CustomerID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    FirstName,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    LastName,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #795E26\">GETDATE<\/span><span style=\"color: #000000\">()     <\/span><span style=\"color: #008000\">-- get the current date and time<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">FROM<\/span><span style=\"color: #000000\"> source_table;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Step 5<\/strong> &#8211; <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">Update <\/mark>EndDate of existing records in <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">dimension table<\/mark> where changes are found:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>UPDATE d\nSET EndDate = s.LoadDate, IsCurrent = 'N'\nFROM DimCustomer d \nJOIN Staging_Customer s \nON d.Customer_BK = s.CustomerID \nWHERE d.IsCurrent = 'Y' \nAND (d.FirstName &lt;> s.FirstName \n   OR d.LastName &lt;> s.LastName);<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0000FF\">UPDATE<\/span><span style=\"color: #000000\"> d<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">SET<\/span><span style=\"color: #000000\"> EndDate = s.LoadDate, IsCurrent = <\/span><span style=\"color: #A31515\">&#39;N&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">FROM<\/span><span style=\"color: #000000\"> DimCustomer d <\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">JOIN<\/span><span style=\"color: #000000\"> Staging_Customer s <\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">ON<\/span><span style=\"color: #000000\"> d.Customer_BK = s.CustomerID <\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">WHERE<\/span><span style=\"color: #000000\"> d.IsCurrent = <\/span><span style=\"color: #A31515\">&#39;Y&#39;<\/span><span style=\"color: #000000\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">AND<\/span><span style=\"color: #000000\"> (d.FirstName &lt;&gt; s.FirstName <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">   <\/span><span style=\"color: #0000FF\">OR<\/span><span style=\"color: #000000\"> d.LastName &lt;&gt; s.LastName);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Step 6<\/strong> &#8211; <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">Insert <\/mark>new records into <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">dimension table<\/mark> for new data:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(2 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>INSERT INTO  DimCustomer (\n                          Customer_BK, \n                          FirstName, \n                          LastName, \n                          EffectiveDate, \n                          IsCurrent\n                          )\n                         \nSELECT \n                          CustomerID, \n                          FirstName, \n                          LastName, \n                          LoadDate, \n                          'Y'\n                          \nFROM         Staging_Customer s\n\nWHERE NOT EXISTS (\n     SELECT 1\n     FROM DimCustomer d \n     WHERE d.Customer_BK = s.CustomerID AND d.IsCurrent = 'Y'\n     );\n     \n\"\"\"\nThe WHERE clause with NOT EXISTS is saying: \n\nIf matching records are found, \nthe NOT EXISTS condition becomes FALSE, \nand the respective record from the staging table \nis not inserted into the main table.\n\"\"\"<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0000FF\">INSERT INTO<\/span><span style=\"color: #000000\">  DimCustomer (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          Customer_BK, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          FirstName, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          LastName, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          EffectiveDate, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          IsCurrent<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">SELECT<\/span><span style=\"color: #000000\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          CustomerID, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          FirstName, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          LastName, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          LoadDate, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          <\/span><span style=\"color: #A31515\">&#39;Y&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">                          <\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">FROM<\/span><span style=\"color: #000000\">         Staging_Customer s<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">WHERE<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">NOT<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">EXISTS<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">     <\/span><span style=\"color: #0000FF\">SELECT<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #098658\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">     <\/span><span style=\"color: #0000FF\">FROM<\/span><span style=\"color: #000000\"> DimCustomer d <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">     <\/span><span style=\"color: #0000FF\">WHERE<\/span><span style=\"color: #000000\"> d.Customer_BK = s.CustomerID <\/span><span style=\"color: #0000FF\">AND<\/span><span style=\"color: #000000\"> d.IsCurrent = <\/span><span style=\"color: #A31515\">&#39;Y&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">     );<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">     <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A31515\">&quot;&quot;&quot;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A31515\">The WHERE clause with NOT EXISTS is saying: <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #A31515\">If matching records are found, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A31515\">the NOT EXISTS condition becomes FALSE, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A31515\">and the respective record from the staging table <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A31515\">is not inserted into the main table.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A31515\">&quot;&quot;&quot;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h5 class=\"wp-block-heading\">Test Data<\/h5>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Step 1<\/strong> &#8211; <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">DimCustomer <\/mark>with <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">initial data<\/mark>:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>--Populating DimCustomer\nINSERT INTO DimCustomer (Customer_BK, FirstName, LastName, EffectiveDate, IsCurrent)\nVALUES \n(101, 'Bob', 'Weber', '2023-01-01', 'Y'),\n(102, 'Alice', 'Miller', '2023-01-01', 'Y');<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #008000\">--Populating DimCustomer<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">INSERT INTO<\/span><span style=\"color: #000000\"> DimCustomer (Customer_BK, FirstName, LastName, EffectiveDate, IsCurrent)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">VALUES<\/span><span style=\"color: #000000\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">101<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;Bob&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;Weber&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;2023-01-01&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;Y&#39;<\/span><span style=\"color: #000000\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">102<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;Alice&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;Miller&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;2023-01-01&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;Y&#39;<\/span><span style=\"color: #000000\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Step 2<\/strong> &#8211; <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">FactSalesTransaction <\/mark>with <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">initial data<\/mark> (associating transactions with the customers Bob Weber and Alice Maier):<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>--Populating FactSalesTransaction\nINSERT INTO FactSalesTransaction (TransactionID, Customer_SNK, SalesDate, SalesAmount)\nVALUES \n(1, 1, '2023-01-02', 100.00),  -- Bob Weber made a $100 transaction\n(2, 2, '2023-01-02', 200.00);  -- Alice Maier made a $200 transaction<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #008000\">--Populating FactSalesTransaction<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">INSERT INTO<\/span><span style=\"color: #000000\"> FactSalesTransaction (TransactionID, Customer_SNK, SalesDate, SalesAmount)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">VALUES<\/span><span style=\"color: #000000\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">1<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #098658\">1<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;2023-01-02&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #098658\">100<\/span><span style=\"color: #000000\">.<\/span><span style=\"color: #098658\">00<\/span><span style=\"color: #000000\">),  <\/span><span style=\"color: #008000\">-- Bob Weber made a $100 transaction<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">2<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #098658\">2<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;2023-01-02&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #098658\">200<\/span><span style=\"color: #000000\">.<\/span><span style=\"color: #098658\">00<\/span><span style=\"color: #000000\">);  <\/span><span style=\"color: #008000\">-- Alice Maier made a $200 transaction<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Step 3<\/strong> &#8211; <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">New data arrives<\/mark> into the Staging_Customer table. Let&#8217;s assume that <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">Alice changes her last name to <strong>Smith<\/strong><\/mark>:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>--Populating Staging_Customer \nINSERT INTO Staging_Customer (CustomerID, FirstName, LastName, LoadDate)\nVALUES \n(101, 'Bob', 'Weber', '2023-01-02'),  --Existing record, no change \n(102, 'Alice', 'Smith', '2023-01-02');  --Existing record, name change<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #008000\">--Populating Staging_Customer <\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">INSERT INTO<\/span><span style=\"color: #000000\"> Staging_Customer (CustomerID, FirstName, LastName, LoadDate)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">VALUES<\/span><span style=\"color: #000000\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">101<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;Bob&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;Weber&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;2023-01-02&#39;<\/span><span style=\"color: #000000\">),  <\/span><span style=\"color: #008000\">--Existing record, no change <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">102<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;Alice&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;Smith&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;2023-01-02&#39;<\/span><span style=\"color: #000000\">);  <\/span><span style=\"color: #008000\">--Existing record, name change<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Step 4<\/strong> &#8211; Run the SCD Type 2 <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">updates to <\/mark><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">DimCustomer<\/mark>:<br><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>First<\/strong>, <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">customers that have changes<\/mark> will be <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">marked as non-current<\/mark>:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>UPDATE d\nSET EndDate = s.LoadDate - 1, IsCurrent = 'N'\nFROM DimCustomer d \nJOIN Staging_Customer s \nON d.Customer_BK = s.CustomerID \nWHERE d.IsCurrent = 'Y' \nAND (d.FirstName &lt;> s.FirstName OR d.LastName &lt;> s.LastName);<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0000FF\">UPDATE<\/span><span style=\"color: #000000\"> d<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">SET<\/span><span style=\"color: #000000\"> EndDate = s.LoadDate - <\/span><span style=\"color: #098658\">1<\/span><span style=\"color: #000000\">, IsCurrent = <\/span><span style=\"color: #A31515\">&#39;N&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">FROM<\/span><span style=\"color: #000000\"> DimCustomer d <\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">JOIN<\/span><span style=\"color: #000000\"> Staging_Customer s <\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">ON<\/span><span style=\"color: #000000\"> d.Customer_BK = s.CustomerID <\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">WHERE<\/span><span style=\"color: #000000\"> d.IsCurrent = <\/span><span style=\"color: #A31515\">&#39;Y&#39;<\/span><span style=\"color: #000000\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">AND<\/span><span style=\"color: #000000\"> (d.FirstName &lt;&gt; s.FirstName <\/span><span style=\"color: #0000FF\">OR<\/span><span style=\"color: #000000\"> d.LastName &lt;&gt; s.LastName);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Then<\/strong>, <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">insert new records<\/mark> for the <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">changed customers<\/mark>:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>INSERT INTO DimCustomer (Customer_BK, FirstName, LastName, EffectiveDate, IsCurrent)\nSELECT CustomerID, FirstName, LastName, LoadDate, 'Y'\nFROM Staging_Customer s\nWHERE NOT EXISTS (\n    SELECT 1\n    FROM DimCustomer d \n    WHERE d.Customer_BK = s.CustomerID AND d.IsCurrent = 'Y');<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0000FF\">INSERT INTO<\/span><span style=\"color: #000000\"> DimCustomer (Customer_BK, FirstName, LastName, EffectiveDate, IsCurrent)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">SELECT<\/span><span style=\"color: #000000\"> CustomerID, FirstName, LastName, LoadDate, <\/span><span style=\"color: #A31515\">&#39;Y&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">FROM<\/span><span style=\"color: #000000\"> Staging_Customer s<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">WHERE<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">NOT<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #0000FF\">EXISTS<\/span><span style=\"color: #000000\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">SELECT<\/span><span style=\"color: #000000\"> <\/span><span style=\"color: #098658\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">FROM<\/span><span style=\"color: #000000\"> DimCustomer d <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">    <\/span><span style=\"color: #0000FF\">WHERE<\/span><span style=\"color: #000000\"> d.Customer_BK = s.CustomerID <\/span><span style=\"color: #0000FF\">AND<\/span><span style=\"color: #000000\"> d.IsCurrent = <\/span><span style=\"color: #A31515\">&#39;Y&#39;<\/span><span style=\"color: #000000\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Now, if you query the DimCustomer table, you&#8217;ll see that Alice Miller  record has been marked as non-current and a new record has been added for Alice Smith.<br><br>For example, if Alice Smith makes a new transaction, you&#8217;d insert into FactSalesTransaction with the new Customer_SNK for Alice Smith (which would be 3 if you&#8217;re following this example):<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:1rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#000000;--cbp-line-number-width:calc(1 * 0.6 * 1rem);line-height:1.5rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#f2f2f2;color:#0d0d0d\">SQL<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#000000;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>INSERT INTO FactSalesTransaction (TransactionID, Customer_SNK, SalesDate, SalesAmount)\nVALUES \n(3, 3, '2023-01-03', 300.00);<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki light-plus\" style=\"background-color: #FFFFFF\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #0000FF\">INSERT INTO<\/span><span style=\"color: #000000\"> FactSalesTransaction (TransactionID, Customer_SNK, SalesDate, SalesAmount)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #0000FF\">VALUES<\/span><span style=\"color: #000000\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #000000\">(<\/span><span style=\"color: #098658\">3<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #098658\">3<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #A31515\">&#39;2023-01-03&#39;<\/span><span style=\"color: #000000\">, <\/span><span style=\"color: #098658\">300<\/span><span style=\"color: #000000\">.<\/span><span style=\"color: #098658\">00<\/span><span style=\"color: #000000\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h5 class=\"wp-block-heading\">Walkthrough of how SCD 2 Type works<\/h5>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Initial Setup<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">DimCustomer <\/mark><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">Initial <\/mark><\/p>\n\n\n\n<div class=\"wp-block-group is-vertical is-layout-flex wp-container-core-group-is-layout-510fc8e3 wp-block-group-is-layout-flex\">\n<figure class=\"wp-block-table is-style-regular\"><table><thead><tr><th>Customer_SNK (PK)<\/th><th>Customer_BK<\/th><th>FirstName<\/th><th>LastName<\/th><th>EffectiveDate<\/th><th>EndDate<\/th><th>IsCurrent<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>101<\/td><td>Bob<\/td><td>Weber<\/td><td>2023-01-01<\/td><td>NULL<\/td><td>&#8218;Y&#8216;<\/td><\/tr><tr><td>2<\/td><td>102<\/td><td>Alice<\/td><td>Miller<\/td><td>2023-01-01<\/td><td>NULL<\/td><td>&#8218;Y&#8216;<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-border-secondary-color\">Each row of the table represents a customer in your system. The &#8218;PK&#8216; in &#8218;Customer_SNK (PK)&#8216; designates it as the primary key of the table. &#8218;NULL&#8216; represents no set date, implying that the customer is currently active. &#8218;Y&#8216; and &#8218;N&#8216; in &#8218;IsCurrent&#8216; column represent whether the record is current or not.<\/mark><br><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">FactSalesTransaction <\/mark><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">Initial <\/mark><\/p>\n\n\n\n<div class=\"wp-block-group is-vertical is-layout-flex wp-container-core-group-is-layout-510fc8e3 wp-block-group-is-layout-flex\">\n<figure class=\"wp-block-table\"><table><thead><tr><th>TransactionID (PK)<\/th><th>Customer_SNK<\/th><th>Customer_BK<\/th><th>SalesDate<\/th><th>SalesAmount<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>1<\/td><td>101<\/td><td>2023-01-02<\/td><td>100.00<\/td><\/tr><tr><td>2<\/td><td>2<\/td><td>102<\/td><td>2023-01-02<\/td><td>200.00<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-border-secondary-color\">In this table, each row corresponds to a sale transaction. The &#8218;PK&#8216; in &#8218;TransactionID (PK)&#8216; designates it as the primary key of the table. SalesDate represents the date of the transaction and SalesAmount represents the amount of the transaction.<\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">Staging_Customer<\/mark> <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">Initial <\/mark><\/p>\n\n\n\n<div class=\"wp-block-group has-global-padding is-layout-constrained wp-block-group-is-layout-constrained\">\n<figure class=\"wp-block-table\"><table><thead><tr><th>CustomerID (PK)<\/th><th>FirstName<\/th><th>LastName<\/th><th>LoadDate<\/th><\/tr><\/thead><tbody><tr><td>101<\/td><td>Bob<\/td><td>Weber<\/td><td>2023-01-02<\/td><\/tr><tr><td>102<\/td><td>Alice<\/td><td>Miller<\/td><td>2023-01-02<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-border-secondary-color\">Each row of the table represents a customer in the staging area. The &#8218;PK&#8216; in &#8218;CustomerID (PK)&#8216; designates it as the primary key of the table. &#8218;LoadDate&#8216; is the date when the data was loaded into the staging area.<\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Event: Alice Miller Changes Her Last Name to Smith<\/strong><br><br><br><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">Staging_Customer <\/mark><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">Update<\/mark><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>CustomerID (PK)<\/th><th>FirstName<\/th><th>LastName<\/th><th>LoadDate<\/th><\/tr><\/thead><tbody><tr><td>101<\/td><td>Bob<\/td><td>Weber<\/td><td>2023-01-03<\/td><\/tr><tr><td>102<\/td><td>Alice<\/td><td>Smith<\/td><td>2023-01-03<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-border-secondary-color\">This table shows changes in customer data. It appears that the LastName of customer with CustomerID 102 has been updated from &#8218;Miller&#8216; to &#8218;Smith&#8216;. The LoadDate has also been updated to &#8218;2023-01-03&#8216;.<\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">DimCustomer <\/mark><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">Update<\/mark><br><br>We mark the old Alice Miller record as inactive and insert a new record for Alice Smith.<\/p>\n\n\n\n<div class=\"wp-block-group is-vertical is-layout-flex wp-container-core-group-is-layout-510fc8e3 wp-block-group-is-layout-flex\">\n<figure class=\"wp-block-table\"><table><thead><tr><th>Customer_SNK (PK)<\/th><th>Customer_BK<\/th><th>FirstName<\/th><th>LastName<\/th><th>EffectiveDate<\/th><th>EndDate<\/th><th>IsCurrent<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>101<\/td><td>Bob<\/td><td>Weber<\/td><td>2023-01-01<\/td><td>NULL<\/td><td>&#8218;Y&#8216;<\/td><\/tr><tr><td>2<\/td><td>102<\/td><td>Alice<\/td><td>Miller<\/td><td>2023-01-01<\/td><td>2023-01-02<\/td><td>&#8218;N&#8216;<\/td><\/tr><tr><td>3<\/td><td>102<\/td><td>Alice<\/td><td>Smith<\/td><td>2023-01-03<\/td><td>NULL<\/td><td>&#8218;Y&#8216;<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-border-secondary-color\">This table indicates that Alice Miller (Customer_BK 102) is no longer current (&#8218;N&#8216;) as of &#8218;2023-01-03&#8216;. A new record has been created for Jane Smith (still Customer_BK 102) with a current status (&#8218;Y&#8216;) starting from &#8218;2023-01-03&#8216;.<\/mark><br><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When New Transactions for Alice arrive, we associate the new transaction with the new Alice Smith record.<br><br><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-surface-brand-primary-color\">FactSalesTransaction <\/mark>after a <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-text-accent-color\">New Transaction for Alice<\/mark><\/p>\n\n\n\n<div class=\"wp-block-group is-vertical is-layout-flex wp-container-core-group-is-layout-510fc8e3 wp-block-group-is-layout-flex\">\n<figure class=\"wp-block-table\"><table><thead><tr><th>TransactionID<\/th><th>Customer_SNK<\/th><th>Customer_BK<\/th><th>SalesDate<\/th><th>SalesAmount<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>1<\/td><td>101<\/td><td>2023-01-02<\/td><td>100.00<\/td><\/tr><tr><td>2<\/td><td>2<\/td><td>102<\/td><td>2023-01-02<\/td><td>200.00<\/td><\/tr><tr><td>3<\/td><td>3<\/td><td>102<\/td><td>2023-01-03<\/td><td>150.00<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-border-secondary-color\">In this table, each row corresponds to a sales transaction. The new entry (last row) indicates that customer with Customer_SNK 3 (Alice Smith with Customer_BK 102) made a purchase on 2023-01-03 with SalesAmount of 150.00.<\/mark><\/p>\n","protected":false},"excerpt":{"rendered":"<p>When dealing with Slowly Changing Dimension Type 2 (SCD2) in a data warehouse, a common practice is to use a staging pattern to handle the loading and processing of data before it is inserted into the final dimension table. This staging pattern helps manage historical changes and ensures that only the necessary updates are applied [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":344,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,16,17],"tags":[],"class_list":["post-340","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-warehouse","category-pattern","category-sql"],"_links":{"self":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/340","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/comments?post=340"}],"version-history":[{"count":31,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/340\/revisions"}],"predecessor-version":[{"id":637,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/posts\/340\/revisions\/637"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media\/344"}],"wp:attachment":[{"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/media?parent=340"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/categories?post=340"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bitwise.exposed\/index.php\/wp-json\/wp\/v2\/tags?post=340"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}