Fact vs Dimension Table
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.
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)
| Feature | Fact Table | Dimension Table |
|---|---|---|
| Data Type | Numeric | Text / Descriptive |
| Purpose | Store metrics | Provide context |
| Size | Large | Smaller |
| Keys | Foreign keys | Primary keys |
| Normalization | Less important | Can be denormalized |
| Example | Sales, Transactions | Customer, Product |
Example of Fact and Dimension Table
Fact Table Example (fact_sales)
| sale_id | customer_id | product_id | amount |
|---|---|---|---|
| 1 | 101 | 501 | 500 |
| 2 | 102 | 502 | 300 |
Dimension Table Example (dim_customer)
| customer_id | customer_name | city |
|---|---|---|
| 101 | John | New York |
| 102 | Alice | London |
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 (CSS UI Style - Optional Inline Design)
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