What Are Fact and Dimension Tables in a Data Warehouse?
Learn what fact and dimension tables are in a data warehouse, with a worked SQL example joining sales facts to dimensions.
Expected Interview Answer
A fact table stores the measurable, numeric events of a business process — like sales amount or quantity sold — along with foreign keys to related dimensions, while dimension tables store the descriptive context, such as who, what, where, and when, that gives those numbers meaning.
Fact tables are typically narrow but very tall, growing by one or more rows per business event (like every sale), and they hold additive or semi-additive measures plus foreign keys pointing to each relevant dimension. Dimension tables are wider but comparatively short, holding descriptive attributes like product name, customer city, or calendar date that analysts filter and group by. Together they let a query like "total sales by region last quarter" join the fact table's numeric rows to the dimension tables' descriptive attributes, which is the core pattern behind dimensional modeling and star/snowflake schemas.
- Separates numeric measures from descriptive context cleanly
- Enables fast slice-and-dice analysis by any dimension attribute
- Keeps fact tables lean since dimensions absorb repetitive descriptive data
- Matches how BI tools naturally build filters, groupings, and drill-downs
AI Mentor Explanation
The fact table is like a ball-by-ball log recording runs scored and wickets taken for every delivery, a pure stream of numbers with foreign keys to the match, player, and venue. The dimension tables are the Player, Team, and Venue reference sheets holding descriptive details like a player's role or a venue's city. To answer "how many runs did this player score at this venue," you join the numeric ball-by-ball fact rows to the descriptive Player and Venue dimension rows.
Fact Table Joined to Its Surrounding Dimension Tables
Fact_Sales
- sale_id
- product_id
- store_id
- date_id
- quantity
- revenue
Dim_Product
- product_id
- name
- category
Dim_Store
- store_id
- city
- region
Step-by-Step Explanation
Step 1
Identify the business process
Choose the event to measure, such as a sale, a booking, or a payment.
Step 2
Define the fact table
Create a table holding numeric measures (revenue, quantity) plus foreign keys to each relevant dimension.
Step 3
Define the dimension tables
Create descriptive tables like Product, Store, and Date, each with a surrogate key referenced by the fact table.
Step 4
Join for analysis
Query the fact table joined to dimensions to filter, group, and aggregate measures by descriptive attributes.
What Interviewer Expects
- Clear distinction between numeric measures (facts) and descriptive context (dimensions)
- Understanding that fact tables grow tall while dimension tables stay wide but comparatively short
- Knowledge of how foreign keys connect fact rows to dimension rows
- Ability to give a concrete example, such as Sales fact with Product, Store, and Date dimensions
Common Mistakes
- Putting descriptive text attributes directly into the fact table instead of a dimension
- Storing non-additive or unrelated data as a "measure" in the fact table
- Confusing a fact table with a dimension table because both have foreign/primary keys
- Forgetting the Date dimension, one of the most commonly reused dimensions
Best Answer (HR Friendly)
“A fact table stores the numbers you want to analyze, like sales revenue or quantity sold, one row per event, along with keys pointing to related details. Dimension tables hold those details, like product name, store location, or date, so you can group and filter the numbers however you need, like sales by region or by month.”
Code Example
CREATE TABLE Dim_Product (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50)
);
CREATE TABLE Dim_Store (
store_id INT PRIMARY KEY,
city VARCHAR(50),
region VARCHAR(50)
);
CREATE TABLE Dim_Date (
date_id INT PRIMARY KEY,
full_date DATE,
quarter INT,
year INT
);
CREATE TABLE Fact_Sales (
sale_id INT PRIMARY KEY,
product_id INT REFERENCES Dim_Product(product_id),
store_id INT REFERENCES Dim_Store(store_id),
date_id INT REFERENCES Dim_Date(date_id),
quantity INT,
revenue DECIMAL(12,2)
);
-- Total revenue by region for a given quarter
SELECT s.region, SUM(f.revenue) AS total_revenue
FROM Fact_Sales f
JOIN Dim_Store s ON f.store_id = s.store_id
JOIN Dim_Date d ON f.date_id = d.date_id
WHERE d.quarter = 2 AND d.year = 2026
GROUP BY s.region;Follow-up Questions
- What is the difference between an additive, semi-additive, and non-additive measure?
- Why is a Date dimension usually pre-populated rather than generated on the fly?
- How do slowly changing dimensions affect a dimension table's design?
- What is a fact-less fact table and when would you use one?
MCQ Practice
1. A fact table primarily stores:
Fact tables hold the measurable, numeric values of a business process along with foreign keys linking to descriptive dimensions.
2. Compared to dimension tables, fact tables are typically:
Fact tables grow by roughly one row per business event, making them comparatively narrow but very tall over time.
3. A Date dimension table is commonly used to:
The Date dimension provides reusable descriptive attributes like quarter and year, letting facts be grouped by any calendar granularity.
Flash Cards
What does a fact table store? — Numeric measures of a business process plus foreign keys to related dimension tables.
What does a dimension table store? — Descriptive context — the who, what, where, when — used to filter and group facts.
Shape of a fact table over time? — Narrow but very tall, growing roughly one row per business event.
Example fact and dimensions? — Fact_Sales (revenue, quantity) joined to Dim_Product, Dim_Store, and Dim_Date.