Incremental Refresh on Dataflow Gen 2 when we are using Replace at Data Destination in Lakehosue

Hi Naveen96,

I think that you don't actually need the built-in incremental refresh feature at all. Microsoft has an official pattern called "incrementally amass data" that does exactly what you want, entirely within Dataflow Gen2, using Append mode. Here is how to set it up for your scenario 

Step 1: Make sure your full load is done

You mentioned you already did a full refresh with Replace. That is your baseline. If your Lakehouse table already has all the historical data, you're good to move to step 2.

Step 2: Disable the built-in incremental refresh

Open your dataflow, right click the query and turn off incremental refresh. You won't need it anymore.

Step 3: Create a helper query to get the max CreatedDate from your Lakehouse

Still inside the same dataflow, click Get Data and connect to your Lakehouse. Select the transaction table that already has your data. Rename this new query to something like MaxCreatedDate. Then right click the CreatedDate column in the data preview and select Drill Down. After that, go to the ribbon and click List Tools, then Statistics, then Maximum. This gives you a single value: the most recent CreatedDate in your Lakehouse. Finally, right click this query and select Disable Staging (this is important because it's a helper query, not a data load).

Step 4: Add a filter to your main source query

Go back to your main query (the one that pulls from your transaction source). Add a filter step on CreatedDate with the operation set to "is greater than" and the value set to the parameter MaxCreatedDate (your helper query). When prompted to allow combining data from the source and the Lakehouse, confirm it.

Step 5: Change the data destination to Append

Edit your data destination settings and switch the update method from Replace to Append. This works perfectly fine here because you are not using the built-in incremental refresh toggle anymore.

Step 6: Publish and run

Each time the dataflow runs, it reads the max CreatedDate from your Lakehouse, pulls only records with a CreatedDate greater than that value from your source, and appends them. No duplicates, no full reloads.

How to do a full reload if you ever need one

Since you're restricted to Dataflow Gen2, just edit the dataflow, remove the filter step you added in Step 4, change the destination back to Replace, publish and run it once. After that, re-edit the dataflow to add the filter back and switch to Append again.

One thing to keep in mind

This pattern works best if you run the dataflow once a day after all your daily data has landed. The filter uses "greater than" so it picks up records with a CreatedDate strictly newer than what's already in the Lakehouse. If your CreatedDate is a date without a time component and you run the dataflow multiple times on the same day, records from that same day that arrive after the first run would be missed. If that is a concern and your table has a unique auto-incrementing ID column, you could use that column instead of CreatedDate for the filter, which is actually what Microsoft uses in their tutorial.

(test on a dev environment first of course!)

The full tutorial from Microsoft is here: Pattern to incrementally amass data with Dataflow Gen2

Hope this helps!

Best regards,

Carlos

Hi Naveen96 ,

Thanks for the update.

I was able to reproduce the same behavior in my environment. Initially, I performed a full refresh which loaded all the data into the Lakehouse. After that, I enabled incremental refresh with a rolling window (past 1 day).

On teh next run, the same 1 day bucket was processed again, and since that data was already loaded during the full refresh, it resulted in duplicates.

So this is not caused by the bucket itself, but by doing a full refresh first and then enabling incremental refresh with an overlapping window.

To avoid this, incremental refresh needs to be enabled from the beginning so that the data is loaded without overlap.

Also to clarify, when incremental refresh is enabled from the beginning, it does not load full historical data automatically. It only loads data based on the configured range, and the bucket size (for example, Day) just defines how that data is partitioned and refreshed.

So if your requirement is to have both historical data and incremental loads, you can configure it in one go using incremental refresh itself. Set “Extract data from the past” to cover your required history (for example, 1 year or more), keep the appropriate bucket size (for example, Day), and run the dataflow. The first run will load that historical range, and subsequent runs will continue incrementally within that range.

The duplication happens specifically when a full refresh is done first and then incremental refresh is enabled with a rolling window that overlaps with already loaded data.

Hope this helps. Please reach out for further assistance.
Thank you.