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

What is the replacement value you use in IsNull or Coalesce function? — Aswin
– Aswin, Commented Jan 26, 2023 at 10:27
@Aswin I am using the 1900-01-01 00:00:00.000 , so if i get null value from my date column it should replace it will the above value — gmp
– gmp, Commented Jan 26, 2023 at 10:35
your max watermark value in the previous run will be greater than '1900-01-01' — Aswin
– Aswin, Commented Jan 26, 2023 at 10:37
Try to replace the null value with max watermark value in the source data. — Aswin
– Aswin, Commented Jan 26, 2023 at 10:38
@Aswin I am using select coalesce(MAX(@{item().WaterMark_Column}),'1900-01-01 00:00:00.000') as NewWatermarkvalue from @{item().TABLE_NAME} in my lookup and its taking that value where date us null , even the pipeline succeeds but the row is not getting copied over and there is only 1 record in this table — gmp
– gmp, Commented Jan 26, 2023 at 11:13

Aswin · Accepted Answer · 2023-01-27 04:04:35Z

0

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

answered Jan 27, 2023 at 4:04

Aswin

7,3242 gold badges9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gmp Over a year ago

ah okay, Thank you so much for clearing somethings, It was a bit confusing while working on ADF or I must have overthought and made it confusing

Collectives™ on Stack Overflow

Handling Null Values in ADF Pipeline

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related