31 Mar

Data Warehousing: Surrogate Keys Vs. Natural Keys

Surrogate Keys

These are keys that have no natural meaning.  An identity property is an example of a surrogate key.

Natural Keys

Natural keys are keys that have a business meaning.  For instance, a SSN, order number or even a warehouse number.

The rule of thumb for data warehousing, actually it’s pretty much law, is to always use surrogate keys. Some of the advantages of surrogate keys include:

  • They separate the operational environment from the data warehouse.
  • There are performance advantages.
  • Slowly Changing Dimensions: Surrogate keys allow for type 2 SCD’s.

I would be willing to argue for natural keys, I don’t think it’s as black and white as many think, but for testing purposes, I think it would be very unlikely that a correct answer would be “use natural keys”.  Of course this probably means I’ll get one.