Conformed dimension
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.
Overview
A conformed dimension has the same structure, keys, and meaning no matter which fact table it's joined to. dim_date, dim_customer, and dim_product are the classic examples: rather than each department or data mart building its own version of "customer" with its own key scheme, the whole warehouse shares one dim_customer table. That's what lets you compare "revenue by region" from a sales fact table against "support tickets by region" from a support fact table — both join to the exact same region values from the same dimension.
Conformed dimensions are the backbone of Kimball's bus architecture: a matrix of business processes (rows) against shared dimensions (columns) that's used to plan a warehouse so that every new fact table reuses existing dimensions instead of creating incompatible, one-off copies.
Example
Copy code
-- One shared dim_date, referenced by every fact table in the warehouse
CREATE TABLE dim_date (
date_key INTEGER PRIMARY KEY,
full_date DATE,
quarter VARCHAR,
fiscal_year INTEGER
);
CREATE TABLE fact_sales (date_key INTEGER REFERENCES dim_date(date_key), revenue DECIMAL(12,2));
CREATE TABLE fact_support (date_key INTEGER REFERENCES dim_date(date_key), tickets_opened INTEGER);
-- Comparable across business processes because both use dim_date
SELECT d.quarter, SUM(s.revenue) AS revenue, SUM(t.tickets_opened) AS tickets
FROM dim_date d
LEFT JOIN fact_sales s USING (date_key)
LEFT JOIN fact_support t USING (date_key)
GROUP BY ALL;
A dimension can also be conformed at a subset of its attributes — two fact tables might share only the region and country columns of an otherwise different customer dimension, which is still enough to compare metrics at that shared grain.
DuckDB angle
There's nothing DuckDB-specific about conforming dimensions — it's a modeling discipline — but building all fact tables in a DuckDB or MotherDuck warehouse against one physical dim_date/dim_customer table (rather than copies per mart) is what makes cross-fact-table joins and comparisons actually correct.
Related terms
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.
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.
Fact constellation schema →A fact constellation schema (also called a galaxy schema) is a dimensional model with multiple fact tables that share one or more common dimension tables.
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.
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.
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.
FAQS
They let metrics from different fact tables and data marts be compared and combined consistently, because everyone joins to the same dimension keys and values instead of maintaining separate, potentially inconsistent copies.
