🔑Surrogate Key

NEW

Also known as: Artificial Key, Synthetic Key

A system-generated unique identifier with no business meaning, used as primary keys to ensure referential integrity and support historical tracking.

DESCRIPTION

Surrogate keys are non-business identifiers, typically integers or GUIDs, assigned to dimension and sometimes fact rows. They simplify integration across source systems, support SCD Type 2 history, and avoid issues with changing natural keys. Surrogates improve join performance and reduce composite key complexity. Generation approaches include sequences, identity columns, UUIDs, or hash keys. Governance requires stable assignment, collision prevention, and careful handling in upserts. While surrogates add indirection, they are standard practice in dimensional modeling and many OLTP designs.

ALTERNATIVE DEFINITIONS

Kimball Group

A surrogate key is a meaningless primary key assigned to a dimension record, usually an integer generated by the ETL system.

Emphasizes ETL-driven integer keys for dimensions.

Source

Oracle

A surrogate key is a system-generated key used to uniquely identify a row, with no business meaning.

Contrasts surrogate keys with natural keys and outlines design choices.

Source

Microsoft Learn

Sequences generate numeric values in order, which can be used as keys to uniquely identify rows.

Describes a common mechanism to create surrogate keys.

Source

RELATED TERMS

Primary Key Foreign Key UUID Sequence Kimball Methodology Slowly Changing Dimension Extract-Transform-Load Data Schema Hash Key

COMPETING TERMS

Natural Key

RELATIONS

• Broader: Key

KNOWLEDGE AREAS

Data Modeling Data Warehousing & BI Data Engineering Data Architecture Data Storage & Operations

TARGET AUDIENCE

Data Modeler Data Engineer Analytics Engineer BI Specialist Data Architect Software Developer

EXTERNAL SOURCES

• Wikipedia - Surrogate key
• Kimball Group - Dimensional modeling guidance
• PostgreSQL - Sequence generation for keys
• Snowflake - Snowflake sequences for keys

TOP QUESTIONS

Why use a Surrogate Key instead of a Natural Key?

Surrogates remain stable across source changes, simplify joins, and enable SCD Type 2 history without coupling to business semantics.

Category: Requirements

Are UUIDs or integers better for Surrogate Keys?

Integers often perform better for joins; UUIDs avoid central generation but may increase storage and index size.

Category: Opinion

How are Surrogate Keys generated?

Common methods include sequences, identity columns, UUID/GUID generation, or hash keys in ELT pipelines.

Category: Example

What are risks with Surrogate Keys?

Key collisions, unstable assignment, and improper handling in merges can corrupt referential integrity and historical tracking.

Category: Risk

Do Surrogate Keys belong in facts?

Facts typically reference dimension surrogate keys; fact surrogate keys can assist deduplication and lineage but are optional.

Category: Opinion

How do Surrogate Keys support Slowly Changing Dimensions?

Each new Type 2 version gets a distinct surrogate key, enabling facts to point to the correct historical version.

Category: Example

What is a common misunderstanding about Surrogate Keys?

That they replace business identifiers; actually, natural keys remain important for matching and business logic.

Category: Misunderstanding

How to document Surrogate Keys?

Record generation method, uniqueness constraints, and mapping to source natural keys in your data catalog.

Category: Best practices

Do NoSQL systems use Surrogate Keys?

Yes, many assign synthetic identifiers or hashed keys to documents or rows to support indexing and distribution.

Category: Relevancy