Question

A data engineer must incrementally load a large SQL Server 'Orders' table into a Fabric Lakehouse every hour. The source table has an 'OrderId' identity column and a 'LastModifiedDate' datetime column that is updated whenever an order row is inserted or updated. The pipeline must capture both newly inserted and previously updated rows since the last successful run, while minimizing the volume of data transferred. Which incremental loading strategy should the engineer implement?

Accepted Answer

Use a high-watermark pattern based on the 'LastModifiedDate' column, storing the maximum value from each run and filtering the next run for rows greater than that stored value.

Answer

Use a high-watermark pattern based only on the 'OrderId' identity column, storing the maximum OrderId and filtering for rows greater than it on the next run.

Answer

Perform a full load of the entire 'Orders' table each hour and overwrite the destination Delta table.

Answer

Enable a scheduled Dataflow Gen2 refresh that truncates and reloads the table without any filter predicate.