A fact table is the central table in a data warehouse star schema, containing measurable business events (orders, page views, payments, sensor readings) with foreign keys to dimension tables and numerical measures used in aggregations like sum, count, average, or min/max.
Why Fact Table Matters
The fact table is where the raw measurable events of your business live in the warehouse. Every dashboard’s key metrics — revenue, active users, churn, conversion — are aggregations of fact-table rows. Modelling fact tables correctly is the foundation of any analytics deployment.
Fact tables sit at the centre of a star schema, surrounded by dimension tables that provide context. Together they form the dominant data modelling pattern in 2026 analytics.
How Fact Table Works
A fact table typically has three column types:
- Foreign keys to dimensions: Each row links to dimension rows that describe the event —
customer_id,product_id,date_id,store_id. - Measures: Numerical values that get aggregated —
revenue,quantity,discount_amount,duration_seconds. - Degenerate dimensions: Single-value attributes that are descriptive but not worth a separate dimension table — order_number, transaction_id.
Three common fact table types:
- Transaction fact table: One row per business event (one row per order, one row per page view). Most common.
- Periodic snapshot fact table: One row per entity per period (daily inventory level for each SKU). Used when you need to track a state over time.
- Accumulating snapshot fact table: One row per process instance, with columns for each milestone (order placed, picked, shipped, delivered). Used for tracking long-running workflows.
Fact tables are typically the largest tables in the warehouse — billions of rows is common for any sizeable SaaS or e-commerce deployment.
Real-World Example
A SaaS company’s order data lives in fct_orders (a fact table). Each row represents one order with: order_id (degenerate dimension), foreign keys customer_id, product_id, date_id, promotion_id, and measures revenue, quantity, discount_amount, cost_of_goods. To compute “monthly revenue by product category,” analysts join fct_orders to dim_product (for category), aggregate by month, and SUM revenue.
Common Fact Table Tools and Platforms in 2026
2026 tools for building fact tables:
dbt
Industry-standard transformation tool for building star schemas (fact + dimension tables) in cloud warehouses.
Snowflake / BigQuery / Databricks SQL
Cloud warehouses where fact tables live. All support star schema patterns natively.
Apache Iceberg / Delta Lake
Open table formats for fact tables in lakehouse architectures with ACID transactions.
Looker / Cube / Holistics
Semantic layer tools that consume fact tables and expose governed metrics.
Analytify
Open-source GenBI platform that reads fact tables for SaaS embedded analytics.
Frequently Asked Questions About Fact Table
What is the difference between a fact table and a dimension table?
A fact table stores measurable events with numerical measures. A dimension table stores descriptive attributes about who, what, when, where. Facts are typically large; dimensions are typically small.
What is a transaction fact table?
The most common fact table type — one row per business event. Each order, page view, click, or payment is one row. Naturally append-only.
What is grain in a fact table?
Grain is the level of detail each row represents. “One row per order” is one grain; “one row per order line item” is a finer grain. Choosing the right grain is the most important fact-table design decision.
Can a fact table have no measures?
Yes — a “factless fact table” tracks events with no numerical values. Common for tracking attendance, occurrences, or coverage where the existence of the row is the fact.
How do you optimise fact tables for analytics?
Cluster or partition by the most common filter column (typically date). Use columnar storage formats (native to cloud warehouses). Pre-aggregate via dbt for high-frequency queries.
Should fact tables include denormalised dimension attributes?
Generally no — keep facts narrow and dimensions separate. Modern warehouses are fast enough to join at query time, and denormalising fact tables makes them harder to maintain when dimension attributes change.