Sometimes, you need to work with tables that have a predefined list of values. You can have these automatically uploaded and refreshed to Snowflake via several means inherent to Snowflake (or any of a variety of methods from PowerShell, R, Python, etc..)
But, sometimes, you want a small list of data inherent to a query in SnowFlake. And that’s what I want to talk about today.
In SQL Server, you would create a temp table and then insert the data into it. But in Snowflake, there may be a better / easier way.
Let’s use the function SPLIT_TO_TABLE. Shockingly, it does what’s on the label – it split data and puts it into a table.
The two parameters for the SPLIT_TO_TABLE function are as follows:
- String to split
- Delimiter
Notice that there is no space between the commas. If we had put spaces after the commas, we would have to trim the Value.
But, I’m sure you’re thinking to yourself “Value? Why Value? Is there more to this function? Tell us Sherpa!”
And so I shall my friends. And so I shall.
Notice that Snowflake keeps the sequence and index values of the function so that it can keep track of everything… Isn’t Snowflake just nice to us?
Next time, we’ll go into some more goodness that we can do with Snowflake. With that, it is now my time to act like a banana and split…