Best Incremental Refresh Strategy in Fabric for On-Prem SQL Server Source with Updateable Records

Hi Alaahady,

You’re dealing with the classic “insert + update” problem from an on-prem SQL Server. The trick is to land only what changed, then upsert efficiently, and serve for reporting. Here’s what I’ve found works consistently.

Quick solution

• Ingestion method:
  Use Dataflow Gen2 (incremental refresh on last_update_date) or Pipeline Copy Activity (incremental mode) through the on-premises data gateway to land just the last N days/weeks of changes into a staging table.
  Docs: Dataflow Gen2 Incremental Refresh, On-premises data (gateway), overview pattern from ADF: Incremental copy (watermark).
• Destination for serving/reporting:
  Prefer Warehouse (good T-SQL surface, easy PBI connectivity). MERGE is supported (preview), which enables straight T-SQL upserts. See: T-SQL surface area (Warehouse).
• Bronze > Gold recommended:
  Yes. Land delta rows into Bronze (staging), then MERGE into Gold (reporting) with keys + last_update_date. The MERGE can be a Warehouse stored proc or a Spark notebook MERGE (if you choose Lakehouse Delta tables).
• Efficient update handling:
  Use a watermark table that stores the last successful last_update_date. Your ingestion filters > watermark at source. After a successful upsert, bump the watermark. For sources that support it, SQL Server Change Tracking also works well; pattern reference (ADF): Change Tracking incremental pattern.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.