Data Modeling/Dimensional Modeling
Facts and Dimensions Deep Dive
The building blocks of dimensional modeling: what makes something a fact vs. a dimension, the different grains and types of fact tables, and how to choose the right grain for a fact table.
Dimensional modeling, popularized by Ralph Kimball, organizes analytical data into two kinds of tables: fact tables, which record measurable events, and dimension tables, which describe the context around those events. Almost every metric a business cares about — revenue, signups, sessions — lives in a fact table, and almost every 'group by' or 'filter by' comes from a dimension.
Fact tables
A fact table stores numeric, additive measurements tied to a specific event, along with foreign keys pointing to the relevant dimensions. fact_orders might have order_amount, quantity, customer_id, product_id, and order_date_key. The defining question for any fact table is its grain: what does one row represent? 'One row per order' and 'one row per line item within an order' are both valid, but very different, grains — and mixing grains within a single fact table is one of the most common dimensional modeling mistakes.
- Transaction facts: one row per discrete business event (an order, a click)
- Periodic snapshot facts: one row per entity per fixed time period (daily account balance)
- Accumulating snapshot facts: one row per process instance, updated as it moves through stages (an order from placed to shipped to delivered)
Dimension tables
A dimension table stores descriptive attributes used to filter, group, or label facts: dim_customer (name, signup date, region), dim_product (name, category, brand), dim_date (calendar attributes like day of week, fiscal quarter). Dimensions are typically much smaller than fact tables and change far less often.
-- Grain: one row per order line item
create table fact_order_items (
order_item_id bigint primary key,
order_id bigint not null,
product_id bigint not null references dim_product(product_id),
customer_id bigint not null references dim_customer(customer_id),
date_key int not null references dim_date(date_key),
quantity int not null,
amount_usd numeric(12,2) not null
);Watch out — Pick the grain before you write a single column
Every modeling mistake compounds from the wrong grain decision. Write down, in a sentence, exactly what one row of the fact table represents, and validate it against a real example before building anything else.
Once facts and dimensions are in place, the last major complication is that dimensions themselves change over time — a customer moves regions, a product gets recategorized — which is what slowly changing dimensions, covered next, are designed to handle.