Day109: How to implement slowly changing dimensions (SCD) Type-0 in Snowflake
SCD TYPE 0:: IMPLEMENTATION
CREATE OR REPLACE TABLE source_table ( ProductID INT, ProductName STRING, Category STRING, Price FLOAT );
INSERT INTO source_table (ProductID, ProductName, Category, Price) VALUES (1, 'Widget', 'Gadgets', 19.99), (2, 'Gizmo', 'Gadgets', 29.99);
SELECT * FROM source_table;
CREATE OR REPLACE TABLE dim_target_table ( ProductID INT, ProductName STRING, Category STRING, Price FLOAT );
INSERT INTO dim_target_table (ProductID, ProductName, Category, Price) VALUES (1, 'Widget', 'Gadgets', 19.99), (2, 'Gizmo', 'Gadgets', 29.99);
SELECT * FROM dim_target_table;
UPDATE source_table
SET ProductName = 'Super Widget'
WHERE ProductID = 1;
MERGE INTO dim_target_table AS TARGET
USING source_table AS SOURCE
ON TARGET.ProductID=SOURCE.ProductID
WHEN MATCHED THEN
UPDATE SET TARGET.ProductName=TARGET.ProductName ------ NO UPDATE
, TARGET.Category=TARGET.Category, ----- NO UPDATE
TARGET.Price=TARGET.Price
WHEN NOT MATCHED THEN
INSERT (PRODUCTID,PRODUCTNAME,CATEGORY,PRICE)VALUES(SOURCE.PRODUCTID,SOURCE.PRODUCTNAME,SOURCE.CATEGORY,SOURCE.PRICE);
INSERT INTO source_table (ProductID, ProductName, Category, Price) VALUES (3, 'SID', 'Gadgets', 39.99), (4, 'HU', 'Gadgets', 59.99);
SELECT * FROM source_table;
SELECT * FROM dim_target_table;