Transaction fact table, Cycle snapshot fact table, and cumulative snapshot fact table

In the field of data warehousing there is a concept called transaction fact table, the Chinese translation is generally "transaction fact table". The Transaction fact table is one of three basic types of fact tables in the Data Warehouse for dimension modeling, and the other two are the periodic snapshot fact table and the cumulative snapshot fact table.

The Transaction fact table uses the same consistency dimension as the periodic snapshot fact table and the cumulative snapshot fact table, but they are very different in describing the business facts.

Transaction fact table records the facts of the transaction level, which holds the most atomic data, also known as the Atomic fact table. The data in the Transaction fact table is generated after the transaction event occurs, and the granularity of the data is usually one record per transaction. Once a transaction is committed and the fact table data is inserted, the data is no longer changed and is updated incrementally.

The date dimension of the Transaction fact table records the date that the transaction occurred, and the fact that it records is the content of the transaction activity. The Transaction fact table allows the user to perform a special detailed analysis of the transaction behavior.

Through the Transaction fact table, you can also establish a clustered fact table to provide users with high-performance analysis.

2 in the field of data warehousing has a concept called periodic snapshot fact table, the Chinese general translation of "Periodic snapshot fact table."
The periodic snapshot fact table records facts at regular, predictable intervals, such as daily, monthly, yearly, and so on. Typical examples are the Sales day snapshot table, Inventory day snapshot table, and so on.

Periodic snapshots The granularity of the fact table is one record per time period, typically thicker than the granularity of the Transaction fact table, and a clustered table established on top of the transaction fact table. The periodic Snapshot fact table has fewer dimensions than the transaction fact table, but it records more facts than the transaction fact table.

The date dimension of the periodic snapshot fact table is usually the day of the expiration of the record period, and the fact is that some of the aggregated fact values are in this time period. The fact table's data cannot be changed once it is inserted and is updated incrementally.

3 in the field of data warehousing has a concept called accumulating snapshot fact table, the Chinese translation is generally "cumulative snapshot fact table."
The cumulative Snapshot fact table and the periodic snapshot fact table are similar in that they store snapshot information for transactional data. But there is also a big difference between the periodic snapshot fact table records the determined cycle data, and the cumulative snapshot fact table records the indeterminate period of data.

The cumulative Snapshot fact table represents a time span that completely overwrites the life cycle of a transaction or product, typically with multiple date fields that record key points in time throughout the lifecycle. Additionally, it will have an additional date field to indicate the date of the last update. Because many of the dates in the fact table are not known at first load, you must use the Proxy keyword to handle undefined dates, and such fact tables can be updated after the data is loaded to supplement the information that is subsequently known.
For example,
Order Date
Scheduled delivery date
Actual Ship Date
Actual delivery date
Number
Amount
Shipping

Features	Transaction facts	Cycle Snapshot fact	Cumulative Snapshot Fact
Time/Time	Time	Period	Time Multiple points with shorter spans
Granularity	Each line represents a transaction event	Each line represents a time period	Each line represents a business cycle
Fact table loading	Add	New Add	Add and modify
Fact table update	Not update	update	When new events are generated
Time dimension	the business date End of time	Multiple business process completion date
The fact	Trading activity	Performance in a time period	Qualify performance within multiple business phases