Using MD5 and missing some records in my output in Upsolver SQLake

Question

I'm losing some data in my output and need help to identify the issue. We create a hashkey using the below 3 columns, rest is pretty much straight forward select from data source Upsert on hashkey.

SET hashkey = MD5(advertiser_id || marketplace_id || retailer);

SELECT hashkey,
       col1, col2, col3...
REPLACE ON DUPLICATE hashkey

score 0 · Answer 1 · answered Dec 15 '22 at 22:05

MD5 returns null if any of the inputs is null. Most likely this is causing the hashkey to return NULL if any of your three columns are null and then those records might be getting missed out. I would COALESCE the column which might be nullable to some fixed value like NA or anything to avoid the null case. For example, if marketplace_id can be null, then I would do below to solve this issue.

SET hashkey = MD5(advertiser_id || COALESCE(marketplace_id,'NA') || retailer);

Using MD5 and missing some records in my output in Upsolver SQLake

1 Answers1