CODEWITHSUNDEEP
azure-data-factory
solarissf
solarissf

Reputation: 1277

azure data factory - creating new column from another table and multiple criterion

Using Azure Data Factory, I am looking to bring in a csv file from blob storage, transform data a bit, then save to azure sql. From the tutorials I am reviewing this looks pretty straight forward. The complication comes in next step I am looking to accomplish...

source file: 20,000 rows, 100 columns

destination sql: 20,000 rows, 101 columns

I would like to add a new column. The old way I did this was an azure function written in c#, but now I'd like to accomplish this in data factory.

Looks like 'derived column' can add a column in data factory, but my process is a bit more complicated. when looking at a particular row in source, using 3 columns, I need to lookup from another sql table returning about 5 fields. Then using those fields with other fields from the original row I need to do things including multiple if/thens, some switch statements and things like that.

Is there a better way to do this? I was trying to stay away from stored procedures. My goal is to use the azure approach of a process like this.

any suggestions would be greatly appreciated.

I was thinking this should be done during the 'copy data' section of pipeline but if it needs to be done afterwards in a different azure feature I am fine with that. Whatever is best practice.

Upvotes: 0

Views: 854

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11454

You can use Dataflow transformations as per your logics.

when looking at a particular row in source, using 3 columns, I need to lookup from another sql table returning about 5 fields

Join transformation in the dataflow might work for this scenario. Take your original table and lookup table as two sources and give the 3 columns as join columns. You can give the join type as per your requirement.

enter image description here

After join, for multiple conditions, you can use conditional split transformation. Give your conditions here and data will split.

enter image description here

Or, you can directly use iif() or case() functions in the Dataflow expression. Take a derived column transformation and use these in the expression builder.

enter image description here

Go through this documentation to know about other functions in Dataflow expression.

Upvotes: 0

Related Questions