Dimension Tables and Surrogate Keys

Dimensions are where the facts get their meaning — wide, denormalized, and keyed by a surrogate the warehouse mints itself. Anatomy, the hash-vs-sequence debate settled in SQL, and the unknown member every dimension owes its facts.

The facts hold the numbers, but a number with no context is trivia. sum(extended_price) = 4,208 means nothing until you can attach it to a genre, a customer segment, a quarter. Dimensions are where that context lives — the nouns and adjectives of the business — and they’re built on instincts that are the exact opposite of the ones the app database trained into you.

What a dimension is made of

A dimension table has four kinds of column, and the surprising one is that redundancy is now a feature.

  • A surrogate key — the primary key, a warehouse-owned identifier the facts point at. This is the column that took the trouble to earn its own section below.
  • A natural key — the business identifier from the source, like the source system’s book_id. You keep it so you can trace a row back to where it came from and look it up when loading facts.
  • Descriptive attributes — the wide part. Title, author, genre, format, price band; name, email, city, segment. Text you filter and group by, stored right on the row.
  • Hierarchies — the roll-up paths. A book belongs to a genre belongs to a category; a date belongs to a month belongs to a quarter belongs to a year. In a dimension these live as plain columns side by side, not as a chain of joined tables.

The word doing the heavy lifting is wide. A dim_book might carry twenty columns where the source books table had eight, because everything you might want to slice by is denormalized onto the one row.

Wide on purpose

In the OLTP world, a genre column repeated across every book row would be a normalization sin — store the genre once in a genres table, reference it by id, and never let two rows disagree. That instinct is correct for writes and wrong for reads.

A dimension inverts it deliberately. The genre name sits right on dim_book, not a join away in a dim_genre table. Yes, “Fiction” is repeated across a thousand book rows. That repetition costs a little storage and buys two things worth far more: every genre query is a group by genre with no join, and an analyst reading the table sees the whole book in one row instead of chasing keys through a schema diagram. Dimensions are small relative to facts — thousands of books, not billions of order lines — so the storage cost of denormalizing them is rounding error, and the query and comprehension wins are constant. When you do keep the hierarchy as separate joined tables it’s called a snowflake schema, and outside a few edge cases it’s a mistake: you’ve reintroduced the join marathon to save storage you weren’t short on.

Why the warehouse mints its own keys

The source already has a book_id. Why not just use it as the dimension’s primary key and the fact’s foreign key? Because tying your model to the source’s key quietly forecloses things you’ll need.

A surrogate key is a key the warehouse owns — decoupled from the source, meaningless outside the model, minted at build time. Three reasons it’s worth the trouble:

  • History. Chapter 8 is about slowly changing dimensions, where a single book gets multiple rows over time as its attributes change. The source’s book_id can’t be the primary key then — it’s no longer unique. A surrogate can, because the warehouse assigns a fresh one per version.
  • Source drift. Source systems renumber, merge, migrate. A book that was id 4471 becomes id 90012 after a platform migration. If that id is baked into a billion fact rows, the migration is a catastrophe; if the facts point at a surrogate, the drift is absorbed in one dimension load.
  • Clean joins. Surrogates are a single uniform column — one key type, one join condition — instead of the source’s grab bag of composite keys, strings, and UUIDs of varying widths.

Hashed keys vs. sequences — in SQL, not in the abstract

Two honest ways to mint a surrogate, and the difference isn’t taste — it’s an architectural fork you can see in the SQL.

Hashed keys, the dbt-idiomatic default, run the natural key through dbt_utils.generate_surrogate_key. It’s worth knowing exactly what that macro compiles to, because two of its behaviors matter later:

-- {{ dbt_utils.generate_surrogate_key(['book_id']) }} compiles to (roughly):
md5(coalesce(cast(book_id as varchar), '_dbt_utils_surrogate_key_null_'))

-- multi-column keys concatenate with a separator before hashing:
-- {{ dbt_utils.generate_surrogate_key(['order_id', 'line_number']) }}
md5(coalesce(cast(order_id as varchar), '_dbt_utils_surrogate_key_null_')
    || '-' ||
    coalesce(cast(line_number as varchar), '_dbt_utils_surrogate_key_null_'))

Behavior one: it’s deterministic — the same natural key always hashes to the same surrogate, on any run, in any environment, with no coordination and no lookup. That determinism is why the facts in the last two chapters could compute their foreign keys directly: hash the natural key on both sides and they match by construction.

Behavior two: null doesn’t hash to null. The coalesce swaps a null natural key for the sentinel string _dbt_utils_surrogate_key_null_ before hashing, so every null input — in every fact, every dimension, every run — produces the same real, non-null key. That looks like a footnote and is actually a load-bearing wall: it’s the hook the unknown member hangs on, one section down.

Two caveats to keep in the drawer. Width: MD5 emits a 32-character hex string, so every key column is a 32-byte varchar instead of an 8-byte integer — fatter storage, fatter join columns, and no meaningful range pruning on the key itself (hashes are deliberately unordered; when Snowflake prunes your fact scans it’ll be on a real date column, never on date_sk). Collisions: MD5 is 128 bits, and the birthday math says you’d need around 2^64 distinct keys — twenty-odd quintillion — before a collision is likely. Your dimension will not get there; it’s a rounding error of a risk for surrogate keys, though it’s the reason some shops standardize on SHA-256 anyway, trading even fatter keys for the extra margin.

Integer sequences are the other camp — Snowflake’s SEQUENCE or an IDENTITY column, assigning 1, 2, 3 as rows arrive:

create sequence if not exists seq_customer_sk;

insert into dim_customer (customer_sk, customer_id, full_name, city, segment)
select seq_customer_sk.nextval, customer_id, full_name, city, segment
from stg_customers;

Compact, ordered, pleasant to read. But watch what it does to every fact build from now on. The fact can’t compute customer_sk anymore — the number was assigned, not derived — so the only way to get it is to ask the dimension:

-- every fact load now carries a lookup join per dimension
select
    d.customer_sk,          -- fetched, not computed
    s.order_id,
    s.quantity,
    s.extended_price
from staged_order_items s
left join dim_customer d
  on d.customer_id = s.customer_id;

One dimension, one extra join. Five dimensions, five extra joins — on every incremental fact run, forever, with a hard ordering dependency: every dimension must finish loading before any fact that references it can start, or the lookup finds nothing. And there’s a dbt-specific landmine on top: sequences are stateful, so a dbt run --full-refresh on the dimension renumbers every key, and every fact row loaded against the old numbering is silently orphaned. Deterministic hashes shrug at a full refresh — the same inputs mint the same keys.

That’s the real shape of the trade: sequences buy narrow keys at the price of lookups, load ordering, and refresh fragility; hashes buy independence at the price of width. For dbt, hashing wins and this series uses it throughout. Whichever you pick, the ironclad rule is the same: use the identical key expression in the dimension and in the fact — same columns, same types, same order — or the join silently returns nothing. More on “silently” below.

The unknown member: the row every dimension owes its facts

Here’s the most important pattern in this chapter, and the one most tutorials skip entirely.

Real fact data is dirty. An order line arrives with no book_id — a data-entry gap, a bulk import, a gift-card sale that never had one. What happens downstream depends on how you modeled for it, and the default is quietly terrible. If the fact’s book_sk is null and your revenue dashboard inner-joins to dim_book, the row vanishes — real revenue, silently dropped, and the total at the bottom of the dashboard no longer matches finance’s number. Switch to a left join and the row survives but groups under a blank label nobody can click on. Either way, an analyst eventually spends an afternoon discovering that “total revenue by genre” and “total revenue” disagree.

Kimball’s fix is old, simple, and non-negotiable: fact foreign keys are never null. Every fact row points at a real dimension row, and when the truth is “we don’t know which one,” it points at a designated unknown member — a placeholder row that every dimension carries for exactly this purpose. In sequence-keyed warehouses this is the famous -1 row (customer_sk = -1, name “Unknown”); with hashed keys, the same idea wears a different key.

And here the determinism pays off — but only if both sides agree on what they hash. This is the part everyone gets wrong, so be precise about it. generate_surrogate_key never returns null: hand it a null and it hashes the string '_dbt_utils_surrogate_key_null_'. That’s a real, deterministic key — but it is not the key we’re about to mint for the unknown member, and a fact that lands on it points at nothing.

So we pick one sentinel and use it on both sides. The dimension mints its unknown row from the literal '-1'; the fact routes a missing key to the same place with coalesce(book_id, '-1') before hashing (that’s the expression in the fact-table chapter, and it is not optional). Both sides then compute md5('-1'), and they meet. All the dimension has to do is build a row that lives there — which means hashing a null through the same macro:

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

with books as (
    select * from {{ ref('stg_books') }}
),

with_unknown as (
    select
        book_id, title, author, genre, category, format, list_price
    from books

    union all

    select
        '-1'                   as book_id,     -- the reserved unknown member
        'Unknown'              as title,
        'Unknown'              as author,
        'Unknown'              as genre,
        'Unknown'              as category,
        'Unknown'              as format,
        cast(null as number)   as list_price
)

select
    {{ dbt_utils.generate_surrogate_key(['book_id']) }} as book_sk,
    book_id,

    title,
    author,
    genre,
    category,          -- genre rolls up to category: the hierarchy, denormalized

    -- the leaf category the book is actually filed under, as an id and a key. The
    -- denormalized `category` name above is what reports group by; `category_sk` is
    -- what the hierarchy bridge (chapter 12) joins on when the roll-up must work from
    -- any depth. Carrying both costs one column and saves a lookup.
    category_id,
    {{ dbt_utils.generate_surrogate_key(["coalesce(category_id, '-1')"]) }} as category_sk,

    format,
    list_price,
    case
        when list_price is null then 'Unknown'
        when list_price < 10    then 'budget'
        when list_price < 25    then 'standard'
        else 'premium'
    end as price_band

from with_unknown

The union injects one all-Unknown row before the hashing select, so the same generate_surrogate_key(['book_id']) expression mints both the real keys and the unknown key. That ordering is the whole trick, and the choice of '-1' as the natural key is load-bearing rather than decorative: because the sentinel is a value, any fact model can reproduce the identical surrogate key by hashing the same literal — generate_surrogate_key(["'-1'"]) — and route an orphan row to it. Hash a null here instead and you cannot: dbt_utils substitutes its own internal placeholder for nulls, so the dimension’s key and anything the fact tries to compute would differ, and every “routed” fact would orphan against the dimension it was supposed to land in. Chapter 10 leans on this exact reproducibility when it left-joins late-arriving facts. Three more details worth the ink:

  • List the columns in the union explicitly. union all matches by position, not name; a lazy select * on top and a reordered staging model below is a genre column full of author names.
  • Derived attributes need a null branch. Look at price_band: without the is null arm first, the unknown row falls through to else 'premium' — and suddenly every keyless sale reports as premium-band revenue. Every case you write over an attribute needs to decide, on purpose, what the unknown member gets.
  • Type the nulls. cast(null as number) on list_price — an untyped null in a union leaves the column’s type to inference, and inference across a union is how you get a mystery variant column. (The key is never null; that’s the point above. It’s the genuinely-unknown measures and attributes that stay null.)

dim_customer gets the same treatment:

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

with customers as (
    select * from {{ ref('stg_customers') }}
),

with_unknown as (
    select customer_id, full_name, email, city, country, segment
    from customers

    union all

    select
        '-1' as customer_id,          -- the reserved unknown member
        'Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unknown'
)

select
    {{ dbt_utils.generate_surrogate_key(['customer_id']) }} as customer_sk,
    customer_id,

    full_name,
    email,
    city,
    country,
    segment

from with_unknown

Now the payoff. The gift-card sale joins successfully, groups under “Unknown,” and shows up in the dashboard as its own visible, clickable, embarrassing line item — sum(extended_price) for genre “Unknown”: $1,940. That’s not a bug, that’s the system working: the number reconciles with finance and the data-quality problem is on display where someone will fix it, instead of hidden inside a dropped join. Some shops go further and mint several placeholder members — “Unknown” for absent keys, “Not applicable” for facts where the dimension genuinely doesn’t apply, “Error” for keys that failed validation — each a distinct row with a distinct sentinel. Start with one; add the others when a real distinction demands them.

One honest boundary: the unknown member handles keys that are null. It does not handle keys that are present but wrong — an order line referencing book_id 8812 when no such book has reached the warehouse yet. That fact row hashes to a perfectly real key with no dimension row behind it, and the join drops it just as silently as before. That’s the late-arriving data problem — orphaned facts, inferred members — and it gets a full chapter of its own (chapter 10). Until then, the tests at the end of this chapter are your tripwire.

Hierarchies: fixed, and then the other kind

The genre→category ladder in dim_book is a fixed-depth hierarchy — every book has exactly one genre and every genre exactly one category, so the whole thing flattens to two columns and every roll-up is a group by. When a hierarchy’s depth is known and uniform, columns are the answer, full stop. Date hierarchies (day→month→quarter→year) are the purest case, and the next chapter milks them properly.

The trouble starts when the depth varies. Suppose the bookshop adopts a real category tree: Fiction → Science Fiction → Space Opera on one branch, Non-fiction → Cooking on another — three levels here, two there, and merchandising adds a fourth level whenever the mood strikes. That’s a ragged (variable-depth) hierarchy, and it’s stored in the source the way trees always are: an adjacency list, each category pointing at its parent.

You can’t group by an adjacency list. The standard dimensional move is to flatten it at build time — walk the tree with a recursive CTE and lay each node’s ancestry out as level columns:

-- inside dim_category: flatten the adjacency list to level columns
with recursive category_paths as (

    select
        category_id,
        category_name,
        parent_category_id,
        category_name        as level_1,
        cast(null as varchar) as level_2,
        cast(null as varchar) as level_3,
        1                    as depth
    from {{ ref('stg_categories') }}
    where parent_category_id is null          -- the roots

    union all

    select
        c.category_id,
        c.category_name,
        c.parent_category_id,
        p.level_1,
        case when p.depth = 1 then c.category_name else p.level_2 end,
        case when p.depth = 2 then c.category_name else p.level_3 end,
        p.depth + 1
    from {{ ref('stg_categories') }} c
    join category_paths p on p.category_id = c.parent_category_id
)

select
    {{ dbt_utils.generate_surrogate_key(['category_id']) }} as category_sk,
    category_id,
    category_name,
    level_1,
    coalesce(level_2, category_name) as level_2,   -- balance the ragged branches:
    coalesce(level_3, level_2, category_name) as level_3   -- shallow leaves repeat downward
from category_paths

The recursive CTE walks root-to-leaf, filling in level_N columns as it descends; the final coalesce cascade balances the raggedness by copying shallow leaves down into the empty levels, so “Cooking” appears at level 2 and level 3 and a group by level_3 never drops the shallow branches. This is the pragmatic 90% answer: pick a maximum depth, flatten, balance, ship.

Its limits are real, though. The depth cap is baked into the columns — a fourth level means a schema change — and questions like “total sales under this node, wherever it sits” get awkward. When the hierarchy is genuinely unbounded or the roll-up must work from any node (org charts are the classic case), the dimensional answer is a hierarchy bridge table, and that’s chapter 12’s territory. Know which problem you have before choosing: most “trees” in business data are three levels deep and stable, and columns beat cleverness.

Conformed dimensions: one truth, many facts

A quiet word that becomes the whole ballgame later: dim_book isn’t just a dimension — it’s about to be shared. fct_order_items points at it; fct_inventory_daily points at it; the wishlist and promotion facts from last chapter point at it. When multiple fact tables share the same dimension — same keys, same attributes, same spellings — that dimension is conformed, and conformance is what lets you lay two facts side by side and trust the comparison: units sold vs. units on hand, by title, in one query, because “title” means the same row in both.

Conformance sounds free when everything lives in one dbt project — of course both facts use the same dim_book, it’s right there. It stops being free the day a second team builds “their own” book dimension with its own genre spellings, and the numbers stop lining up in ways nobody can explain in a meeting. Treat the shared dimensions as the contract they are; chapter 11 shows what conformance buys (drill-across) and what it costs to govern.

When the join silently returns nothing

The ironclad rule from earlier — identical key expression on both sides — deserves a demonstration, because the failure mode is the worst kind: no error, no warning, just zero matched rows.

Suppose stg_orders carries order_date as a timestamp (the source sends 2026-05-13 14:22:07.331) and the fact hashes it as-is, while dim_date hashes its date_day, a proper date:

-- in the fact: hashes the string '2026-05-13 14:22:07.331'
{{ dbt_utils.generate_surrogate_key(['order_date']) }}      as date_sk

-- in the dimension: hashes the string '2026-05-13'
{{ dbt_utils.generate_surrogate_key(['date_day']) }}        as date_sk

Both compile, both run, both produce well-formed 32-character keys — and no key from one side ever equals a key from the other, because the macro casts each input to varchar and then hashes the text. '2026-05-13 14:22:07.331' and '2026-05-13' are different strings, so they’re different hashes. The fact builds green, the dimension builds green, and every date join in the warehouse returns nothing. The fix is to make the text identical before it’s hashed:

-- fixed: both sides hash the string '2026-05-13'
{{ dbt_utils.generate_surrogate_key(['cast(order_date as date)']) }} as date_sk

The deeper fix is a discipline, not a cast: staging owns the types. If stg_orders had cast order_date to a date once, at the boundary, the fact and the dimension would both have hashed the same clean column and the mismatch could never have been written. Hash only columns staging has already typed, and the whole class of bug evaporates. (Same trap, subtler trigger: hashing a raw date relies on the session’s date-to-text rendering, so keep an explicit cast in staging rather than trusting whatever cast(… as varchar) happens to produce.) And for the mismatches that sneak through anyway, the next section’s relationships test turns “silently returns nothing” into a red build.

Testing the keys

Every _sk in the warehouse gets the same two tests, and the facts get a third that closes the loop:

# models/marts/_dimensions.yml
models:
  - name: dim_book
    columns:
      - name: book_sk
        data_tests: [unique, not_null]

  - name: dim_customer
    columns:
      - name: customer_sk
        data_tests: [unique, not_null]
# in models/marts/_facts.yml — the fact's keys must land on real dimension rows
  - name: fct_order_items
    columns:
      - name: book_sk
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_book')
              field: book_sk

unique on the dimension side is the grain test — one row per member, and note the unknown member passes it happily, since its sentinel key is just another unique value. not_null on the fact side enforces the never-null-FK discipline mechanically. And relationships is the tripwire promised above: it fails the build if any fact key points at a dimension row that doesn’t exist — which catches both the hash-mismatch bug (every key orphaned, loudly) and the late-arriving book_id 8812 (one key orphaned, precisely). Cheap to write, and it converts the two silent failure modes in this chapter into build failures with a row count attached.

The dimension we haven’t built

One dimension is conspicuously missing from this chapter: the calendar. Every fact so far hashes a date_sk, and nothing has been said about the table those keys land on — because dim_date is not like the others. It isn’t sourced from the business at all; it’s generated, it’s joined by more queries than every other dimension combined, and doing it properly — fiscal calendars, holidays, business days, the “hasn’t happened yet” row that fct_orders is already counting on — is a full chapter’s work. It’s next.

Final thoughts

Dimensions are where dimensional modeling stops being about tables and starts being about people. The facts are for the machine; the dimensions are for the human who wants to ask a question in the words they’d actually use — genre, segment, quarter — and get an answer without a data dictionary. Making them wide and denormalized feels wrong for exactly one post, until you write the third join-free query and the instinct flips. The surrogate key, opaque and forgettable as it looks, is the hinge the history chapters turn on. And the unknown member is the pattern that separates a warehouse that reconciles from one that merely runs — because the difference between dropping a dirty row and displaying it is the difference between hiding a problem and fixing one.

Next: The Hardest-Working Table in the Warehouse

Comments