I am rereading The Data Warehouse Toolkit by Kimball and Ross and would like to share something interesting concerning fact tables.

According to the book, there are 3 main types of fact tables:

• Transaction
• Periodic snapshot
• Accumulating snapshot

A transaction fact table inserts a new record for every transaction that takes place. An obvious example would be a sales fact table: for every sale, a new record is inserted in a table.

A periodic snapshot fact table inserts a new record with the most recent state at regular intervals. A classic example is a daily inventory snapshot fact table: everyday new records are inserted with the amount of product that is present in the inventory that day.

An accumulating snapshot fact table updates existing records according to changes in status of the record. An example of this could be an order fulfillment fact table. Whenever an order is placed, a new record is inserted with the “OrderDate” column filled with the order date. The “ShippedDate” column is left empty, or filled with a default surrogate key like 99991231.

A lot of times, a single type of fact table does not suffice for the information needs from the business. Understanding the different types of fact tables could assist you in helping your stakeholders get the information they need.