Fact vs. Dimension Tables: All You Need to Know

1. Dimension Table

The dimension table describes the context of data and provides attributes used for filtering and grouping in analysis. Its core content consists of non‑numeric, descriptive attributes such as:

Time dimension: year, month, day, week, quarter

Customer dimension: customer ID, region, age, consumption level

Product dimension: product ID, category, specification, brand

Key features:

Must have a unique primary‑key identifier, which serves as the foreign key in fact tables.

Usually hierarchical (e.g., product category → sub‑category → product; country → province → city), enabling drill‑down analysis.

Design considerations include reserving space for future attributes (e.g., adding membership level to a customer dimension) and aligning attributes with analysis requirements. Dimension tables typically contain fewer rows, small data volume, and infrequent updates.

2. Fact Table

The fact table stores measurable business process data. Its core content is numeric metrics that can be aggregated, such as sales amount, sales quantity, discount amount, transaction count, and transaction amount.

Three core characteristics:

Large data volume: each row records a specific business event, potentially millions to billions of rows per day.

Measure additivity classification: additive (e.g., sales amount, meaningful across any dimension), semi‑additive (e.g., inventory, meaningful only for certain dimensions), non‑additive (e.g., discount rate, must be decomposed into additive components).

High update frequency: often real‑time or near‑real‑time incremental loads, contrasting with the static nature of dimension tables.

Design details:

Foreign keys must never be null to preserve referential integrity; missing measure values should be filled with zero.

Declare the grain (e.g., per order line, per transaction) before designing the table, then determine required dimensions and measures.

Fact tables have many rows but few columns (primarily foreign keys and measures). They can be classified into three types:

Transaction fact table: one row per business event, immutable after insertion, suitable for detailed traceability.

Periodic snapshot fact table: one row per fixed time interval regardless of activity, semi‑additive measures, ideal for trend and period reports.

Cumulative snapshot fact table: records the full lifecycle state with multiple time fields, updated as the business progresses, useful for calculating time intervals between business nodes.

3. Core Differences

Dimension tables focus on descriptive, relatively static attributes; fact tables focus on quantitative, frequently changing metrics. The primary distinction is "description vs. measurement".

4. How They Work Together

The relationship is established through the dimension table’s primary key and the fact table’s foreign key.

Example: a sales fact table contains customerID, productID, and timeID as foreign keys. By joining these keys to the corresponding dimension tables, the numeric sales figures can be analyzed by customer segment, product category, or time period.

Designing tables in isolation leads to mismatched grain and missing attributes, causing analysis failures. All multidimensional analysis follows the pattern: dimension filters + fact aggregations.

5. Design and Maintenance

Dimension Table Design & Maintenance

Organize attributes logically, placing frequently used filter/group fields in the table to avoid redundancy.

Handle slowly changing dimensions (e.g., changes in region or consumption level) by preserving historical values.

Always design dimensions to satisfy the analysis needs of the associated fact tables.

Fact Table Design & Maintenance

Follow strict principles: include all relevant business facts, avoid mixing multiple grains in one table, keep measure units consistent.

Ensure consistency across fact tables: identical measures must share the same definition and naming.

Process measures correctly: non‑additive facts must be broken into additive components; foreign keys must never be null.

6. Visual Illustrations