Previous Next  

  Overview: Surrogate Keys

Keys assign each row in a table a unique code. This code is typically a number or sometimes a set of numbers and letters in order to uniquely identify a specific row or entry.

In an operational database such as the AdventureWorks data source, every table entry will be assigned a unique number. Customers will have a customer number and purchase orders will have a purchase order number etc. This is known in TX DWA as the Natural Key.

However, when building a Data Warehouse, it is important that an additional key is assigned to each row we bring into the DWH. This “Surrogate key” is created by TX DWA automatically and is only created and used within Data Warehouse architecture. This way, if the client decides to change how they assign keys, or they acquire a company for example, the new or modified natural keys will collide with the existing keys in the data warehouse because the surrogate key acts independently of the natural key.

In addition, the surrogate key is typically always an integer data type starting at one and incrementally increasing by one for every record. This ensures the size of this field remains as small as possible which improves performance during reporting and analysis.