How to handle Case statement in Azure Data Factory (ADF) compare to SSIS?

This post will describe how do you use CASE WHEN statement in Azure data factory(ADF). If you are coming from SSIS background, you know a piece of SQL statement will do the task. However let's see how you do it in SSIS and the very same thing can be achieved in ADF.

Problem statement:

For my simple scenario, In case PortfolioTypeCode is either 'Mutual Fund' or 'Pooled Fund' it should return 1 Else it should return 0.

 

How do you do in SSIS?

In SSIS, under data flow you will have OLEDB source like below fig 1:

Fig 1: SSIS OLEDB source

 

And open the OLEDB source and then Write SQL command like below and you are done:

SELECT Col1,

          CASE WHEN PortfolioCode IN('Mutual fund','Pooled fund')

            THEN 1

            ELSE 0

END  IsFund,

Col2

From Table1

Fig 2: CASE WHEN under SQL command in SSIS

 

How do you implement in ADF?

However in ADF, to achieve the same you need to use Expressions. ADF have very same concept of data flow like SSIS. In the data flow, after the source dataset is established you can add 'Derived Column' activity like below Fig 3:

Fig 3: Adding derive column under data flow

 

Now you can give a new column name and then add the expression (Fig 4):

Fig 4: Derived column expression

 

Let's see how Case expression works: it takes 3 arguments, those are condition, true and false. However, it can have alternating condition which describe in the figure 5:

Fig 5: Case in Expression

 

For my simple scenario, If PortfolioTypeCode is either 'Mutual Fund' or 'Pooled Fund' it should return 1 Else it should return 0.

Since you can't have CASE WHEN rather using case as Expression, the code will look like below:

  case( PortfolioTypeCode=='Mutual Fund',1,

       PortfolioTypeCode=='Pooled Fund',1,0)

 

SQL: Pay attention when using Group by and CASE WHEN statement together

When you are working with SQL and having case when statement together with 

group by then please have a close look at the what are you putting in the case

when statement. 

For example, Cases like below is pretty much straight forward:

 
Select cust_key,
       Cust_site_key,
       Max(rating_key) as rating_key
From Customer_rating
Where year=2018
Group by 1,2
 
However, Now let's look at the below code and see if you find any issue with

this code.

Select
event_key,
Event_code,
CASE Event_code
   When ‘start’ then min(f.event_date_key)
   When ‘Closed’ then max(f.event_date_key)
ELSE
   f.event_date_key
END as c 
FROM
Event_fact f
Join
event_dim ed
On
f.event_key=ed.event_key
Group
by event_key,Event_code

After running above code you will get error with "Failed
3504: Selected non-aggregate values must be part of the associated group."

What can go wrong? Well, you may think; event_date_key is using aggregate

function (min and max) so you don't need to use group by with 

event_date_key. However, if you look closely then will find ELSE part of the CASE

statement also using the event_date_key where no aggregation applied. It means 

we need to use group by with event_date_key.

Hence the correct code will be:

Select

event_key,
Event_code,
CASE Event_code
   When ‘start’ then min(f.event_date_key)
   When ‘Closed’ then max(f.event_date_key)
ELSE
   f.event_date_key
END as c 
FROM
Event_fact f
Join
event_dim ed
On
f.event_key=ed.event_key
Group
by event_key,Event_code,event_date_key