Generating unique random number with condition Snowflake

I would like to generate some UNIQUE random numbers in Snowflake with a specific starting/ending point. I would like for the numbers to start at 1,000 and end at 1,000,000.

Another requirement is joining a string at the beginning of these numbers.

So far I have been using this statement:

SELECT CONCAT('TEST-' , uniform(10000, 99000, RANDOM()));

Which works as expected and gives me the output of e.g. 'TEST-31633'.

However the problem is I am generating these for a large amount of rows, and I need for them to be completely unique.

I have heard of the 'SEQ1' functions however not sure how I could specify a starting point as well as adding a 'TEST-' with the CONCAT function at the beginning. Ideally they won't be in a strict sequence but differ from each other.

Thank You


  • It's not possible to guarantee uniqueness from a random number generator function unless you use some kind of calculated part. For example:

    create or replace table testX as
    select  CONCAT('TEST-' , to_char(seq4(),'0000'),  
    uniform(100000, 990000, random())) c 
    from table(generator(rowcount => 1000));