Fact table

A fact table is the central table in a dimensional model that stores numeric measures (like revenue or quantity) alongside foreign keys linking to descriptive dimension tables.

Overview

A fact table records the measurable events of a business process — a sale, a page view, a shipment — at a defined grain (one row per order line, one row per session, etc.). Each row typically consists of two kinds of columns: foreign keys pointing to dimension tables (customer_key, product_key, date_key) and measures, the additive numeric facts you aggregate (quantity, revenue, duration_seconds).

Fact tables are usually the largest tables in a warehouse by row count, often millions to billions of rows, while dimension tables stay comparatively small. Kimball distinguishes a few fact table types: transaction fact tables (one row per event), periodic snapshot fact tables (one row per entity per time period, e.g., daily account balance), and accumulating snapshot fact tables (one row per process instance, updated as it moves through stages, e.g., an order from placed to shipped to delivered).

Related terms

Dimension table →

A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.

Fact constellation schema →

A fact constellation schema (also called a galaxy schema) is a dimensional model with multiple fact tables that share one or more common dimension tables.

Star schema →

A star schema is a dimensional data warehouse design where a central fact table of numeric measures connects directly to denormalized dimension tables, forming a shape like a star.

Conformed dimension →

A conformed dimension is a dimension table — like a shared date or customer dimension — that is defined identically and reused across multiple fact tables or data marts, so metrics stay comparable across the warehouse.

Degenerate dimension →

A degenerate dimension is a dimension attribute — like an invoice or order number — that's stored directly as a column in the fact table, with no corresponding dimension table, because it has no other descriptive attributes.

Dimensional modeling →

Dimensional modeling is a data warehouse design methodology, developed by Ralph Kimball, that organizes data into fact tables (measures) and dimension tables (descriptive context) optimized for business-user querying.

FAQS

A fact table stores numeric measures and foreign keys for a business event, and is usually large and narrow. A dimension table stores descriptive attributes for filtering and grouping, and is usually smaller and wider.

Grain is the level of detail one row represents — for example, one row per order line item versus one row per order. Declaring the grain explicitly before adding columns is a core step in dimensional modeling.