Incremental Refresh Dataflow Gen2

Incremental refresh in Dataflow Gen2 requires a stable change or watermark column. In your case, the source table is rebuilt daily and the date column is overwritten with the current date. Because of this, the dataflow cannot identify new rows reliably. Incremental refresh fails by design in this pattern.

This behavior is expected and documented.

The correct fix is to switch from truncate-and-reload to an incremental amass pattern and introduce a reliable load timestamp.

Step 1. Stop truncating the destination

Incremental refresh only works when historical data is preserved. Configure the Dataflow Gen2 destination to incrementally amass data into a Lakehouse or Warehouse table. New rows are appended, existing rows remain intact.

Step 2. Add a LoadDate column inside the dataflow

Since the source Created Date is unreliable, generate a load timestamp during ingestion.

In Power Query, add a custom column:

LoadDate = DateTimeZone.UtcNow()

This records when the row entered Fabric. Users then sort or filter by LoadDate to find the most recent additions.

Step 3. Use LoadDate as the freshness indicator

Use LoadDate for operational reporting and recency checks. Treat Created Date as a business attribute only, not as an ingestion signal.

This aligns with Microsoft guidance for sources without trustworthy change tracking.

Step 4. Configure incremental refresh correctly

In Dataflow Gen2 incremental refresh settings:

• Choose the DateTime column used for filtering

• Ensure the destination supports incremental append

• Validate that historical partitions remain untouched

Fallback option if no change column exists

If the source truly has no stable change indicator, incremental filtering is not possible. In that case, use a snapshot comparison pattern.

Steps:

1. Load the daily full snapshot into a staging table.

2. Compare staging with the target using a business key.

3. Insert rows missing from the target.

4. Optionally detect updates using a hash comparison.

This pattern is commonly used when tables are rebuilt upstream.

Key takeaway

Incremental refresh does not work on tables rebuilt daily unless you preserve history and introduce a stable ingestion marker. Adding a LoadDate and switching to an incremental amass pattern is the supported and scalable solution in Dataflow Gen2.

Thank you!
📩 Need more help?
✔️ Don’t forget to Accept as Solution if this guidance worked for you.
💛 Your Like motivates me to keep helping

I appreciate your prompt response!

I do have a clarification and a question, maybe two.

First, I do not truncate the destination. The original source table is truncated daily. I have created a Lakehouse to land the original source data. This is my starting point for the dataflow. The critical column for detecting changes is the CreationDate. An additional column is present for the LoadDate (this changes daily). I can add a datetime column to the dataflow that maps to the destination. The destination is a warehouse table with the columns from the Lakehouse plus the column I added in the dataflow (run date).

I am confused about which column to use for the filter by. It seems to me that the column for the max value ought to be the created date, but of course that might not be correct. Your assistance is very appreciated!!

You are not truncating the destination. The upstream source is rebuilt daily, you land a full snapshot in a Lakehouse, then a Dataflow Gen2 loads into a Warehouse table. You also have two date-like columns:

1. CreationDate (business date)

2. LoadDate (changes every day, because the source snapshot reloads)

The confusion is which column to use for incremental refresh filtering.

Key point to align on

Incremental refresh works only when the column used for filtering is a reliable signal of new data arriving since the last run.

If CreationDate is stable and truly reflects when the record was created in the business system, then it is the correct filter column.

If CreationDate is rewritten during the daily rebuild, or if historical rows get a new CreationDate, then CreationDate is not a reliable filter column. In that case, incremental refresh cannot detect new rows correctly.

Why LoadDate is not a good filter column here

LoadDate changes for every row every day due to the daily rebuild. If you filter by LoadDate, the filter will keep matching the full table each day. That is why you see all rows reloaded.

So, LoadDate is useful for reporting freshness, not for incremental filtering in your scenario.

Recommended pattern for your case

Step 1. Decide if CreationDate is a true watermark
Check two days of snapshots in your Lakehouse:

• Do old rows keep the same CreationDate?

• Do only new rows have a later CreationDate?

If yes, then CreationDate is a valid watermark.

Step 2. If CreationDate is valid, use it for incremental refresh filter
In Dataflow Gen2 incremental refresh settings:

• Filter column: CreationDate

• Store historical data in the destination (append pattern)

Then, add your own ingestion timestamp column for users:

• IngestionRunDate = DateTimeZone.UtcNow()

Users use IngestionRunDate to see when Fabric loaded rows.
Dataflow uses CreationDate to detect new rows.

Step 3. If CreationDate is not valid, incremental refresh will not work as expected
If the daily rebuild rewrites CreationDate or reorders history, you have two realistic choices:

Choice A. Fix upstream, best outcome
Get a stable change column from the source system:

• ModifiedOn, UpdatedAt, CDC version, rowversion, audit timestamp, identity sequence

Then use that as the incremental filter.

Choice B. Snapshot compare inside Fabric
Keep doing full snapshot into Lakehouse, then in the Warehouse:

• Insert only new business keys

• Optionally update changed rows using hash compare

This works even when the source is rebuilt daily, because you detect differences rather than relying on timestamps.

Let me know how it goes.