Overview

Welcome to the Powered by Tasty Bytes - Zero to Snowflake Quickstart focused on Financial Governance!

Within this Quickstart, we will learn about Financial Governance in Snowflake by diving into Snowflake Warehouses and their configurabilities, Resource Monitors, and Account and Warehouse Level Timeout Parameters.

For more detail on Financial Governance in Snowflake please visit the Financial Governance Overview documentation.

Prerequisites

What You Will Learn

What You Will Build

Overview

Within this Quickstart we will follow a Tasty Bytes themed story via a Snowsight SQL Worksheet with this page serving as a side by side guide complete with additional commentary, images and documentation links.

This section will walk you through logging into Snowflake, Creating a New Worksheet, Renaming the Worksheet, Copying SQL from GitHub, and Pasting the SQL we will be leveraging within this Quickstart.

Step 1 - Accessing Snowflake via URL

Step 2 - Logging into Snowflake

Step 3 - Navigating to Worksheets

Step 4 - Creating a Worksheet

Step 5 - Renaming a Worksheet

Step 6 - Accessing Quickstart SQL in GitHub

Step 7 - Copying Setup SQL from GitHub

Step 8 - Pasting Setup SQL from GitHub into your Snowflake Worksheet

Step 9 - Click Next –>

Overview

As a Tasty Bytes Snowflake Administrator we have been tasked with gaining an understanding of the features Snowflake provides to help ensure proper Financial Governance is in place before we begin querying and analyzing data.

Step 1 - Role and Warehouse Context

Before we create a Warehouse, let's first set our Role and Warehouse context.

The queries below will assume the role of tasty_admin via USE ROLE and leverage the tasty_de_wh warehouse via USE WAREHOUSE.

To run the queries, please highlight the two queries in your created Worksheet that match what you see below and click the "► Run" button in the top-right hand corner.

Once these are executed you will a Statement executed successfully. result and notice the Worksheet context reflect the Role and Warehouse as shown in the screenshot below.

USE ROLE tasty_admin;
USE WAREHOUSE tasty_de_wh;

Step 2 - Creating and Configuring a Warehouse

Within Snowflake, Warehouses are highly configurable to meet your compute demands. This can range from scaling up and down to meet compute needs or scaling out to meet concurrency needs.

The next query will create our first Warehouse named tasty_test_wh. Please execute this query now which result in another Statement executed successfully. message.

CREATE OR REPLACE WAREHOUSE tasty_test_wh WITH
COMMENT = 'test warehouse for tasty bytes'
    WAREHOUSE_TYPE = 'standard'
    WAREHOUSE_SIZE = 'xsmall' 
    MIN_CLUSTER_COUNT = 1 
    MAX_CLUSTER_COUNT = 2 
    SCALING_POLICY = 'standard'
    AUTO_SUSPEND = 60
    AUTO_RESUME = true
    INITIALLY_SUSPENDED = true;

Based on the query we ran, please see the details below on what each configuration handles within our CREATE WAREHOUSE statement.

Step 3 - Click Next –>

Overview

With a Warehouse in place, let's now leverage Snowflakes Resource Monitors to ensure the Warehouse has a monthly quota that will allow our admins to track it's consumed credits and ensure it is suspended if it exceeds its assigned quota.

Step 1 - Creating a Resource Monitor

To begin, lets assume our accountadmin role and then create our first Resource Monitor using CREATE RESOURCE MONITOR by executing the next set of queries.

USE ROLE accountadmin;

CREATE OR REPLACE RESOURCE MONITOR tasty_test_rm
WITH 
    CREDIT_QUOTA = 100 -- 100 credits
    FREQUENCY = monthly -- reset the monitor monthly
    START_TIMESTAMP = immediately -- begin tracking immediately
    TRIGGERS 
        ON 75 PERCENT DO NOTIFY -- notify accountadmins at 75%
        ON 100 PERCENT DO SUSPEND -- suspend warehouse at 100 percent, let queries finish
        ON 110 PERCENT DO SUSPEND_IMMEDIATE;

For additional detail on what each configuration handles in our statement above please see below:

Step 2 - Applying our Resource Monitor to our Warehouse

With our Resource Monitor successfully created, let's now apply it to our created Warehouse using ALTER WAREHOUSE ... SET RESOURCE_MONITOR.

Please execute the final query of this step which will result in a Statement executed successfully. message.

ALTER WAREHOUSE tasty_test_wh SET RESOURCE_MONITOR = tasty_test_rm;

Step 3 - Click Next —>

Overview

With monitoring in place, let's now make sure we are protecting ourselves from bad long running queries ensuring timeout parameters are adjusted on the Warehouse.

Step 1 - Exploring Warehouse Statement Parameters

To begin, let's run the next query to find all Warehouse Parameters related to Statements using the SHOW PARAMETERS command.

SHOW PARAMETERS LIKE '%statement%' IN WAREHOUSE tasty_test_wh;

Step 2 - Adjusting Warehouse Statement Timeout Parameter

Having seen the two available Warehouse Statement Parameters, let's first adjust statement_timeout_in_seconds to 30 minutes by running the next query.

Since this parameter is in seconds we will set it equal to 1800 (30 minutes x 60 seconds).

Once executed we will receive another Statement executed successfully. result.

ALTER WAREHOUSE tasty_test_wh SET statement_timeout_in_seconds = 1800;

Step 3 - Adjusting Warehouse Statement Queued Timeout Parameter

Next, we will adjust statement_queued_timeout_in_seconds to 10 minutes by running the next query.

Since this parameter is also in seconds we will set it equal to 600 (10 minutes x 60 seconds).

Once executed we will receive another Statement executed successfully. result.

ALTER WAREHOUSE tasty_test_wh SET statement_queued_timeout_in_seconds = 600;

Step 4 - Click Next –>

Overview

These timeout parameters are also available at the Account, User and Session level. As we do not expect any extremely long running queries let's also adjust these parameters on our Account.

Moving forward we will plan to monitor these as our Snowflake Workloads and Usage grow to ensure they are continuing to protect our account from unneccesary consumption but also not cancelling longer jobs we expect to be running.

Step 1 - Adjusting the Account Statement Timeout Parameter

To begin, our Account level Statement Parameter changes let's adjust statement_timeout_in_seconds to 5 hours by running the next query.

Since this parameter is in seconds we will set it equal to 18000 ([5 hours x 60 minutes] x 60 seconds).

Once executed we will receive another Statement executed successfully. result.

ALTER ACCOUNT SET statement_timeout_in_seconds = 18000;

Step 2 - Adjusting the Account Statement Queued Timeout Parameter

As we did with our Warehouse, let's now adjust statement_queued_timeout_in_seconds to 1 hour by running the next query.

Since this parameter is also in seconds we will set it equal to 3600 ([1 hour x 60 minutes] x 60 seconds). Once executed we will receive another Statement executed successfully. result.

ALTER ACCOUNT SET statement_queued_timeout_in_seconds = 3600;

Step 3 - Click Next –>

Overview

With Financial Governance building blocks in place, let's now leverage the Snowflake Warehouse we created to execute a few queries. Along the way, let's scale this Warehouse up and back down as well as test manually suspending it.

Step 1 - Use our Warehouse to Run a Simple Query

To begin, let's run the next three queries. The first two will set our tasty_admin role and tasty_test_wh context and the final one will query our raw_pos.menu table to find all food items sold at our Cheeky Greek branded trucks.

USE ROLE tasty_admin;
USE WAREHOUSE tasty_test_wh; 

SELECT 
    m.menu_type,
    m.truck_brand_name,
    m.menu_item_id,
    m.menu_item_name
FROM frostbyte_tasty_bytes.raw_pos.menu m
WHERE truck_brand_name = 'Cheeky Greek';

Step 2 - Scale our Warehouse Up

After completing a basic query against one of our dimension tables, let's now get ready to query our much larger orders data set.

Let's now instantly scale our tasty_test_wh up by executing our next query leveraging ALTER WAREHOUSE... SET warehouse_size.

Upon completion we will recieve another Statement executed successfully result.

ALTER WAREHOUSE tasty_test_wh SET warehouse_size = 'XLarge';

Step 3 - Run an Aggregation Query Against a Large Data Set

With our Warehouse scaled up, let's now run our next query which uses CONCAT, COUNT and SUM to calculate orders and total sales for Tasty Bytes customer loyalty members.

SELECT 
    o.customer_id,
    CONCAT(clm.first_name, ' ', clm.last_name) AS name,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(o.price) AS total_sales
FROM frostbyte_tasty_bytes.analytics.orders_v o
JOIN frostbyte_tasty_bytes.analytics.customer_loyalty_metrics_v clm
    ON o.customer_id = clm.customer_id
GROUP BY o.customer_id, name
ORDER BY order_count DESC;

Step 4 - Scale our Warehouse Down

Having seen the instant upward scalability of our Snowflake Warehouse and how it can aggregate large result sets with ease, let's now instantly scale our tasty_test_wh back down by running the next query.

Upon completion we will recieve another Statement executed successfully result.

ALTER WAREHOUSE tasty_test_wh SET warehouse_size = 'XSmall';

Step 5 - Suspend our Warehouse

To cap things off, we previously set the auto_suspend to 60 seconds on our tasty_test_wh but let's also take a look at how to manually suspend a warehouse by executing our final query.

ALTER WAREHOUSE tasty_test_wh SUSPEND;

Note: Depending on how fast you have ran through these last statements you will receive of the two following results:

  1. Statement executed successfully: This means you were very fast in executing the steps in this section and have beat the auto_suspend Warehouse setting we configured to 60 seconds.
  2. Invalid state. Warehouse ‘TASTY_TEST_WH' cannot be suspended: This means that the amazing auto_suspend Warehouse setting we configured to 60 seconds has won the Warehouse suspension race.

Step 6 - Click Next –>

Conclusion

Fantastic work! You have successfully completed the Tasty Bytes - Zero to Snowflake - Financial Governance Quickstart.

By doing so you have now:

If you would like to re-run this Quickstart please leverage the Reset scripts in the bottom of your associated Worksheet.

Next Steps

To continue your journey in the Snowflake Data Cloud, please now visit the link below to see all other Powered by Taste Bytes - Quickstarts available to you.