Fact Table

A Fact Table is the central table in a data warehouse schema (primarily in star and snowflake schemas). It stores quantitative data (measures) about a business process and links to dimension tables through foreign keys. Each row represents a business event or measurement such as a sale, order, or shipment. This design helps achieve:

• Fast analytical queries
• Efficient joins
• Easy slicing, dicing, aggregation, and reporting

The following schema (diagram below) illustrates a typical Sales Fact Table (factSales) connected to four dimensions:

This fact table links to:

• dimCustomer → Who made the purchase
• dimProduct → What product was purchased
• dimTerritory → Where the purchase happened
• dimDate → When events occurred (order, ship, payment dates)

This design allows business questions such as:

• Total sales by product
• Customer-wise revenue by territory
• Units sold per month
• Shipment delay analysis using order and ship dates

Types of Fact Tables

There are several types of fact tables, each serving different purposes in a data warehouse:

1. Transaction Fact Table

• Stores data at the most granular level
• Example: each sale, each click, each order
• Most commonly used

2. Periodic Snapshot Fact Table

• Captures data at regular intervals (daily, monthly, weekly)
• Example: daily inventory levels, monthly account balances

3. Accumulating Snapshot Fact Table

• Tracks process workflows across stages
• Continuously updated
• Example: order lifecycle (order → pack → ship → deliver)

Structure of a Fact Table

1. Measures (Quantitative Data)

Contains numeric values used for analysis, such as:

• SalesAmount
• UnitsSold
• Revenue
• Cost

These are the "facts".

2. Granularity

Defines the level of detail stored. For examples:

• One row per transaction (fine-grained)
• One row per day per product (aggregated)

Choosing the right granularity is critical for performance and storage.

3. Foreign Keys

Fact tables use foreign keys to link to dimension tables. For example:

• CustomerKey → dimCustomer
• ProductKey → dimProduct
• DateKey → dimDate

These keys provide context for measures.

4. Surrogate Keys

Fact tables often use surrogate keys which are system-generated numeric identifiers- to maintain consistency, improve join performance, and integrate data from multiple source systems.

5. Degenerate Dimensions

Sometimes a fact table contains attributes that do not belong to any dimension table. These are called degenerate dimensions.
Examples include:

• InvoiceNumber
• OrderNumber

These values originate from operational systems and help uniquely identify transactions.

Types of Facts

There are three types of facts in a fact table, classified based on whether the stored measures can be aggregated across different dimensions.