Incrementally (or delta) loading data after an initial full data load

Different ways of loading data incrementally with Azure Data Factory.

Delta data loading from database by using a watermark

1. Define a watermark in your source database. A watermark is a column that has the last updated time stamp or an incrementing key.
2. The delta loading solution loads the changed data between an old watermark and a new watermark.

The workflow for this approach is depicted in the following diagram:

For step-by-step instructions, see the following tutorials:

• Incrementally copy data from one table in Azure SQL Database to Azure Blob storage
• Incrementally copy data from multiple tables in a SQL Server instance to Azure SQL Database

For templates, see the following:

• Delta copy with control table

Delta data loading from SQL DB by using the Change Tracking technology

Change Tracking: A lightweight solution in SQL Server and Azure SQL Database, providing an efficient change tracking mechanism for applications.

It enables an application to easily identify data that was inserted, updated, or deleted.

The workflow for this approach is depicted in the following diagram:

For step-by-step instructions, see the following tutorial:

• Incrementally copy data from Azure SQL Database to Azure Blob storage by using Change Tracking technology

Loading new and changed files only by using LastModifiedDate

One can copy new and changed files only by using LastModifiedDate to the destination store.

1. ADF will scan all the files from the source store,
2. Apply the file filter by their LastModifiedDate,
3. Only copy the new and updated file since last time to the destination store.

Please be aware that if you let ADF scan huge amounts of files but you only copy a few files to the destination, this will still take a long time because of the file scanning process.

For step-by-step instructions, see the following tutorial:

• Incrementally copy new and changed files based on LastModifiedDate from Azure Blob storage to Azure Blob storage

For templates, see the following:

• Copy new files by LastModifiedDate

Loading new files only by using time partitioned folder or file name.

You can copy new files only, where files or folders has already been time partitioned with timeslice information as part of the file or folder name (for example, /yyyy/mm/dd/file.csv). It is the most performant approach for incrementally loading new files.

For step-by-step instructions, see the following tutorial:

• Incrementally copy new files based on time partitioned folder or file name from Azure Blob storage to Azure Blob storage