Pirate Dev's Recommendation

When designing a dimension, you must first choose between Type 0, Type 1 and Type 2 for each attribute.

• Default to Type 2: When in doubt, always choose Type 2 to preserve data for future analysis.
• Avoid Type 3: Avoid this approach unless a highly specific requirement demands it, such as a planned sales territory realignment requiring dual reporting during a transition period.

Once the base type is settled, you may face distinct challenges with Type 2 dimensions regarding row growth or complex query requirements.

If rapid row growth is becoming a real performance issue:

• Type 4 (Mini-Dimension): Isolate rapidly changing attributes into a mini-dimension if they can be neatly grouped into banded ranges, such as age groups or income levels.

If reports frequently need to evaluate historical facts against an entity's current state:

• Type 5 (Outrigger): Upgrade your mini-dimension to Type 5 when reports need to group historical facts by an entity's current profile.
• Type 7 (Dual Keys): Implement this if reporting requires seamless access to both historical context and current state simultaneously. It requires exposing named views or using a semantic layer so analysts do not accidentally join the wrong key and silently misattribute facts to the wrong version of the entity.
• Avoid Type 6: Avoid this pattern where possible. Type 7 provides the same analytical capabilities without requiring expensive retroactive updates. However, Type 6 remains an acceptable fallback for very small dimensions or when engineering constraints prevent you from adding the new durable key column to an existing fact table that Type 7 requires.

Additionally, if the business primarily requires current-state reporting but you want to preserve history for future use, you can optimize query performance with a physical split:

• Type 4 (Industry Split Tables): Split the dimension into a Current table and a History table. Maintain the durable key in your fact tables and design the Current table as a Type 1 dimension using the durable key as the primary key. The History table retains all superseded versions using proper surrogate keys.

  Trade-offs: Total row count remains unchanged, full-timeline queries require a UNION across both tables and point-in-time historical reporting forces slower date-range joins instead of direct surrogate key lookups.