Dataflow Gen2 Incremental Load

Hello M98 , 

You’re using Dataflow Gen2 in Microsoft Fabric with an incremental load pattern based on LastModifiedDate (in Unix epoch).
Your logic:

#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[LastModifiedDate]/1000)

is perfectly valid to convert Unix timestamps to UTC datetime.
However, the issue seems to appear during refresh execution (SQL Server crash or overload).

🔍Root Causes to Consider

1. Transformation applied row-by-row before filtering

When you convert [LastModifiedDate] before applying the incremental filter, the transformation forces Power Query to materialize and compute all rows from the source before it can filter them.

This means your SQL Server is being asked to:

• Pull all data (not just new/updated rows),

• Apply the conversion logic on each row,

Then compare to your reference table.

⚠️Result: high CPU and memory load → SQL Server crash or timeout.

💡Recommendation:
Perform filtering at the source using native SQL, or delay conversion until after filtering.

2. Reference table out of sync with destination

If your incremental logic uses a “reference” table (for example, a stored Max(LastModifiedDate) from the previous load) but this table is not updated transactionally with the destination table, you can end up:

• Re-loading existing rows (duplicates or merge conflicts),

• Missing updates (if the reference table lags behind),

• Triggering large scans due to reprocessing overlaps.

This can amplify data volume, making SQL Server appear to “crash”.

Best practice:

• Always update the reference table after a successful write to the destination.

• Ensure both are in the same Lakehouse or database for atomicity.

3. Incremental logic not pushed down to SQL Server

Power Query transformations (like your #datetime + #duration) are not foldable for most SQL sources.
If query folding breaks, Power BI/Fabric will extract the entire dataset and process it in the Dataflow runtime instead of pushing a filtered query to SQL Server.

You can check this by right-clicking the step → “View Native Query”.
If it’s disabled, the folding is broken.

Fix:
Perform the conversion in SQL before connecting the Dataflow. Example: 

SELECT *, DATEADD(SECOND, LastModifiedDate/1000, '1970-01-01') AS LastModifiedUTC
FROM dbo.SourceTable

SELECT *, DATEADD(SECOND, LastModifiedDate / 1000, '1970-01-01') AS LastModifiedUTC 
FROM dbo.SourceTable 
WHERE LastModifiedDate > @LastLoadEpoch

Filtered = Table.SelectRows(Source, each [LastModifiedUTC] > RangeStart and [LastModifiedUTC] <= RangeEnd)