Support Restatements on SCD Type 2 and Use Them When Creating Preview Clone

[StuffbyYuki]

Currently SCD Type 2 supports only restating the model from the beginning of time. One could also restate a model at any point in history too but also end with the latest date. This how how it would work:

Lets say I am restating Jan 10th

Delete from the table where all valid_from >= Jan 10th
Update the table where all valid_to >= Jan 10th
This would reset the table to look like it was last run before the given start date.

Ideally, once this is implemented, when we create a preview clone of a model we would then configure the clone to restate this latest model with the new logic.

Suggestion for resolving that is to update the SCD Type 2 logic to always go through this delete/update logic on each insert. That way in most cases where we are just processing the newest interval it would be no-op (does require a scan though) and when we have to restate it would delete/update as expected.

Inherited from #3642

[plaflamme]

For context, we've had at least one case where the SQLMesh state did not correctly reflect the table's state: the SQLMesh state was not aware that an interval had been successfully computed. This resulted in subsequent runs failing because of audit failures if I remember correctly.

So the suggestion to do this always is good, it would catch these weird situations and get things back into a good state again. That said, the cost of making these queries may be high in some contexts, not sure.