Page tree
Skip to end of metadata
Go to start of metadata

SCD Type 1 (Upsert)


SCD Type 2

SC Type 2
MERGE INTO `<project>.<dataset>.dimensiontable`
USING (
  SELECT `<project>.<dataset>.staging`.*
  FROM   `<project>.<dataset>.staging`
  UNION ALL

  SELECT `<project>.<dataset>.staging`.*
  FROM `<project>.<dataset>.staging`
  JOIN `<project>.<dataset>.dimensiontable`
  ON `<project>.<dataset>.staging`.<key1> = `<project>.<dataset>.dimensiontable`.<key1>
  WHERE (
     `<project>.<dataset>.stg1`.<attribute1> <> `<project>.<dataset>.fact`.<attribute1> OR
     `<project>.<dataset>.stg1`.<attribute2> <> `<project>.<dataset>.fact`.<attribute2> OR
     `<project>.<dataset>.stg1`.<attribute3> <> `<project>.<dataset>.fact`.<attribute3> OR
     `<project>.<dataset>.stg1`.<attribute4> <> `<project>.<dataset>.fact`.<attribute4>
  )
) subQuery
ON ( 
  sub.<key1> = `<project>.<dataset>.dimensiontable`.<key1> AND
  sub.<key2> = `<project>.<dataset>.dimensiontable`.<key2> AND
  sub.<key3> = `<project>.<dataset>.dimensiontable`.<key3>
)
  
WHEN MATCHED AND ( 
  subQuery.<attribute1> <> `<project>.<dataset>.dimensiontable`.<key1> OR
  subQuery.<attribute2> <> `<project>.<dataset>.dimensiontable`.<key2> OR
  subQuery.<attribute3> <> `<project>.<dataset>.dimensiontable`.<key3> OR
  subQuery.<attribute4> <> `<project>.<dataset>.dimensiontable`.<key4>
)
THEN UPDATE SET 
  lastupdt = CURRENT_DATETIME(), 
  version = version + 1
  
WHEN NOT MATCHED
THEN INSERT (
  <key1>, 
  <key2>,
  <key3>,
  <attribute1>,
  ... 
  <attribute4>,
  ...
  <attributen>, 
  version,   
  last_updt) 
VALUES (
  subQuery.<key1>, 
  subQuery.<key2>, 
  subQuery.<attribute1>, 
  ..., 
  <attribute4>, 
  ... 
  <attributen>, 
  1, 
  CURRENT_DATETIME()
);

How to

  • No labels