What is a Fact Table?

How it works

A fact table’s columns fall into four groups:

• Dimension foreign keys, one per dimension that is true at the grain, never NULL (unknown values point to an Unknown member).
• Degenerate dimensions: identifiers such as order_id that have no attributes of their own but are useful for grouping and tracing.
• Measures: the numbers, ideally additive.
• Audit columns such as load time and source system.

The primary key is normally the grain itself, for example (order_id, line_number).

Every measure has an additivity. Additive measures (quantity, amount) can be summed across every dimension. Semi-additive measures (account balance, stock on hand) can be summed across customers or products at one point in time but not across days. Non-additive measures (unit price, conversion rate) must be recomputed from additive parts.

There are three classic types. A transaction fact table has one row per event. A periodic snapshot has one row per entity per period, such as each account’s balance at the end of each day. An accumulating snapshot has one row per process instance, such as an order, updated as it passes milestones (placed, shipped, delivered). A factless fact table records events or coverage with no measures at all, such as which products were on promotion.

Example

Balances are semi-additive: summing them across days gives a meaningless number. The month-end figure uses the closing balance per account instead.

CREATE TABLE fact_wallet_balance (account_id int, balance_date date, balance numeric(10,2));
INSERT INTO fact_wallet_balance VALUES
  (1, '2026-03-30', 100.00), (1, '2026-03-31', 120.00),
  (2, '2026-03-30', 50.00),  (2, '2026-03-31', 40.00);

SELECT 'wrong: summed over days' AS method, sum(balance) AS total
FROM fact_wallet_balance
UNION ALL
SELECT 'right: closing balance', sum(balance)
FROM fact_wallet_balance
WHERE balance_date = DATE '2026-03-31';

         method          | total
-------------------------+--------
 wrong: summed over days | 310.00
 right: closing balance  | 160.00

Common mistakes

• Summing semi-additive measures over time, which BI tools do by default.
• Averaging a ratio instead of recomputing it from its additive parts.
• Storing only derived values such as net_amount, so new calculations from quantity and price become impossible.
• Putting descriptive text, such as product names or status labels, in the fact table instead of a dimension.