Skip null rows while reading Azure Data Factory

user_19618836 0
Viewed 124

I am new to Azure Data Factory, and I currently have the following setup for a pipeline.

Azure Data Factory Pipeline
Azure Data Factory Pipeline

Inside the for each
Inside the for each

The pipeline does the following:

  1. Reads files for a directory everyday
  2. Filters the children in the directory based on file type [only selects TSV files]
  3. Iterates over each file and copies the data to Azure Data Explorer if they have the correct schema, which I have defined in mapping for the copy activity.
  4. It copied files are then moved to a different directory and deleted from the original directory so that they aren't copied again.

[Question]: I want to delete or skip the rows which have null value in any one of the attributes.

I was looking into using data flow, but I am not sure how to use data flows to read multiple tsv files and validate their schema before applying transformations to delete the null records.

Please let me know if there is a solution where I can skip the null values in the for each loop or if I can use data flow to do the same.

If I can use data flow, how do I read multiple files and validate their column names (schema) before applying row transformations?

Any suggestions that would help me delete or skip those null values will be hugely helpful

Thanks!

azure-data-factory-pipeline azure-data-factory azure
1 Answers

Ok, inside the ForEach activity, you only need to add a dataflow activity.

The main idea is to do the filter/assert activity then you write to multiple sinks.

ADF dataflow : enter image description here

Source: add your tsv file as requested, and make sure to select in After completion ->Delete source files this will save you from adding a delete activity.

Filter activity:

Now, depends on your use case, do you want to filter rows with null values? or do you want to validate that you don't have null values. if you want to filter, just add a filter activity, in filter settings -> filter on -> 'here add your condition'. if you need to validate rows and make the dataflow fail, use the assert activity

filter condition : false(isNull(columnName))

Sink:

i added 2 sinks,one for ADE and one for new directory.

You can read more about it here:

https://docs.microsoft.com/en-us/azure/data-factory/data-flow-assert

https://docs.microsoft.com/en-us/azure/data-factory/data-flow-filter

https://microsoft-bitools.blogspot.com/2019/05/azure-incremental-load-using-adf-data.html

please consider the incremental load and change the dataflow accordingly.

user_67888850
Related