Slowly Changing Dimension: Expiration field from the Source table

Question

I need to implement slowly changing dimension (SCD) type 2 logic in Azure Data Factory. The row expiration logic is based on a field in the source table called 'last_modified_date' For a new record, this value should be the 'start_date', and for a changed record, it should be the 'end_date'.

How to achieve this in ADF?

I followed this youtube video but it would not work if the expiry date is coming from the source but works fine with the current_date as start date.

Answer 1

• In order to update the end_date column with last_updated_value of source csv, you can change the broadcast settings of exist transformation as shown in below image.

• This setting will make sure that data from csv and sql table are displayed as output of exist transformation.
• Then take the derived column transformation and set the value for isactive as 0 and end_date as last_modified_date

This way, you can add the value of last_modified_date in the enddate column.