topic Surrogate Key Limitation in Fabric Warehouse - No support for SEQUENCE or IDENTITY in Data Warehouse

Surrogate Key Limitation in Fabric Warehouse - No support for SEQUENCE or IDENTITY

FabricLearner — Thu, 27 Mar 2025 21:14:13 GMT

Hi Fabric Community,

While implementing a Slowly Changing Dimension (SCD) Type 2 pattern in Microsoft Fabric Warehouse (T-SQL), I ran into a key limitation I wanted to share:

Fabric Warehouse does not support IDENTITY or SEQUENCE for auto-generating surrogate keys.

This poses a challenge when building dimension tables where each version of a business entity (e.g., an employee) needs a unique surrogate key — especially in SCD Type 2 scenarios where multiple versions of a record exist.

I attempted the following:
• IDENTITY – not supported in Fabric Warehouse
• CREATE SEQUENCE – not supported either

Questions for the community:
1. Are there any recommended best practices for surrogate key generation in Fabric Warehouse?
2. Is support for SEQUENCE or IDENTITY on the roadmap?
3. How are others handling surrogate key generation for SCD or fact table loads in the Warehouse environment?

Looking forward to your thoughts and suggestions!

Re: Surrogate Key Limitation in Fabric Warehouse - No support for SEQUENCE or IDENTITY

lbendlin — Fri, 28 Mar 2025 00:18:31 GMT

Table constraints

SQL analytics endpoint and Warehouse in Microsoft Fabric support these table constraints:

PRIMARY KEY is only supported when NONCLUSTERED and NOT ENFORCED are both used.
FOREIGN KEY is only supported when NOT ENFORCED is used.
UNIQUE constraint is only supported when NONCLUSTERED and NOT ENFORCED are both used.
Primary, foreign, and unique keys - Microsoft Fabric | Microsoft Learn

Re: Surrogate Key Limitation in Fabric Warehouse - No support for SEQUENCE or IDENTITY

Anonymous — Fri, 28 Mar 2025 11:57:46 GMT

Hi FabricLearner ,
Thank you for posting in the Microsoft Fabric Community. Also thankyou lbendlin for your inputs.

Microsoft Fabric Warehouse currently does not support IDENTITY or SEQUENCE for auto-generating surrogate keys, which presents challenges when implementing Slowly Changing Dimension (SCD) Type 2 patterns or managing fact table loads.

However, there are alternative approaches to handle surrogate key generation effectively. One recommended approach is to use ROW_NUMBER (), which generates unique keys dynamically during batch inserts, though it does not persist across multiple sessions. Another approach is to maintain a key generation table, which tracks and increments key values manually to simulate the behavior of an identity column.

Additionally, using hash-based surrogate keys, where a combination of business keys and timestamps is hashed, can ensure uniqueness in SCD Type 2 scenarios.

For more details on managing unique identifiers in Fabric Warehouse, refer to the official Microsoft documentation:

Generate unique identifiers in a warehouse table - Microsoft Fabric | Microsoft Learn

Primary, foreign, and unique keys - Microsoft Fabric | Microsoft Learn

I hope my suggestion help you to get an idea, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

Re: Surrogate Key Limitation in Fabric Warehouse - No support for SEQUENCE or IDENTITY

Anonymous — Mon, 31 Mar 2025 17:12:46 GMT

Hi FabricLearner ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Surrogate Key Limitation in Fabric Warehouse - No support for SEQUENCE or IDENTITY

FabricLearner — Tue, 01 Apr 2025 14:44:06 GMT

I've successfully generated unique identifiers for the surrigate key using the NEWID() function. This ensures that each row has a distinct identifier, enhancing data integrity and uniqueness.

Re: Surrogate Key Limitation in Fabric Warehouse - No support for SEQUENCE or IDENTITY

Anonymous — Wed, 02 Apr 2025 08:35:56 GMT

Hello FabricLearner ,

Glad to hear that NEWID() worked for generating unique surrogate keys! It’s a reliable way to ensure uniqueness, but there are some considerations to keep in mind. Since NEWID() generates 16-byte GUIDs, it can lead to larger storage requirements and potential indexing inefficiencies compared to integer-based keys.

Additionally, because GUIDs are randomly distributed, they may cause index fragmentation, which can impact query performance over time. If performance becomes a concern, alternative approaches such as using ROW_NUMBER() during batch processing or maintaining a key-tracking table to simulate an identity column could be more efficient.

Another option is hash-based keys, where a combination of business keys and timestamps is hashed to maintain uniqueness while keeping storage overhead lower. It would be interesting to hear if you’ve noticed any performance implications with NEWID() so far and how it’s working in your specific use case.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

Re: Surrogate Key Limitation in Fabric Warehouse - No support for SEQUENCE or IDENTITY

Anonymous — Mon, 07 Apr 2025 02:24:03 GMT

Thank you.

Re: Surrogate Key Limitation in Fabric Warehouse - No support for SEQUENCE or IDENTITY

Anonymous — Fri, 11 Apr 2025 00:37:57 GMT

Hi FabricLearner ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.