Month: October 2020

Everyone is excited about Snowflake cloud database! IPO stock price aside, Snowflake really is a revolutionary concept. Today we will look more into a major architecture component of Snowflake called the Virtual(data) warehouse.

At their core, virtual warehouses are simply one or more clusters of compute resources used to process queries and other dml operations.  You can create many warehouses and use the Web User Interface to run queries on data and other dml operations.

You can use various virtual warehouses for different purposes such as loading, analysis, and to support software development lifecycles such as development, test, integration, test, and production.

You can then configure the database with a name, and size (always pick the smallest and scale up). The max # of clusters, in this case we will select 2;  with a min # of clusters set to 1. Scaling policy is set to standard, this will kick in when activity picks up to a certain level and resources are consumed. The auto suspend option will deactivate the ware house after 15 min of inactivity.  This will reduce costs and you actually only pay for what you use! The Auto Resume option will startup the warehouse when activity picks back up! 

In addition, the “Show SQL” feature (bottom left) can create the command to generate a virtual warehouse at the command line

CREATE WAREHOUSE Test_DW WITH WAREHOUSE_SIZE = ‘XSMALL’ WAREHOUSE_TYPE = ‘STANDARD’ AUTO_SUSPEND = 600 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = ‘STANDARD’ COMMENT = ‘create virtual warehouse example’;

Now that your virtual warehouse is created, the fun begins! You can load some sample data and create databases and then you can select which virtual warehouse you can run your queries on!  In the screenshot below you can select which data warehouse to run your queries on.  For the traditional on-prem database administrator, this is a dream! You can have the ability to run your queries and data on the virtual data warehouse compute power of your choice!  This is a significant switch since usually the dba is restricted to a specific virtual cluster/hardware.

You can now have multiple virtual warehouses to select from to execute your query! Great for software development lifecycle development and testing, no more loading your data into different environments before you can use it. A simple configuration change and you’re in different virtual warehouses.

As you can see below, we ran the

select current_database(), current_schema();

command to see the current database and schema.

So to summarize, you can create a virtual warehouse. Then select your database and commands you wish to run and execute your queries with the virtual warehouse of your choice! Snowflake makes it easy to learn this helpful interface as well as climb the learning curve that comes with any new platform. In addition, with the help of certified Snowflake partners, there is no limit to the applications for businesses large and small.