Did you ever want to be able to produce a lot of historic data for your users, and at the same time not have to administrate a huge amount of data?
Delta updated tables in your DW is a very effective way to preserve disk space, but not that easy for everybody to retrieve data from later on.
Snapshot data is a lot easier to access for the end user, whether it be the programmer or the analyst.
Snapshot data is every piece of data layed out every day, making it easy to pick all your data at a specific date.
It's redundant as hell and some database administrators are not that pleased with this approach.
That's why I took the best from both worlds and combined it into a tool that preserves space and makes it easy to retrieve your data.
It gives a little extra work on the data preparation side, but it's actually quite easy to handle.
In the attached Excel document there is a simple example of how to model your DW tables and create a snapshot view on top of them, making it easy to retrieve data based on specific dates.
The table CUSTOMER_BASE is the base table holding all the information about the customers.
It has an ID column that is unique for every record in the table.
The FROMDATE column tells us from what date the actual record apply.
It also has a column names SOCSECNO (social security number) with an index. This is used for selecting data in the example.
The table CUSTOMER_SNAP is the actual snapshot table, holding a reference to the base data for a customer, data that applies at the date found in the DATE column.
This table holds a record for each customer for each and every date up until now.
You have to make a small batch program/job that creates this table from the BASE table, and when adding data to the BASE table you have to make some housekeeping in the SNAP table.
On top of these two tables put a view that joins the table on the ID column.
Now your users can easily access data from a delta updated DW table using the snapshot access principle.
