Update rows in dimension excluding the surrogate key / MERGE

[ObungiNiels]

Hi all, 

we are using a data warehouse for creating dimension tables for reporting. The entries have surrogate keys and it is possible, that existing rows are updated with new data. 

During the update, we want to make sure that the surrogate keys of existing records in the dimension table do not change. This would be possible with a merge statement while excluding the surrogate key column, which is not possible in a data warehouse stored procedure (see limitations: T-SQL Surface Area in Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn).

We have a solution in mind which involves creating a temporary table while running the CTE in a stored procedure. However, this seems expensive and development-heavy.

Do you know a good way to manage the updates in this scenario? 

Thanks a lot! Kind regards,

Niels

[nilendraFabric · Accepted solution]

Hi @ObungiNiels 

I think you are going in right direction 

Using a staging table in Fabric Warehouse allows you to separate the incoming data load from the final updates and inserts into your dimension table, thereby preserving surrogate keys.

2 steps process:

Update Existing Records: Use the staging table to update mutable attributes in the dimension table by matching on the business key, ensuring surrogate keys remain unchanged.
2. Insert New Records: Insert unmatched rows from the staging table into the dimension table, generating new surrogate keys for these records

https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-dimension-tables

[v-ssriganesh]

Hi @ObungiNiels,
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.

[v-ssriganesh]

Hi @ObungiNiels,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @nilendraFabric for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[nilendraFabric · Accepted solution]

Hi @ObungiNiels 

I think you are going in right direction 

Using a staging table in Fabric Warehouse allows you to separate the incoming data load from the final updates and inserts into your dimension table, thereby preserving surrogate keys.

2 steps process:

Update Existing Records: Use the staging table to update mutable attributes in the dimension table by matching on the business key, ensuring surrogate keys remain unchanged.
2. Insert New Records: Insert unmatched rows from the staging table into the dimension table, generating new surrogate keys for these records

https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-dimension-tables

[ObungiNiels · Accepted solution]

Hi  @nilendraFabric,

thanks for the confirmation. This is how he implemented it in the end:

In each Stored procecure, we are following these steps. 

• CREATE a temporary table A with same schema as the dimension table B
• Write a CTE with INSERT statement to load all data from the source table to temp table A
• Run an UPDATE query which updates all values in dimension table B EXCEPT the surrogate key for all matching entries between table A and B based on the identifier column(s)
• Run an IMPORT INTO statement and insert all entries which are in table A but not in table B (WHERE NOT EXISTS (SELECT statement matching on identifier column(s) )
• DROP temp table A

Our concerns regarding performance remain due to the fact that tables are being created and dropped regularly with this approach. Hoping to see MERGE statements coming in for Fabric warehouses in the future. 🙂 

[v-ssriganesh]

Hi @ObungiNiels,

We appreciate your efforts and are pleased to hear that your issue was resolved. Please mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you.