What is an S-key in a data warehouse

s-Key

In a data warehouse, an S-key usually refers to a Surrogate Key.

Definition: Surrogate Key (S-Key)

A surrogate key is an artificial, system-generated identifier used in a data warehouse dimension table.
It has no business meaning—its only purpose is to uniquely identify a row.

Common forms:

• Integer identity column (1, 2, 3…)
• UUID (less common)
• Hash key (in Data Vault)

Why Surrogate Keys Are Used

Surrogate keys solve several issues that arise in data warehouses:

1. Slowly Changing Dimensions (SCD)

They allow tracking multiple versions of a dimension (e.g., customer address changes).
Each version gets a different surrogate key, even if the business key (e.g., customer_id) is the same.

2. Stability

Business keys can change (e.g., product codes, emails).
Surrogate keys do not.

3. Performance

Integer surrogate keys join faster than long strings or composite business keys.

4. Simplifies integration

Multiple source systems may use different natural keys.
A surrogate key standardizes them.

Where Surrogate Keys Are Used

Dimension Tables

• dim_customer
• dim_product
• dim_date

 

Example:

customer_sk (surrogate key)

customer_id (business key)

customer_name

customer_address

effective_date

expiry_date

 

Fact Tables

Fact tables store the surrogate keys for linking:

fact_sales

-----------

sale_id

customer_sk

product_sk

date_sk

amount

 

S-Key vs Natural (Business) Key

 

Type	Meaning	Pros	Cons
Surrogate Key (S-Key)	Artificial, no business meaning	Fast, stable, supports SCD	Needs ETL to generate
Natural/Business Key	From source system, meaningful	Already exists	Can change; composite; slow joins

 

 

Quick Example

A customer changes address 3 times:

customer_sk	customer_id	address	start_date	end_date
101	C123	Old Street	2019-01-01	2020-05-01
102	C123	New Street	2020-05-02	2023-02-10
103	C123	High Street	2023-02-11	NULL

 

The surrogate key makes each version distinct while keeping the business key the same.