Outrigger dimensions

An outrigger dimension is a secondary dimension referenced by another dimension table rather than connecting directly to the central fact table. While permitted in rare cases where a dimension contains its own date or address reference, it must be used sparingly because chaining dimensions turns a clean star schema into a snowflake schema.

Defining the outrigger pattern

In standard Kimball modeling, all dimensions connect directly to fact tables. An outrigger occurs when a dimension contains a foreign key to another dimension. A classic example is dim_customer storing a foreign key to dim_date representing first_purchase_date_key. This design allows analysts to analyze customer acquisition cohorts using corporate fiscal calendars and holiday flags without duplicating calendar logic inside the customer dimension:

Clean outrigger:
dim_date <---- (first_purchase_date_key) ---- dim_customer <---- fact_orders

A short look at how the tables connect:

• The fact table links directly to dim_customer using customer_key.
• The customer dimension references dim_date to access corporate calendar attributes.

Snowflaking risk and flattening

Allowing multiple outriggers introduces snowflaking:

Snowflake anti-pattern:
dim_country -> dim_state -> dim_city -> dim_customer -> fact_orders

Notice how chains of normalized dimensions degrade query readability and require extra joins:

• Every extra join layer degrades SQL performance in BI tools.
• Complex join paths increase the chance of query planning mistakes.
• Analysts get confused navigating multi-tiered dimension trees.

When outriggers make sense

Keep outriggers limited to shared date dimensions or large standard reference tables that carry significant enterprise logic. For simple hierarchical attributes like customer city, state, or product category, always flatten the descriptive text directly into the primary dimension table.