Floemdata
Data & ETL

Surrogate Keys vs Business Keys in a Warehouse

If a customer code changes, what happens to past reports? The difference between surrogate and business keys, how to choose, and which key Floemdata uses today.

Floemdata · · 4 min read
A central post that stays put, with a dashed old tag on one side and a solid new tag on the other

In the ERP a customer has the code C-0412. One day, because of a merger, the code is changed to C-9001. In the warehouse the old sales lines still carry the old code; new lines arrive with the new one. The customer report shows the same company as two separate rows. That is the result of the key you chose.

Two kinds of key

  • Business key: the source system's own identifier, such as a card code, item code or document number. A value people can read and that matches the source one to one.
  • Surrogate key: a meaningless number (1, 2, 3...) generated by the warehouse itself. It stands in for the business key and does not depend on it.

When a business key causes trouble

  • The code changes (the example above) and history splits from new rows.
  • A code is reused: a retired item code is given to another item and history attaches to the wrong item.
  • Two source systems use the same code for different things; they collide when combined.
  • Because codes are text, differences in case, leading zeros or spaces split records apart.

The price of a surrogate key

You need an extra mapping table (business key to surrogate key) and loading logic to manage it. Comparing a figure with the source gets slightly harder when writing reports, because the readable code must be kept as an additional column.

A practical rule for a small warehouse

  • With one source and stable codes, the business key is often enough.
  • With several sources, frequently changing codes, or a need to keep history unchanged, use a surrogate key.
  • Whichever you choose, also store the business key as a column; whoever reads the report must be able to match it to the source.
  • Ask in advance how likely a code change is: does the ERP owner know that codes can be changed?

Where Floemdata stands

In Floemdata's Silver layer the customer and item tables are held by business key: the primary key of silver_is_ortagi is (tenant_id, kart_kodu) and that of silver_urun is (tenant_id, urun_kodu). These are the card and item codes from SAP Business One. We did not find a separate surrogate key generated by the warehouse in the code, nor a mapping that tracks code changes.

The source-system column is not part of the primary key of these tables. We have not tried what happens if two sources use the same customer code in the same company; the "two sources" risk in this post is a possibility read from the code, not a measured result.

To start, ask your ERP owner for a list of cards whose code changed in the last two years. Questions are welcome at info@floemdata.com or via Floemdata.

← All guides