Comparison
Natural keyvsSurrogate key
Natural key
you joined on the email address, and then two people changed their email and one of them was reused by a new account.
The identifier the business already uses — an order number, an ISBN, a customer reference from the source system. It is meaningful, which makes it readable and makes it change: business keys get reissued, reformatted, and merged during acquisitions. Keep it on the dimension row so humans can find things and so loads can match on it, but do not make it the thing a billion fact rows point at.
Full entry →Surrogate key
the dimension row has a meaningless integer id of your own invention, which is why the source renumbering its customers did not break anything.
A key generated by the warehouse, carrying no business meaning, used to join facts to dimensions. It insulates you from the source changing its identifiers, it lets one logical entity have several rows over time, and it is short, which matters when it is repeated across a billion fact rows. It is the enabling trick for type-2 history: without it, there is no way for a fact to point at the version of the customer that existed when the fact occurred.
Full entry →