topic Re: SCD Type 2 in ADF only works when update sink runs before insert sink — why? in Pipelines

SCD Type 2 in ADF only works when update sink runs before insert sink — why?

AhmedShalaby — Thu, 16 Oct 2025 23:54:26 GMT

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

Re: SCD Type 2 in ADF only works when update sink runs before insert sink — why?

tayloramy — Fri, 17 Oct 2025 04:03:32 GMT

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.

Re: SCD Type 2 in ADF only works when update sink runs before insert sink — why?

v-menakakota — Fri, 17 Oct 2025 11:30:34 GMT

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.

Re: SCD Type 2 in ADF only works when update sink runs before insert sink — why?

v-menakakota — Tue, 21 Oct 2025 04:53:50 GMT

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.