Internal Identity Versus Business Identity
A business key comes from the domain or source system. An account number, product code, or employee identifier may be meaningful to users and is often the value that arrives with new data. A surrogate key is assigned inside the warehouse and exists only to identify a stored row. It should not encode a region, category, date, or other attribute that can change.
Separating the keys protects the warehouse from source instability. A source may widen an identifier, change its format, recycle a deleted code, or merge records. Several sources may also use the same value for unrelated entities. The warehouse can keep a consistent internal key while retaining each source identifier, source-system context, and matching rule as descriptive integration data.
Surrogate keys do not remove the need to define business uniqueness. Loading logic still needs to know which source fields identify the same real entity and under what scope. A warehouse-managed key can make every row technically unique while duplicates remain semantically unresolved. Key design succeeds only when internal identity and business matching are both explicit.
Why Historical Dimensions Depend on Row Identity
When a dimension preserves change history, one business entity can have several rows. Each row represents a version that was valid during a particular interval. The business key is shared across those versions, so it cannot tell a fact which historical description to use. A distinct surrogate key for every version provides that precise reference.
During fact loading, the pipeline uses the business identifier and event time to locate the applicable dimension version, then stores its surrogate key. Earlier facts continue pointing to the earlier version after a new one is created. Reports can therefore reproduce the context attached to each event without joining through uncertain date conditions every time they run.
Special members also benefit from managed keys. An unresolved source reference, a value declared not applicable, and a genuinely missing value can have different warehouse members so reports do not collapse them into one null category. Their meaning must be documented and consistent across related models. When the real member later arrives, the repair policy determines whether existing facts are reassigned or retain the original unresolved state for audit.
Generate and Load Keys Predictably
A key can be allocated from a sequence or derived deterministically from stable input. Sequential allocation is compact and simple within one managed loading path, but parallel pipelines need coordination to prevent collisions. Deterministic generation can reproduce the same result across runs, yet it depends on careful normalization, namespace rules, and collision handling. Choose according to integration and recovery requirements rather than appearance.
The dimension should be loaded before dependent facts whenever possible. Incoming business keys are matched to existing members, material changes create the required dimension state, and the resulting surrogate keys are supplied to the fact load. If a reference cannot be resolved, route it through the documented special member and record enough detail to retry. Silently dropping the fact or leaving an unconstrained null hides the quality issue.
Reruns must not assign a fresh key to an unchanged member. Test idempotence by replaying input and confirming that keys remain stable. Also protect uniqueness at the database level and monitor attempted duplicates, unresolved lookups, and unexpected remapping. A key-generation mechanism is only dependable when its loading workflow behaves consistently after retries and partial failures.
Keep Surrogate Keys Within Their Proper Role
Surrogate keys simplify joins and history, but they should not become user-facing identifiers or cross-system business contracts. Their values may differ between environments after reloads or migration. Reports should display recognized business identifiers and descriptions, while lineage tools retain the mapping needed to trace a warehouse row back to its sources.
Not every table needs a surrogate key. A stable reference with an immutable, compact, globally unique business identifier may gain little from another column. Bridge and fact tables also have different uniqueness needs that may be expressed through their grain. Add a managed key when it resolves a specific problem involving history, source integration, physical joins, or row identity.
Use the worksheet on this page to document each entity's business key, source scope, history behavior, generation method, and unresolved-member policy. Review the map during migrations and source onboarding because new identifier scopes can invalidate old assumptions. A surrogate key is effective when it remains meaningless to the business, stable for warehouse relationships, and backed by matching rules that explain which real entity each row represents.
Key strategy depends on source stability, history needs, and loading architecture, so use surrogate keys to solve defined identity problems.