Surrogate key

What is a surrogate key?

A surrogate key is an artificial integer that your warehouse assigns to each row in a dimension table. It carries no meaning: you cannot read a customer, a product or a date from the number itself. It is just a value handed out in sequence (1, 2, 3, and so on) that becomes the primary key of the dimension and the value every fact row uses to point back to that row.

The alternative is the natural key, also called the business key: the identifier the source system already uses, such as a customer number, a product code or a VAT number. Ralph Kimball's advice in dimensional modelling is to generate your own anonymous integer for every dimension and demote the natural key to an ordinary column. In a star schema each dimension gets a surrogate key as its primary key, and each fact row references the dimension through that integer, never through the customer number.

Think of the numbered ticket you get at a cloakroom. It says nothing about who you are, it only holds the link to your coat. Two people with the same name still get two different tickets.

Surrogate key versus business key

It is tempting to skip the extra column and just use the customer number from the source as the primary key. That works until the source system does something you do not control. Kimball warns that source systems reuse codes they have purged, mis-assign codes by mistake, and reformat their key space to cope with a new situation.

Take an ERP that deletes product code P-100 when an article leaves the catalogue, then reuses P-100 a year later for a different product. If P-100 is your primary key, the two products merge into one row and their sales are counted together. With a surrogate key each product gets its own integer, and P-100 stays an ordinary column that is allowed to appear twice.

So the surrogate key decouples the warehouse from the habits of the source. The business key does not disappear: it drops to a plain attribute you keep in order to match incoming source data to the right row. It is still a candidate key for the row, but it is no longer the one the table is built on. Where the natural key spans several columns, the surrogate key also collapses that composite key into one narrow integer, so the fact table carries one small foreign key per dimension instead of a wide compound one. The same narrow integer also lets you file a customer that two merged systems number differently as one row.

A worked example with SCD Type 2

The clearest case for surrogate keys is slowly changing dimensions Type 2. Say a salesperson with employee number E-2231 works the Antwerp region. Your salesperson dimension holds one row: surrogate key 4401, business key E-2231, region Antwerp. Every sale that person books points to 4401.

On 1 March the salesperson moves to the Ghent region. Because region is a Type 2 attribute, you do not overwrite the old row. You close row 4401 with an end date and set its current flag to false, then insert a new row: surrogate key 4402, business key E-2231 again, region Ghent, current flag true. Same person, two rows, two surrogate keys.

From then on new sales carry 4402 and older sales keep pointing at 4401. Ask for last year's numbers and that revenue still hangs on the Antwerp version, because it was the one in force then. The employee number could not do this on its own, it is identical on both rows. This is also why the fact table sits at the grain of the salesperson version rather than the salesperson: the granularity follows the surrogate key, not the person.

Unknown members and late arriving dimensions

A fact row must always point at a real dimension row, even when you do not yet know which one. Warehouses solve this with special members: fixed rows reserved on predictable surrogate keys. Microsoft's guidance for its Fabric warehouse, for example, uses 0 for a missing member, -1 for an unknown one where a lookup failed, and -2 for not applicable.

These rows are what late arriving dimensions lean on. When a sale arrives before the customer record does, you point the fact at the unknown-member row, or you insert a stub row with a fresh surrogate key and the business key you do have, then fill in the real attributes when they turn up. Either way the fact loads on time, and the surrogate key it was given never has to change afterwards.

What to watch out for with surrogate keys

Generate them in the data warehouse, not in the source. The point of the key is that it stands apart from the source system. Let your load process assign it, through a database identity or sequence column, or a Power Query index column when you model in Power BI, and let the source never influence the value.

Always keep the business key as a column. You still need it to match incoming source data to the right row. Do not throw it away, it only moves from primary key to ordinary attribute.

Do not hide meaning in the number. A surrogate key that encodes a region or a year is no longer a surrogate key, and the day that logic changes you are stuck. Keep it an empty, rising integer. The one accepted exception is the date dimension, where a readable key such as 20260301 in YYYYMMDD form is standard practice.

Budget for the lookup cost. Surrogate keys are not free. Every fact load has to look up the current surrogate key for each business key before it can write the row, and that step, sometimes called the surrogate key pipeline, is real work in your load process. For a small single-source lookup dimension that never versions, a clean natural key can be enough, so do not reach for a surrogate key out of habit alone.