Surrogate Keys And Natural Keys

• Choose a surrogate key for dim_customer so facts still point to the right historical record after a customer changes email.
• Merge product catalogs from two systems with conflicting product codes.
• Refactor a model that used a natural key and now shows broken joins.

Think of a license plate (natural key) vs the car's internal chip ID (surrogate key). People recognize plates and they can change. The chip ID is stable and used by systems to track the car precisely.

• If attribute updates should NOT relink history, you need a surrogate key.
• If multiple systems send different IDs, use a surrogate key and map all natural keys to it.
• If joining on the natural key would merge different people/products over time, never do it.

Problem: Customers can change email. Facts should keep pointing to the correct historical profile.

• Natural key candidates: email, external_customer_id.
• Approach: Use surrogate key customer_sk as the PK. Keep external_customer_id as business_key. On email change (SCD2), insert a new row with a new customer_sk, same business_key.
• Result: Facts from before the change still reference the old customer_sk.

Problem: A SKU might be retired and later reused for a different product line.

• Natural key candidate: sku.
• Approach: Use product_sk as PK. sku remains a descriptive attribute or a constrained business_key only if guaranteed unique and non-reused. For SCD2, each change to attributes like brand or size gets a new product_sk.
• Result: Historical orders still connect to the correct historical product definition.

Problem: A date like 2025-12-31 is inherently stable.

• Natural key: calendar_date (YYYY-MM-DD).
• Approach: For dim_date, you can use a deterministic integer surrogate like date_key = 20251231. Here the natural key is stable enough, so a surrogate is simply a standardized representation.
• Result: Efficient joins and clear readability.

-- Example generic SQL (adjust to your warehouse)
create table dim_customer (
  customer_sk      bigint generated always as identity primary key,
  business_key     varchar not null,         -- e.g., external_customer_id
  email            varchar,
  first_name       varchar,
  last_name        varchar,
  is_current       boolean default true,
  valid_from       timestamp not null,
  valid_to         timestamp
);
create unique index uq_dim_customer_bk_current
  on dim_customer(business_key, is_current) where is_current = true;

Notes: business_key is the stable natural key from the source used to match new records. Surrogate key is the join target from facts.

-- Use for de-dup or change detection, not as the dimension PK if you need SCD2 versions
select md5(coalesce(business_key, '')) as bk_hash,
       md5(coalesce(business_key,'')||'|'||coalesce(email,'')) as scd2_fingerprint
from staging_customer;

Use hashes to detect changes and to match across systems. For the dimension primary key, prefer an integer surrogate. Hash collisions are possible but extremely rare with strong hashes; still, treat as a practical, not perfect, approach.

-- Lookup current dimension row to fetch surrogate key
insert into fct_orders (order_id, order_ts, customer_sk, product_sk, amount)
select s.order_id,
       s.order_ts,
       d_c.customer_sk,
       d_p.product_sk,
       s.amount
from staging_orders s
join dim_customer d_c
  on d_c.business_key = s.external_customer_id and d_c.is_current = true
join dim_product d_p
  on d_p.business_key = s.product_code and d_p.is_current = true;

Facts reference surrogate keys. When a dimension changes, new facts automatically link to the new surrogate key version.

• Pick any dimension row. If an attribute changes, would facts from last month remain correct?
• Verify you can map any source record to exactly one current dimension row.
• Ensure all fact tables store only surrogate keys for dimension references.

• No duplicate current rows per business_key.
• Facts resolve to exactly one current dimension row.
• Historical facts keep their original meaning after dimension attribute changes.