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.
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.
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.
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.