Junk, Mini, and Role-Playing Dimensions

The bridge table solved a structural problem — a genuine many-to-many that no foreign key could carry. The patterns in this chapter solve smaller, scrappier problems: a fistful of boolean flags nobody wants to model, a customer profile that changes faster than the dimension it lives in, an order with three dates all pointing at the same calendar. None of them breaks the star the way an unresolved many-to-many does. They just make it worse in slow, cumulative ways — a fact table bloated with low-cardinality columns, a dimension churning out near-duplicate rows, a BI user staring at three columns named quarter with no idea which one they grabbed.

Each pattern here is a deliberate trade — a little indirection for a lot of tidiness — and each one has a failure mode when applied without a reason. The judgement that ran through bridge tables runs through these too: reach for a pattern when the shape of your data forces the issue, not because it looks sophisticated on the architecture diagram.

Junk dimensions: where flags go to live

Every transaction fact accumulates debris. The bookshop’s orders carry is_gift_wrapped, is_expedited, is_first_order, and a channel that’s one of web, store, or phone. None of these is a measure — you don’t sum a gift-wrap flag. None deserves its own dimension — a one-column dim_gift_wrap with two rows is ceremony, not modeling. And left on the fact, each one is a low-cardinality column repeated across millions of rows, cluttering every query and inviting analysts to treat the fact table as a place to browse attributes.

The junk dimension sweeps them into one small table: every combination of the flags gets a row and a surrogate key, and the fact stores just that key. Three booleans and a three-value channel make at most 24 combinations — a 24-row dimension replacing four columns of noise. The name is honestly self-deprecating — it’s a home for junk — but the tidiness it buys is real, and so is the modeling question hiding inside it: which combinations get rows?

Two ways to populate it

There are two honest strategies, and they differ in when a combination is born.

Cross-join the domains. If every flag’s domain is known and small, build the complete combination space up front:

-- models/marts/dim_order_flags.sql
{{ config(materialized='table') }}

with gift_wrap as (
    select * from (values (true), (false)) as t(is_gift_wrapped)
),
expedited as (
    select * from (values (true), (false)) as t(is_expedited)
),
first_order as (
    select * from (values (true), (false)) as t(is_first_order)
),
channels as (
    select * from (values ('web'), ('store'), ('phone'), ('unknown')) as t(channel)
),

combos as (
    select
        g.is_gift_wrapped,
        e.is_expedited,
        f.is_first_order,
        c.channel
    from gift_wrap g
    cross join expedited e
    cross join first_order f
    cross join channels c
)

select
    {{ dbt_utils.generate_surrogate_key([
        'is_gift_wrapped', 'is_expedited', 'is_first_order', 'channel'
    ]) }} as order_flags_sk,
    is_gift_wrapped,
    is_expedited,
    is_first_order,
    channel
from combos

Thirty-two rows, minted once, complete forever. Every combination the fact could ever compute already has a home, including the ones no order has produced yet. The dimension never has to race the fact — a brand-new combination arriving at 9 a.m. joins to a row that has existed since the model was first built.

Select the observed combinations. When a domain isn’t enumerable — say channel grows values you don’t control — build the dimension from what actually occurs:

with combos as (
    select distinct
        coalesce(is_gift_wrapped, false)  as is_gift_wrapped,
        coalesce(is_expedited, false)     as is_expedited,
        coalesce(is_first_order, false)   as is_first_order,
        coalesce(channel, 'unknown')      as channel
    from {{ ref('stg_orders') }}
)

select
    {{ dbt_utils.generate_surrogate_key([
        'is_gift_wrapped', 'is_expedited', 'is_first_order', 'channel'
    ]) }} as order_flags_sk,
    *
from combos

The select distinct is the dimension — the grain is “one row per combination that occurs.” It’s compact and self-maintaining, and it has a subtlety worth understanding: the fact doesn’t ref() this dimension, so dbt’s DAG doesn’t order one before the other. That’s fine in a batch world — both models read the same staging table in the same run, so every combination the fact computes is also visible to the dimension’s select distinct, and the referential-integrity test at the end of dbt build passes. It stops being fine if the two models ever read different snapshots of the source — which is one more reason the cross-join build, with its complete domain, is the safer default when the domains allow it.

Choose by domain shape. Enumerable, low-cardinality flags — the common case — want the cross-join. Open-ended values want the observed build, plus a test that screams when cardinality grows past what a junk dimension should hold. A junk dimension with fifty thousand rows isn’t junk anymore; it’s a real dimension that deserves a name and an owner.

The fact side, and the unknown member

The fact computes the same surrogate key from its own flag columns — same hash, same column order, same coalesced defaults:

-- inside fct_order_items (or a header-grain fact)
{{ dbt_utils.generate_surrogate_key([
    "coalesce(is_gift_wrapped, false)",
    "coalesce(is_expedited, false)",
    "coalesce(is_first_order, false)",
    "coalesce(channel, 'unknown')"
]) }} as order_flags_sk

The coalesce calls are load-bearing. With hashed surrogate keys there’s no magic -1 row — the unknown member is simply the hash of the coalesced defaults, minted identically on both sides. An order with a null channel hashes to the same key the dimension minted for (false, false, false, 'unknown'), the join resolves, and no fact row ever carries a null foreign key. You still materialize that row in the dimension — that’s why 'unknown' sits in the cross-join’s channel domain — because a key that resolves to nothing is an orphan whether or not it was deterministic. The rule from the dimension chapters holds here with extra force: junk dimensions are where nulls congregate, and every null must be routed to a value before the hash on both sides, or the fact and the dimension will hash different things and quietly disagree.

Testing it

Two tests, both cheap. Uniqueness on the surrogate key proves the hash inputs form the grain, and a combination test pins the grain to the named columns so a careless edit to the hash can’t drift them apart:

models:
  - name: dim_order_flags
    columns:
      - name: order_flags_sk
        data_tests:
          - unique
          - not_null
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - is_gift_wrapped
            - is_expedited
            - is_first_order
            - channel

The next chapter builds the full test battery for the star; the junk dimension just needs these two plus a relationships test from the fact — and it’ll pass, because the unknown member made it honest.

Mini-dimensions: for the attributes that won’t sit still

Some customer attributes are geological — name, city, signup date. Others are weather: loyalty tier, spend band, preferred format. The weather attributes can shift monthly, and if you version them with Type 2 rows, dim_customer grows a new version every time someone crosses a spend threshold — thousands of near-duplicate rows per customer, each differing in one volatile column, dragging twenty stable columns along for the ride. The SCD chapter named the cure Type 4: split the volatile attributes into a mini-dimension. This is where we actually build it — including the part that chapter deferred, the fact carrying two keys and looking up the right profile per event.

The profile dimension

The mini-dimension is a junk dimension in disguise: the distinct combinations of the banded volatile attributes, each with a surrogate key. Banding is what keeps it small — you don’t store a spend of $1,847.23, you store the band 1000-2500, because a dimension row per distinct dollar amount is just the fact table wearing a costume.

-- models/marts/dim_customer_profile.sql
{{ config(materialized='table') }}

with tiers as (
    select * from (values ('bronze'), ('silver'), ('gold'), ('unknown')) as t(loyalty_tier)
),
bands as (
    select * from (values
        ('0-100'), ('100-500'), ('500-1000'), ('1000-2500'), ('2500+'), ('unknown')
    ) as t(spend_band)
),
formats as (
    select * from (values ('paperback'), ('hardcover'), ('ebook'), ('unknown')) as t(preferred_format)
)

select
    {{ dbt_utils.generate_surrogate_key([
        'loyalty_tier', 'spend_band', 'preferred_format'
    ]) }} as customer_profile_sk,
    t.loyalty_tier,
    b.spend_band,
    f.preferred_format
from tiers t
cross join bands b
cross join formats f

A hundred-odd rows covering every profile any customer will ever have. Note what’s not here: no customer_id. The mini-dimension doesn’t know about customers — it’s a catalog of possible profiles, and many customers share each one. That’s the trick that stops the churn: when a customer moves from silver to gold, no dimension row is written anywhere. The row for “gold, 1000–2500, paperback” already exists. What changes is which row her next fact points at.

The two-key fact, and the profile at event time

Here’s the piece that makes Type 4 real. The fact carries two customer-shaped keys — one to the stable dimension, one to the profile that was current when the event happened:

fct_order_items
├── customer_sk          → dim_customer          (who, stable attributes)
└── customer_profile_sk  → dim_customer_profile  (what they looked like that day)

To assign customer_profile_sk at build time you need to know each customer’s profile as of each order date — which means you need profile history. The volatile columns come out of dim_customer but they don’t stop being versioned; the versioning just moves somewhere cheaper. A dbt snapshot over a slim staging model that carries only the volatile columns does the job:

# snapshots/customer_profile_snapshot.yml
snapshots:
  - name: customer_profile_snapshot
    relation: ref('stg_customer_profiles')
    config:
      unique_key: customer_id
      strategy: timestamp
      updated_at: updated_at
      dbt_valid_to_current: "to_date('9999-12-31')"

The snapshot is narrow — customer_id, the three volatile columns, updated_at — so its Type 2 churn is cheap: new versions carry four columns, not twenty-four. Then the fact build does a windowed lookup, exactly the point-in-time join from the SCD chapter, aimed at the profile instead of the whole customer:

-- inside fct_order_items
select
    -- ... grain key, dimension keys, measures as before ...

    {{ dbt_utils.generate_surrogate_key([
        "coalesce(p.loyalty_tier, 'unknown')",
        "coalesce(p.spend_band, 'unknown')",
        "coalesce(p.preferred_format, 'unknown')"
    ]) }} as customer_profile_sk

from order_items oi
join orders o
  on oi.order_id = o.order_id
left join {{ ref('customer_profile_snapshot') }} p
  on o.customer_id = p.customer_id
 and o.order_date >= p.dbt_valid_from
 and o.order_date <  p.dbt_valid_to

Walk through what happens. An order placed on March 3rd joins to the snapshot row whose window contains March 3rd — the profile the customer had that day — and hashes its banded values into customer_profile_sk. When she crosses into gold on March 20th, the snapshot closes one window and opens another; her April orders hash to the gold profile’s key. Nothing about March’s facts changes, ever. And the left join plus coalesce handles the customer with no profile history at all — an order predating the snapshot’s first run lands on the all-'unknown' profile row, which the cross-join build already minted. The unknown member and the mini-dimension aren’t separate patterns; the second doesn’t work honestly without the first.

The query payoff is that profile history becomes sliceable without touching dim_customer at all. “Revenue by loyalty tier at time of purchase” is a one-hop join from the fact to dim_customer_profile — no point-in-time logic in the query, because the point-in-time work was done once, at load. Compare the Type 2 alternative: the same question against a versioned dim_customer needs the as-of join every single time, and a dim_customer with tier churn in it might carry fifty versions per customer to make it possible.

One refinement Kimball notes and the bookshop can skip: you can also stamp the current profile key onto dim_customer as an attribute, so “what do gold customers buy?” (today’s tier, applied to all history) doesn’t have to route through the fact. That’s convenient and mildly dangerous — it’s a Type 1 attribute pointing at a Type 4 structure, and someone will eventually confuse it with the at-event-time key. Add it when analysts actually ask the current-profile question; label it loudly.

Role-playing dimensions: one table, several hats

The accumulating snapshot from the fact-table chapters is where this pattern earns its keep. fct_orders tracks each order’s march through fulfillment — order_id, customer_sk, order_date_sk, ship_date_sk, delivery_date_sk, order_status, days_to_ship, days_to_deliver — and three of those columns are dates. You already have one beautifully built dim_date. The wrong instinct is to build three date dimensions. The right one is to let the single dim_date play three roles.

Physically there’s one calendar. Logically it appears in the model three times, once per foreign key, each appearance wearing the name of its role. Kimball calls this a role-playing dimension, and it’s the most common advanced pattern you’ll meet — facts are lousy with dates, and dates are expensive to model well, so the one good calendar gets reused everywhere.

There are two ways to express the roles, and the difference is where the renaming happens.

Physical views: rename at build time

The dbt-idiomatic move is a thin view per role:

-- models/marts/dim_date_order.sql
{{ config(materialized='view') }}

select
    date_sk          as order_date_sk,
    date_day         as order_date,
    calendar_year    as order_year,
    calendar_quarter as order_quarter,
    calendar_month   as order_month,
    day_name         as order_day_of_week,
    is_weekend       as order_is_weekend
from {{ ref('dim_date') }}

dim_date_ship and dim_date_delivery are the same view with the prefix swapped. The materialization is view, so you’re not copying the calendar three times — you’re renaming its columns three ways, at zero storage cost. Each fact key joins to its own role, and the renaming is the entire point: a BI user who drags in order_quarter never wonders which of three dates they sliced by, and an unqualified quarter column that could mean any of them is a support ticket waiting to happen.

Query-time aliasing: rename in the join

The alternative keeps one physical dim_date and aliases it per query:

select
    d_order.year        as order_year,
    d_order.month_name  as order_month,
    avg(f.days_to_deliver) as avg_days_to_deliver
from fct_orders f
join dim_date d_order    on f.order_date_sk    = d_order.date_sk
join dim_date d_delivery on f.delivery_date_sk = d_delivery.date_sk
where d_delivery.is_weekend
group by 1, 2

Same table, two aliases, two roles — “orders that delivered on a weekend, by order month,” a question that needs both roles in one query and answers it in ten lines. For anyone writing SQL by hand this is strictly fine; the aliasing is explicit and the intent is readable.

Which one, and why the BI layer decides

The honest answer is that the choice is rarely about SQL — it’s about what sits on top of it.

The views approach makes every tool happy at the cost of catalog clutter. Each role is a first-class object with unambiguous column names; drag-and-drop tools, auto-generated data catalogs, and semantic layers all see three cleanly named date tables and never need to understand aliasing. The cost is 3× the objects — a warehouse with a dozen multi-date facts sprouts dozens of near-identical calendar views, and someone eventually asks why.

The aliasing approach keeps the catalog minimal but pushes the role logic into every consumer, and consumers vary wildly in how gracefully they take it. Power BI’s model allows only one active relationship between two tables — a second date key relates to the same calendar through an inactive relationship you must invoke per-measure with USERELATIONSHIP(), or you import the date table multiple times, which is the views approach reinvented inside the tool. Looker handles roles natively: the same LookML view joins into an explore several times under different names via from: aliasing, which is query-time aliasing formalized. Hand-written SQL and dbt models alias freely. So the real question is: who consumes the star? A drag-and-drop BI population wants the views; a SQL-fluent team with a semantic layer can skip them. (Tool behaviors here are stable and well documented, but they do evolve — check your BI tool’s current story before committing the pattern.)

One more accumulating-snapshot interaction: delivery_date_sk is unknowable for an order that hasn’t delivered. It must not be null — the never-null rule for fact keys doesn’t bend — so it points at the calendar’s designated not-yet member (the “hasn’t happened” date row the date-dimension build set aside) until the milestone lands and the merge updates it. Role-playing views inherit that member automatically, since they’re just renamings; queries through dim_date_delivery see undelivered orders grouped under the not-yet row rather than silently dropped by an inner join.

Outriggers: the join you allow yourself sparingly

Occasionally a dimension wants to reference another dimension. dim_store might point at a dim_region carrying demographic rollups — population, median income, market classification — that you’d rather not copy onto every store row. That second hop, a dimension hanging off a dimension, is an outrigger:

fct_order_items ──> dim_store ──> dim_region

It works, and Kimball permits it, with a raised eyebrow. The permitted case has a specific shape: the outrigger is a genuinely separate thing, with its own grain, its own source, its own refresh cadence — census demographics updated yearly have no business being maintained inside a store dimension refreshed nightly. The classic sanctioned example is a date outrigger: dim_customer carrying a first_order_date_sk that points at dim_date, so “customers who first ordered in Q4” gets the full calendar for free instead of re-deriving fiscal quarters on a raw date column.

The eyebrow exists because every outrigger is a snowflake in miniature, and snowflakes metastasize. Region points at country, country points at economic zone, someone normalizes genre out of dim_book because an outrigger worked last time — and three refactors later your star is the snowflake schema that chapter spent its length warning you about, a two-hop join everywhere the star promised one. The test before adding one: is this actually its own well-defined thing with its own maintenance story, or am I saving a few redundant columns? Redundant columns are what dimensions are made of. Save the outrigger for the census data.

Value-band and behavioral dimensions

Two related patterns hide inside the mini-dimension build above, and each deserves its own name.

A value-band dimension turns banding itself into data. The dimension chapter hard-coded price_band as a case expression inside dim_book — fine, until the business wants to re-band. Bands baked into a case statement change only when an engineer edits a model; bands stored as rows change when someone edits a table:

-- models/marts/dim_order_size_band.sql
{{ config(materialized='table') }}

select * from (values
    ('small',  0::number(10,2),    25::number(10,2)),
    ('medium', 25::number(10,2),   100::number(10,2)),
    ('large',  100::number(10,2),  100000::number(10,2))
) as t(band_name, min_amount, max_amount)

Queries attach the band with a range join — on f.extended_price >= b.min_amount and f.extended_price < b.max_amount — so re-banding is an update to three rows, and no fact is touched. The trade: a non-equi join, which the warehouse handles fine at dashboard scale but which no BI drag-and-drop understands natively. Most teams band at build time for the common bands (the case in the dimension) and keep a band table for the ones the business fiddles with. Both are value-banding; the difference is who’s allowed to change the definition, and how fast.

A behavioral dimension is a dimension attribute computed from the facts. The spend_band in dim_customer_profile doesn’t come from any source system — it comes from aggregating fct_order_items, banding the result, and feeding it back into a dimension. That loop — facts → aggregation → banded attribute → dimension → back into new facts — is how RFM segments, “lapsed customer” flags, and lifetime-value tiers get built, and it has one rule: compute it at load time on a declared cadence, never at query time. A behavioral attribute recomputed inside every query is a different number in every dashboard; one stamped into the mini-dimension nightly is a fact about the customer as-of that night, versioned like anything else. The mini-dimension is the natural home precisely because behavioral attributes are the fastest-changing attributes a customer has.

Supertype and subtype: heterogeneous products

The last pattern arrives with success. The bookshop starts selling e-books and audiobooks, and suddenly dim_book has a problem: physical books have a format and a page_count, e-books have a file_size_mb and a DRM flag, audiobooks have a narrator and a duration_minutes. One wide table with every column means every row is mostly null, and the nulls carry meaning (“not applicable”) that null does a bad job of carrying.

The Kimball answer is a supertype/subtype split. One core dimension carries the attributes every product shares — and every fact key points at it:

dim_product (supertype)          dim_product_ebook (subtype)
  product_sk  ◄───────────────────  product_sk   (same key, 1:1)
  product_id                        file_size_mb
  title                             drm_scheme
  author
  genre
  list_price
  product_type  ── 'ebook'        dim_product_audiobook (subtype)
                                     product_sk
                                     narrator
                                     duration_minutes

The subtype tables share the supertype’s surrogate key one-to-one — no new key to mint, no bridge, just an optional extension per type. Cross-catalog queries (“revenue by genre”) touch only the supertype and never see a null. Type-specific queries (“audiobook hours sold by narrator”) join the subtype in, and the product_type column tells any query which extension applies. Facts stay uniform: one product_sk, whatever the product is.

Honest guidance on when to bother: later than you think. Three or four type-specific columns per subtype don’t justify the split — take the wide table, take the nulls, keep the model one-hop. The split pays when subtypes have many attributes each, or different teams own them, or the heterogeneity extends to the facts themselves — a lending library’s loan events and a shop’s sale events want different fact tables entirely, each pointing at the shared product supertype. That’s the full heterogeneous-products pattern: shared core dimension, custom facts per line of business, and the supertype is what makes cross-business questions answerable at all.

Final thoughts

None of these patterns is a default, and all of them are the same pattern if you squint: put the attribute where its rate of change and its cardinality say it belongs. Fast-changing banded attributes leave the big dimension for a mini-dimension. Correlated low-cardinality flags leave the fact for a junk dimension. A calendar used three ways stays one table wearing three names. Attributes shared by every product type stay in the supertype; the rest move out. The star doesn’t get more complicated when you apply these well — it gets more honest, because each table’s grain and churn finally match its contents. The skill isn’t knowing the catalog; it’s hearing the specific creak in your model — the churning dimension, the flag-studded fact, the ambiguous quarter — and reaching for the one pattern that answers it. A modeler who deploys a mini-dimension because the dimension might churn someday is gold-plating. One who deploys it because dim_customer grew forty versions per customer in a quarter is doing the job.

Next: Tests That Keep a Star a Star