0

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

gmp
  • 1
  • 3
  • What is the replacement value you use in IsNull or Coalesce function? – Aswin Jan 26 '23 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 Jan 26 '23 at 10:35
  • your max watermark value in the previous run will be greater than '1900-01-01' – Aswin Jan 26 '23 at 10:37
  • Try to replace the null value with max watermark value in the source data. – Aswin Jan 26 '23 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 Jan 26 '23 at 11:13
  • Why are replacing in lookup table? Replace the null value in source table with max watermark date value of previous pipeline run. – Aswin Jan 26 '23 at 11:42
  • @Aswin one more thing, if I dont want to replace the null value but I want that record in my target because only date is null but rest of the columns are not null . I have this query in my copy activity, somehow this is ignoring the rows with null dates. 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}= null) – gmp Jan 26 '23 at 14:09

1 Answers1

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

Aswin
  • 4,090
  • 2
  • 4
  • 16
  • 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 – gmp Jan 27 '23 at 08:58