Azure Data Factory V2 – Filter Activity

Hi All. Staying with the Data Factory V2 theme for this blog. Version 2 introduced a few Iteration & Conditionals activities.
One of these is the Filter activity.
There is a number of use cases for this activity, such as filtering the outputs from the Get Metadata and Lookup Activities.
In this example, I’m going to show you a few ways to apply filtering using the Filter with a few Pipeline parameters. The last example, which I’ll also include the ARM template for, is dynamic for both the Operator, and also the item being filtered.

The InputDataset is as follows. Just a simple array of dates.

[
  {
    "date": "2018-07-10",
    "value": "Date 2018-07-10"
  },
  {
    "date": "2018-07-12",
    "value": "Date 2018-07-12"
  },
  {
    "date": "2018-07-13",
    "value": "Date 2018-07-13"
  }
]

For my Pipeline, I’m just going to use a Lookup activity to fetch this input from a blob on Azure Storage.
Output from this activity looks like this.

If we now add a Filter Activity. Using Items from the previous activity, and filtering on a single value, the Settings table looks like this.

And if we Debug this, we get the following result as expected.

We can also use expressions in here as follows. This example will filter on the trigger startTime.

To set up a Dynamic date filter, let’s create a parameter in the Pipeline. In this example I’ll call it filterDate. I’ll also update the Condition in the Filter Activity to show how to use the parameter. The parameter can have a default value that is an expression.

formatDateTime(trigger().startTime,'yyyy-MM-dd')

And the Condition

After execution. The result should be as above, showing a single record for the 2018-07-12 record in the array.

But what if you want to control not only the Date, but also the Operator?
Unfortunately, replacing the Condition with a parameter reference only doesn’t work.


Gives us this error

We can work around this, by having two Pipeline parameters. The filterDate we created earlier, and a new one, filterCondition.

Then we update the Condition in the Filter Activity with the following (remove any line feeds if copying this)

@if(equals('equals',pipeline().parameters.filterCondition),if(equals(item().date,pipeline().parameters.filterDate),true,false)
,if(equals('greater',pipeline().parameters.filterCondition),if(greater(item().date,pipeline().parameters.filterDate),true,false)
,if(equals('greaterOrEquals',pipeline().parameters.filterCondition),if(greaterOrEquals(item().date,pipeline().parameters.filterDate),true,false)
,if(equals('less',pipeline().parameters.filterCondition),if(less(item().date,pipeline().parameters.filterDate),true,false)
,if(equals('lessOrEquals',pipeline().parameters.filterCondition),if(lessOrEquals(item().date,pipeline().parameters.filterDate),true,false)
,false)))))

Now when we run the Pipeline, we can enter two parameters.

And here are the results after running all 5 filterCondition values, with filterDate of 2018-07-12.

ARM Template for this Pipeline is here.

4 Comments

Leave a Reply to admin Cancel reply

Your email address will not be published. Required fields are marked *