Facts and dimensional table modeling

Facts and dimension tables are the core building blocks of dimensional modeling in data analysis.

If dimensional modeling is the structure, then:

• Fact tables = What happened (numbers)
• Dimension tables = Context (descriptions)

This design is mainly used in data warehouses and BI systems.

1️⃣ What is a Fact Table?

A fact table stores measurable business events.

It contains:

• Numeric measurements (metrics)
• Foreign keys to dimension tables

Think of it as:

A log of business events with numbers attached.

Example: Sales Fact Table

| sale_id | date_key | product_key | customer_key | quantity | total_amount |
|---------|----------|------------|--------------|----------|-------------|
| 1       | 20240101 | 101        | 501          | 2        | 200         |
| 2       | 20240102 | 102        | 502          | 1        | 150         |

What’s inside?

• quantity → numeric fact
• total_amount → numeric fact
• date_key, product_key, customer_key → links to dimensions

Each row represents one sales transaction.

This is called defining the grain of the fact table.

2️⃣ What is Grain?

Grain answers:

“What does one row represent?”

Examples:

| Business Process | Grain |
|------------------|-------|
| Sales | One row per sale |
| Banking | One row per transaction |
| Inventory | One row per product per day |

Choosing grain is VERY important because it determines how detailed your analysis can be.

3️⃣ What is a Dimension Table?

A dimension table stores descriptive attributes about facts.

It answers:

• Who?
• What?
• When?
• Where?
• How?

Dimensions contain:

• Text data
• Categories
• Hierarchies

Example: Product Dimension

| product_key | product_name | category     | brand  |
|------------|--------------|--------------|--------|
| 101        | Laptop       | Electronics  | Dell   |
| 102        | Phone        | Electronics  | Apple  |

Example: Date Dimension

| date_key | full_date  | day | month | year | quarter |
|----------|------------|-----|-------|------|---------|
| 20240101 | 2024-01-01 | 1   | Jan   | 2024 | Q1      |
| 20240102 | 2024-01-02 | 2   | Jan   | 2024 | Q1      |

Notice:

• Dimensions are descriptive
• They usually have a primary key (e.g., product_key)

4️⃣ How Fact and Dimension Tables Work Together

Fact table connects to dimension tables using foreign keys.

Visual structure (Star Schema):

                Date Dimension
                      |
Customer --- Sales Fact --- Product
                      |
                Store Dimension

The fact table sits in the center.

Dimensions surround it like a star.

5️⃣ Fact Table Characteristics

Fact tables usually:

• Are very large (millions or billions of rows)
• Contain mostly numeric data
• Grow over time
• Are append-heavy (new rows added)

Types of Facts

| Type | Meaning | Example |
|------|----------|----------|
| Additive | Can sum across all dimensions | Sales amount |
| Semi-additive | Cannot sum across time | Account balance |
| Non-additive | Cannot sum | Percentage |

Example:

• Sales amount → Additive
• Bank balance → Semi-additive
• Profit margin % → Non-additive

6️⃣ Dimension Table Characteristics

Dimension tables:

• Contain descriptive attributes
• Are smaller than fact tables
• Change less frequently
• Contain hierarchies

Example hierarchy:

Product → Category → Department

Date → Month → Quarter → Year

7️⃣ Simple SQL Implementation Example

Below is a simple model.

Create Dimension Tables

CREATE TABLE product_dimension (
    product_key INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    brand VARCHAR(50)
);

CREATE TABLE date_dimension (
    date_key INT PRIMARY KEY,
    full_date DATE,
    month VARCHAR(10),
    year INT,
    quarter VARCHAR(5)
);

Create Fact Table

CREATE TABLE sales_fact (
    sale_id INT PRIMARY KEY,
    date_key INT,
    product_key INT,
    customer_key INT,
    quantity INT,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (date_key) REFERENCES date_dimension(date_key),
    FOREIGN KEY (product_key) REFERENCES product_dimension(product_key)
);

8️⃣ Example Analytical Query

Question:

What are total sales by product category?

SELECT 
    p.category,
    SUM(f.total_amount) AS total_sales
FROM sales_fact f
JOIN product_dimension p
    ON f.product_key = p.product_key
GROUP BY p.category;

Because of fact + dimension modeling:

• Query is simple
• Easy to read
• Fast in data warehouses

9️⃣ Comparison: Fact vs Dimension

| Feature | Fact Table | Dimension Table |
|----------|------------|----------------|
| Stores | Numbers | Descriptions |
| Size | Very large | Smaller |
| Purpose | Measure events | Provide context |
| Examples | Sales, Revenue, Quantity | Product, Customer, Date |
| Contains foreign keys? | Yes | No |

🔟 Real-World Use

Fact and dimension modeling is widely used in modern data warehouses like:

• Snowflake
• Amazon Redshift
• Google BigQuery

Companies use this structure to power dashboards and BI tools.

1️⃣1️⃣ Full SQL Compilation (All Code Together)

-- Create Product Dimension
CREATE TABLE product_dimension (
    product_key INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    brand VARCHAR(50)
);

-- Create Date Dimension
CREATE TABLE date_dimension (
    date_key INT PRIMARY KEY,
    full_date DATE,
    month VARCHAR(10),
    year INT,
    quarter VARCHAR(5)
);

-- Create Sales Fact Table
CREATE TABLE sales_fact (
    sale_id INT PRIMARY KEY,
    date_key INT,
    product_key INT,
    customer_key INT,
    quantity INT,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (date_key) REFERENCES date_dimension(date_key),
    FOREIGN KEY (product_key) REFERENCES product_dimension(product_key)
);

-- Example Query
SELECT 
    p.category,
    SUM(f.total_amount) AS total_sales
FROM sales_fact f
JOIN product_dimension p
    ON f.product_key = p.product_key
GROUP BY p.category;