Solved: SCD Type 2 in ADF only works when update sink runs...

AhmedShalaby · ‎10-16-2025

Hi everyone,

I'm implementing Slowly Changing Dimension Type 2 (SCD2) in Azure Data Factory using two sinks:

One for updating existing records (setting IsActive = 0 when changes are detected)
One for inserting new records (with IsActive = 1 and new hash value).

I noticed that the pipeline only works correctly if I run the update sink first and then the insert sink.
If I run the insert sink first, both old and new rows end up having IsActive = 1.

Can someone please explain why the order of sinks matters in this case?

Thank you

tayloramy · ‎10-16-2025

Hi @AhmedShalaby,

When you run the insert sink first, you create the new "current" row (IsActive = 1) before your update sink tries to retire the old one. Most SCD2 update predicates look for the active row for that business key whose attributes differ. After the insert has happened, the row that is “active” and matches the source hash is the new row, so the update sink no longer finds the old row to set IsActive = 0. That’s why both rows remain IsActive = 1 unless you do the update step first.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

View solution in original post

tayloramy · ‎10-16-2025

Hi @AhmedShalaby,

When you run the insert sink first, you create the new "current" row (IsActive = 1) before your update sink tries to retire the old one. Most SCD2 update predicates look for the active row for that business key whose attributes differ. After the insert has happened, the row that is “active” and matches the source hash is the new row, so the update sink no longer finds the old row to set IsActive = 0. That’s why both rows remain IsActive = 1 unless you do the update step first.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

v-menakakota · ‎10-17-2025

Hi @AhmedShalaby ,
Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank @tayloramy   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Regards,
Microsoft Fabric Community Support Team.

v-menakakota · ‎10-20-2025

Hi @AhmedShalaby ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Regards,
Microsoft Fabric Community Support Team.