Types of Fact Tables

Concepts: dmFactTables

There are three fundamental types of fact tables. Each models a different kind of business process. Choosing the wrong type means your table cannot answer the questions it was built for. Transaction Facts One row per discrete business event: a sale, a click, a payment. Transaction facts are insert-only. Rows are never updated. They are the most common fact type and the right choice for any event-level data. Transaction facts cannot answer 'what is the current state?' questions. They record events, not states. 'What is the current inventory level?' requires summing all inventory transactions from the beginning of time. For state queries, use a periodic snapshot. Periodic Snapshots One row per entity per time period: daily account balance, monthly inventory level, quarterly headcount. Period