---
title: "Fact table"
description: "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."
canonical: "https://motherduck.com/glossary/fact-table/"
related:
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "STORAGE_INFO views | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/storage_info/"
  - title: "Data types | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/data-types/"
---

# 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).

<glossary-callout guide="duckdb-cheatsheet-full" />

### Example

```sql
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.