Forum Discussion

BeyzaKzlky's avatar
BeyzaKzlky
Frequent Visitor
2 years ago
Solved

Dataflow Gen2 Incremental Refresh and Data Refresh

Hi all,

I have a problem again with dataflow gen2. 

I'm getting my data from on-prem sql server with dataflow, and because there is no Incremental refresh on gen2, I set standard scheduled refresh daily on dataflow. 

But I realized that this refresh duplicates data on the lakehouse table. 

Is it a bug or is there any option to prevent this I couldn't see?

Thanks

Dataflows
Need Help
  • LuitwielerMSFT's avatar
    LuitwielerMSFT
    2 years ago

    Hi!

     

    My name is Jeroen Luitwieler, and I’m a Senior Product Manager on the Dataflow team. While this guide shows how to use an OData source, it’s just an example. You can use this method with your on-premises SQL Server without any issues. If you want to perform a full refresh of your data every time you refresh, you can change the current data destination setting from “append” to “replace”.

     

    Hope this resolves your issue! 😁

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi BeyzaKzlky 
    Thanks for using Fabric Community.
    As I understand the above question, you are trying to achieve incremental load using Dataflow Gen2. 
    Incrementally amassing data in a data destination requires a technique to load only new or updated data into your data destination. This technique can be done by using a query to filter the data based on the data destination.
    Please refer to this link for more information: https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2

    Hope this helps. Please let us know if you have any further queries.

    • BeyzaKzlky's avatar
      BeyzaKzlky
      Frequent Visitor
      2 years ago

      Hi Anonymous , 

      Thanks for your response. I have seen the learning page which you have shared with me, but it's OData source. Unfortunately not proper for my case. 

      I'm getting data from on-prem sql server. 

      And also there is a data duplication problem with dataflow like I have mentioned above. 

       

      • fabricator1's avatar
        fabricator1
        Advocate II
        2 years ago

        I think you can use that link from Anonymous also with on-prem sql server.


        That procedure is only to append rows to the Lakehouse table.
        Not update (change) existing data in the Lakehouse table. 
        So it's called Incrementally amass data.


        I think the main thinking behind that procedure is that you connect your Dataflow Gen2 to your Lakehouse table, to find out what is the newest row in your Lakehouse table. Then you use that information to filter your on-prem sql server data, so you ask the sql server to only return rows which are newer than your newest row in the Lakehouse. Then you append this new data from sql server into the Lakehouse.
        When using that procedure, it shouldn't become duplicate rows, because your query to the SQL server is only asking for new rows.

        There is an idea for Incremental refresh (which includes both append and updates to existing data), please vote: 
        Microsoft Idea