CSE Roadmap

Specialist tier · about 2 hours · 6 parts

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.