Forum Discussion
Power Participantwhy 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
Power Participanttayloramy Thanks for sharing the use case. I start to get the gist. So the SK here makes sure each user is mapped to one SK. That means oen can use SK to create a clean logic when properties like UPN, PUID can change over time.
However, I'm still a bit confused, according to MS documentation, each dimension table must have a unique identifier column, but the SK in dim_user is not a unique identifier as two rows can be mapped to the same SK due to UPN change. The real dimension table is the UUID-SK mapping, but since UUID is unique for each user, why not simply use UUID as the primary key?
One last question, do you use SK in joining tables or building relationships? It seems to me each SK represents one unique user, hence it can be used in building relationships.
- tayloramy
Super User
Hi Jeanxyz,
THe SKs are unique, when a UPN changes, the new UPN is mapped to the SK of the old UPN using the user's Azure UUID and the mapping table.
So the SK represents the account as a whole. 1 SK per account, an account will never have 2 SKs regardless of the change of their UPN.Because the SKs are primary keys on the tables, I do use them to join the tables - that is their intended purpose. I then usually hide the SKs from users in PowrBI reports as they ahve no need to see this system generated number.
Depending on the dataset, I sometimes mnake a 64 bit hash of the SK to use as a join key for performance reasons, but it's the same concept.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.