Designing Dimensions Right: Identity Columns in Fabric Warehouse Explained

[AparnaRamakris]

For years, Fabric Warehouse builders have asked for a simple, database‑managed way to generate surrogate keys which is the backbone of dimensional models, SCD Type 2 histories, fact‑to‑dimension relationships, and robust referential integrity. That capability is now available with Identity columns in Microsoft Fabric Warehouse (Preview), bringing automatic, system‑managed unique values to every inserted row without custom ETL logic or application‑side scripts.

Uses of Surrogate keys :

• Ensures consistency and integrity across the data warehouse.
• Surrogate keys also simplify the relationships between tables, as they can be used as primary keys and foreign keys without any additional logic or transformation.
• Surrogate keys can improve the performance of queries and joins, as they are usually shorter and simpler than natural keys.

Practically, this is a game changer for Fabric because it reduces ETL complexity, improves migration parity with SQL‑centric warehouses, and hardens data integrity across concurrent COPY INTO loads. You define a BIGINT IDENTITY in your table schema, load data while omitting the identity column from your source mapping, and let Fabric handle keys.

Before the availability of this feature the surrogate keys were generated either using a window function or by using some control table to hold the value of the surrogate key like a sequence .Both approaches had its own drawbacks and requires more ETL heavy logic being written.

Creating a Dimension Table  using the new IDENTITY Feature in Fabric Datawarehouse 

CREATE TABLE dbo.Dim_Product
(
ProductId BIGINT IDENTITY,
ProductName VARCHAR(100),
Category VARCHAR(100),
ListPrice VARCHAR(100)
);

Limitations :

• Currently Fabric Datawarehouse does not support the IDENTITY with Seed or increment like IDENTITY(1,1) which we normally do in SQL.
• The IDENTITY column data need not be consequetive or have a definite range.
• The IDENTITY column should always be of a BIGINT datatype.
• IDENTITY column cannot be updated.

COPY INTO dbo.Dim_Product (ProductName,Category,ListPrice)
FROM 'https://<onelake>/<workspace-id>/Files/products.csv'
WITH (
    FILE_TYPE = 'CSV'
);

We can see from the data that a unique ID is created which can be used as surrogate key in our data models 

We can see the unique bigint value getting generated for the ProductID which are unique across each row.Identity columns eliminate MAX + ROW_NUMBER patterns, key‑control tables, and race conditions—bringing warehouse‑managed surrogate keys back where they belong.

Conclusion - A small feature with a big architectural impact 

Surrogate keys are not an optional design choice in a dimensional warehouse—they are foundational. With the introduction of Identity columns in Microsoft Fabric Warehouse, surrogate key generation finally becomes what it should be: a native responsibility of the warehouse itself.This shift has a ripple effect across the entire analytics stack. Pipelines get simpler because they no longer need to “think” about keys. Ingestion becomes safer under parallel and streaming workloads. Dimensional models become easier to reason about, easier to migrate from traditional SQL warehouses, and far more resilient at scale. Perhaps most importantly, data engineers and warehouse designers can focus on modeling and business logic instead of defensive key‑management patterns.

Identity columns may look like a small addition on paper, but in practice they remove one of the most persistent sources of complexity in modern data warehouse implementations. For anyone building star or snowflake schemas in Fabric—especially at enterprise scale—this is a genuinely game‑changing capability.As Fabric Warehouse continues to mature, features like Identity columns signal a clear direction: enterprise‑grade warehousing, simplified, without sacrificing correctness, performance, or modeling best practices.