Share via

Business Central: Slow Change Dimension

Jorge Marmol Rivera 0 Reputation points
2024-07-29T16:27:49.4233333+00:00

Hi folks,

 

I need to apply a Slowly Changing Dimension (SCD type 1) using Azure Databricks with data from an on-premise Business Central database. I want to build a medallion architecture.

 

I am bringing the tables to the bronze level by selecting data incrementally using the field _systemModifiedAt. For building the silver level, I want to perform a Merge using the field _systemId. Is this possible? Specifically, is _systemId a unique identifier for every record in all the tables of Business Central, allowing me to apply a Merge for SCD type 1?

 

When a record changes in Business Central, does it produce a new record in the database table with the same _systemId but with updated information?

 

Many thanks!

Jorge.

Azure
Azure
A cloud computing platform and infrastructure for building, deploying and managing applications and services through a worldwide network of Microsoft-managed datacenters.
1,261 questions
Dynamics 365 Training
Dynamics 365 Training
Dynamics 365: A Microsoft cloud-based business platform that provides customer relationship management and enterprise resource planning solutions.Training: Instruction to develop new skills.
180 questions
Microsoft Fabric Training
Microsoft Fabric Training
Microsoft Fabric: A Microsoft unified data platform.Training: Instruction to develop new skills.
36 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Suwarna S Kale 2,131 Reputation points
    2025-05-01T03:02:07.48+00:00

    Hello Jorge Marmol Rivera,

    Thank you for posting your question in the Microsoft Q&A forum. 

    Yes, you can apply an SCD Type 1 (overwrite changes) in Azure Databricks using _systemId as the unique identifier for merging records in the Silver layer. 

    1. Is _systemId a Reliable Unique Key? 

    Yes, _systemId is a GUID that uniquely identifies each record in Business Central tables. 

    Unlike surrogate keys, _systemId remains constant even when records are updated (unlike SQL auto-increment IDs). 

    This makes it ideal for MERGE operations in Delta Lake. 

    2. Does Business Central Generate New Records on Update? 

    • No, Business Central does not create a new record when a row is updated. 

    Instead, it modifies the existing record while keeping the same _systemId. 

    The _systemModifiedAt field is updated to reflect the latest change. 

    If the above answer helped, please do not forget to "Accept Answer" as this may help other community members to refer the info if facing a similar issue. Your contribution to the Microsoft Q&A community is highly appreciated. 

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.