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
Dimension Table

Side-by-Side Technical Matrix
Direct evaluation across performance, complexity, latency, and data modeling parameters.
| Evaluation Criterion | 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 |
Core Concepts & Architecture Workflows
Foundational definition, data flow patterns, and architectural mechanics for each system.
What is a Fact Table?
Paradigm AA 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 BA 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_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
Performance Consideration
Real-world throughput, latency constraints, and operational cost trade-offs.
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