A fact table is the central table in a dimensional model that stores the numeric measurements of business events, such as sales amounts, quantities or durations, together with foreign keys to dimension tables that describe the context of each event. In data modeling for analytical databases, it sits at the center of a star schema or snowflake schema and usually holds by far the most rows.
Each row of a fact table represents one event or one snapshot at a precisely defined level of detail, called the grain. The design of a fact table decides which questions a data warehouse can answer, how fast it answers them and how reliably totals add up across reports.
How a fact table is structured
A fact table consists of two kinds of columns: keys that point to dimensions and measures that hold the numbers being analyzed. A retail sales fact table, for example, contains date, store, product and customer keys plus measures such as quantity sold, net amount and discount amount.
The keys are usually surrogate keys, compact integers generated in the warehouse rather than the identifiers of source systems. They keep joins fast, isolate the model from source changes and allow a dimension to track history. Many fact tables also carry degenerate dimensions, such as an invoice number, which are identifiers with no dimension table of their own.
The table below lists the typical column roles and what each one contributes to analysis.
| Column role | Example | Purpose |
|---|---|---|
| Dimension foreign key | date_key, product_key | Links the event to its descriptive context |
| Additive measure | Net sales amount | Can be summed across every dimension |
| Semi-additive measure | Account balance | Can be summed across some dimensions, not time |
| Non-additive measure | Unit price, margin percentage | Must be recomputed from components |
| Degenerate dimension | Order number | Groups lines of the same transaction |
Declaring the grain
The grain is the exact meaning of one row, and declaring it is the first and most important design decision for any fact table. Examples are “one row per order line”, “one row per account per day” or “one row per page view”.
A clear grain prevents the most damaging modeling error: mixing grains in one table. If order-level shipping costs are stored on every order line, a simple sum overstates shipping by the number of lines. The safer choice is to model at the atomic grain, the lowest level of detail that source systems provide, because atomic facts can always be aggregated upward while summaries can never be broken back down.
Every measure and every dimension key must be true to the declared grain. A measure that belongs to a coarser level, such as an order-level discount, is either allocated down to the lines with a documented rule or stored in a separate fact table at its own grain.
Types of fact tables
Fact tables fall into three classic types, distinguished by what a row represents in time. Choosing the right type depends on whether the business process is a stream of events, a state measured at intervals or a workflow with milestones.
| Type | One row represents | Typical example | Row behavior |
|---|---|---|---|
| Transaction fact table | One event at a point in time | Sales line, payment, click | Inserted once, rarely updated |
| Periodic snapshot | State at the end of each period | Daily inventory, monthly balance | One row per entity per period |
| Accumulating snapshot | One process instance with milestones | Order from placement to delivery | Updated as milestones occur |
| Factless fact table | An event or coverage with no measure | Student attendance, promotion coverage | Counted rather than summed |
Periodic snapshots are where semi-additive measures appear: summing daily inventory levels across 30 days produces a meaningless number, so reports use the closing value or an average over time instead. Accumulating snapshots carry several date keys, one per milestone, and allow lag calculations such as days from order to shipment.
Fact tables compared with dimension tables
A fact table records what happened and how much, while a dimension table records who, what, where and when in descriptive terms. The two are designed with opposite priorities, which the following comparison makes explicit.
| Property | Fact table | Dimension table |
|---|---|---|
| Content | Numeric measures and keys | Descriptive text attributes |
| Row count | Millions to billions | Hundreds to a few million |
| Shape | Narrow and very deep | Wide and relatively shallow |
| Growth | Grows with every business event | Grows slowly as entities appear |
| Typical query role | Aggregated with SUM, COUNT, AVG | Used to filter and group |
In a star schema, each dimension joins directly to the fact table. In a snowflake schema, dimensions are further normalized into sub-tables, but the fact table itself stays the same. Compared with a fully normalized operational model, the dimensional design is a deliberate form of denormalization that favors simple, fast analytical queries.
Design practices and common mistakes
Good fact table design keeps the table narrow, consistent and conformed. Dimensions shared across several fact tables, called conformed dimensions, allow results from sales, returns and inventory to be compared on the same product and calendar attributes.
Physical design matters at scale. Large fact tables are usually partitioned by date, stored in columnar formats and loaded incrementally, so that queries scanning one month do not read years of history.
| Mistake | Symptom | Fix |
|---|---|---|
| Mixed grain | Totals inflated or double counted | Split into separate fact tables |
| Stored ratios | Averages of percentages are wrong | Store numerator and denominator |
| Text in the fact table | Wide, slow table | Move attributes to dimensions |
| Null foreign keys | Rows disappear in inner joins | Use an unknown member row |
| Summed snapshot balances | Inflated balances over time | Aggregate with closing value or average |
A frequent source of confusion is the many-to-many relationship between facts and dimensions, such as one bank account with several holders. It is usually resolved with a bridge table and weighting factors, never by duplicating fact rows.
Explained without jargon
Think of a shop receipt. The receipt lists what was bought, how many and for how much. A fact table is a giant, organized pile of such receipt lines, one line for each thing that happened.
The details about each product, store or customer live in separate lists, so the pile of receipts stays compact. When someone asks how much was sold in one city last March, the system adds up the right receipt lines and looks up the city and the month in those lists. The fact table holds the numbers; the other tables explain them.
A typical use case
A grocery chain with 120 stores builds a sales data warehouse. The team declares the grain as one row per product per receipt line, with keys for date, store, product, customer loyalty account and promotion, plus quantity, gross amount and discount amount.
The table receives about 3 million rows per day and is partitioned by month. A separate daily inventory snapshot fact table stores stock on hand per product per store, and both fact tables share the same conformed product and date dimensions.
Category managers can now compare promotional sales against stock levels in one report. Because quantities and amounts are stored at the atomic grain, finance can reconcile warehouse totals with the accounting system to the cent, and new questions, such as sales by hour of day, need no redesign.