# Surrogate key

> A surrogate key is an identifier created by the data platform instead of taken from the source, usually a sequence number or a hash.

A surrogate key is an identifier created by the data platform rather than taken from the source, usually a sequence number or a hash. It stays stable when source keys change, lets several sources be combined, and gives each version of an [SCD Type 2](https://emolysis.com/glossary/scd-type-2) record its own key. Fact tables point to [dimension](https://emolysis.com/glossary/dimension) versions through it.

For example, a customer dimension built from two source systems gives each customer row a surrogate key, such as an integer from a sequence or a hash of the source system's name and the customer's account number. Fact tables store that key instead of the account number. When a customer's account number is reissued, the dimension updates its natural key column, and every fact still points to the right customer through the unchanged surrogate key.

Sequence-based keys are compact and fast to join but depend on load order, so rebuilding a table from scratch can give rows different keys than before. Hash-based keys, computed from the natural key and, for Type 2 dimensions, the version's start date, give the same key every time the same data is loaded, which makes rebuilds and parallel loads simpler, at the cost of wider keys and a very small chance of collisions.

Many models reserve special surrogate key values, such as -1 for unknown, so that facts whose dimension row has not arrived yet still join to a row. A surrogate key has no business meaning and should not appear in reports. See also [late-arriving records](https://emolysis.com/glossary/late-arriving-data), [fact table](https://emolysis.com/glossary/fact-table) and [merge](https://emolysis.com/glossary/merge-upsert).

On Databricks, an identity column can generate sequence keys inside a Delta table; on Snowflake, sequences and identity columns do the same, and hash functions are available on every platform. Whichever method a model uses, it should be the same for every table built the same way, so keys can be compared and rebuilt. See also [SCD Type 2](https://emolysis.com/glossary/scd-type-2).

Emolysis's [SCD Type 2](https://emolysis.com/shapes/scd2) template takes the surrogate key strategy as a slot. Compare the [natural key](https://emolysis.com/glossary/natural-key), which comes from the source.

---

Page: https://emolysis.com/glossary/surrogate-key. Emolysis is a product of Bandpey GmbH, Munich, Germany. It is in private development, and nothing is released yet.