📊Fact Table

NEW

Also known as: Measurement Table, Event Table

A central table that stores quantitative measurements at a defined grain and links to dimension tables via foreign keys.

DESCRIPTION

In dimensional modeling, a fact table captures events or snapshots as numeric measures with foreign keys to dimensions. The grain defines the level of detail, such as order line or daily inventory. Facts often include additive, semi-additive, or non-additive measures for analysis. Proper design emphasizes conformed dimensions and clear surrogate keys. Fact tables support efficient OLAP queries, aggregations, and BI dashboards. Variants include transaction, periodic snapshot, and accumulating snapshot fact tables.

ALTERNATIVE DEFINITIONS

Wikipedia

A fact table is a primary table in a star schema of a data warehouse.

Positions fact tables within star schema architecture.

Source

Microsoft Learn

Fact tables store quantitative data about a business process and connect to dimension tables to provide context.

Emphasizes process orientation and context via dimensions.

Source

Oracle

Fact tables contain measurements resulting from business events and are linked to dimensions that describe the context of those events.

Highlights events and contextual description via dimensions.

Source

RELATED TERMS

Star Schema Snowflake Schema Data Granularity OLAP Surrogate Key Data Mart Aggregation Slowly Changing Dimension

COMPETING TERMS

Dimension

RELATIONS

• Broader: Table

KNOWLEDGE AREAS

Data Warehousing & BI Data Modeling Data Management

TARGET AUDIENCE

Analytics Engineer Data Modeler BI Specialist Data Engineer Data Architect

EXTERNAL SOURCES

• Wikipedia - Fact table
• Microsoft - Star schema guidance for BI
• Snowflake - Guide on fact and dimension tables
• Kimball Group - Dimensional modeling design tips

TOP QUESTIONS

How do you define the grain of a Fact Table?

Specify the exact level of detail (e.g., order line). All rows must conform to this singular, consistent grain.

Category: Requirements

What types of Fact Tables exist?

Transaction, periodic snapshot, and accumulating snapshot, each suited to different process semantics.

Category: Concept

Why use surrogate keys in Fact Tables?

They stabilize joins to dimensions across source key changes and improve query performance and governance.

Category: Relevancy

How do you handle semi-additive measures in Fact Tables?

Aggregate along permissible dimensions and use snapshots or special rollups for non-additive dimensions like time.

Category: HowTo

What is a common mistake when designing a Fact Table?

Mixing multiple grains leads to ambiguous measures and broken aggregations.

Category: Mistakes

How do Fact Tables support BI performance?

Denormalized joins to dimensions and star schemas optimize scan patterns and filter pushdown for fast aggregations.

Category: Benefits

When should you create aggregate Fact Tables?

For heavy recurring queries, pre-aggregates can cut costs and latency, provided you manage rollups and lineage.

Category: Management

How do Slowly Changing Dimensions affect Fact Tables?

Type 2 dimensions preserve history; fact rows link to the dimension version valid at the event time.

Category: Concept

What is a hot topic around Fact Tables in the cloud?

Columnar storage and query acceleration services dynamically optimize fact scans for interactive analytics.

Category: Topic