Natural Keys Versus Surrogate Keys

Data Modeling

A source identifier that can change is not a stable identity; it is an attribute of the identity at a point in time. The practical answer in natural keys versus surrogate keys is to separate the business-facing identifier from the durable identifier your model uses for relationships. A natural key can be useful when it is truly stable and enforced, but when the source value changes, gets reassigned, or differs across systems, a surrogate key or durable internal key protects history and joins from being rewritten by accident.

Direct answer: the stable identifier is not always the source identifier

Practitioners often get stuck because a source system calls something an ID, so everyone assumes it is permanent. That assumption is unsafe. An ID in a source application may be editable, regenerated during migration, reused after deletion, or scoped only within one tenant, region, or source system.

The core modeling move is simple: do not ask only, What column identifies this record today? Ask, What concept must stay the same when this column changes?

If the source identifier can change while the real-world entity remains the same, then the source identifier should not be the only primary key of your analytical concept. It can remain as a natural key, alternate key, or historical attribute, but relationships should point to a stable modeled identity.

Natural key, business key, and surrogate key in plain English

A natural key is made from values that come from the business domain. Examples include an account number, employee number, vehicle identification number, SKU, or source customer ID. A natural key carries meaning outside the database.

A business key is often used similarly, but it is useful to be precise: it is the identifier the business recognizes for a concept. In some environments, the business key is stable and governed. In others, it is just the current identifier in an application.

A surrogate key is generated by the system and has no business meaning. It might be an integer sequence, UUID, or warehouse-generated hash. Its job is not to explain the business. Its job is to give the model a stable handle for joins, history, and constraints.

The important distinction is not whether the value looks numeric or textual. The important distinction is whether the value is part of the business meaning and whether it remains stable for the lifetime of the entity.

Hypothetical dataset: a source customer ID that changes

Consider a hypothetical subscription company. The billing system stores customers and invoices. At first, customer Ada has source customer ID C100. Later, a migration in the billing system changes Ada's source customer ID to C900. Ada is the same customer, but the source identifier has changed.

The old source export might look like this:

• customers: source_customer_id = C100, email = [email protected], name = Ada Rivera
• invoices: invoice_id = I501, source_customer_id = C100, amount = 120

After the migration, the current source export might look like this:

• customers: source_customer_id = C900, email = [email protected], name = Ada Rivera
• invoices: invoice_id = I777, source_customer_id = C900, amount = 180

If your analytical model treats source_customer_id as the permanent customer identity, it now sees two possible customers: C100 and C900. If it overwrites C100 with C900 without preserving the mapping, older invoices can become orphaned or be incorrectly reclassified.

Moment	Source customer ID	Real-world customer	What the model should understand
Before migration	C100	Ada Rivera	Ada is one customer with the billing identifier C100
After migration	C900	Ada Rivera	Ada is the same customer with a new billing identifier
Bad interpretation	C100 and C900	Two apparent customers	History is split because the source ID was treated as permanent
Better interpretation	customer_sk 42 with ID history	One modeled customer	Both source identifiers map to the same durable identity

Counterexamples that break the natural-key assumption

A natural key works only if its assumptions hold. The failure cases are usually not exotic. They happen during migrations, merges, application redesigns, manual corrections, and integrations.

• The source ID changes for the same entity. Ada moves from C100 to C900, but she is still the same customer.
• The source ID is reused. C100 is deleted and later assigned to a different customer in a poorly governed source system.
• The source ID is scoped. Store 7 and Store 9 both have customer_id 123, so customer_id alone is not globally unique.
• The natural identifier changes. An email address, phone number, company name, or tax registration value can change while the entity remains the same.
• Two systems disagree. The CRM says the customer is CRM-45, while billing says the same customer is BILL-900.

These cases do not mean natural keys are bad. They mean a key must be judged by the stability and uniqueness rules of the domain, not by the label printed on a source column.

What breaks when a changing natural key is used as the main join key

When a changing source identifier is used as the main key, the damage usually appears as ordinary reporting confusion rather than an obvious database error.

• Historical facts split across identities. Revenue before the source ID change appears under C100, while revenue after the change appears under C900.
• Facts become orphaned. Old invoices still point to C100, but the current customer table only contains C900.
• Metrics change after backfills. A refresh rewrites identifiers and shifts historical counts without a real business event.
• Slowly changing dimensions become harder to reason about. It is unclear whether a new row means the customer changed, the identifier changed, or the model lost track of identity.
• Cross-system joins become brittle. A dashboard depends on a source-specific identifier that another system does not recognize.

The deeper issue is semantic. A source identifier describes how one system currently names an entity. A durable model identity describes what the entity is across time and systems.

How to model stable identity when the source identifier changes

A safer pattern is to separate three ideas: the modeled identity, the source identifiers, and the historical facts that point to the modeled identity.

In a warehouse or analytical model, you might create a customer dimension with a surrogate key such as customer_sk. That key is generated inside the model and remains stable for Ada. You then store the source identifiers in a separate mapping or history structure.

For the hypothetical example, the modeled customer might look like this:

• customer_sk: 42
• current_name: Ada Rivera
• current_email: [email protected]

The identifier history might look like this:

• customer_sk = 42, source_system = billing, source_customer_id = C100, valid_from = initial load, valid_to = migration date
• customer_sk = 42, source_system = billing, source_customer_id = C900, valid_from = migration date, valid_to = current

Facts then point to customer_sk after the source rows have been resolved. Invoice I501 and invoice I777 can both attach to customer_sk 42, even though the source customer ID changed.

A surrogate key does not solve identity by itself

A surrogate key is useful, but it is not magic. If the model cannot determine that C100 and C900 are the same customer, generating a new integer will only create a cleaner-looking duplicate.

The hard work is the identity rule: how does the model know that two source identifiers refer to the same business entity? Sometimes the answer comes from a migration crosswalk. Sometimes it comes from a master data system. Sometimes it comes from a carefully reviewed matching rule. Sometimes the honest answer is that the model does not know, and the records should remain separate until there is evidence.

Use the surrogate key as the stable reference after identity has been resolved. Do not use it to hide unresolved identity problems.

When a natural key is acceptable

A natural key can be a good key when it is stable, unique, mandatory, and governed at the right grain. For example, an externally regulated identifier may be suitable in some domains if the business rules guarantee that it is not reused and does not change for the modeled concept.

Even then, practical models often keep a surrogate key for internal joins and store the natural key as an alternate key with a uniqueness constraint. This gives users a recognizable identifier while keeping the model free to handle history, source changes, and multi-system integration.

The decision is not natural keys are good or surrogate keys are good. The decision is whether the key's meaning, uniqueness, and stability match the relationship you are trying to protect.

Decision rules for practitioners

Use these questions before choosing between a natural key and a surrogate key:

1. Can the value change while the entity remains the same? If yes, do not use it as the only durable identity.
2. Can the value be reused for a different entity? If yes, it is unsafe as a permanent key.
3. Is the value unique at the grain of the table? If uniqueness requires source_system, tenant_id, region, or date range, the single column is not enough.
4. Does the business recognize the value? If yes, keep it visible as a business identifier even if it is not the internal join key.
5. Do facts need to survive source migrations? If yes, resolve facts to a stable modeled identity rather than depending only on the source ID.
6. Can you explain the identity rule? If not, the key design is probably masking a business definition problem.

A practical default for analytical models is to use surrogate keys for internal relationships, preserve natural keys as attributes or alternate keys, and document the rule that maps source identifiers to durable identities.

Situation	Use natural key as primary identity?	Recommended modeling response
The value is stable, unique, mandatory, and governed	Possibly	It can be a key, but storing a surrogate key may still simplify joins and history
The value can change for the same entity	No	Use a surrogate or durable internal key and keep the natural key as history
The value is only unique inside a source system or tenant	No, not by itself	Include the scope in the alternate key or mapping rule
The value can be reused	No	Track validity and avoid treating it as a permanent identity
The value is useful to business users	Yes, as a visible identifier	Keep it in the model, but do not confuse visibility with stability

Constraints still matter

Relational constraints are still useful even when you use surrogate keys. A primary key can ensure that the surrogate key identifies one row. A unique constraint can protect a natural key or a source mapping where the business rule says it must be unique. A foreign key can express that a fact should reference an existing dimension row.

The modeling point is that constraints should enforce the rule you actually believe. If source_customer_id is only unique within source_system, then the uniqueness rule is not source_customer_id alone. It is the combination of source_system and source_customer_id, possibly with a validity period if identifiers can change over time.

Good key design and good constraints work together. The key design states the semantic identity. The constraints prevent the data from silently violating that identity.

How to explain this to stakeholders

A clear non-technical explanation is often better than a database lecture:

The billing system changed the customer's label, not the customer. If we use the label as the customer identity, old and new activity may appear under different customers. We need an internal customer identity that remains stable, and we should keep the billing labels as history.

This explanation helps separate business continuity from source-system naming. It also makes the tradeoff visible: the model is not rejecting the source identifier; it is putting that identifier in the right role.

Key takeaways

• The central issue in natural keys versus surrogate keys is identifier stability, not whether the value is numeric or meaningful.
• A source identifier that changes should be modeled as an attribute or identifier history, not as the only durable identity.
• Surrogate keys are useful for stable joins, but they do not solve identity matching by themselves.
• Natural keys are acceptable when their uniqueness and stability rules are true at the grain being modeled.
• Use constraints to enforce the identity rules you actually believe, including scope and validity when needed.

Next step

Take one important entity in your warehouse, such as customer, account, employee, or product. List every identifier used for it across source systems. For each identifier, ask whether it can change, be reused, or require a scope such as source_system or tenant_id. Then decide which value represents durable identity and which values should be stored as natural-key history or alternate keys.