Fact vs Dimension Tables

The two foundational table types in every data warehouse.

In data warehousing, every table is either a fact table or a dimension table. Understanding which is which — and why — is the first question in almost every data engineering interview.

The short version

Fact tables store events — what happened, how much, how many
Dimension tables store context — who, what, where, when

Think of fact tables as the verbs of your data (sales, clicks, orders) and dimension tables as the nouns (customer, product, date).

Dimension table

A dimension table describes a business entity. Its columns are mostly textual or categorical, and they change slowly over time.

Characteristics Descriptive attributes (non-numeric, mostly static) · Provides context to business events · Has a primary key and "slowly changing" attributes · Relatively small (hundreds to millions of rows)

CustomerDimension ├── CustomerID (PK) ├── CustomerName ├── Email ├── City ├── Country ├── Segment -- e.g. "Premium", "Regular" └── DateOfBirth

Fact table

A fact table stores quantitative metrics about business events. Its columns are mostly numeric, and rows are added continuously as events happen.

Characteristics Quantitative metrics (numeric, additive) · Records business events or transactions · Foreign keys to dimension tables · Very large (millions to billions of rows)

SalesFact ├── SalesID (PK) ├── CustomerID (FK) -- → CustomerDimension ├── ProductID (FK) -- → ProductDimension ├── DateID (FK) -- → DateDimension ├── Quantity -- measure ├── Amount -- measure └── Discount -- measure

Side-by-side comparison

Aspect	Dimension Table	Fact Table
Purpose	Describe entities	Record events
Data type	Mostly text / categorical	Mostly numeric measures
Size	Smaller (1K – millions of rows)	Larger (millions – billions of rows)
Growth rate	Slow (new customers occasionally)	Fast (new transactions constantly)
Updates	Slowly changing attributes	Rarely updated; mostly appended
Granularity	One row per entity	One row per event
Keys	Primary key + descriptive attributes	Foreign keys + measures

How they connect

A fact table joins to multiple dimension tables. Each foreign key in the fact maps to a primary key in a dimension. This is the foundation of star and snowflake schemas.

SQL — joining fact to dimensions

SELECT c.CustomerName, p.ProductName, d.Date, sf.Quantity, sf.Amount FROM SalesFact sf JOIN CustomerDimension c ON sf.CustomerID = c.CustomerID JOIN ProductDimension p ON sf.ProductID = p.ProductID JOIN DateDimension d ON sf.DateID = d.DateID WHERE d.Year = 2024 ORDER BY sf.Amount DESC;

Types of fact tables

Transactional — one row per event (most common). Example: one row per sale.
Periodic snapshot — captures state at regular intervals. Example: end-of-day account balances.
Accumulating snapshot — tracks events with multiple milestones. Example: order placed → packed → shipped → delivered.

The interview answer

"What's the difference between a fact and a dimension table?"

"Fact tables store the measurable events of a business — sales, clicks, transactions — with numeric measures and foreign keys to dimensions. Dimension tables store the context that gives those events meaning — who the customer is, what the product is, when it happened. Facts are typically large and grow fast; dimensions are smaller and change slowly. Together they form a star schema, where one central fact table joins to multiple dimension tables."

← Introduction Normalization (1NF/2NF/3NF) →