Types of Dimensions

Concepts covered: Dimension Tables

Dimensions are the descriptive lookup tables that give meaning to fact rows. A fact row says 'customer_sk = 42, amount = $100.' The dimension tells you customer 42 is 'Alice Zhang from Seattle in the Enterprise segment.' Without dimensions, facts are just numbers.

Conformed Dimensions

A conformed dimension is shared across multiple fact tables. dim_customer is referenced by fact_sales, fact_returns, fact_support_tickets. The same customer_sk and the same attribute definitions. This is what makes cross-domain analysis possible: 'show me customers who bought product X but also filed a support ticket.'

Without conformed dimensions, each fact table defines 'customer' differently. Sales has one customer table, support has another. Their definitions of 'active customer' differ. Cross-domain queries produce inconsistent results.

Role-Playing Dimensions

The same physical dimension used multiple times in one fact table, each time in a different role. The classic example: dim_date appears as order_date, ship_date, and deliver_date. Three foreign keys in the fact table, all pointing to the same dim_date.

To query revenue by order month, join on order_date_sk. To query revenue by ship month, join on ship_date_sk. Same table, different role, different analytical perspective.

Junk Dimensions

Low-cardinality flags (is_gift, is_prime, payment_method) that do not belong in any real dimension get consolidated into a junk dimension. Instead of five boolean columns on the fact table, one FK to a 32-row dim_order_flags table. The fact table stays narrow, and adding new flags does not require altering the fact table.

Degenerate Dimensions

Transaction identifiers (invoice_number, receipt_id) that live directly on the fact table with no separate dimension. They are dimensions in the conceptual sense (they describe the transaction) but do not warrant their own table because they have no additional attributes. Creating dim_invoice with one column would be a one-to-one dimension anti-pattern.

The Date Dimension

Every data warehouse has a dim_date table: one row per calendar date, pre-populated for 100+ years. It contains every attribute you might filter or group by: day_of_week, month_name, quarter, fiscal_year, is_weekend, is_holiday. Without it, every query that groups by quarter must compute the quarter from the date, and every team computes it differently.

About this lesson

This section is part of the Star Schemas lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.