Hi @Jeanxyz,
Why surrogate keys matter (even if you build in Power BI)
Surrogate keys (SKs) are warehouse-generated IDs that identify each row version of a dimension. They're not for end-user search. They exist to make your star schema stable, performant, and historically correct. Microsoft's guidance and Kimball's dimensional modeling both recommend them for analytic models. Microsoft: Star schema & surrogate keys, Kimball Group: Surrogate keys.
What surrogate keys give you
- History (SCD Type 2): When a customer's attributes change (segment, region, name), you insert a new dimension row with a new SK and date ranges, while the old row is retained. Facts for old dates point to the old SK; new facts point to the new SK. This is the canonical pattern and requires SKs. Kimball: SCD Type 2 requires a new surrogate key per change, Microsoft Fabric: SCD Type 2.
- Stable joins from facts: Fact tables store foreign keys to dimension SKs, not business keys, so queries always resolve to the correct historical version. Microsoft's warehouse loading guidance explicitly shows looking up the dimension SK when processing facts. Microsoft: Process fact tables with SK lookups.
- Insulation from business key changes and multi-source integration: Business keys (emails, product codes, GUIDs) can change, collide across systems, or be composite. SKs decouple the warehouse from those issues and let you conform dimensions across sources. Microsoft: Dimension tables & surrogate keys.
- Performance & storage: Narrow integer keys reduce index size and can speed joins compared to long or composite natural keys. Kimball Group: performance & storage rationale.
"But I can't use SKs for search or relationships in Power BI."
End users shouldn't search on SKs; you hide them. But your relationships should still use SKs when you have SCD Type 2 or conformed dimensions. That ensures each fact connects to the correct historical row. Microsoft's star-schema guidance assumes SK-based dimensional modeling even if the report only shows business attributes. Microsoft: Star schema guidance.
"Won't SKs change every reload?"
They must not. If SKs are changing on reload, that's a loading anti-pattern (e.g., truncating and re-inserting the dimension). The fix is to upsert with a business key (BK) lookup so existing members keep their SK, and only SCD-relevant changes create a new SK (and close the old one's date range). Microsoft: Fact processing with SK lookups, Kimball: SCD2 mechanics.
Minimal example
-- Dimension (note the surrogate key and the business key)
CREATE TABLE dbo.DimCustomer (
CustomerSK INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
CustomerBK NVARCHAR(50) NOT NULL, -- natural/business key from source
Name NVARCHAR(200) NOT NULL,
Segment NVARCHAR(50) NOT NULL,
EffectiveStart DATETIME2(3) NOT NULL,
EffectiveEnd DATETIME2(3) NOT NULL,
IsCurrent BIT NOT NULL
);
CREATE UNIQUE INDEX UX_DimCustomer_Current
ON dbo.DimCustomer(CustomerBK, IsCurrent)
WHERE IsCurrent = 1;
-- Stage holds latest snapshot from source system
-- 1) Close out rows that changed (SCD2 detect)
UPDATE d
SET EffectiveEnd = SYSUTCDATETIME(), IsCurrent = 0
FROM dbo.DimCustomer d
JOIN dbo.StgCustomer s
ON s.CustomerBK = d.CustomerBK
WHERE d.IsCurrent = 1
AND (d.Name <> s.Name OR d.Segment <> s.Segment);
-- 2) Insert new current versions (new SKs) for new or changed BKs
INSERT dbo.DimCustomer (CustomerBK, Name, Segment, EffectiveStart, EffectiveEnd, IsCurrent)
SELECT s.CustomerBK, s.Name, s.Segment, SYSUTCDATETIME(), '9999-12-31', 1
FROM dbo.StgCustomer s
LEFT JOIN dbo.DimCustomer d
ON d.CustomerBK = s.CustomerBK AND d.IsCurrent = 1
WHERE d.CustomerBK IS NULL
OR d.Name <> s.Name
OR d.Segment <> s.Segment;
-- Fact load: look up the SK for the BK that was current on the fact date
INSERT dbo.FactSales (CustomerSK, SaleDate, Amount)
SELECT d.CustomerSK, f.SaleDate, f.Amount
FROM dbo.StgSales f
JOIN dbo.DimCustomer d
ON d.CustomerBK = f.CustomerBK
AND d.IsCurrent = 1
AND f.SaleDate >= d.EffectiveStart
AND f.SaleDate < d.EffectiveEnd;
When you might skip SKs
If your dimension is tiny, static, single-sourced, and you don't need history (no SCD), you can get by with a durable natural key. But once you need history, conformance across systems, or better join characteristics, surrogate keys are the safer default. Microsoft: Star schema guidance, Microsoft: Dimensional modeling overview.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
