What is an outrigger dimension, and when is attaching one to another dimension justified?

## The default and the exception A dimensional model's default is flat dimensions: the fact table joins a dimension, and every attribute an analyst needs is a column on that one row. That flatness is the whole ergonomic argument for the design — one join, no hierarchy to navigate, obvious column names. An outrigger is the sanctioned exception. It is a dimension table that itself holds a foreign key to another dimension table: ```sql CREATE TABLE dim_store ( store_key INTEGER PRIMARY KEY, store_name VARCHAR(80) NOT NULL, store_type VARCHAR(30) NOT NULL, region VARCHAR(40) NOT NULL, first_open_date_key INTEGER NOT NULL -- outrigger to dim_date ); ``` Now `dim_store` can be joined to `dim_date` to answer "revenue by store opening fiscal quarter", reusing the full calendar — fiscal periods, holiday flags, day-of-week — without copying dozens of columns into every store row or re-deriving fiscal logic in a report. ## When it earns its place Three conditions, and it is worth being able to state them: 1. **The referenced table is a genuinely shared, standard dimension.** Date is the archetype; a currency or a geography dimension can qualify. The value comes from reusing one definition that other parts of the model already trust. 2. **The attribute set is large enough that copying it in would be wasteful or inconsistent.** A calendar has dozens of attributes; flattening all of them into a host dimension for one date is silly, and flattening a few of them invites someone to derive the missing one badly. 3. **The relationship is stable and single-valued.** One store has one opening date. If the relationship were multi-valued you would need a different construct entirely. A second use is attaching an existing demographic or profile dimension to a host dimension so that the host can expose a current profile without inlining its columns. ## When to flatten instead Most of the time, flatten. If reports only ever want the opening year and month name, put `first_open_year` and `first_open_month_name` directly on `dim_store`. Two extra columns on a small dimension cost nothing, remove a join, and remove a question from every analyst who meets the model. The rule of thumb is: outrigger when you need the referenced dimension's whole vocabulary, flatten when you need two attributes from it. ## The costs to name - **An extra join.** Small in itself, but each one is a step the analyst must know to take, and the ergonomic argument for the flat star is exactly that they should not have to. - **Ambiguity when the outrigger is also joined directly.** If the fact already joins `dim_date` as the transaction date and the store outrigger reaches `dim_date` as the opening date, a query touching both must alias carefully or it will produce a confusing result. Role naming discipline applies here too. - **Independent change.** The host dimension row and the outrigger row version on different schedules, so a query reading them together may mix a historical host attribute with a current outrigger attribute unless you are deliberate about it. - **Drift.** One outrigger is a documented exception; five outriggers is a normalized dimension nobody agreed to build, and the model quietly loses the property that made it easy to query. ## How it differs from neighbouring ideas An outrigger is a reference from one dimension to another *independently meaningful* dimension. It is not a bridge table — bridges resolve multi-valued relationships. It is not a mini-dimension, which is a volatility split whose key normally lands on the fact table. And it is distinct from snowflaking a hierarchy out of a dimension for normalization's sake: the outrigger points at a table that exists in its own right and is used elsewhere, whereas a snowflaked hierarchy level exists only as a decomposed piece of its parent dimension. ## What an interviewer is checking That you know the term, that you can give the date-inside-a-non-date-dimension example, and — most of all — that your instinct is "sparingly". A candidate who reaches for outriggers routinely is rebuilding a normalized schema inside a dimensional model without noticing; a candidate who has never heard the word but says "I'd just copy the opening year onto the store row" has the right instinct with the wrong vocabulary. The good answer has both.