title: Designing a slowly changing dimension type 2 script with postgresql
body: <p>Let's say I have the following target table:</p>
  
  <pre><code>CREATE TABLE DimCustomer (
  CustomerKey serial PRIMARY KEY,
      CustomerNum int NOT NULL,
      CustomerName varchar(25) NOT NULL,
      Planet varchar(25) NOT NULL,
      RowIsCurrent char(1) NOT NULL DEFAULT 'Y',
      RowStartDate date NOT NULL DEFAULT CURRENT_TIMESTAMP,
      RowEndDate date NOT NULL DEFAULT '12/31/9999'
  );
  
  INSERT INTO DimCustomer
  (CustomerNum, CustomerName, Planet,  RowStartDate) 
  VALUES (101,'Anakin Skywalker', 'Tatooine',   CURRENT_TIMESTAMP - INTERVAL '101 days'),
         (102,'Yoda', 'Coruscant',  CURRENT_TIMESTAMP - INTERVAL '100 days'),
         (103,'Obi-Wan Kenobi', 'Coruscant',  CURRENT_TIMESTAMP - INTERVAL '100 days')
  </code></pre>
  
  <p>And I have a following staging table:</p>
  
  <pre><code>CREATE TABLE Staging_DimCustomer
  (
      CustomerNum int NOT NULL,
      CustomerName varchar(25) NOT NULL,
      Planet varchar(25) NOT NULL,
      ChangeDate date NOT NULL DEFAULT CURRENT_TIMESTAMP,
      RankNo int NOT NULL DEFAULT 1
  )
  INSERT INTO Staging_DimCustomer(CustomerNum, CustomerName, Planet, ChangeDate)
  VALUES
  (103,'Ben Kenobi', 'Coruscant',   CURRENT_TIMESTAMP - INTERVAL '99 days')
  </code></pre>
  
  <p>In the staging table, it looks like <code>'Obi-Wan Kenobi'</code>(<code>customernum 103</code>) changed his name to 
   <code>'Ben Kenobi'</code>. I want to create a script that implements scd type 2 and produces the following result(slowly changing dimension type 2):</p>
  
  <p><a href="https://i.stack.imgur.com/pqbxF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pqbxF.png" alt="enter image description here"></a></p>
  
  <p>Following is my attempt:</p>
  
  <pre><code>INSERT INTO DimCustomer (
    CustomerNum, CustomerName, Planet, RowIsCurrent, RowStartDate, RowEndDate
    ) 
   select CustomerNum, CustomerName, Planet, 'Y', ChangeDate, '12/31/9999'
   from Staging_DimCustomer 
  
   ON CONFLICT (CustomerNum) and RowIsCurrent = 'Y'
    DO UPDATE SET
      CustomerName = EXCLUDED.CustomerName,
      Planet = EXCLUDED.Planet,
      RowIsCurrent = 'N',
      RowEndDate = EXCLUDED.ChangeDate
  </code></pre>
  
  <p>I dont know how to look for the values that changed, update the existing rows to retire it and then insert the new rows with <code>rowiscurrent = 'Y'</code> flag. I am trying to model my solution based on this sql server article
  <a href="http://www.made2mentor.com/2013/08/how-to-load-slowly-changing-dimensions-using-t-sql-merge/" rel="nofollow noreferrer">http://www.made2mentor.com/2013/08/how-to-load-slowly-changing-dimensions-using-t-sql-merge/</a>.</p>