@Kearon McNicol​ :

It sounds like you are trying to use the table that feeds the SCD table as the source for identifying changes in the data. If I understand correctly, this could lead to circular dependencies and conflicts.

To avoid these issues, you may want to consider using a separate source table or view to identify changes in the data. This source table or view should be created based on the business logic for identifying changes and should not have any circular dependencies with the SCD table or the source data.

Once you have the source table or view in place, you can use the MERGE statement in DBR 12.1 to efficiently update the SCD table based on the changes identified in the source table or view.

Keep in mind that the MERGE statement requires careful consideration of the join conditions and update logic to avoid conflicts and ensure correct results. It may be helpful to test the MERGE statement on a small subset of data before running it on the full dataset.