Dimension Design Patterns

Overview

Beyond basic dimension tables, real-world dimensional models require specialized patterns to handle time-variant roles, degenerate keys, low-cardinality flags, deep hierarchies, and NULL values. Choosing the wrong pattern leads to query complexity, redundant joins, or data quality issues that surface late in development.

Key Principle: Denormalize aggressively within dimensions. Each dimension row should carry all the context a query needs without requiring additional joins.

Companion skill: For SCD Type 1/2 documentation standards (surrogate keys, effective dates, is_current flags), see design-workers/06-table-documentation/SKILL.md.

When to Use This Skill

• Designing dimension tables that play multiple roles (e.g., order_date vs ship_date)
• Deciding whether to create a junk dimension for low-cardinality flags
• Handling NULL foreign keys or missing dimension attributes
• Flattening hierarchies (org charts, product categories, geographies)
• Choosing between outrigger dimensions vs denormalization

Dimension Pattern Quick Reference