Use Azure Data Factory to Conditionally Split data to different tables

Question

I want to use Azure Data Factory to split data similar to the data below based on the Name column to different tables. Ideally this could be done dynamically so that if a new Name value is added, then it will automatically split out that data to a separate table. I know how to manually specify a Conditional Split, I'm just wondering if theres any way to write an expression or etc. that would dynamically split these into separate tables, i.e. tbl_apple would have the first three rows, tbl_banana the next two, etc. ?

Thanks!

Name	Number	label
apple	1	a
apple	2	a
apple	3	a
banana	001	b
banana	002	b
carrot	0	dfb
carrot	1	dfb
carrot	2	dfb
carrot	3	dfb
plum	010	p
avocado	021	v
avocado	022	v

Answer 1

You can use Data flow script for conditional script but dynamic split condition isn't possible.

You can refer below syntax to write a conditional split script:

<incomingStream>
    split(
        <conditionalExpression1>
        <conditionalExpression2>
        ...
        disjoint: {true | false}
    ) ~> <splitTx>@(stream1, stream2, ..., <defaultStream>)

If you want it dynamically, you need to manage it programmatically. You can choose any data manipulation language like SQL or Python and read all the unique values in from the table and based on that you can split.

Use custom activity to run such scripts.

To move data to/from a data store that the service does not support, or to transform/process data in a way that isn't supported by the service, you can create a Custom activity with your own data movement or transformation logic and use the activity in a pipeline.

For example, using Python Pandas module you can find the unique values in the dataframe. Refer below syntax:

<DataFrame_Name>.<Column_Name>.unique()

You will get the all unique values in the given column in a list.

Now you can loop over the list and store the records for each unique value in a separate table.