SCD type 2 in Delta table using databricks

Question

I want to implement SCD type 2 in databricks, I know this can be handled with MERGE. But I have historical data which is appended and am not sure how to implement SCD type 2 since the target table is empty as its initial load sample of my data.

ID	Name
1	A
2	B
1	A2
3	C
3	C2

Now how do implement SCD type 2 since target table is empty as its initial load.

Please do explain me with example.

Answer 1

You could either load the records in age order and in separate load runs - which is is unlikely to be practicable for large data volumes - or you manually create the SCD records for your historic data and then insert them into your target

• CloudEngineer: Can you please let me know with an example how to manually create an SCD records for historical data?
• NickW: Not without having some sample data. Also, as I'm afraid this is not a free code writing service, you would also need to show an attempt at doing this yourself. I suggest that if you get stuck you create a new post showing sample data, the result you want to achieve, the SQL you've managed to write and explain precisely what issue you are facing