A dimension table is a table in a data warehouse star schema that holds descriptive attributes — customer name, product category, geographic region, date attributes — used to filter, group, and contextualise the measurable events stored in fact tables.
Why Dimension Table Matters
Dimension tables are how analytics gets meaningful. A fact table tells you something happened (an order was placed); a dimension table tells you who, what, when, and where it happened (which customer, which product, which date, which store). Without dimensions, fact data is just numbers without context.
Dimension modelling is also where most of the data-quality decisions in analytics happen. Slowly changing dimensions, hierarchy attributes, and business rule definitions all live in dimension tables. Modelling them correctly is the difference between trustworthy and confusing analytics.
How Dimension Table Works
A dimension table contains:
- Surrogate primary key: A meaningless integer used as the join key. Often called
customer_keyorproduct_id. - Natural key: The “real” identifier from source systems (customer email, product SKU). Useful for ETL and debugging.
- Descriptive attributes: Human-readable fields that describe the entity — name, category, segment, country, signup date.
- Hierarchy levels: Many dimensions have hierarchies (country → region → continent; year → quarter → month → day). These are flattened into the dimension table.
- SCD tracking columns: Slowly changing dimension columns like
valid_from,valid_to, andis_currentfor historical accuracy.
Dimension tables are typically much smaller than fact tables (thousands to millions of rows vs billions). They join to multiple fact tables — the same dim_customer joins to fct_orders, fct_subscriptions, and fct_support_tickets.
Real-World Example
A SaaS company’s dim_customer dimension table has 50,000 rows, one per customer. Columns: customer_key (surrogate PK), customer_id (natural key from CRM), name, email, signup_date, country, region, continent, segment (SMB / Mid-Market / Enterprise), plan (Starter / Pro / Enterprise), and SCD columns. The same dim_customer joins to fact tables for orders, subscriptions, support tickets, and product events — providing consistent customer context across all analytics.
Common Dimension Table Tools and Platforms in 2026
2026 dimension table tools:
dbt
Standard tool for building dimension tables. Includes SCD type-2 macros (dbt_utils, dbtvault) for historical tracking.
Snowflake / BigQuery / Databricks
Cloud warehouses where dimension tables live.
Looker / Cube / Holistics
Semantic layer tools that expose dimension attributes as filterable in dashboards and AI queries.
Master Data Management (MDM) tools
Tools like Reltio and Tibco MDM for managing canonical dimension data across systems.
Analytify
Open-source GenBI platform that reads dimension tables for SaaS embedded analytics.
Frequently Asked Questions About Dimension Table
What is the difference between a fact table and a dimension table?
A fact table stores measurable events with numerical measures. A dimension table stores descriptive attributes that contextualise the events. Facts are large; dimensions are small.
What is a slowly changing dimension (SCD)?
A pattern for tracking dimension attribute changes over time. SCD Type 1 overwrites the value (no history). SCD Type 2 adds a new row with valid_from/valid_to dates (full history). SCD Type 3 stores previous and current value in separate columns.
What is a conformed dimension?
A dimension that is shared across multiple fact tables, ensuring consistent definitions. dim_customer conformed across fct_orders, fct_support_tickets, and fct_subscriptions means “customer” means the same thing everywhere.
What is a degenerate dimension?
A dimension attribute that lives directly in a fact table because it is too small to warrant its own dimension table — order_number, transaction_id. The “dimension” is degenerate because it has only the one column.
What is a junk dimension?
A grouping of low-cardinality flag attributes (is_promotion, is_returned, is_first_order) into a single dimension table to reduce fact table column count.
Should I denormalise dimensions or use snowflake schema?
For most modern warehouses, denormalised (flat) dimensions are preferred — easier to query and modern warehouses make joins cheap. Snowflake schemas (normalised dimensions) save storage but make queries more complex.