• A transactional table is the most basic and fundamental.
• The grain associated with a transactional fact table is usually specified as “one row per line in a transaction”, e.g., every line on a receipt.
• Typically a transactional fact table holds data of the most detailed level, causing it to have a great number of dimensions associated with it.
• These fact tables represent an event that occurred at an instantaneous point in time.
• A row exists in the fact table for a given customer or product only if a transaction has occurred.
• A given customer or product is likely linked to multiple rows in the fact table because the customer or product is involved in more than one transaction.
• Transaction data often is structured quite easily into a dimensional framework.
• The lowest-level data is the most natural dimensional data, supporting analyses that cannot be done on summarized data.
• Unfortunately, even with transaction-level data, there is still a whole class of urgent business questions that are impractical to answer using only transaction detail.

Periodic snapshots

• The periodic snapshot, as the name implies, takes a “picture of the moment“, where the moment could be any defined period of time, e.g. a performance summary of a salesman over the previous month.
• A periodic snapshot table is dependent on the transactional table, as it needs the detailed data held in the transactional fact table in order to deliver the chosen performance output.
• This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. 
• Example: Daily balances fact can be summed up through the customers’ dimension but not through the time dimension.
• Periodic snapshots are needed to see the cumulative performance of the business at regular, predictable time intervals.
• Unlike the transaction fact table, where we load a row for each event occurrence, with the periodic snapshot, we take a picture of the activity at the end of a day, week, or month, then another picture at the end of the next period, and so on.
• Example: A performance summary of a salesman over the previous month.

Accumulating snapshots

• This type of fact table is used to show the activity of a process that has a well-defined beginning and end, e.g., the processing of an order.
• An order moves through specific steps until it is fully processed. As steps towards fulfilling the order are completed, the associated row in the fact table is updated.
• An accumulating snapshot table often has multiple date columns, each representing a milestone in the process.
• Therefore, it’s important to have an entry in the associated date dimension that represents an unknown date, as many of the milestone dates are unknown at the time of the creation of the row.
• This type of fact table is used to show the activity of a process that has a well-defined beginning and end. For example, the processing of an order.
• An order moves through specific steps until it is fully processed. As steps towards fulfilling the order are completed, the associated row in the fact table is updated.
• Accumulating snapshots almost always have multiple date stamps, representing the predictable major events or phases that take place during the course of a lifetime.
• Often there’s an additional date column that indicates when the snapshot row was last updated. Since many of these dates are not known when the fact row was first loaded, we must use surrogate date keys to handle undefined dates.
• In sharp contrast to the other fact table types, we revisit accumulating snapshot fact table rows to update them.
• Unlike the periodic snapshot, where we hang onto the prior snapshot, the accumulating snapshot merely reflects the accumulated status and metrics. Sometimes accumulating and periodic snapshots work in conjunction with one another.

Temporal snapshots

• By applying temporal database theory and modeling techniques the temporal snapshot fact table  allows to have the equivalent of daily snapshots without really having daily snapshots.
• It introduces the concept of time Intervals into a fact table, allowing to save a lot of space, optimizing performances while allowing the end user to have the logical equivalent of the “picture of the moment” they are interested in.

Centipede Fact Table

Centipede fact table is a normalized fact table. Modeller may decide to normalize the fact instead of snow flaking dimensions tables.

Conformed Fact Tables

They are measures re-used across multiple dimension models.

For example, KPI such as profit, revenue etc

Incident and Snapshot Facts

A fact table stores some kind of measurements and are captured against a specific time. Now it might so happen that the business might not able to capture all of its measures always for every point in time.

Then those unavailable measurements can be kept empty (Null) or can be filled up with the last available measurements. The first case is the example of incident fact and the second one is the example of snapshot fact.

Cumulative Fact

This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day

Fact-less Fact Tables

A fact table that does not contain any measure is a fact-less fact table. This table will only contain keys from different dimension tables. This is often used to resolve a many-to-many cardinality issue.