Dimensional modeling has a set of design patterns for situations that do not fit the standard fact-and-dimension structure: attributes that change over time, relationships that have no associated measures, and low-cardinality flags that would produce dimension table proliferation. This guide covers the patterns — when to use them and how to implement them correctly.
Dimensional modeling's standard patterns — fact tables at a defined grain with foreign keys to dimension tables — handle most analytical modeling requirements. But real business data contains situations that do not fit the standard model cleanly: attributes that change over time (a customer changes their address or segment), relationships that have no associated measures (insurance policy coverage periods), and collections of low-cardinality flags that would produce unwieldy dimension proliferation. Dimensional modeling has defined patterns for each of these situations.
Slowly Changing Dimensions (SCDs)
A slowly changing dimension is a dimension where the attribute values change over time. The classic example: a customer's region, segment, or account manager changes. In a standard dimension, changing the attribute overwrites the historical value — any analysis of historical orders suddenly reflects the current customer attributes, not the attributes at the time of the order.
Kimball's SCD types define different approaches to handling this:
**Type 0 (retain original)**: Never update the dimension attribute; it retains the value it had when the record was first loaded. Appropriate for attributes that should be fixed at record creation (original acquisition channel, original signup date).
**Type 1 (overwrite)**: Update the dimension attribute in place; historical records are not preserved. All analyses will reflect the current attribute value, even for historical facts. Appropriate when the historical value is not meaningful — a corrected typo in a name, for example. Operationally simple; analytically limited.
**Type 2 (add row)**: When an attribute changes, a new dimension row is added with the new attribute value and a start date; the old row gets an end date. The fact table's foreign key points to the specific dimension row that was current at the time of the fact. Historical facts reflect the attributes at the time they occurred; current facts reflect current attributes.
Type 2 is the most analytically powerful SCD type and the most operationally complex. The dimension table gains a row for every historical change, and the fact table's foreign key must be managed carefully — loads must resolve the foreign key to the correct (current-at-time-of-fact) dimension row.
**Type 3 (add column)**: Add a new column to the dimension for the "prior" value: current_region, prior_region. Allows analysis of "before and after" a change. Limited to a single historical value; cannot track more than one prior state.
**Hybrid approaches**: A Type 2 dimension that also carries a separate column with the current customer ID (enabling "show all historical activity for this customer" without needing to know all historical keys) is a common production compromise.
In dbt, Type 2 SCDs are implemented with snapshot models — dbt runs the snapshot on a defined schedule, detects changed rows, and inserts new rows with effective date ranges. The snapshot model's output is the slowly changing dimension table.
Factless Fact Tables
A factless fact table records the occurrence of an event that has no associated measure — the event itself is the fact. The classic example: student class attendance. The fact that a student attended class on a date has no obvious numeric measure; what matters is whether the attendance happened.
Factless fact tables typically have only foreign keys (to dimension tables) and no measure columns. The "measure" is the COUNT of rows matching a condition, derived by querying the table.
Applications in common analytical scenarios:
**Coverage facts**: Recording which products are covered by which promotions during which time periods. The fact that Product A is covered by Promotion B from date X to date Y has no numeric measure; the table simply records the existence of the relationship.
**Bridge tables for many-to-many relationships**: When a customer can belong to multiple customer segments, or a product can belong to multiple categories, a bridge table records each relationship as a row. The bridge table is a factless fact table that resolves the many-to-many relationship between the fact table and the dimension.
**Negative analysis**: "Which students did NOT attend class?" requires a factless attendance fact table: generate all student-date combinations (a cross join of the student dimension and the date dimension for the relevant period), left-join to the attendance fact table, and filter for no match. The absence of a row is the analytical finding.
Junk Dimensions
Fact tables frequently carry low-cardinality Boolean or flag attributes: is this a promotional transaction, is this a return, was this order expedited shipping, is this a B2B customer. These flags are attributes of the fact — they describe the transaction — but they are not true dimension attributes with meaningful relationships to other data.
The naive approach is to put these flags directly in the fact table. For a few flags, this is fine. For ten or twenty flags, the fact table becomes wide and the flags increase its row size without the structural benefits of a dimension relationship.
A junk dimension collects these miscellaneous flags into a single dimension with one row for each unique combination of flag values. A fact table with three Boolean flags produces at most 8 unique combinations (2 cubed); the junk dimension has at most 8 rows. The fact table stores a foreign key to the junk dimension row rather than three separate flag columns.
The junk dimension reduces fact table row width and provides a named grouping for related attributes. It is particularly valuable when the same set of flags appears in multiple fact tables — a single shared junk dimension eliminates duplication.
Degenerate Dimensions
A degenerate dimension is a dimension attribute that has no corresponding dimension table — it lives directly in the fact table as a foreign key that points to nothing. The most common example is a transaction identifier: the order number, invoice number, or transaction ID.
These identifiers are not useful for analytical grouping or filtering in the way a product or customer dimension is. They serve as a reference key for drilling back to source system records or grouping line items within an order. Including them in the fact table without a dimension table is the correct modeling choice — there are no attributes to put in the dimension.
In analytical tools, degenerate dimensions appear as attributes on the fact table that can be filtered or grouped but do not have associated dimensional context.
Role-Playing Dimensions
A role-playing dimension is a single dimension table that is used multiple times in the same fact table, each use representing a different role. The most common example is the date dimension used as order date, ship date, and delivery date in an orders fact table.
Each use of the date dimension in the fact table is a separate foreign key (order_date_key, ship_date_key, delivery_date_key), each pointing to the same date dimension table. In a relational database or data warehouse, this typically requires creating views of the date dimension with role-specific aliases (date_dim_order, date_dim_ship, date_dim_deliver) so that each can be joined separately in a query.
In Tableau, role-playing dimensions require separate data source connections to the same underlying dimension table with different join aliases, or calculations that create separate date attributes from the multiple date keys.
Our data architecture practice designs dimensional models that handle these patterns correctly from the start — contact us to discuss data warehouse modeling for your analytical environment.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →