---
title: "Dimension table"
description: "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."
canonical: "https://motherduck.com/glossary/dimension-table/"
related:
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "STORAGE_INFO views | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/storage_info/"
  - title: "TEMPORARY TABLES | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/temporary-tables/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# 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.

<glossary-callout guide="duckdb-cheatsheet-full" />

### Example

```sql
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.