why use surrogate key

Hi Jeanxyz, 

As I mentioned these get into some pretty advanced data engineering concepts. If you're able to work without them and and you're happy, then continue being happy. 

Here's a different example for where I'm using SKs: 

I am building a dim_user table for my org, and I'm collecting various things: a user's Azure UUID, UPN, and legacy powershell PUID, and name.

One of the main use cases for this table is to use with Fabric audit data from the REST APIs, which identify users by UPN or PUID, not UUID. 

To add a layer of complexity, PUID is not exposed to microsoft customers anymore. 

My process is to first extract users from Azure using the Graph API, which gives me a list of UUIDs and UPNs and names. 

I am then combing through the audit data to find records where PUID is populated as well as UPN so I can link in PUIDs - this results in not a complete list of PUIDs, but a workable list for active users in Fabric. 

In my org, UPNs can change, which creates a challenge as the fabric audit data uses the UPN as the key to identify who caused the action. 

I also don't want to need to fully reload my fact tables that depend on this dim_user table every night. 

To achieve this, when I build my dim_user table, I am greating SKs on the fly, and saving them to a SK mapping table that maps Azure UUIDs to the generated SK. 

Every night when I rebuild my dim_user table, using that maping table I am assigning users to the same SK every run so as to keep my fact table valid. This ensures that if a UPN changes, that user's record gets updated with the new UPN, but it maps to the same SK as their old UPN, and the audit data is still pointing to the correct user. 

I am then able to incrementally update my fact tables without a worry that a changed UPN will make my audit data link to invalid users. 

I hope this helps, 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution