The "Key" To Building A Reliable Data Model

The key to a reliable data model is a surrogate key: a unique identifier your data team generates for each warehouse table rather than one inherited from a source system. It is usually a hash of whichever columns define a unique record, created with the hashing functions most databases now include. Using one on every table gives you consistency, because every table has the same kind of _sk column and joins the same way. It gives you uniqueness, because you decide what a unique row is and can test for duplicates and nulls. And it gives you simplicity, because the hard join logic is worked out once, when the key is stamped on the fact table, and never again downstream. Dimensions are the obvious place for one. On fact tables it's optional, and I add one anyway.

What a surrogate key is

Why relationships come first

There is a lot to consider when building a data model, but one of the most foundational pieces is the relationships between tables. Without those you don't have a model, you have a collection of disconnected tables.

How you join those tables goes a long way toward determining how well the model holds up and how well it scales.

A key you create yourself

A surrogate key is a replacement for a primary key, the column that uniquely identifies a row in a table. It is something you, as a data team, create to act as the unique identifier.

It doesn't exist anywhere in real life: not in any source system, not in any business application. It exists only inside your warehouse for your modeling purposes.

Why control matters

The main benefit is control. As engineers we deal with data arriving from many different systems and try to connect it into one model.

By defining a surrogate key, and the logic behind what makes a record unique, you decide what counts as unique regardless of what the source system says.

When you then build relationships, you use those keys and know exactly what they mean, rather than hoping the various systems all play nicely together.

What about natural keys

Some teams skip this and use a natural key instead, the ID that already exists in a source table. If there is a clean ID and the model is built carefully around it, that works.

The reasons below are why I'd still use a surrogate key even when a usable natural key exists.

How to create one

There are plenty of ways to generate a surrogate key, but the common approach is a hashed key. Most databases ship with hashing functions, so you pass in the columns that define a unique record and get back a hashed value.

That might be a single ID column or a combination of columns. Either way, you are the one deciding what unique means.

In plain SQL the idea looks like this:

select
    md5(concat_ws('|', customer_id, source_system)) as customer_sk,
    customer_id,
    source_system,
    customer_name
from stg_customers

Why the hash is stable

The same two columns always produce the same hash, so the key is stable across runs. Changing which columns you pass in changes the definition of uniqueness in one place.

In a dbt project the generate_surrogate_key macro in dbt_utils does the same thing and handles nulls and type casting for you.

Reason 1: Consistency

One shape for every table

By deciding that surrogate keys are how every table works, all of your tables end up with the same general structure. Every one of them has some kind of _sk column as its unique identifier.

The mixed alternative

Consider the alternative. One table has a truly unique ID in the source, so you use it directly.

Another dimension combines data from multiple systems, and there's no single unique column, but you know a combination of columns is unique, so that table gets a surrogate key.

Technically it works, but now you have a mixture:

• Different tables look different.
• The underlying joins behave differently from table to table.
• Every table needs its own conversation with the team about how its key works.

If instead every table has a surrogate key, even when it's just a hash of one column, you get one consistent look and feel. The model is cleaner and easier to work with.

Reason 2: Uniqueness

You own the definition

Some tables already have a unique value, but the surrogate key brings it back to control. You establish it, so you decide what the unique value is and you understand the logic behind it.

You're not relying on the source system to uphold referential integrity, which you hope it does but it doesn't always.

Because you own the key, you can test it. A test for no duplicates and a test for no nulls on the _sk column are quick to write. If either fails, something upstream is wrong and you find out before a stakeholder does.

Slowly changing dimensions

This also becomes very useful with slowly changing dimensions. A slowly changing dimension is a dimension where you keep history: when an attribute changes, you add a new row rather than overwriting the old one.

The hash approach works on a combination of columns. If you include the tracked columns in the key, a change in any of them generates a new row and a new key automatically. You then stamp that key on the fact table.

Later, it's easy to know exactly which version of the dimension record a fact row was tied to, even though the dimension changed over time. There is only ever one surrogate key for any given record.

Reason 3: Simplicity

Joins in the marts layer

All of this leads to the third point. When the surrogate key has been stamped on the fact table, joining a dimension in the marts layer, the presentation layer on top of the warehouse, is just a join on _sk.

You don't have to work out which dimension value was in effect on a certain date. That thinking has already been done.

Do the hard work once

The work happens when you build the fact. What makes a record unique might be:

• A unique ID that already exists in the source.
• A combination of columns.
• Whichever dimension row was active at the time of the event.

You resolve all of that once, in the fact table, and stamp the resulting surrogate key. Everything downstream references it.

That one hard step is where the value is. It's genuinely the challenging part from a modeling perspective, and once it's established, everything after it gets easier:

• Joins are simple: one key, one equality.
• The structure is simple to follow, because every table has the same key.
• Testing is simple, because every table has a unique value to check.

Compare that to mixing and matching the unique identifiers of every source system in every downstream query, which is what you're left doing without a surrogate key concept.

Surrogate keys on fact tables

The case for skipping it

Dimensions are the clear candidate. Whether a fact table needs one is debatable.

In the purest form of a star schema, a fact table represents a unique granular action, and the combination of the dimension keys on that row makes it unique by itself. In theory, no additional key is required.

Why I add one anyway

I still add one. I like consistency, and I like a little more control.

A surrogate key on the fact lets me run the same quick tests I run on dimensions and keeps every table in the model with the same look and feel.

It's optional, so decide what suits your team, but that's my default.

Related reading

• How to Track History in a Data Warehouse
• Creating a Data Model with dbt: Dimensions
• Creating a Data Model with dbt: Tests
• The 4 Types of Dimensions

Final takeaway

Surrogate keys on every table is one of the first conventions I put in place on a client warehouse, because the teams I work with are almost always combining several source systems and need a definition of uniqueness that doesn't depend on any one of them. It is a small decision that makes every later join and test easier.