Is a fact table always the largest table?

It is often large because it contains many observations, but size does not define its role.

Can a fact table change after loading?

Yes. Correction rules and accumulating-snapshot designs can involve updates; the policy should match the business meaning.

Is a dimension table just another fact table?

Their roles differ. Dimensions primarily describe context, while facts record observations or relationships at a declared grain.

AI Glossary / Data engineering and architecture / Beginner

Fact table

Definition

A fact table is a table in a dimensional data model that records measurable business events, observations or relationships at a defined level of detail. It typically connects those records to descriptive dimension tables and may contain numeric measures such as quantity or revenue.

What it means

The key design choice is the grain: what one row represents. A sales fact table might contain one row per order line, while an inventory snapshot table might contain one row per product, location and day. These are different observations even if both refer to products.

Dimensions supply descriptive context, such as customer, product, location and date. Keys in the fact table connect an observation to that context. Measures express what happened or what was observed at the declared grain.

The name does not guarantee that the data is correct, and numeric columns are not mandatory. A factless fact table can record relationships or occurrences, such as a student's attendance at a session, without storing a separate numeric measurement. Its rows can be counted meaningfully.

How it works

Design begins with a business process and a clear statement of grain. The team then selects dimensions and measures that describe that same observation. Adding a monthly target to every individual sale, for example, would repeat the target and encourage incorrect totals.

Common designs include transaction facts for events, periodic snapshots for observations at regular intervals, and accumulating snapshots for processes whose milestones are updated as work progresses. The design determines what a row means and how it changes.

Measures also have aggregation rules. Sales quantities can often be summed across relevant dimensions. Inventory balances can be summed across compatible products or locations at one time, but adding successive daily balances usually does not answer an inventory-level question. Ratios need their component quantities preserved so that totals can be calculated appropriately.

Loading procedures must handle keys, duplicates, corrections and missing context. A valid foreign key does not prove that a record uses the right historical customer or product description. Testing should compare business totals as well as database structure.

Three practical examples

A retailer records one sales line per product on a receipt, linked to product, store and date dimensions.
A warehouse records each product's closing stock at each location every day, supporting comparisons of inventory over time.
A training provider records one learner's attendance at one session in a factless table and counts attended sessions.

Think of it this way

Think of a well-designed observation sheet. Each line records the same kind of observation, while reference lists explain the codes for people, places and items.

The analogy is limited because a dimensional model also needs explicit keys, historical rules and aggregation behavior. Merely placing numbers in rows does not create a reliable fact table.

Worked example

Calculating an average from the correct components

An invented sales fact table contains two order-line observations. The first sells two units for a total of twenty currency units. The second sells eight units for a total of sixty-four currency units.

The recorded quantities total 2 + 8 = 10. Revenue totals 20 + 64 = 84. Revenue per unit across these sales is therefore 84 ÷ 10 = 8.4.

The line-level unit prices are ten and eight. Their simple average is nine, which gives each line equal weight even though their quantities differ. That calculation answers a different question and does not represent revenue per unit sold.

Keeping quantity and revenue at the same grain makes the intended calculation possible. The illustration assumes comparable units and currency, with any relevant discounts already included. A model that mixes currencies or units without conversion requires additional handling before the figures can be combined.

Illustrative case study

A fictional equipment distributor develops a dashboard for orders and deliveries. Its first design joins every order line directly to every associated shipment record. A line shipped in three parts then appears three times, inflating ordered revenue.

The team identifies the grains separately: an order-line observation and a shipment-line observation. It designs reporting that aggregates the appropriate measures before comparing the processes, using shared dimensions where applicable.

An AI assistant is allowed to describe the resulting dashboard, but the metric definitions remain explicit. The assistant cannot repair a multiplied total simply by explaining it fluently. The case shows why grain and relationships must be settled before business users rely on a fact table.

Common misconceptions and mistakes

Every numeric field can be summed. Ratios, prices and balances have different aggregation rules.
A fact table must contain numeric measures. Factless tables record meaningful occurrences or relationships.
More detail can be added without changing meaning. A new repeating relationship may alter the effective grain.
The word fact guarantees truth. Loading errors and incorrect definitions can still produce misleading records.

Business use

Fact tables make recurring business questions easier to answer when observations have consistent meanings. They support reporting, planning and the preparation of some analytical datasets.

Require a plain-language grain statement for each table and a definition for each measure. Review how missing observations differ from zero values, when corrections appear and which time periods are represented. A missing daily snapshot should not silently become a zero stock balance.

For AI and machine-learning work, assess whether the records and historical dimensions reflect what was known at the relevant decision time. A useful reporting table can still introduce future information into a training dataset if historical availability is ignored.

Check your understanding

Two sales lines contain quantities two and eight, with revenue twenty and sixty-four. Is averaging their unit prices the correct way to calculate revenue per unit sold?

Answer: No. Divide total revenue, eighty-four, by total quantity, ten. The result is 8.4 under the stated unit and currency assumptions.

Related terms

Dimensional modelling
grain
dimension table
star schema
additive measure
factless fact table
snapshot

Learn next

Read grain and dimensional modeling. Describe one row of an existing report in a single sentence, then identify which measures can be summed and which require another aggregation rule.

Sources

Ralph Kimball, Fact Tables: https://www.kimballgroup.com/2008/11/fact-tables/. Primary practitioner explanation of grain and fact-table designs.

Kimball Group, Fact Tables and Dimension Tables: https://www.kimballgroup.com/2003/01/fact-tables-and-dimension-tables/. Primary guidance on observations, dimensions and measures.

Bob Becker, Design Tip #133 Factless Fact Tables for Simplification: https://www.kimballgroup.com/2011/04/design-tip-133-factless-fact-tables-for-simplification/. Primary explanation of factless designs. The sales arithmetic and distributor case are original illustrations.

Previous: Fact Next: Factoid questions