Skip to content
BENIBIX Learn

Natural Key vs Surrogate Key

People think a natural key and a surrogate key are two names for one ID, and they are not. A natural key comes from the source, like an email or a product code. People can read it. A surrogate key is a plain number the warehouse makes up. Warehouses join on the surrogate key. They keep the natural key to trace each row back.

Data🗄️ Storage & Warehousing

Natural Key

An ID that comes from the real world or the source, like an email address.

A natural key is a value that already names a thing in the real world or in the source system. An email address, a product code or a tax number can be one. It has meaning, so people can read it. But it can change, be reused, or clash when two sources are merged. That is why warehouses often add a surrogate key and keep the natural key beside it.

Example
A shop's product table uses codes like TSH-RED-M as the natural key. The warehouse adds a plain number as the surrogate key and keeps the code too.
Why it matters
It is how rows from the source are matched to rows in the warehouse. Lose it, and you cannot trace a row back.
Full entry
Data🗄️ Storage & Warehousing

Surrogate Key

A made-up ID the warehouse gives each row. It means nothing on its own.

A surrogate key is an ID the warehouse makes up for each dimension row. It is usually just a counting number with no meaning. The ID from the source app is kept too, as a normal column. Surrogate keys let one customer have many rows, one per version. They also keep the warehouse safe if a source app reuses or changes its IDs.

Example
Lucy is customer 881 in the shop app. In the warehouse she has keys 10452 and 20917, one for each city she lived in.
Why it matters
Type 2 history is not possible without it. Two rows for one customer need two different keys.
Full entry

In one line

Natural Key
An ID that comes from the real world or the source, like an email address.
Surrogate Key
A made-up ID the warehouse gives each row. It means nothing on its own.