Dimension table
A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.
Overview
Dimension tables answer the "who, what, where, when, why" of a fact table's measures. A dim_customer table might hold customer_name, email, region, and segment; a dim_date table holds day_of_week, month, fiscal_quarter, and is_holiday. Each dimension has a primary key — usually a surrogate key — that the fact table references as a foreign key.
Compared to fact tables, dimensions are typically much smaller in row count (thousands to millions of rows versus billions) but wider, with many descriptive columns. They're also denormalized by convention in a star schema: a dim_product row repeats its category and department names directly rather than normalizing them into separate lookup tables, trading some redundancy for query simplicity.
Example
Copy code
CREATE SEQUENCE seq_customer_key START 1;
CREATE TABLE dim_customer (
customer_key INTEGER PRIMARY KEY DEFAULT nextval('seq_customer_key'),
customer_id VARCHAR UNIQUE, -- natural key from the source system
customer_name VARCHAR,
region VARCHAR,
segment VARCHAR,
effective_date DATE,
is_current BOOLEAN
);
SELECT region, COUNT(*) AS customers
FROM dim_customer
WHERE is_current
GROUP BY ALL;
A few dimension attributes recur across warehouses: the dim_date (or dim_calendar) dimension is nearly universal, pre-populated with one row per day for years into the future so date arithmetic and fiscal-calendar logic don't have to be recomputed at query time. Dimensions can also track history — see slowly changing dimensions — using effective_date, end_date, and is_current columns like the ones above.
DuckDB angle
Because dimensions are small relative to fact tables, DuckDB can happily hold every dimension in memory and hash-join it against a large fact table in a single pass, which is why star-schema queries against DuckDB or MotherDuck tend to be dominated by fact table scan time rather than join cost.
Related terms
A conformed dimension is a dimension table — like a shared date or customer dimension — that is defined identically and reused across multiple fact tables or data marts, so metrics stay comparable across the warehouse.
Degenerate dimension →A degenerate dimension is a dimension attribute — like an invoice or order number — that's stored directly as a column in the fact table, with no corresponding dimension table, because it has no other descriptive attributes.
Fact table →A fact table is the central table in a dimensional model that stores numeric measures (like revenue or quantity) alongside foreign keys linking to descriptive dimension tables.
Star schema →A star schema is a dimensional data warehouse design where a central fact table of numeric measures connects directly to denormalized dimension tables, forming a shape like a star.
Role-playing dimension →A role-playing dimension is a single physical dimension table, most often a date dimension, that is joined into a fact table multiple times to represent different roles, such as order date, ship date, and delivery date.
Junk dimension →A junk dimension is a single dimension table that groups together several low-cardinality, otherwise-unrelated flags and indicators, to avoid cluttering a fact table with many tiny separate dimension keys.
FAQS
There's no fixed limit, but dimensions are typically far smaller than the fact tables they describe — from a few rows (a boolean flag dimension) up to millions (a large customer or product dimension). If a dimension approaches the size of your fact table, reconsider whether it should be a dimension at all.
