In most of the data warehouses we often encounter tables populated with SCD logic to save space. It could be on the staging layer or on the Dimention or the fact tables.
So if your consumption requirement demands you to have a daily snapshot (or monthly snapshot), then the first thing we do is to set up a process which reads from the table with history , get active records and insert into a new daily snapshot or from the staging start populating a daily snapshot (in case of staging tables). But there is another way to tackle this requirement without any new process to load this daily snapshot.
The above diagram gives a very simple flow of information from a source file to a target table and for the sake of simplicity lets assume that we are running some business rules on this sinlge file (although in real world you are highly likely to have either reference tables or using data from other tables to calculate) and the results are populated in a target entity.
Now the target table will look like the below for the KPI Net Avg Bal
So for a customer 001, on the first day of the batch (01-10-2021), The target table will look like this,
On day 2, when the Net Avg Bal changes, it will have a new record with a new value and the old record will be marked as closed as shown in the picture below,
On day 3, if there is no change in the vaule of Net Avg Bal, then it will still have just 2 records, this will help to reduce the amount of storge in contrast to having a new record inserted for every day.