A customer code arrives, and tomorrow the source system changes it. Surrogate keys give a warehouse its own stable row identity while natural keys keep the link to the source. Both keys have work to do, especially when history matters.
Give Surrogate Keys and Natural Keys Separate Jobs
A natural key comes from the business or source system, such as a customer code. A surrogate key is generated by the warehouse for a dimension row. Facts reference the surrogate key so they can point to the right historical version of a member. The natural key remains necessary for matching new source data to existing members.
I ask who owns the source key and whether it can change or be reused. A key called permanent in a requirements document can still be recycled after a merger. The warehouse should not build its entire history on that promise alone.
Keep Source Identity Visible
Store source system identifier and natural key in the dimension, with appropriate uniqueness rules for active records. Two source systems can both have CustomerID 42, so a bare number is not globally unique. Normalize types and collations before matching. Keep the original value for traceability when a cleaned value is also stored.
I put the source key in load logs and reject reports. A fact that cannot find its dimension should not disappear silently. The source key is the bridge for diagnosis even when the report only displays a friendly name.
Use Surrogate Keys for History
In a Type 2 slowly changing dimension, a customer can have several rows with the same natural key and different effective periods. Each row gets a distinct surrogate key. A fact event uses the key for the version active at the event time. That preserves the attributes that were true when the fact occurred.
A natural key alone cannot distinguish those versions. I test a customer whose region changed between two orders. If both orders join to the newest region, the historical model has lost its purpose. Surrogate keys make the distinction explicit.
Create a Small Dimension
This example shows a surrogate identity key and source key with effective dates. It is a teaching shape, not a complete production dimension. Add a source system identifier when several sources share codes. Check that date periods do not overlap for one natural key during loading.
The unique constraint includes the start date to allow history. A separate filtered unique index can enforce one current row when the model has an IsCurrent flag.
CREATE TABLE dbo.DimCustomer
(
CustomerKey bigint IDENTITY(1,1) NOT NULL
CONSTRAINT PK_DimCustomer PRIMARY KEY,
SourceCustomerID nvarchar(50) NOT NULL,
CustomerName nvarchar(200) NOT NULL,
EffectiveFrom datetime2(0) NOT NULL,
EffectiveTo datetime2(0) NOT NULL,
CONSTRAINT UQ_DimCustomer_Source_Start
UNIQUE (SourceCustomerID, EffectiveFrom)
);
Resolve Keys During Fact Loads
A fact load uses the source natural key and event time to find the correct dimension row. Join on source key and a half-open effective interval. If no row matches, apply a documented unknown-member or reject policy. If two rows match, fail the load and repair overlapping dimension periods.
I count unmatched and multiply matched facts before insertion. A simple join that silently drops unmatched rows can make revenue disappear from reports. The query below identifies facts with no valid dimension version.
SELECT s.SourceCustomerID, s.SaleDate
FROM dbo.FactSaleStage AS s
LEFT JOIN dbo.DimCustomer AS d
ON d.SourceCustomerID = s.SourceCustomerID
AND s.SaleDate >= d.EffectiveFrom
AND s.SaleDate < d.EffectiveTo
WHERE d.CustomerKey IS NULL;Do Not Confuse Surrogate With Meaning
A surrogate key is an internal identifier. It should not encode region, status, or other business attributes. Those values change and belong in columns. An identity value can be narrow and efficient, but it does not prove uniqueness of a source customer. Enforce natural-key rules separately.
I keep the surrogate out of external contracts where possible. Sending it back to a source system can create a dependency on warehouse row versions the source does not understand. Use source identifiers for exchange and surrogate identifiers for warehouse joins.
Handle Natural-Key Changes
When the source corrects or replaces a natural key, decide whether the warehouse treats it as the same entity. Keep a crosswalk from old key to stable business identity if needed. Do not update every historical fact just because a code changed unless the business definition requires restatement.
I test a corrected customer code and a reused old code. Those cases reveal whether the lookup logic depends on a value that is not truly stable. Document the decision so future loads do not invent a second customer by accident.
Protect Lookup Performance for Surrogate Keys
Dimension lookups run for many staged facts. Index source system, natural key, and effective dates according to the load predicate. A Type 2 lookup can return several rows before the date filter narrows it. Check plans and row counts under a representative load. An index that helps lookup also adds cost to dimension updates.
The query below shows whether more than one dimension version claims the same source key and start time. It supplements, not replaces, the actual overlap test.
SELECT SourceCustomerID, EffectiveFrom,
COUNT_BIG(*) AS version_count
FROM dbo.DimCustomer
GROUP BY SourceCustomerID, EffectiveFrom
HAVING COUNT_BIG(*) > 1;Keep the Two Keys Together
Surrogate and natural keys are complementary. The surrogate gives a stable warehouse row identity. The natural key ties that row to a source and supports reconciliation. Effective dates and ownership rules complete the relationship. None of these columns should be added by habit without a clear load and report use.
Surrogate keys and natural keys work well when the team can trace a fact back to the source and still preserve history. The schema is correct when an old report can answer what was known then and a new load can find the right member now.
A fact load should resolve a natural key to exactly one valid surrogate key for the event date. A Type 2 dimension can hold several versions of the same natural key, so a join on the natural key alone can multiply facts. Add the effective date predicate and check for overlaps before publishing.
I keep an unknown member policy for facts whose dimension row has not arrived. The load can reject them or use a documented unknown surrogate key and later repair them. Silent NULL foreign keys make reports harder to reconcile. The natural key remains useful for matching source changes, while the surrogate key ties a fact to the intended historical version. Each key does a different job, and both should be visible in the load log.
When a customer attribute changes, which dimension version should an older sale still reference?
Related reading on this blog: What is Slowly Changing Dimension: Quiz: Puzzle: 31 of 31 and Lookups in a Data Load: Cached, Uncached and Wrong.
A surrogate key is not a replacement for source identity, it is a stable identity for a warehouse row.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
