Multi Step Incremental load and processing using Azure Data Factory

Question

I wanted to achieve an incremental load/processing and store them in different places using Azure Data Factory after processing them, e.g:

External data source (data is structured) -> ADLS (Raw) -> ADLS (Processed) -> SQL DB

Hence, I will need to extract a sample of the raw data from the source, based on the current date, store them in an ADLS container, then process the same sample data, store them in another ADLS container, and finally append the processed result in a SQL DB.

ADLS raw:

2022-03-01.txt

2022-03-02.txt

ADLS processed:

2022-03-01-processed.txt

2022-03-02-processed.txt

SQL DB:

All the txt files in the ADLS processed container will be appended and stored inside SQL DB.

Hence would like to check what will be the best way to achieve this in a single pipeline that has to be run in batches?

Answer 1

You can achieve this using a dynamic pipeline as follows:

1. Create a Config / Metadata table in SQL DB wherein you would place the details like source table name, source name etc.

2. Create a pipeline as follows:

   a) Add a lookup activity wherein you would create a query based on your Config table https://learn.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity

   b) Add a ForEach activity and use Lookup output as an input to ForEach https://learn.microsoft.com/en-us/azure/data-factory/control-flow-for-each-activity

   c) Inside ForEach you can add a switch activity where each Switch case distinguishes table or source

   d) In each case add a COPY or other activities which you need to create file in RAW layer

   e) Add another ForEach in your pipeline for Processed layer wherein you can add similar type of inner activities as you did for RAW layer and in this activity you can add processing logic

This way you can create a single pipeline and that too a dynamic one which can perform necessary operations for all sources