Question

You are building a Fabric Data Factory pipeline to ingest data from an on-premises SQL Server orders table into a Fabric Lakehouse. The source table has a LastModifiedDate column and is updated frequently. You must load only records that changed since the previous pipeline run, while minimizing data movement and load on the source system. Which approach should you implement?

Accepted Answer

Store the maximum LastModifiedDate from each run in a control table, then use it as a watermark in the Copy activity's source query to filter only newer rows.

Answer

Use a Copy activity that truncates the Lakehouse table and reloads the entire source table on every run.

Answer

Create a OneLake shortcut to the on-premises SQL Server table so no data movement occurs.

Answer

Schedule a Dataflow Gen2 that imports the full table hourly and relies on Power Query to remove previously loaded rows.