Azure Data Factory Filter Activity and Debugging Capabilities

By:   |   Comments (1)   |   Related: > Azure Data Factory


In a previous post (see Stairway to Azure Data Factory: Lookup activity) we used a Lookup activity to read data from the database system. In some cases, data generated by Lookup activities may require some filtering before passing to other activities. ADF's Filter activity allows filtering its input data and we are going to discuss this activity in this post. We will also look into pipeline debugging functionality in ADF.


Debugging Functionality in Azure Data Factory

ADF's debugging functionality allows testing pipelines without publishing changes. This functionality also allows setting breakpoints on activities, which would ensure partial pipeline execution.

To demonstrate debugging functionality, I will use pipeline ControlFlow2_PL we built in Stairway to Azure Data Factory: Lookup activity. This pipeline reads data from the database and assigns it to the pipeline variable.

Let's select the pipeline ControlFlow2_PL and click the Debug button at the top of the central panel:

azure data factory debugging

Once the pipeline starts execution, a new Output window appears at the bottom of the screen, with the execution related details, like activity name, type, start time and duration, as well as input/output values:

azure data factory debugging

We can examine the input and output values of each activity from this window, as follows:

azure data factory debugging

Now, if we want to execute this pipeline partially (say only until activity Lookup_AC), we can set a breakpoint on this activity, using a small red circle on its corner:

azure data factory debugging

If we start this pipeline in debug mode again, we will see that it only executed the first activity:

azure data factory debugging

Azure Data Factory Creating Filter Activity

The Filter activity allows filtering its input data, so that subsequent activities can use filtered data.

To show the Filter activity at work, I am going to use the pipeline ControlFlow2_PL. Here are the required steps:

Select the pipeline ControlFlow2_PL and add array-type variable FilteredTableNames to its variable list:

azure data factory filter activity

Next, let's drag-drop Filter activity from Iteration & Conditionals group and link it to the Lookup_AC activity on Success criteria (I've named this activity as Filter_AC):

azure data factory filter activity

Next, select Filter_AC activity and enter an expression @activity('Lookup_AC').output.value to its Items text box, which reflects output from the Lookup_AC' activity. Next, enter @startswith(string(item().name),'SalesLT.P') to the Condition text box, which sets the filtering condition for table names starting with 'P' character. Here's how your screen should look:

azure data factory filter activity

Add another Set Variable activity with the variable name FilteredTableNames and an expression @activity('Filter_AC').output.value as its value. This will ensure that filtered output from Filter_AC activity is assigned to the variable FilteredTableNames:

azure data factory filter activity

Finally, let's start this pipeline in Debug mode and examine the results in the Output window:

azure data factory filter activity

Now, if we examine output values for activity Set_Variable2_AC, we will see that it contains only table names starting with the character 'P', which is what we expected:

azure data factory filter activity

I have attached JSON scripts for both pipelines here.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Fikrat Azizov Fikrat Azizov has been working with SQL Server since 2002 and has earned two MCSE certifications. Hes currently working as a Solutions Architect at Slalom Canada.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, April 25, 2020 - 8:20:57 AM - Nalin Back To Top (85473)

Thank you very much for the post.

get free sql tips
agree to terms