What are the types of fact tables?

A fact table in a data warehouseData warehousing combines data and information from multiple sources into a comprehensive database. stores the measurable and numeric data associated with business events or transactions. It represents the quantitative facts or metrics that businesses want to analyze and understand. A fact table is a central table in a data schema, commonly found in starIt consists of a central fact table surrounded by multiple dimension tables. or snowflakeIt further normalizes dimension tables, creating a hierarchical structure of interconnected dimensions. schemas, surrounded by dimension tables.

Types of fact tables

Following are the three types of fact tables:

1. Transaction fact table

2. Snapshot fact table

3. Accumulating snapshot fact table

Transaction fact table

A transaction fact table captures detailed information about individual business transactions or events. It records every occurrence at the most granular levelLevel of detail at which the measurements or metrics are captured and stored., providing a comprehensive view of operational data.

Example

Consider a retail business with a transaction fact table capturing sales data. The table may include the following columns:

This transaction fact table allows analysis at the transaction level, enabling insights such as top-selling products, customer buying patterns, and revenue per transaction.

Snapshot fact table

A periodic snapshot fact table captures aggregated metrics at specific intervals or time periods, summarizing business activities. It provides a concise overview of performance and trends over time.

Example

Consider a manufacturing company that maintains a periodic snapshot fact table to track monthly production data:

This snapshot fact table enables analysis of production trends, comparison of defect rates, and identification of potential areas for improvement.

Accumulating snapshot fact table

An accumulating snapshot fact table tracks the incremental progress of a specific business process or workflow. It captures significant milestones and associated measures throughout the process.

Example

Consider an e-commerce company with an accumulating snapshot fact table monitoring the order fulfillment process:

In the above example, the table tracks the key stages of the order fulfillment process. Each row represents a unique order. The accumulating snapshot fact table allows easy analysis of the order fulfillment process by capturing the dates at each stage. It enables organizations to measure the time taken for each step, monitor the status of orders in real-time, identify bottlenecks, and optimize the overall order fulfillment cycle.

Conclusion

Each type serves a specific purpose in capturing and analyzing different aspects of business data. Organizations can effectively analyze and understand their business data, make informed decisions, and gain valuable insights into various operations by utilizing different types of fact tables.