How to fully load data once and then incrementally update with yesterday’s data in Dataflow Gen2?

[ge83rep]

Hi all,

I'm new to Dataflow Gen2 and exploring it to centralize data transformations for Power BI reporting. I’m using F2 capacity and pulling data from multiple sources like SQL Server, Salesforce, and Microsoft Business Central.

While building the dataflows, I didn’t define any explicit output data destination (bottom left panel in the dataflow UI).

But I noticed that files like “StagingLakehouseForDataflows_...” are automatically getting created in my workspace under type “Lakehouse” or “Warehouse”.

• What exactly are these files?

• Is it okay to leave the output destination undefined if I can still see the tables and use them in Power BI?

Incremental Refresh Doubts:

I tried setting up incremental refresh on one of the queries (Currency Exchange Rate). This table has three columns:

• Currency Code

• Starting Date

• Currency Exchange Rate

There’s historical data available from 2020 onwards. Since the exchange rates update daily, my goal is to:

1. Load the full historical dataset initially

2. Then, refresh only yesterday’s data daily (i.e., append new records to the existing dataset)

So I used these settings in Incremental Refresh:

• Date column: Starting Date

• Extract data from the past: 1 day

• Bucket size: Day

  

But after enabling and running the refresh, I noticed that only records with yesterday’s date were loaded — the older data (from 2020 onwards) wasn't loaded at all.

What I need:

• I want the dataflow to load the entire dataset initially, and then on each scheduled refresh, only add new records from yesterday (without removing the older ones).

• How can I properly configure incremental refresh to support this pattern in Dataflow Gen2?

---

Thanks in advance for any help or clarification!

[lbendlin · Accepted solution]

Please follow the documentation.  Your scenario may not be supported.

Incremental refresh in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

[v-aatheeque]

Hi @ge83rep 

Just following up on your query regarding Dataflow Gen2 output destinations and setting up incremental refresh for your Currency Exchange Rate table.

As noted earlier, @lbendlin  has shared the official documentation on configuring incremental refresh in Dataflow Gen2, which outlines the steps needed to ensure an initial full load followed by daily incremental loads.

Whenever you get a chance, could you confirm if the guidance helped to resolve the issue.

We’re happy to assist further if needed.

Looking forward to your response!

[v-aatheeque]

Hi @ge83rep 

Just a quick reminder that @lbendlin shared the official documentation that covers how Dataflow Gen2 handles output destinations and how to configure incremental refresh correctly to support full historical load followed by daily appends.

• The auto-created “StagingLakehouseForDataflows ” is expected behavior when no explicit destination is defined  Dataflow Gen2 stages data in a temporary Lakehouse.

• For incremental refresh, the document explains how to ensure an initial full load and correctly partition for daily updates without losing historical data.

Whenever you get a chance, please review the provided link and let us know if anything needs further clarification or adjustment for your setup.

Thank You.

[v-aatheeque]

Hi @ge83rep 

Have you had a chance to review that documentation closely, especially the part about defining the RangeStart and RangeEnd parameters in the Power Query (M) layer before publishing?

Also:

Did you publish the dataflow first before triggering a refresh?
Incremental policies only apply after that initial publish step.

In your applied steps, do you see any filters tied to the Starting Date using those parameters?

It sounds like the dataflow might be applying the 1-day filter even during the first refresh, which typically means the RangeStart/RangeEnd logic might be missing or not applied correctly.

Let me know what you observe  happy to help troubleshoot further if something’s unclear or not behaving as expected.