One of the many things that the business team asks me to do is to create random-ish data. Thankfully, in Snowflake, there are many ways to make this happen. Today, I want to go thru just a few of them.
Perhaps the one that most people are familiar with is making Snowflake create a random number.
SELECT RANDOM();This will generate a random number that Snowflake comes up with. Now the thing to be aware of is that it can be a very very large or small number, as shown below:
This kind of number – for those of us who have had to work with “randomness” – may or may not be very useful in this form when it comes to data.
Thankfully, Snowflake allows us a way to get a bit more valuable data to use. This uses the uniform function to get data over to us in a – you guessed it – “uniform” manner.
SELECT UNIFORM(1, 10, RANDOM()) AS RandomNumber;With this function, we actually get an integer returned to us, as shown below:
Notice that we still have the random function to give us randomness, but now the uniform function tells Snowflake that we want a number between 1 and 10 (min and max, respectively, of the values we want to be returned) and that it has to be an integer (shown by us only providing integers to the function).
If we would like to get data with 2 decimals, we would change the function only slightly:
Notice that the 1, in this occasion, sets the minimum value – seen internal to Snowflake as 1.00 – so that we can have any number with 2 decimals returned between 1 and 10.
For even more Snowflake fun, let’s say you want to create data goodness like we’ve been working with here – but you want to have a table with 12 rows. Shockingly, Snowflake has a function that will help you with that.
SELECT uniform(1, 10::number(10,2), RANDOM()) AS RandomNumber
FROM TABLE(GENERATOR(ROWCOUNT => 12)) v
ORDER BY 1;Putting the fun in function this time is the Generator function! We are telling Snowflake how many rows we want ( => 12) and Snowflake generates it. No muss, no fuss.
The table function – if you haven’t used it before – forces the data within the function call to be seen as a table to Snowflake. There are some other uses for that function that we’ll go through in an upcoming blog post!
And so with that, we have reached the end of yet another blog post. Hopefully, you’ve learned a few different ways to get random data into Snowflake that can be useful in a few ways.