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).
Example
Copy code
CREATE TABLE fact_orders (
order_key BIGINT PRIMARY KEY,
customer_key INTEGER,
product_key INTEGER,
date_key INTEGER,
quantity INTEGER,
unit_price DECIMAL(10,2),
revenue DECIMAL(12,2)
);
SELECT date_key, SUM(revenue) AS daily_revenue
FROM fact_orders
GROUP BY ALL
ORDER BY ALL;
Not every measure is fully additive. Revenue sums cleanly across any dimension; an inventory balance is only meaningful summed across products, not across time (semi-additive); a margin percentage can't be summed at all (non-additive) — you'd sum the numerator and denominator and divide afterward.
DuckDB angle
DuckDB's columnar storage is well suited to fact tables: aggregating a single revenue column across hundreds of millions of rows only reads that column, not the whole row, and vectorized execution keeps GROUP BY and SUM fast without indexes. Loading a fact table straight from partitioned Parquet with read_parquet('s3://bucket/orders/*.parquet') into a DuckDB or MotherDuck table is a common, low-friction pattern.
Related terms
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.
