Understanding Fact Tables: Types, Granularity, and Design Best Practices

Fact Table Overview

A fact table stores large volumes of business‑metric data. Each row combines foreign‑key references to dimension tables with numeric measures that can be aggregated. Optional degenerate dimensions (e.g., timestamps) may also be present. The table is the core of dimensional modeling because it captures the quantitative outcome of a business event.

Granularity

Granularity is the level of detail represented by a single row (e.g., one order, one payment, one refund). Defining granularity determines the finest analytical slice available and drives ETL frequency.

Enables frequent incremental ETL.

Supports data‑mining scenarios that require the most detailed data.

Facilitates drill‑down analysis.

Higher storage and maintenance cost.

Often requires additional summary fact tables for aggregated queries.

Fact Types

Numeric measures fall into three categories:

Additive fact: Can be summed across any dimension (e.g., order_amount).

Semi‑additive fact: Can be summed only along specific dimensions (e.g., account_balance summed by user but not by time).

Non‑additive fact: Summation is meaningless (e.g., membership_level or ratio metrics). Non‑additive facts can sometimes be decomposed into additive components for aggregation.

Null Handling

Aggregate functions ( SUM, COUNT, MIN, MAX, AVG) safely ignore null measures.

Foreign‑key columns must never be null; otherwise referential integrity is violated.

Dimension tables should provide a default surrogate row (e.g., UNKNOWN) to represent missing foreign‑key values.

Consistent Facts

Facts compared across tables must share identical technical definitions (data type, unit, grain).

When definitions are identical, use the same fact name to avoid confusion.

If definitions differ, assign distinct names to warn business users and BI tools.

Transaction Fact Tables

Transaction (or atomic) fact tables record each business transaction at the finest grain. Rows are immutable after insertion; updates are performed only by appending new rows (incremental load). Typical events include order creation, payment, shipment, and refund.

Single‑Transaction Fact Table

One fact table per business process provides clear isolation and simplifies process‑specific analysis, but can lead to a proliferation of tables and a less intuitive view of end‑to‑end flow.

Multi‑Transaction Fact Table

Multiple processes share a single fact table, aligning the model with the source system and simplifying flow tracking. Two common design patterns are:

① When a row contains a measure that does not belong to the current process, store a zero value.
② Add a flag column to indicate which process the row represents for the given day.

② Use a single measure column for all processes and add a process‑type label column. This may generate multiple rows per period for the same business entity.

Periodic Snapshot Fact Table

A periodic snapshot (or regular snapshot) records the state of a business object at fixed, predictable intervals (daily, weekly, monthly, etc.). Each row represents a “period + state‑metric” combination, making the table dense—rows exist for every period regardless of activity.

Key characteristics:

Granularity is defined by the sampling interval and the set of dimensions used to identify the object.

Metrics are typically semi‑additive; they cannot be meaningfully summed across time.

Provides a stable view for trend analysis, forecasting, and period‑over‑period comparisons.

Cumulative Snapshot Fact Table

Cumulative snapshots capture the entire lifecycle of a fact whose duration is not known in advance. A single row stores multiple timestamps (start, intermediate, end) and any intermediate metrics. Typical use cases include measuring time intervals between events, such as “order placed → payment” or “payment → shipment.” The row is updated as each milestone occurs, resulting in a record that contains several date fields representing the workflow.

Fact‑less Fact Table

A fact‑less table contains only foreign keys to dimensions and no numeric measures. It is useful for:

1) Tracking the occurrence of events (e.g., log tables).

2) Answering “what did not happen” questions, such as products that were promoted but never sold.

Aggregate Fact Table

Aggregate tables store pre‑summarized data derived from transaction fact tables. They improve query performance and reduce inconsistencies among users.

Aggregation granularity can differ from the source detail (e.g., daily product sales, monthly regional sales).

Aggregates must align with the original model’s dimensions and measures; they never cross facts.

Maintaining aggregates adds ETL complexity, especially when source hierarchies change.

Merged Fact Table

A merged fact table combines measures from multiple business processes that share the same grain. The principle is “same granularity, multiple sources.” This design is ideal when several processes are frequently analyzed together. Like aggregate tables, merged tables increase ETL workload but lower the analytical cost for downstream BI tools.