Snapshot Tables Data Warehouse

Often our customers ask us to capture a specific point in time – say inventory or how much they are owed in Accounts Receivable – so that they can refer back to it later. Of course, this can be recreated by rolling back all the transactions but it is far easier to capture a snapshot of the data as of a point in time. This is stored then in data warehouse snapshot tables. Then you can trend back and compare today vs. last week or last month. This information is very basic in business intelligence because we want to compare today vs. something, whether it’s the past or a goal or what have you. [quote align=”right” color=”#999999″]…the most utility is trending for the last quarter and then keeping the snapshot as of the last day of a month…[/quote]

The drawback come in storing these points in time, these snapshots. Each snapshot is not altogether overwhelming in terms of storage, but keeping inventory by every product by every warehouse by every bin can be 10000+ records for a day and if you keep that each day for a year and then two years and so on… well, what is the utility of having your inventory position on Flag Day 2010? And it does add up in terms of space, especially when you add indexing.

We find that the most utility is trending for the last quarter and then keeping the snapshot as of the last day of a month or the week end (this depends on whether a business measures itself weekly or monthly). Thus, you keep every day for up to three or four months and then only keep the snapshot as of the period you measure. This balances storage v. utility.