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

Hi Alaahady, 

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to tayloramy, for his inputs on this thread.

This is a common pattern when working with on-prem SQL Server sources and incremental refresh in Fabric. Here’s a practical approach you can consider:

Ingest only changed rows: Use a Copy Activity or Copy Job via a self-hosted Integration Runtime to pull only rows where last_update_date is greater than the latest value in your destination (watermark). This keeps your ingestion lightweight and avoids unnecessary full-table copies.

If you can enable Change Data Capture (CDC) on SQL Server, it’s even more robust for inserts/updates/deletes.
Landing zone (Bronze layer): Store the delta rows in a Bronze Delta Lakehouse table. Keep this append-only; each pipeline run writes only the new/updated rows.

Upsert into Gold layer: Use a Notebook (Spark) or Dataflow Gen2 to perform a MERGE from Bronze into your Gold Delta table. Match on ID and compare last_update_date to handle inserts/updates atomically.

Expose Gold for reporting: Use the Lakehouse SQL endpoint for smaller concurrency reports. Use a Warehouse if you have high-concurrency or interactive Power BI users.

Refer these links:
1. https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-incremental-refresh 
2. https://learn.microsoft.com/en-us/fabric/fundamentals/decision-guide-lakehouse-warehouse 
3. https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-overview 
4. https://learn.microsoft.com/en-us/azure/databricks/delta/merge 

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.