Forum Discussion
Dataflow Gen2 with incremental refresh won't allow appending data in SQL DB or Lakehouse
Tldr:
Dataflow Gen2 supports incremental data extraction, but does not support appending data to destinations like Lakehouse or SQL DB, the destination always replaces the output table.
Clarification
The "replace" functionality in incremental refresh means the dataflow outputs only the incr data, but the destination table or partition is replaced with that data during the refresh.
You cannot directly configure Dataflow Gen2 incremental refresh to append new data to SQLDB or Lakehouse destinations in Fabric; the system enforces "replace" mode for these supported destinations.
To achieve an append like behavior, you must implement a custom pattern involving staging incremental data and then merging/appending it into the destination via additional steps outside the dataflow incremental refresh itself.
Couple of workaround suggestions:
Since this is currently a platform limitation, here are some alternatives:
1. Use Dataflow Gen2 to Stage the Data
Create an output table in the Lakehouse with the incremental extract (even though it replaces each time).
Then run a Spark Notebook or Data Pipeline to append this staged data into a final table.
This gives you control:
Staging table (api_incremental_temp) gets replaced.
Final table (api_cumulative) gets updated via an INSERT INTO SELECT or merge logic.
2. Move to Pipelines with Notebooks or Spark Jobs
If your use case is complex (like deduplication, watermark tracking, etc.), use Fabric pipelines with notebooks that implement full delta logic:
Call API directly from notebook.
Use checkpointing/watermark logic in Spark.
Append to Lakehouse as needed.
Recommendation
Since you are already using incremental refresh in DFG2, the best path today is:
Use DFG2 to pull incremental data and write it to a staging Lakehouse table.
Run a Fabric Notebook or a Pipeline activity to:
Read from the staging table.
Append/merge into the final destination table.
You get:
API delta load benefits.
Historical accumulation in your Lakehouse.
Please Kudos and 'Accept' as solution if the reply was helpful. This will be benefitting other community members who face the same issue.
Tldr:
Dataflow Gen2 supports incremental data extraction, but does not support appending data to destinations like Lakehouse or SQL DB, the destination always replaces the output table.
Clarification
The "replace" functionality in incremental refresh means the dataflow outputs only the incr data, but the destination table or partition is replaced with that data during the refresh.
You cannot directly configure Dataflow Gen2 incremental refresh to append new data to SQLDB or Lakehouse destinations in Fabric; the system enforces "replace" mode for these supported destinations.
To achieve an append like behavior, you must implement a custom pattern involving staging incremental data and then merging/appending it into the destination via additional steps outside the dataflow incremental refresh itself.
Couple of workaround suggestions:
Since this is currently a platform limitation, here are some alternatives:
1. Use Dataflow Gen2 to Stage the Data
Create an output table in the Lakehouse with the incremental extract (even though it replaces each time).
Then run a Spark Notebook or Data Pipeline to append this staged data into a final table.
This gives you control:
Staging table (api_incremental_temp) gets replaced.
Final table (api_cumulative) gets updated via an INSERT INTO SELECT or merge logic.
2. Move to Pipelines with Notebooks or Spark Jobs
If your use case is complex (like deduplication, watermark tracking, etc.), use Fabric pipelines with notebooks that implement full delta logic:
Call API directly from notebook.
Use checkpointing/watermark logic in Spark.
Append to Lakehouse as needed.
Recommendation
Since you are already using incremental refresh in DFG2, the best path today is:
Use DFG2 to pull incremental data and write it to a staging Lakehouse table.
Run a Fabric Notebook or a Pipeline activity to:
Read from the staging table.
Append/merge into the final destination table.
You get:
API delta load benefits.
Historical accumulation in your Lakehouse.
Please Kudos and 'Accept' as solution if the reply was helpful. This will be benefitting other community members who face the same issue.
Vinodh247, thank you for this response. I'm marking this as the solution, even if I'm not able to implement it. I'm still pretty new to the Fabric environment, and have only really ever worked in SQL and PBI up til now. That being said, your response makes a lot of sense, and I'll start digging into seeing if I can get it to work.
Thanks again!
