Part 4 of 6 · 2 min read
Fact and dimension tables
Think of a fact table as a single long receipt listing one line per sale, and the dimension tables as the address book, the item catalog, and the calendar that the receipt refers to instead of repeating in full each time.
In plain words: A fact table records individual events, typically one row per transaction, and is usually the largest table in the warehouse. Its columns split into two kinds. Measure attributes hold the numbers that get aggregated, such as quantity sold or price. Dimension attributes hold short identifiers that say which store, which customer, which item, and which date the event belongs to. Each dimension attribute is a foreign key into its own dimension table, which holds the full descriptive detail for that dimension, such as a customer's name and address, so the fact table does not repeat that detail on every row.
A worked example. The bookstore's warehouse fact table sales_fact has one row per book sold: book_id, customer_id, store_id, date, quantity, price_dollars. quantity and price_dollars are measures. book_id, customer_id, store_id, and date are dimensions, each pointing at a separate dimension table holding the full details.
The key lines.
CREATE TABLE sales_fact (
sale_id BIGINT PRIMARY KEY,
book_id INT REFERENCES book_dim(book_id),
customer_id INT REFERENCES customer_dim(customer_id),
store_id INT REFERENCES store_dim(store_id),
sale_date DATE REFERENCES date_dim(sale_date),
quantity INT,
price_dollars NUMERIC(10,2)
);
CREATE TABLE book_dim (
book_id INT PRIMARY KEY,
title TEXT,
genre TEXT
);
Fact table, dimension table, measure attribute, dimension attribute
A fact table records individual events and holds two kinds of columns: measure attributes, which store the quantitative values that get summed or averaged, and dimension attributes, which are foreign keys pointing at dimension tables. A dimension table holds the descriptive detail for one dimension, such as every attribute of a customer or a store, referenced by short identifiers from the fact table.
Example: in sales_fact above, quantity and price_dollars are measure attributes, while book_id is a dimension attribute pointing at the book_dim dimension table.