Skip to main content
DatabricksPySparkSnowflakeApache Airflow

Fact vs Dimension Table

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

👉 Fact Table vs Dimension Table​

Fact vs Dimension Diagram

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

What is a Fact Table?​

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?​

A Dimension Table stores:

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

Key Idea​

👉 Adds meaning and context to facts

Fact vs Dimension Table (6 Key Differences)​

FeatureFact TableDimension Table
Data TypeNumericText / Descriptive
PurposeStore metricsProvide context
SizeLargeSmaller
KeysForeign keysPrimary keys
NormalizationLess importantCan be denormalized
ExampleSales, TransactionsCustomer, Product

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

When to Use Fact vs Dimension Table​

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

Performance Consideration​

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

👉 In analytics: Fact + Dimension together = Powerful insights

Common Mistakes 🚨​

❌ 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 🔥​

Common Questions:​

1. What is a Fact Table?
👉 Stores measurable business data

2. What is a Dimension Table?
👉 Stores descriptive attributes

3. What is the relationship?
👉 Fact table joins dimension tables using keys

4. Can a fact table exist without dimensions?
👉 Not useful in analytics

FAQ​

What is fact table in simple terms?​

A fact table stores measurable business data like sales or revenue.

What is dimension table?​

A dimension table stores descriptive attributes like customer or product details.

What is difference between fact and dimension table?​

Fact table stores numbers, while dimension table provides context.

Why are both used together?​

To combine metrics + meaning for analysis.

Comparison Diagram​

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