Surrogate Key Assignment, Natural Key Reuse, Source-System Key Collisions, and Durable Identity

Learning outcomes

AtlasMart receives customer identifiers from CRM, a marketplace, and a legacy system. The string C001 appears in more than one source, and legacy key 77 is eventually reused. If the warehouse treats those strings as globally unique customer keys, unrelated people can be merged before any SCD logic even begins.

1. Four identity layers, four jobs

A natural/business key is assigned by a source system. A source-qualified key adds the source namespace so CRM:C001 and MARKETPLACE:C001 are not accidentally treated as the same record. A durable identity is the warehouse's persistent identity for the entity across source-key changes and Type 2 versions. A dimension surrogate key identifies one specific dimension version row.

The tiny identity-map fixture intentionally includes identities with no sales facts. The point is to prove the identity rule, not to imply every mapped source identity is already represented in every downstream mart.

2. DDL that makes the distinctions observable

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS deletion_manifest;DROP TABLE IF EXISTS source_tombstone;DROP TABLE IF EXISTS correction_ledger;DROP TABLE IF EXISTS dimension_change_ledger;DROP TABLE IF EXISTS fact_sales_revision;DROP TABLE IF EXISTS fact_event_registry;DROP TABLE IF EXISTS dim_customer_history;DROP TABLE IF EXISTS identity_map;CREATE TABLE identity_map (  source_system TEXT NOT NULL,  source_customer_id TEXT NOT NULL,  valid_from TEXT NOT NULL,  valid_to TEXT NOT NULL,  durable_customer_id TEXT NOT NULL,  identity_note TEXT NOT NULL,  CHECK(valid_from < valid_to),  PRIMARY KEY(source_system,source_customer_id,valid_from));CREATE TABLE dim_customer_history (  customer_sk INTEGER PRIMARY KEY AUTOINCREMENT,  durable_customer_id TEXT NOT NULL,  source_system TEXT NOT NULL,  source_customer_id TEXT NOT NULL,  customer_name TEXT NOT NULL,  segment TEXT NOT NULL,  geography_code TEXT NOT NULL,  lifecycle_status TEXT NOT NULL,  privacy_state TEXT NOT NULL DEFAULT 'normal',  effective_from TEXT NOT NULL,  effective_to TEXT NOT NULL,  is_current INTEGER NOT NULL CHECK(is_current IN (0,1)),  CHECK(effective_from < effective_to),  UNIQUE(durable_customer_id,effective_from));CREATE UNIQUE INDEX ux_customer_one_current  ON dim_customer_history(durable_customer_id)  WHERE is_current=1;CREATE TABLE fact_event_registry (  source_event_id TEXT PRIMARY KEY,  order_id TEXT NOT NULL,  line_no INTEGER NOT NULL,  first_seen_at TEXT NOT NULL);CREATE TABLE fact_sales_revision (  fact_revision_sk INTEGER PRIMARY KEY AUTOINCREMENT,  order_id TEXT NOT NULL,  line_no INTEGER NOT NULL,  source_event_id TEXT NOT NULL,  event_ts TEXT NOT NULL,  durable_customer_id TEXT NOT NULL,  customer_sk INTEGER NOT NULL REFERENCES dim_customer_history(customer_sk),  product_id TEXT NOT NULL,  quantity INTEGER NOT NULL CHECK(quantity>0),  extended_amount NUMERIC NOT NULL CHECK(extended_amount>=0),  extended_cost NUMERIC NOT NULL CHECK(extended_cost>=0),  revision_no INTEGER NOT NULL,  is_current INTEGER NOT NULL CHECK(is_current IN (0,1)),  correction_id TEXT,  recorded_at TEXT NOT NULL,  UNIQUE(order_id,line_no,revision_no));CREATE UNIQUE INDEX ux_sales_one_current_revision  ON fact_sales_revision(order_id,line_no)  WHERE is_current=1;CREATE TABLE dimension_change_ledger (  event_id TEXT PRIMARY KEY,  durable_customer_id TEXT NOT NULL,  attribute_name TEXT NOT NULL,  business_effective_from TEXT NOT NULL,  old_value TEXT NOT NULL,  new_value TEXT NOT NULL,  applied_at TEXT NOT NULL);CREATE TABLE correction_ledger (  correction_id TEXT NOT NULL,  item_key TEXT NOT NULL,  correction_type TEXT NOT NULL,  reason TEXT NOT NULL,  authorized_by TEXT NOT NULL,  requested_at TEXT NOT NULL,  applied_at TEXT NOT NULL,  before_hash TEXT NOT NULL,  after_hash TEXT NOT NULL,  PRIMARY KEY(correction_id,item_key));CREATE TABLE source_tombstone (  tombstone_id TEXT PRIMARY KEY,  source_system TEXT NOT NULL,  source_customer_id TEXT NOT NULL,  durable_customer_id TEXT NOT NULL,  observed_at TEXT NOT NULL,  business_effective_at TEXT NOT NULL,  reason TEXT NOT NULL,  payload_hash TEXT NOT NULL);CREATE TABLE deletion_manifest (  request_id TEXT PRIMARY KEY,  durable_customer_id TEXT NOT NULL,  action TEXT NOT NULL,  scope TEXT NOT NULL,  policy_context TEXT NOT NULL,  requested_at TEXT NOT NULL,  applied_at TEXT NOT NULL,  before_hash TEXT NOT NULL,  after_hash TEXT NOT NULL,  status TEXT NOT NULL);SELECT source_system,source_customer_id,valid_from,valid_to,durable_customer_idFROM identity_mapORDER BY source_system,source_customer_id,valid_from;

The warehouse does not infer durable identity from matching text. It resolves a source-qualified key at the relevant validity interval, then resolves a version surrogate key from business event time.

3. Deliberately wrong approach: natural key as dimension primary key

A fact table that stores only customer_id='C001' cannot tell whether the row came from CRM or Marketplace. A legacy fact with key 77 cannot tell which identity epoch is intended after key reuse. Appending an effective date to the natural key also fails to establish enterprise identity; it merely manufactures another source-shaped identifier.

-- Deliberately ambiguous:SELECT f.order_id,d.customer_nameFROM fact_sales fJOIN dim_customer d ON d.source_customer_id=f.customer_id;-- Safer pipeline idea:-- 1) resolve (source_system, source_customer_id, event_ts) -> durable_customer_id-- 2) resolve (durable_customer_id, event_ts) -> customer_sk-- 3) persist the version surrogate key in the fact

The second step is temporal because Type 2 history can produce multiple dimension rows for the same durable identity.

4. Baseline controls remain unchanged

Changing key architecture must not silently change revenue, order, unit, cost, or profit controls. A key migration is accepted only after referential-integrity and reconciliation checks pass.

5. Unknown members and unresolved identities

AtlasMart retains a governed D-UNKNOWN dimension member for cases that truly cannot yet resolve. But unresolved source identifiers should be retained in an error/placeholder workflow so they can be repaired later. Collapsing every unresolved fact onto one anonymous row without preserving the source identity makes later correction difficult.

6. Production judgment

Identity policy needs ownership. Decide which source is authoritative, when two source identities represent the same entity, how mergers/splits are handled, whether source-key reuse is allowed, and how changes propagate to conformed dimensions. A simple sequential integer surrogate key is an implementation detail; the hard part is the durable identity contract behind it.

Knowledge check

Summary and next step

Chapter 11 begins by separating identity continuity from dimension-version identity. Lesson 2 uses that distinction to load a genuinely late fact without binding it to the customer version that happens to be current at load time.

Authoritative references

• Kimball Group — Dimension Surrogate Keys — Why warehouse dimensions need warehouse-controlled surrogate keys rather than relying only on operational natural keys.
• Kimball Group — Natural, Durable, and Supernatural Keys — Reference for persistent durable identity distinct from mutable/reused natural keys and from Type 2 version surrogate keys.
• Kimball Group — Late Arriving Fact — Late facts must resolve dimension keys that were effective when the measurement event occurred.
• Kimball Group — Late Arriving Dimension — Placeholder/late-dimension handling and retroactive Type 2 changes that may require fact restatement.
• Kimball Group — Dimensional Modeling Techniques — Authoritative technique index for surrogate keys, late-arriving facts/dimensions, SCDs, and related dimensional patterns.
• European Commission — Information for individuals — Official overview of GDPR data-subject rights, including the right to erasure and its limits.
• European Commission — Dealing with requests from individuals — Official guidance explaining that erasure applies in certain cases and has exceptions; technical deletion scope must follow the applicable controller/legal policy.
• SQLite — CREATE TABLE — Constraint semantics used by the deterministic local fixture.
• SQLite — Partial Indexes — Used by the lab to enforce one current customer version and one current fact revision per business key.