Business Central: Slow Change Dimension

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.