Dimension Table

A dimension table provides the descriptive context — the who, what, where, when, why — for the numeric measures stored in a Fact Table. Dimension tables are typically wide (many columns), shallow (fewer rows than fact tables), and denormalized in a Star Schema.

Common Dimensions

Dimension	Typical Attributes
dim_date	year, quarter, month, day, weekday, is_holiday, fiscal_period
dim_product	name, category, subcategory, brand, sku, price_tier
dim_customer	name, segment, region, tier, acquisition_date
dim_store	name, city, state, country, format, square_footage
dim_employee	name, department, role, hire_date, manager

Slowly Changing Dimensions (SCD)

Dimension attributes change over time. How to handle this:

SCD Type	Strategy	History	Example
Type 1	Overwrite old value	None	Customer email changes — just update it
Type 2	Add new row with versioning	Full	Customer moves cities — new row with start_date, end_date, is_current
Type 3	Add column for previous value	One level	current_city, previous_city
Type 4 (Hybrid)	Combine multiple SCD techniques within the same dimension	Mixed	Type 1 for email, Type 2 for city

Type 2 is the most common for analytical accuracy — it preserves the dimension value as it was when each fact was recorded. Type 4 (Hybrid) is used when different attributes within the same dimension have different change-tracking requirements.

Star vs Snowflake Dimensions

• Star Schema — Dimensions are fully denormalized (flat). dim_product contains category_name directly.
• Snowflake Schema — Dimensions are normalized. dim_product references dim_category via foreign key.