TL;DR
A dimension table provides the descriptive context for facts in a dimensional model — the who, what, where, when, why surrounding business events. Dimensions tend to be wide (many descriptive attributes) and short (typically thousands to millions of rows, vs billions for facts). Common dimensions include Customer, Product, Date, Store, Employee, and Geography. Dimensions are the backbone of analytical queries — every BI question is fundamentally a fact aggregated by dimension attributes.
What is a dimension table?
A dimension table holds the descriptive context for fact-table rows. Where facts answer 'what happened and how much?', dimensions answer 'who, what, where, when, why?'.
Dimensions tend to be wide (often dozens of attribute columns) and shorter than facts — a customer dim might have 50 attributes and 500K rows, where the related sales fact has 8 columns and 500M rows. The asymmetry is intentional: rich dimensions enable nuanced analytical queries; narrow facts keep aggregation fast.
Anatomy of a dimension table
Customer Dim: - customer_sk (PK, surrogate integer key) - customer_id (natural key, e.g. CRM ID) - first_name, last_name - email - segment (SMB, Mid-Market, Enterprise) - industry - country, region, city - first_purchase_date - lifetime_value_band - acquisition_channel - is_current (Type 2 SCD flag) - effective_from (Type 2 SCD) - effective_to (Type 2 SCD) - created_at, updated_at
Slowly Changing Dimensions (SCDs)
Type 2 is the most common in modern dimensional models because it preserves full history and supports point-in-time queries. The trade-off is more rows in the dimension and slightly more complex join logic (joining facts to the right dimension version by date).
| SCD Type | Behaviour | Use case |
|---|---|---|
| Type 0 | Don't change attribute (immutable) | Historical anchor attributes |
| Type 1 | Overwrite with new value | Corrections; attributes where history doesn't matter |
| Type 2 | Add new row with effective dates | Most common for analytical history |
| Type 3 | Add new column for previous value | Limited history (current + previous only) |
| Type 6 | Combination of Types 1, 2, and 3 | Complex requirements |
Common dimension patterns
- Date dimension: the most-used dim, with attributes for fiscal calendar, holidays, period flags, day-of-week. Pre-built date dimensions save analysts hundreds of hours.
- Junk dimension: bundles low-cardinality flag attributes (yes/no flags, status codes) into a single dimension to reduce fact-table column count.
- Conformed dimension: shared definition of customer, product, or geography used consistently across multiple fact tables — enables cross-fact analytical queries.
- Mini-dimension: high-volatility customer attributes split into a separate frequently-changing dim to keep main customer dim stable.
Common pitfalls
- 1. Skipping the date dim. Date arithmetic in queries (fiscal month, quarter, holiday flags) is expensive when computed inline. A pre-built date dim saves analysts hundreds of hours per year.
- 2. Type 1 when Type 2 is needed. Overwriting customer segment when a customer changes from SMB to Enterprise loses history; cohort analysis breaks. Most analytical attributes warrant Type 2 handling.
- 3. Inconsistent dimensions across facts. Sales fact uses one Product dim definition, marketing fact uses another. Cross-fact queries fail. Conformed dimensions are non-negotiable for healthy analytical models.
Related concepts
Fact tables are the events being measured. Star schema is the canonical layout. Dimensional modeling is the broader discipline. Data marts contain dimension tables for specific subject areas.
At a glance
- Category
- Business Intelligence
- Related
- 5 terms
Frequently asked questions
Should I use natural or surrogate keys?
Surrogate keys for fact-to-dim joins; keep natural keys as columns for human readability. Surrogate keys produce smaller, faster joins and are essential for SCD Type 2 handling. Most modern dimensional models use both: surrogate as PK, natural key as a column.
Type 1 or Type 2 SCD?
Type 2 by default for analytical attributes (segment, industry, region) — preserves history for cohort analysis. Type 1 for corrections (fixing typos) and for attributes where history doesn't matter (current email address). When in doubt, default to Type 2 in modern warehouses where storage is cheap.
How many dimensions should a fact have?
Typically 5–15 per fact table. Fewer than 3 suggests under-modeled dimensions. More than 20 suggests the fact table is mixing analytical subjects that should be split. The right number is 'all the context needed for the analytical questions the fact answers' — no more, no less.
Sources
- The Data Warehouse Toolkit, Kimball (3rd ed)
- Kimball Group dimensional-modeling resources
- dbt Labs analytics engineering documentation
Fairview is an operating intelligence platform that reads dimensional models directly — preserving the SCD handling, conformed dimensions, and dimensional discipline that analysts have invested in, rather than recreating it in a Fairview-specific layer. Start your free trial →
Siddharth Gangal is the founder of Fairview. He built the dimension-aware ingestion path after watching operating dashboards point-in-time-join facts against current dimensional state instead of effective-date dimensional state — producing cohort numbers that didn't match the analytical-warehouse versions because the tool was ignoring SCD Type 2 history.
See it in Fairview
Track Dimension Table automatically.
14-day free trial. No credit card. First data source connected in 5 minutes.