Transaction fact table, periodic snapshot fact table, and cumulative snapshot fact table

In the data warehouse field, there is a concept called transaction fact table, which is generally translated as "transaction fact table" in Chinese ". Transaction fact tables are one of the three basic types of fact tables in the data warehouse for dimensional modeling. The other two types are periodic snapshot fact tables and cumulative snapshot fact tables.

Transaction fact tables use the same consistency dimension as cyclic snapshot fact tables and cumulative snapshot fact tables, but they differ greatly in describing Business facts.

The fact at the transaction level recorded in the transaction fact table stores the most atomic data, also known as the "atomic fact table ". Data in the transaction fact table is generated after a transaction event. The data granularity is usually one record for each transaction. Once a transaction is committed, the fact table data is inserted, and the data is not changed. The update method is incremental update.

The date dimension of the transaction fact table records the date when the transaction occurred, and the fact it records is the content of the transaction activity. You can analyze the transaction behavior in detail through the transaction fact table.

The transaction fact table can also be used to create a clustered fact table to provide users with high-performance analysis.

2) in the data warehouse field, there is a concept called periodic snapshot fact table. Chinese characters are generally translated as "Periodic snapshot fact tables ".
Periodic snapshot fact tables record facts at regular and foreseeable intervals, such as daily, monthly, and annual intervals. Typical examples include sale day snapshot table and inventory day snapshot table.

The granularity of a periodic snapshot fact table is a record in each time period. It is generally coarse-grained than that of a transaction fact table and is an aggregation table created on the transaction fact table. The number of dimensions of the periodic snapshot fact table is smaller than that of the transaction fact table, but the number of records is more than that of the transaction fact table.

The date dimension of the periodic snapshot fact table is usually the end day of the record period, and the fact recorded is some aggregated fact values in this period. The data in a fact table cannot be changed once inserted. The update method is incremental update.

3) There is an accumulating snapshot fact table in the data warehouse field, which is generally translated as "cumulative snapshot fact table" in Chinese ".
The cumulative snapshot fact table and the periodic snapshot fact table share some similarities. They store snapshot information of transaction data. However, they are also quite different. Periodic snapshot fact table records determine the periodic data, while the cumulative snapshot fact table records uncertain periodic data.

A cumulative snapshot fact table fully covers the time span of a transaction or product lifecycle. It usually has multiple date fields to record key time points throughout the lifecycle. In addition, it has an additional Date Field to indicate the last update date. Because many dates in fact tables are unknown when they are loaded for the first time, you must use the proxy keyword to process undefined dates, it can be updated to supplement the date information that will be known later.
For example,
Order Date
Scheduled delivery date
Actual shipping date
Actual delivery date
Quantity
Amount
Freight

Features	Transaction fact	Cyclic snapshot fact	Snapshot accumulation fact
Time/Period	Time	Period	Multiple time points with a short time span
Granularity	Each row represents a transaction event	Each line represents a time period	Each line represents a business cycle
Fact table Loading	New	New	Add and modify
Fact table update	Not updated	Not updated	Update when new events are generated
Time Dimension	Business date	End of period	Completion date of multiple business processes
Fact	Transaction activity	Performance within a time period	Limits the performance of multiple business stages

Transaction fact table, periodic snapshot fact table, and cumulative snapshot fact table