TL;DR
A fact table is the central table in a dimensional model — containing the rows that record business events (sales, logins, page views) along with their measures (quantities, dollar amounts, counts). Fact tables are typically tall (millions to billions of rows) and narrow (a small number of foreign keys to dimensions plus a small number of measure columns). Every fact table has a defined grain — the smallest unit of measurement that one row represents.
What is a fact table?
A fact table is the table at the centre of a star schema or snowflake schema that contains the events being measured. It records business activity at a defined grain (one row per sale, one row per page view, one row per minute of usage) and joins out to dimension tables for context.
Fact tables tend to be tall (millions to billions of rows for active businesses) and narrow (typically a handful of foreign keys to dimensions plus a handful of measure columns). The structural simplicity is intentional: it makes aggregation queries fast and predictable.
Anatomy of a fact table
Sales Fact (grain: one row per line item per order): - sale_line_id (PK, surrogate) - order_id (degenerate dim, references no dim table) - product_id (FK -> Product Dim) - customer_id (FK -> Customer Dim) - store_id (FK -> Store Dim) - date_id (FK -> Date Dim) - quantity (additive measure) - unit_price (semi-additive measure) - discount_amount (additive measure) - extended_amount (additive measure) - cost_of_goods (additive measure)
Types of fact tables
- Transaction facts: one row per business event — the most common type. Sales, logins, support tickets, page views.
- Periodic snapshot facts: one row per entity per period — captures state at regular intervals. Inventory levels at end of day, account balances at end of month.
- Accumulating snapshot facts: one row per business process instance — updated as the process progresses. Order lifecycle (placed, paid, shipped, delivered, returned) with date columns updated as state changes.
- Factless facts: facts with no measures — capturing the existence of an event. Customer-product association events, registration events.
Measure types
Measures within a fact table fall into three additivity categories — important for aggregation behaviour:
| Measure type | Example | Aggregation rule |
|---|---|---|
| Additive | Quantity, dollars sold, units shipped | Sum across any dimensions |
| Semi-additive | Account balance, inventory level | Sum across some dimensions; average or last across time |
| Non-additive | Unit price, percentages, ratios | Don't sum; weight by additive measure |
Common pitfalls
- 1. Mixing grains. A fact table with some per-order rows and some per-customer-month summary rows produces undefined aggregation behaviour. Pick one grain per fact table; create separate facts if multiple grains are needed.
- 2. Storing dimensional attributes in fact tables. Customer name in the sales fact instead of the customer dim duplicates data and breaks slowly-changing-dimension handling. Keep attributes in dimensions.
- 3. Over-aggregating. Storing pre-aggregated rows in the fact table (one row per customer-month rather than per order) loses detail and limits future analysis. Store at the lowest reasonable grain; aggregate in queries.
Related concepts
Dimension tables are the context-side complement. Star schema and snowflake schema are the structural patterns containing fact tables. Dimensional modeling is the broader discipline.
At a glance
- Category
- Business Intelligence
- Related
- 5 terms
Frequently asked questions
What's the grain of a fact table?
The smallest unit of measurement that one row represents. Common examples: one row per order line, one row per page view, one row per minute of session activity. Grain must be defined explicitly and applied consistently — mixed grains produce undefined aggregation.
Should fact tables include dimensional attributes?
No — keep attributes in dimension tables. Storing customer name or product category in the fact table duplicates data and breaks change-handling. The exception is 'degenerate dimensions' (order_id, invoice_number) — single-attribute IDs with no separate dim table.
How big should fact tables be?
As big as needed at the right grain. Modern columnar warehouses handle billion-row fact tables comfortably. Don't pre-aggregate to keep tables small; store at low grain and aggregate in queries. Pre-aggregation is a last-resort performance optimisation.
Sources
- The Data Warehouse Toolkit, Kimball (3rd ed)
- Kimball Group dimensional-modeling resources
- dbt Labs analytics engineering documentation
Fairview is an operating intelligence platform that reads fact-and-dimension data directly — preserving the grain and additivity rules analysts have set rather than re-aggregating into Fairview-specific structures. Start your free trial →
Siddharth Gangal is the founder of Fairview. He built the grain-aware ingestion layer after watching operating dashboards report sums of semi-additive measures (account balance summed across dates) — producing nonsense numbers that analysts had warned the dashboard tool about, but couldn't enforce because the tool didn't respect the underlying additivity rules.
See it in Fairview
Track Fact Table automatically.
14-day free trial. No credit card. First data source connected in 5 minutes.