The short answer
A full copy gets slower every day as the table grows. The standard fix is an incremental load, and the classic version in ADF uses a watermark: a column in the source that only moves forward, such as last_modified or an auto-increment id.
The pattern keeps the last value you loaded in a small control table, then copies only the rows between the old and new watermark:
1. Lookup activity -> read the old watermark from the control table
2. Lookup activity -> read the new watermark: SELECT MAX(last_modified) FROM source_table
3. Copy activity -> copy rows where last_modified > old AND last_modified <= new
4. Stored Procedure -> write the new watermark into the control table (only if step 3 succeeded)The Copy activity's source query is built with an expression:
SELECT * FROM orders
WHERE last_modified > '@{activity('LookupOldWatermark').output.firstRow.WatermarkValue}'
AND last_modified <= '@{activity('LookupNewWatermark').output.firstRow.NewWatermarkValue}'Two details are what interviewers listen for:
- Upper bound. Rows can arrive while the copy is running. Fixing the new watermark first and using
<=means those rows are picked up by the next run instead of being skipped. - Update the watermark last. If the copy fails, the watermark is not moved, so a retry reloads the same range. That is safe when the sink uses an upsert or the range is overwritten, and it creates duplicates if the sink is a plain append.
ADF supports other incremental patterns too: Change Tracking in SQL Server and Azure SQL Database, which can also report deletes; filtering files by LastModifiedDate, where ADF still scans the whole folder; and loading time-partitioned folders such as /2026/10/17/, which Microsoft describes as the most performant approach for files.
Limits of the watermark pattern: it only works if the column is reliably updated, it misses hard deletes, and it misses rows whose timestamp is later backfilled to an older value.