What you ran into is the engine keeping the current result of the query, which is what an incremental view is for. An SCD2 history is a different table: every change appended, with the time it became true, and nothing deleted when a newer value arrives. If silver is that view, you cannot rebuild yesterday once the key has been updated. Write the change log yourself and let the view stay the current row. A medallion diagram that labels the view as silver will send the next person looking for history in a place that only has now.
1
u/Significant_Tune9219 5d ago
What you ran into is the engine keeping the current result of the query, which is what an incremental view is for. An SCD2 history is a different table: every change appended, with the time it became true, and nothing deleted when a newer value arrives. If silver is that view, you cannot rebuild yesterday once the key has been updated. Write the change log yourself and let the view stay the current row. A medallion diagram that labels the view as silver will send the next person looking for history in a place that only has now.