Forum Discussion
Clarification on Dataflow Gen2 for Incremental Loads (Delta/SCD Type 2)
I'm designing a data pipeline in Fabric and need to implement an incremental loading pattern (delta load / SCD Type 2). The goal is to process daily data increments from a source system to a star schema.
The Microsoft Learn documentation seems to suggest that Dataflow Gen2 defaults to overwriting the destination. I'm looking to confirm if there's a way to configure it to handle more complex logic, such as:
Inserting new rows.
Updating existing rows.
Soft-deleting rows that are no longer in the source (e.g., marking them as inactive).
If this pattern isn't a good fit for Dataflow Gen2, is a Fabric Notebook the more appropriate tool for this kind of row-by-row processing?
Appreciate any guidance or best practices you can share.
Hi JohnT_2307 ,
You mentioned about the Scalability and Limitations in your previous response. Please refer below things.
1. Performance Bottlenecks : Query folding is critical, Without query folding the performance will be degrades. Alternatively avoid complex transformations in Power Query that prevent folding. Use staging in Lakehouse/Warehouse to simplify logic.
2.Data Volume: There is No hard limits published, but performance may degrade with tens of millions of rows. Please consider partitioning and incremental refresh to manage large volumes.
3.Timeouts: Long-running queries may time out, especially if folding is not enabled.
Solution: Break data into smaller parts using filters. Use staging layers to isolate heavy transformations. Offload to Notebooks for more control.
Please refer below Microsoft official documents.
Incremental refresh in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn
Slowly changing dimension type 2 - Microsoft Fabric | Microsoft Learn
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi @JohnT_2307 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
6 Replies
Yes, Dataflow Gen2 can be used effectively to implement SCD Type 2 logic, including incremental loads, especially when the use case involves low to moderate complexity and performance needs.
While it's true that Dataflow Gen2 by default overwrites the destination, you can configure it to support SCD Type 2 scenarios using techniques like:
Detecting changes via joins between current and incoming data.
Creating conditional columns to flag new, changed, or unchanged rows.
Using filters and union logic to build the final dataset with:
New records inserted,
Existing records updated (with historical rows marked inactive),
Soft-deleted records (optional based on source vs destination comparison).
This approach avoids code and fits well within low-code environments.
You can refer to a great walkthrough here:
Managing SCD with Fabric Dataflow Gen2 – Inkey SolutionsHi JohnT_2307 ,
Thank you for reaching out to the Microsoft Community Forum.
Hi bhavya5903 , Thank you for the prompt response.
Hi JohnT_2307 , Could you please try the proposed solution shared by bhavya5903 . I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi @bhavya5903 & v-dineshya,
Thank you for the quick reply and the link to the article. It looks very promising, and I will now attempt the solution.
Before I begin, I have a follow-up question about the solution's limitations, specifically concerning scalability. Could you advise on any known issues related to the following?
Performance: Are there any known performance bottlenecks with this approach?
Data Volume: Is there a practical limit or recommended maximum amount of data it can handle efficiently?
Timeouts: What is the risk of a timeout when processing very large datasets, and are there any recommended workarounds?
Any insights on this would be greatly appreciated.
Hi JohnT_2307 ,
You mentioned about the Scalability and Limitations in your previous response. Please refer below things.
1. Performance Bottlenecks : Query folding is critical, Without query folding the performance will be degrades. Alternatively avoid complex transformations in Power Query that prevent folding. Use staging in Lakehouse/Warehouse to simplify logic.
2.Data Volume: There is No hard limits published, but performance may degrade with tens of millions of rows. Please consider partitioning and incremental refresh to manage large volumes.
3.Timeouts: Long-running queries may time out, especially if folding is not enabled.
Solution: Break data into smaller parts using filters. Use staging layers to isolate heavy transformations. Offload to Notebooks for more control.
Please refer below Microsoft official documents.
Incremental refresh in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn
Slowly changing dimension type 2 - Microsoft Fabric | Microsoft Learn
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
