No, what Dieter is explaining to you is that you don't need the surrogate key as the database will already handle the update on it's own, and giving you only one row for an id at one point of time.
The work you want to do with the surrogate key is redundant with temporal. Except it will be harder to code, maintain, and will probably perform lesser.
thanks for your effort
I was trying to use temporal feature as it almost required zero effort from the ETL , and at same time to stick on dimenssion modeling kimball standard,
I will compromise between the two solution
Just for your info , have a look on
Yes, anyone who has been doing this long enough is familiar with Kimball (and Inmon, Codd et al.). Temporal will solve most of the problems that artificial keys were supposed to solve - think of it as a natural key that includes a timestamp. But you have a bigger problem than that - some people seem to be identified by Passport Number and others by Debit Card Number, and that implies that you have no way of knowing if some people are in there twice. This is two completely different sets of data, probably with completely different attributes. Don't you really have two different dimentions here?
Yes , In my organization there is a system for debit card and other for Credit card and Other for Morgage , the DWH is suppose to have one Customer dimenssion to be filled from different source system , In the way that prevent duplicattion of same customer. Link between Passport number and Credit card and Any other identifier will be manage and monitor by a tool outside DWH , Which provide to the DWH the link between the different idenfier.
this why I need to have an Identity column and Need temporal to use this Column, at the same time I need to generate surrograte key (integer) So the table will have One column representing the primary Key ( the surrogate key ), Not a composite primary key ( Identity and Start of validity )