I have a table in Snowflake generated like:

create or replace temporary table example (asset_id int, assignment_id int);
insert into example (asset_id, assignment_id)
    values 
        (5000, 1),
        (5000, 1),
        (5000, 1),
        (6000, 1),
        (6000, 2);

select
    asset_id,
    assignment_id,
    uuid_string(uuid_string(), md5(concat(asset_id, assignment_id))) as uuid_assignment
from
    example;

I’d like to generate a uuid_string() for the combination of both asset_id and assignment_id using an md5 hash. After reading the documentation on uuid_string() I am a little confused on how to do what I am asking. I know uuid_string() is generating a random uuid, but I want to be able to assign the random generated uuid to a deterministic md5 hash.

https://docs.snowflake.com/en/sql-reference/functions/uuid_string

>Solution :

For the version of uuid_string that takes parameters, the original use case is for avoiding clashes by including a namespace (so that the same value in different namespaces produces different UUIDs) – so you would have a fixed UUID for each of your namespaces and pass the appropriate value into this function.

The way you have used it, the namespace value will be a random UUID each time you run it and therefore for the same input you will get a different output each time. If that’s not the behaviour you want then you need to provided a fixed string UUID as the first parameter e.g.

 uuid_string('8e884ace-bee4-11e4-8dfc-aa07a5b093db', md5(concat(asset_id, assignment_id))) as uuid_assignment

There are some good explanations of UUID generation here