Type 1 slowly changing dimension – very simple example

gistfile1.sql

-- Structure:

-- src_customers: id, name, address
-- dim_customers: id, name, address

-- Find new records

DROP TABLE IF EXISTS tmp_new;

CREATE TABLE tmp_new AS 
SELECT id FROM src_customers
    EXCEPT
    SELECT id FROM dim_customers;

INSERT INTO dim_customers
    SELECT s.* FROM tmp_new n
    JOIN src_customers s on s.id = n.id;

-- Update changed records

UPDATE dim_customers
SET name = diff.name,
    address = diff.address
FROM (
    SELECT s.*
    FROM dim_customers d
    JOIN src_customers s ON s.id = d.id
    WHERE d.name != s.name
        OR d.address != s.address) diff
WHERE dim_customers.id = diff.id;