The two keys answer different identity questions

A code can be unique today yet change through migration, merger, reuse, or a historical dimension update.

A warehouse-controlled surrogate carries no business meaning and keeps facts attached to the intended dimension row.

Namespace business keys by source

Customer 1001 in CRM is not automatically customer 1001 in ERP. Mapping needs source, native key, canonical entity, and effective period.

Do not share a surrogate merely because codes or names look alike before master-data resolution.

History creates several rows for one entity

A Type 2 change in territory, tier, or owner creates another row and surrogate while old facts retain the prior state.

Fact loading should match the row valid at event time rather than always using the current member.

Unknown and late members need explicit treatment

An early fact can reference a defined unknown member and be remediated later. Do not overload one key for unknown, not applicable, and error.

Key generation must be idempotent so a rerun does not assign a different surrogate to the same version.

Accept through migration and history cases

Test equal codes across sources, code changes, reuse, entity merge, late dimensions, and reruns while reconciling fact count and historical attributes.

BuildTable can be evaluated for dimension design; key generation, historical matching, entity mapping, and remediation orchestration need confirmation.

Separate source, canonical, and historical identity

Model a namespaced source business key, a master-data canonical entity, and a warehouse surrogate for one dimension-version row. Equal codes across systems do not establish entity equality.

A Type 2 change creates another surrogate for the same canonical entity, preserving the attributes valid when a fact occurred.

Generate keys idempotently without business meaning

Sequence, deterministic hash, or platform ID can work if uniqueness and rerun stability are enforced. Hash inputs and null normalization must be fixed; sequence loads need a natural uniqueness constraint.

Do not encode region, year, or source into a supposedly technical key. Put interpretation in attributes and mapping tables.

Match Type 2 versions by event time

Maintain non-overlapping effective intervals and match valid-from <= event-time < valid-to. A late fact must not attach to the current row merely because it was loaded today.

Standardize interval boundaries and timestamp precision. Historical corrections need impact analysis and controlled fact remediation.

Handle unknown, late, merge, and split explicitly

Use distinct special members for pending, not applicable, anonymous, and error. Late remediation changes the foreign key without changing fact count or measures.

Support as-was and as-is identity views when entity merges should not erase audited history. One destructive update cannot serve both questions.

Test migration and verify BuildTable scope

Exercise equal cross-source codes, key reuse, Type 2 boundaries, late facts, remediation, merge, concurrency, and replay. Test interval overlap, referential integrity, and measure conservation.

BuildTable may be assessed for dimension design, while key generation, SCD, entity resolution, and backfill need verification. Stable controlled dimensions do not universally require surrogates.

Why does a dimension need a surrogate key when the customer code is already unique?