topic Re: IDENTITY column for generation of Surrogate keys in Fabric Warehouse in Data Warehouse

IDENTITY column for generation of Surrogate keys in Fabric Warehouse

VirajD — Tue, 29 Apr 2025 09:42:05 GMT

Hi,

Since Fabric Warehouse does not support the IDENTITY functionality on a column for Surrogate key generation, is anyone aware of any alternate practical method to generate surrogate keys apart from the NEWID() function and MAXID + ROW_NUMBER() approach ?

Re: IDENTITY column for generation of Surrogate keys in Fabric Warehouse

DataBard — Tue, 29 Apr 2025 17:21:09 GMT

Hi VirajD ,

One other approach I've seen used in Fabric is creating a hash of your data and using that as a surrogate key.

For example if you have a PersonID, you could do this:

HASHBYTES('SHA2_256', CONVERT(varchar(800), PersonID))

Depending on your definition, it's not a surrogate key because it's derived from your existing data.

If this isn't a viable option, I haven't seen any alternatives to date beyond the ones you've ruled out. You'd likely need to either build your own solution to create a surrogate or otherwise find another option.

Re: IDENTITY column for generation of Surrogate keys in Fabric Warehouse

v-prasare — Mon, 05 May 2025 11:34:58 GMT

Hi VirajD,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

Re: IDENTITY column for generation of Surrogate keys in Fabric Warehouse

VirajD — Thu, 08 May 2025 06:08:36 GMT

The solution provided is accepted.

Re: IDENTITY column for generation of Surrogate keys in Fabric Warehouse

payyan_s — Wed, 12 Nov 2025 16:48:01 GMT

Not making much sense to me. The identity column is giving protection and feasibility to the user to update any other column values without creating duplicates. When you use haskey, will it not change when the underlying data is changed and lead to potential duplicate values?