Let me preface by saying that I am not using this for storing passwords or any other sensitive info -- I simply want a row-level sha/hash that I can use later or to check for unique records. My tables will be on the long side, in the range of 0.1 - 10 trillion rows.

I am using a Snowflake datawarehouse, and thus my options are SHA1, SHA2, MD5 (each with binary options), and HASH.

I guess I would like to minimize the chance of collisions (given the long tables) while not burning my compute credits needlessly.

Which one is the best option given my use case?

  • I can't speak to HASH, but the speed of SHA1, SHA2 (SHA256/SHA512) and MD5 vary depending on implementation, hardware and architecture (64 vs 32 bit). Can you run any simple experiments on the Snowflake platform to solve the performance part of your question? Dec 19 '17 at 19:49

No matter what you pick ...

Snowflake supports defining and maintaining constraints, but does not enforce them, except for NOT NULL constraints, which are always enforced.


So you have to... Given Snowflake Db table structures and how micro-partitions are pruned/scanned, I suspect something like the query below could get very slow. It will most likely scan entire table for the rows inserted.

  insert into T 
    Select * from (Select ... union Select ... union Select ... union ...) x 
    where x.hash not in (Select hash from T)

Using clustering keys may speed up the check for unique, but at the cost of much more data writes.

With native clustering you will need to write something closer to

insert into T 
select * 
from   (Select ... union Select ... union Select ... union ...) s
left   Join T t1
       -- f1,f2 ... are part of a natural unique key 
       on s.f1 = t1.f1
       and s.f2 = t1.f2 
       and s.hash = t.hash
where  t.hash is null

Good Luck

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.