How to Design a Perfect Fact Table — Deep Dive into Fact Types & Fact Table Types

Introduction

Fact tables are where the measurements of business events live, and these measurements are almost always numeric.

Each row corresponds to one-or-more measurements at a specific granularity, or grain. In this monthly sales fact table, one row represents the sale of a specific product to a specific customer at a specific store during a specific month.

This means if a customer buys the same product multiple times from the same store within a month, all those individual transactions are aggregated and stored as a single row in this fact table.

Matching Measures to the Grain

Only measurements that match this grain should be included in the table.

For example, you cannot include total monthly sales for stores in this fact table, because the grain of that measure is just month and store, whereas the grain of this fact table is month, store, product, and customer. If you really need to track monthly sales across stores, the best practice is to create a separate fact table, such as FactMonthlyStoreSales, that matches the month + store grain.

In this new fact table, you can include other measures that fit the same grain, like total profit per store per month, total discounts per store per month, and more.

This keeps each fact table consistent, and makes analysis much more reliable.

Alternatively, if you just need a quick summary without creating a new table, you can run a GROUP BY query on the Fact-Monthly-Sales table using Month-ID and Store-ID as group-by keys to calculate aggregated measures on the fly.

However, keep in mind that this type of aggregation does not work for all measures — this is exactly where the different types of facts come into play.

Components of a Fact Table

Inside a fact table, you’ll usually find:

• Numeric measures
• Foreign keys for each associated dimension
• Additional columns such as timestamp columns added for auditing and tracking purposes

The process that loads fact tables often involves aggregations, and these rows can be further aggregated in subsequent processes or BI tools.

Types of Facts ( At column Level)

The numeric measures in a fact table can behave differently depending on how you aggregate them.

1. Fully Additive Facts

Some facts are fully additive, meaning they can be summed across any dimension. Total sales or quantity sold are classic examples, and you can slice and dice these numbers in any direction without any problem.

For example, if you want the total sales for each store, or for each month, or even for each product, you can get it with a simple SQL aggregation.

2. Semi-Additive Facts

Some facts are semi-additive. These can be summed across some dimensions, but not all. For example:

• Inventory-on-hand: You can sum it across stores, or products, or even both stores and products, but not across date or time dimensions. Adding this month’s inventory to last month’s inventory just doesn’t make sense.
• Daily customer account balance: You can sum balances across customers for the total balance on that day, but you cannot sum across days.

3. Non-Additive Facts

These cannot be summed at all, like ratios or percentages. In these cases, a good practice is to store the components that make up the ratio or percentage in the fact table, and calculate the final percentage or ratio later, either in BI tools or the consumption layer.

Types of Fact Tables (At overall Table Level)

Fact tables themselves also come in different types, depending on how the business events are represented and recorded.

Transaction Fact Tables

These are the most detailed, capturing every individual business event. Each row represents a single event — for example, a customer buying a product, a shipment being sent, a payment being made, or a service interaction taking place.

Data is stored at the lowest level of granularity, allowing users to aggregate or drill down as needed, which provides maximum flexibility for analysis. Because they store every event, the data volume can grow very large, and queries that scan the full table may be slower than those on pre-aggregated tables.

Aggregate Fact Tables

These tables store pre-computed summaries of transaction data, where each row represents aggregated values over one-or-more dimensions, such as total sales for a product in a month rather than individual business transactions.

By summarizing the data, aggregate tables make queries faster and reduce the need to scan all transactions.

Periodic Snapshot Fact Tables

These summarize activity over a fixed time interval, such as daily, weekly, or monthly totals. Each row represents a combination of a time period and business dimensions.

• Useful for tracking balances, inventories, or other counts and totals over time
• Include rows with zero or null values for periods with no activity to provide consistent time-series reporting

Accumulating Snapshot Fact Tables

Used to track processes that move through predictable stages with a well-defined beginning and end, such as order fulfillment or claims processing workflows. Each row represents a single process instance, and is updated over time as milestones are completed.

Factless Fact Tables

As the name implies, these do not contain numeric measures but capture the occurrence of events, such as a student attending a class or an employee working on a given day.

They are also useful for analyzing events that did not happen. For example, by comparing a list of all students with the factless fact table of those who actually attended, you can easily identify the students who didn’t show up.

Conclusion

Fact tables are essential for solid data modeling. They are the backbone that makes analytics and insights possible.

You can also watch my YouTube video here:

https://youtu.be/kXV2O_MDEHc