There are plenty of guides to setting up a trial of Snowflake online, but as part of my “Roll for Database” series, I want to have the basics of setting up a free trial of any platform I work with, including my own experiences. Here, we’ll cover Snowflake, which is the platform I now use for “rolling” my dice – using SQL to find random numbers in the ranges I need, along with it being a platform I might work on depending on what the dice gods send my way.
Note: I’m an employee of Snowflake, and as such have access to significant resources at work. I plan to not use that access for this personal blog. I do my best to not let my day job impact my blog, and vice versa. My opinions are my own and in no way associated with my employer.
If your employer uses Snowflake and you’re learning for work, they may be able to provide you with an id with the right access to experiment as well.
Snowflake: The Short Description
Snowflake is a cloud-native database with particular strengths in data warehousing workloads. It lives in AWS, Azure, and GCP. The interface to Snowflake and the experience is the same between these three clouds. While interfaces for working with it can be installed locally, it cannot itself be installed locally. It is only in the cloud and is cloud-native in some very interesting ways. It has many features that go beyond this into full data lake functionality and support for AI/ML. Some of the data engineering it can do natively is really interesting. It enables seamless and outage-free scaling of compute resources.
Free Trial
The Snowflake free trial is a 30-day trial with up to $400 available. I’ve heard, though not yet confirmed, that I can sign up for an additional trial with the same email address after my first one expires. We may end up testing the limits of this through the course of blog entries here!
We can sign up for the free trial at https://signup.snowflake.com/. Snowflake is a fully cloud database. There is no local version to install or experiment with. Personal information is required to sign up for a free trial. However, you don’t need ANY separate resources – no other cloud accounts, no local processing power are needed to use Snowflake.
At the time of this blog entry the landing page for signup looks something like this:
When I signed up, my gmail address worked just fine. After that, we have to pick an edition and a cloud provider. I’ll pick Enterprise since it has all the features I might use. I’ll select AWS since that’s the cloud I’ve spent the most time with. From a functionality standpoint, the choice of cloud doesn’t make much difference, but if using real data here, choose a cloud and region where the data lives to minimize egress costs. You don’t need to know anything about the cloud provider to be successful with Snowflake.
I’ll select the region of US West (Oregon) since that’s not unreasonably far from my Colorado home, as far as cloud regions go. You should also read and agree to the terms and conditions.
There will be several questions to click through. Then there is a confirmation screen that looks like this:
In my email, I find this:
The link I’ve blocked here is critical to accessing the account. When clicking on the button for activation, it prompts for a user name and password.
Save the username, password, and that link in a secure password manager. I’d also recommend setting up two-factor authentication.
Once a user and password are specified it will pull up the snowsight console, with an screen offering some tutorials. The Quick Starts are really good tutorials – just avoid the urge to simply copy/paste without understanding what the tutorial is doing.
I just skip the introductions here.
Interfaces to Use
All the classic database interface methods are available with Snowflake – connectors for most languages, ODBC, JDBC, etc. I don’t think there are Rails drivers, but that goes along with the focus on data warehousing workloads. For basic interfaces that are not really application focused, there are two main ones – a GUI and a command line. Which you use depends entirely on your preferences.
GUI – Snowsight
The snowsight GUI should have opened after the setup of a new account. There is an older GUI in some tutorials and such, and it is still available. I use the Snowsight GUI for two reasons – first, it is the strategic direction for future improvements and changes. Second, there are some interesting fast visualizations available from query results by simply clicking on “Chart” and playing with some options. I find this incredibly handy when I’m exploring data and don’t want to write code to get visual representations.
In this GUI, click on worksheets at the left, and then the plus in the upper right to create a new SQL worksheet to interact with Snowflake.
At this point select a warehouse and it is immediately possible to run commands that do not require a database:
In this case, I selected the default warehouse that was created for me – compute_wh. Remember that in Snowflake, warehouses define compute, but NOT storage. I then ran a simple SQL command to generate a random number between 1 and 6. Snowflake doesn’t require a dummy table like Oracle’s DUAL or Db2’s SYSIBM.SYSDUMMY1.
Notice that even in my brand-new account, there are two databases on the left. One is called SNOWFLAKE, and one is called SNOWFLAKE_SAMPLE_DATA. The SNOWFLAKE database contains information about your Snowflake like the information schema views and information about account utilization. The SNOWFLAKE_SAMPLE_DATA database contains sample data for playing with and learning – it is quite useful to have it already there at several different scales to be able to play with.
SnowSQL
Coming from over twenty years of using command line interfaces with Db2 and MySQL, I’m a bit of a fan of command lines. So I go through the work to set up SnowSQL. Yes, everything has names relating to snow, skiing, or something similar. It is unavoidable and everywhere internally as well.
There is excellent documentation on setting up SnowSQL, and there is a native version for Mac, which makes me happy.
Pay attention to the section on modifying the SnowSQL file, as this is what will make connecting easy.
In my case, I need to look up my account identifier in the lower left of the Snowsight interface. If you’re in a worksheet, you may have to go back to the home to see this.
If you have multiple accounts (like I do), make sure to select the right one. I had to redact a lot there, so I hope it is clear.
Once that account identifier is copied, open up the config file locally. In my case that’s $HOME/.snowsql/config.
$ vi .snowsql/config
I fill in the accountname and the username. The accountname is what we copied, HOWEVER, the copied text includes a dot between two identifiers, and that must be changed to a dash. For example, if the account identifier was EXAMPLE.REDACTED, change it to EXAMPLE-REDACTED. The account identifier is likely to be composed of strings of letters and numbers. I don't like saving a password in plain text even for a sandbox, so I just fill that in when it prompts me.
Once I've edited my .snowsql/config, I can then just connect like this:
$ snowsql
Password:
* SnowSQL * v1.2.30
Type SQL statements or !help
ember#COMPUTE_WH@(no database).(no schema)>Note that it has already selected a warehouse for me. I can now run the same SQL I used from snowsight:
ember#COMPUTE_WH@(no database).(no schema)>SELECT UNIFORM(1,6,RANDOM());
+-----------------------+
| UNIFORM(1,6,RANDOM()) |
|-----------------------|
| 6 |
+-----------------------+
1 Row(s) produced. Time Elapsed: 0.655sSummary
Hopefully there is some useful information here. It is relatively painless to set up and use a free Snowflake account to experiment. When I was job searching earlier this year, I was interviewing with Snowflake and one of their competitors, Databricks, at the same time. I found it far easier to set up a free trial for Snowflake than I did for Databricks. At that time, Databricks required me to have my own account with a cloud provider to experiment. Snowflake does not.
