Handling Null Values in ADF Pipeline

Question

I am performing an incremental load using timestamp as watermark column. I have few null values in the date column in my source. When I am replicating the data using copy activity, only rows whose date column is not null are getting copied, but I want to copy all other rows too where date column has null values.

I have tried using IsNull and Coalesce in the lookup activites but that does not work . is there a way where I can handle those null values in ADF

Answer 1

If you are not replacing null watermark values in source table and copying the data as is, it is not incremental load. For every pipeline run, same rows with null value will be copied. If null values data can be copied in every run, you can use the same query. In that query, change (@{item().WaterMark_Column}= null) as (@{item().WaterMark_Column} is null) .

Corrected Code:

select * from @{item().TABLE_NAME} where 
(@{item().WaterMark_Column} >= '@{activity('Oldwatermark').output.firstRow.WatermarkValue}'and
@{item().WaterMark_Column} <= '@{activity('Newwatermark').output.firstRow.NewWatermarkvalue}') 
or (@{item().WaterMark_Column} is null)

Reference: MS document on IS NULL or IS NOT NULL instead of comparison operators