Reference for my Pluralsight course “Star Schema Foundations”
3 Types of Fact Tables
Transaction Fact Tables
Transaction Fact Tables are the most expressive and dimensional fact tables in your star schema. They, hopefully, contain the lowest level of detail, storing data at the atomic level. That is the level at which the event we are tracking actually occurs.
Transaction Fact Tables are great for storing event-level data. A good example would be sales order details or clicks on a web page. They track each event at the lowest grain and link to all the related dimensions.
Periodic Snapshot Fact Tables
Periodic Snapshot Fact Tables, or just Snapshot Fact Tables, contain a summary of measurements for a given time period. These types of fact tables can contain many measures since most processes can be aggregated in similar time dimensions.
Some examples include daily snapshots of stock positions and monthly bank statements of account balances.
Accumulating Snapshot Fact Tables
Accumulating Snapshot Fact Tables contain key milestones through a given business process. They allow of analyzing the steps in the process and time between them.
A good example for an Accumulating Snapshot Fact Table is a user sign up process on a website. In this example, we would store one row for every user and mark the key success points in the signup process.