Forum Discussion

marmoljorge98's avatar
marmoljorge98
Copper Contributor
Jul 29, 2024

Business Central: Slow Change Dimension

Hi folks,

 

I need to apply a Slowly Changing Dimension (SCD) 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?

 

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.

1 Reply

  • rajeshgan's avatar
    rajeshgan
    MCT
    Aug 31, 2026

    SystemId is a unique identifier for the Business Central record, but Business Central does not create a new version of that record every time it is modified.

     

    When an existing record is changed, the same record and SystemId remain, while SystemModifiedAt is updated to reflect the latest modification.

     

    So using SystemId as the key and SystemModifiedAt as the incremental watermark is a good approach for identifying records that have changed since the previous extraction. However, by itself this only gives you the current/latest state of the Business Central record.

     

    If your objective in Databricks is to build an SCD Type 2 dimension and retain historical versions, that history needs to be created in your data pipeline/lakehouse. When a newer version of the same SystemId arrives, compare it with the current dimension record, expire the previous version and insert the new version with the appropriate effective dates/current-row flag.

     

    If you specifically need the history of individual changes made inside Business Central, you would need a separate auditing mechanism such as the Business Central Change Log for the relevant tables/fields. That is different from using SystemModifiedAt for incremental extraction.

     

    One additional consideration is deletes: an incremental process based only on SystemModifiedAt should also have a strategy for detecting records that have been deleted from Business Central.