Sorry i thought i had listed the process clearly.

Im trying to build a Product dimension that has both type 1 and type 2 scd. Most product tables i have work on will have one unique product key for each product so that if any type 2 scd occur i can pick them up and insert them into my product dimension i can look at the change history of that product in my dimension using the natural key(productid) as it is unique for each product.

MY problem with this products table is because the product can be moved between a live and draft status (which actually marks the record inactive in the products table, moves it to a staging table) when the product is moved back to live (it is then moved from the staging table back to the products table) a new record is inserted into the products table which gives it a new productid (if the original record was updated then i wouldn't have a problem)

so now when i run my etl although this is the same product, just that it has been moved to draft to be edited and then made live again, because of the insert it now has a new Product id, in my product dimension it looks like an entirely new product.

So source product table with a live product

ProductID| Name | Color | IsActive

12345 a1 Red 1

source product table after that product is moved to draft (into a staging table)

ProductID| Name | Color | IsActive

12345 a1 Red 0

after that product is moved back to live (inserted back into product table)

ProductID| Name | Color | IsActive

12345 a1 Red 0

12346 a1 Green 1

its really like my actual sorce products table is tracking the changes to a degree before it gets to my etl. hope this make it clearer.