The creation of snapshot fact tables is very dependent on the method used to create the snapshot. In some cases they may be created in exactly the same way as detail fact tables. These cases are typically where the OLTP application provides the 'as at' state. For example, an inventory system may provide a weekly stock take table which is the basis for our snapshot fact table. In such cases the table is created as per a detail fact table, but with additional tailoring around the time dimension.
In other situations, the snapshot is built from an existing transactional fact table, where all information up to a specific date is summarized or frozen to create the snapshot. In these instances, the snapshot can be created as a rollup fact table.
Regardless of whether a detail or roll-up method is used for the creation of the snapshot table, a significant amount of work will be required to tailor the update procedure to reflect the particular requirements of the snapshot table.
Snapshot Fact Columns
The columns for a snapshot Fact table vary to a large degree. A date dimension is always present to reflect the 'as at' date. Some snapshots may have a large number of measures, reflecting $values quantities, etc., whereas others may be rich in attribute (non-additive) facts.