We have run into an issue where if the foreign key is reverted to an older value. For e.g. the Account on Opportunity changes but it is reverted back to the original Account. Here is example of the sequence of events
|Opportunity||Account||Load Date||Link Hashkey|
|Opp A||Acct 1||01-01-2021||1|
|Opp A||Acct 2||01-02-2021||2|
|Opp A||Acct 1||01-03-2021||1|
Based on our current LINK implementation the step would not be reflected in the LINK table since the LINK Hashkey will be same as the one for the first step, which is an issue when we are projecting into the INFOMART. Since we take the latest LOAD_DATE from the LINK table, the projected view will have the Acct2 for Opp A. Whereas based on the above table it should Acc 1.
How do we handle such situations?