Skip to main content
๐Ÿง  Data ModelingArchitecture GuideSenior Engineering

Fact vs Dimension Table: Differences, Examples & When to Use

Learn the difference between fact table vs dimension table with examples, performance, schema usage, and interview questions.

๐Ÿง 

Fact Table

Primary Paradigm A
VS
๐Ÿง 

Dimension Table

Primary Paradigm B

When working with data warehouse schema types, one of the most important concepts is:

๐Ÿ‘‰ *Fact Table vs Dimension Table*

Understanding this is the foundation of Star Schema, Snowflake Schema, and Data Modeling.

๐Ÿ“ Architectural Reference Diagram
High-Resolution Visual
Fact vs Dimension Diagram
โš–๏ธ

Side-by-Side Technical Matrix

Direct evaluation across performance, complexity, latency, and data modeling parameters.

Evaluation CriterionFact TableDimension Table
Data TypeNumericText / Descriptive
PurposeStore metricsProvide context
SizeLargeSmaller
KeysForeign keysPrimary keys
NormalizationLess importantCan be denormalized
ExampleSales, TransactionsCustomer, Product
๐Ÿ’ก

Core Concepts & Architecture Workflows

Foundational definition, data flow patterns, and architectural mechanics for each system.

What is a Fact Table?

Paradigm A

A Fact Table stores:

  • Quantitative data (numbers)
  • Business metrics like:
  • - sales_amount
  • - quantity
  • - revenue

Key Idea ๐Ÿ‘‰ Contains measurable values + foreign keys

What is a Dimension Table?

Paradigm B

A Dimension Table stores:

  • Descriptive data (text)
  • Context about facts:
  • - customer_name
  • - product_name
  • - region

Key Idea ๐Ÿ‘‰ Adds meaning and context to facts

๐Ÿ’ป

Real-World Implementation, Queries & Architecture Code

Concrete SQL queries and pipeline implementation patterns.

Example of Fact and Dimension Table

Fact Table Example (fact_sales)

sale_idcustomer_idproduct_idamount
1101501500
2102502300

Dimension Table Example (dim_customer)

customer_idcustomer_namecity
101JohnNew York
102AliceLondon

Example Code (SQL)

Query Using Fact + Dimension Tables

SELECT 
    c.customer_name,
    SUM(f.amount) AS total_sales
FROM fact_sales f
JOIN dim_customer c 
    ON f.customer_id = c.customer_id
GROUP BY c.customer_name;

๐Ÿ‘‰ Insight:

  • Fact table provides numbers
  • Dimension table provides meaning
โšก

Performance Consideration

Real-world throughput, latency constraints, and operational cost trade-offs.

  • Fact tables are large and heavily queried
  • Dimension tables are optimized for filtering
  • Proper indexing improves performance

๐Ÿ‘‰ In analytics: Fact + Dimension together = Powerful insights

๐ŸŽฏ

When to Use Fact vs Dimension Table

Guidelines for selecting the optimal architecture based on system requirements.

Use Fact Table when:

  • Storing metrics / KPIs
  • Recording transactions
  • Tracking business events

Use Dimension Table when:

  • Adding descriptive context
  • Filtering & grouping data
  • Supporting analytics queries
๐Ÿšจ

Common Mistakes ๐Ÿšจ

Avoid costly anti-patterns, misconfigurations, and system design oversights.

โŒ Mixing Fact and Dimension Data

  • Putting text fields in fact table

โŒ Too Many Columns in Fact Table

  • Makes queries slow

โŒ Not Using Surrogate Keys

  • Causes join issues
๐Ÿ”ฅ

Interview Angle ๐Ÿ”ฅ

Essential questions and key talking points frequently asked in staff/senior interviews.

๐Ÿ’ก 1. What is a Fact Table?

+

๐Ÿ’ก 2. What is a Dimension Table?

+

๐Ÿ’ก 3. What is the relationship?

+

๐Ÿ’ก 4. Can a fact table exist without dimensions?

+
โ“

FAQ

Direct answers to common architecture queries.

โ“ What is fact table in simple terms?

+

โ“ What is dimension table?

+

โ“ What is difference between fact and dimension table?

+

โ“ Why are both used together?

+
๐Ÿ“Œ

Key Architectural Summary

Quick recap of primary characteristics and core takeaways.

๐Ÿ“‘

Comparison Diagram

Detailed architectural insights and system considerations.

Fact Table

  • Stores metrics
  • Large volume
  • Contains foreign keys
  • Used in aggregations

Dimension Table

  • Stores descriptive data
  • Smaller size
  • Contains primary keys
  • Used for filtering

๐Ÿ Final Summary
  • Fact Table = Numbers (metrics) ๐Ÿ“Š
  • Dimension Table = Context (descriptions) ๐Ÿงฉ

๐Ÿ‘‰ Together, they form the backbone of data warehouse design