🎯Fact

NEW

Also known as: Measure Table

A table capturing business process measurements at a declared grain, linked to dimensions through foreign keys for analysis.

DESCRIPTION

Fact tables store numeric measures such as sales amount or counts, referencing dimensions for context. The grain defines the level of detail (e.g., order line, daily snapshot). Additive measures sum across all dimensions; semi-additive add across some; non-additive require ratios or other techniques. Surrogate keys and constraints ensure integrity. Clear grain and measure definitions underpin accurate analytics and aggregations.

ALTERNATIVE DEFINITIONS

Wikipedia

In data warehousing, a fact table is a table that contains the measurements, metrics, or facts of a business process.

Defines facts as measurements of a process.

Source

Oracle

A fact table stores the numeric measures of an event or transaction and foreign keys to related dimensions in a star schema.

Links measures and dimension foreign keys within a star schema.

Source

RELATED TERMS

Star Schema Data Granularity Measure Additive Measure Semi-Additive Non-Additive Surrogate Key Aggregation OLAP

COMPETING TERMS

Dimension

RELATIONS

• Narrower: Actuals

KNOWLEDGE AREAS

Data Warehousing & BI Data Modeling

TARGET AUDIENCE

BI Specialist Analytics Engineer Data Modeler Data Engineer Data Analyst

EXTERNAL SOURCES

• Wikipedia - Fact table
• Kimball Group - Grain definition guidance
• Microsoft - Star schema design with facts
• Google Cloud - BigQuery performance patterns

TOP QUESTIONS

How do I choose the grain of a fact?

Declare the business event’s atomic level (e.g., order line) and enforce it consistently across all measures and keys.

Category: Requirements

What is the difference between additive and semi-additive facts?

Additive facts sum across all dimensions; semi-additive sum across some (e.g., time excluded); non-additive require ratios or distinct logic.

Category: Misunderstanding

When should I use a snapshot fact table?

Use snapshots for periodic states (e.g., daily balances), complementing transaction or accumulating facts.

Category: Example

What are common fact table mistakes?

Mixing grains, ambiguous measures, and omitting required dimension references cause incorrect aggregations.

Category: Mistakes

How do null foreign keys in facts affect analysis?

They weaken referential integrity and can skew aggregates; use unknown or default dimension rows instead.

Category: Risks

What is a degenerate dimension?

A business identifier (e.g., order number) stored in the fact without a separate dimension table.

Category: Example

How to handle late-arriving facts or dimensions?

Use placeholder keys and backfill processes; ensure audit trails for corrections.

Category: Management

How do I validate fact correctness?

Reconcile against source systems, test aggregations, and verify referential integrity with dimensions.

Category: Testing

What performance practices apply to fact tables?

Partitioning, clustering, and columnar storage improve scan and aggregation performance in modern warehouses.

Category: Requirements