Your problem is exactly like SCD2 . You just add one more column with valid to date ( optionals you can add flag is actual to tag current records)

You can use DLT apply changes syntax. Alternatively Merge statement .

On the top of that table you can build views or function to queryy table at given timestamp.

Current state view will be with WHERE valit_to is null.

To query history at given state you just do 'your date' is between valid_from and valod_to.