Here’s Your Day 1 and 2 Checklist for Snowflake Adoption

Hashmap
Hashmap
Nov 13, 2019 · 13 min read

by Venkatesh Sekar

Image for post
Image for post

It was an Easy Decision

In addition, you have also identified some high-value use cases which you and your team intend to implement and deliver over the next quarter. Your team proceeds with use case development and they are able to use a lot of the great tips and quick starts as demonstrated in the Zero to Snowflake events and other Snowflake how-to documentation. You are really pleased that Snowflake just works and works as advertised!

Fast forward 6 months and word has gotten around on the success of Snowflake, users are very happy, and you have a number of additional use cases and analytics applications being developed very quickly in Snowflake across business units.

But Don’t Skip Over Best Practices

  • New databases were created on an “as needed” basis
  • Tables were created and owned by SYSADMIN or ACCOUNTADMIN
  • Databases were also created and owned by SYSADMIN or ACCOUNTADMIN
  • There were no clear privileges and roles defined, i.e. everyone is a SYSADMIN

You get the idea — in the rush to get new analytics applications out the door and start taking advantage of Snowflake, there were no clear best practices that were adopted.

Snowflake has provided awesome documentation, videos, tutorials, etc. The ability to use SQL for almost everything has also made it easier for developers to quickly learn and implement solutions in the service.

You realize that there was no clear cut direction provided as to where to start, what needs to be implemented first, and how to proceed in order to maximize value for the overall organization.

You might feel a bit like this…

Image for post
Image for post

Some of the clients that have called me in to assist are in the above situation, and I’ve assisted in reviewing the environment, conveying best practices to be implemented, and usually end up identifying, mentoring, and working with the client team on adopting a range of best practices for both the cloud overall as well as Snowflake.

But Sometimes You Are Given a Blank Slate

This client request really became my foundation for this post. I’ll lay out for you a series of steps for adopting Snowflake in an orderly fashion with best practices that will benefit you both today and going forward with what is sure to be the inevitable growth of the service across your organization.

If you’re already down the path with Snowflake, I hope I can help you better understand a few steps that you might want to consider.

The Users

Image for post
Image for post

Here’s the Checklist for Snowflake Adoption on Day 1 and 2

Day 1 (Administration)

  • Enable MFA
  • Define resource monitors
  • Create roles and role hierarchy
  • Create sandbox users
  • Assign roles to sandbox users

Day 2 (Open the Gates to Snowflake)

  • Creating databases
  • Creating schemas
  • Defining tables
  • Defining stages
  • Loading tables

I’ll briefly explain each of these steps and also point you to necessary, i.e. required reading, Snowflake documentation, which will give an even deeper perspective. As I’m reviewing the steps, I’ll also provide sample code when appropriate.

While the names used (e.g. Breaking Bad users) or even the methodology used may not follow your specific existing enterprise standards, this should give your team a starting point for using and developing in Snowflake today.

When the appropriate rules, guidelines, and integrations are properly implemented (maybe at a later date for you), your team should know what to do, how to do it, and who will do it giving you a much more predictable and governed adoption experience.

Day 1 (Administration)

Define the ACCOUNTADMIN Role

Assign this role to at least 2 users

We follow strict security procedures for resetting a forgotten or lost password for users with the ACCOUNTADMIN role. These procedures can take up to two business days. Assigning the ACCOUNTADMIN role to more than one user avoids having to go through these procedures because the users can reset each other’s passwords.

References:

Below are the steps for defining an account admin role:

#1 — Walter White (existing ACCOUNTADMIN) creates a user account for Skyler (Enterprise Architect).

USE ROLE SECURITYADMIN;CREATE USER SWHITE@LOSPOLLOS.COMPASSWORD = ‘password1234~’EMAIL = ‘SWHITE@LOSPOLLOS.COMMUST_CHANGE_PASSWORD = TRUEEXT_AUTHN_DUO = TRUEDISABLE_MFA = FALSE;GRANT ROLE ACCOUNTADMIN TO USER SWHITE@LOSPOLLOS.COM;

#2 — Walter asks Skyler to login immediately and validate that she can login. She is also forced to change her password.

Enable Multi-Factor Authentication

I’ll set aside into a “technical parking lot” the discussion on which MFA method should be adopted, and will opt to start with Duo. Users do not need to separately sign up with Duo or perform any tasks, other than installing the Duo Mobile application, which is supported on multiple smartphone platforms (iOS, Android, Windows, etc.).

Reference:

Go ahead and enable MFA for both accountadmin users Walter and Skyler.

Define resource monitors

Similarly, it is better to define your thresholds in Snowflake ahead of time before opening up the gates to the users. Otherwise, you could burn through credits quicker than anticipated and be left wondering what happened at the end of the month.

Reference:

Let’s define some arbitrary limits for today, knowing that we can come back on a later date to reset to appropriate levels:

USE ROLE ACCOUNTADMIN;CREATE RESOURCE MONITOR ACCOUNT_LIMIT_250WITH CREDIT_QUOTA = 250FREQUENCY = MONTHLYSTART_TIMESTAMP = IMMEDIATELYTRIGGERS ON 100 PERCENT DO NOTIFY;CREATE RESOURCE MONITOR ACCOUNT_LIMIT_300WITH CREDIT_QUOTA = 300FREQUENCY = MONTHLYSTART_TIMESTAMP = IMMEDIATELYTRIGGERS ON 100 PERCENT DO SUSPEND;CREATE RESOURCE MONITOR ACCOUNT_LIMIT_375WITH CREDIT_QUOTA = 375FREQUENCY = MONTHLYSTART_TIMESTAMP = IMMEDIATELYTRIGGERS ON 100 PERCENT DO SUSPEND_IMMEDIATE;

You can also enable notifications by following the link below.

Reference:

Creating Roles and Role Hierarchy

References:

This is where we tend to see some missteps in the early days of getting setup. So, to keep it simple, if you are wondering what roles to create, instead of giving all the developers and analysts a SYSADMIN role, you could adopt the below roles as an initial pattern.

Image for post
Image for post

The below diagram shows the role hierarchy.

Image for post
Image for post

Get started by creating the roles:

USE ROLE SECURITYADMIN;CREATE ROLE DBA;CREATE ROLE DEVELOPER;CREATE ROLE LOADER;CREATE ROLE ANALYST;CREATE ROLE VISUALIZER;

Reference:

Then define the role hierarchy:

USE ROLE SECURITYADMIN;GRANT ROLE LOADER TO ROLE DEVELOPER;GRANT ROLE DEVELOPER TO ROLE DBA;GRANT ROLE VISUALIZER TO ROLE ANALYST;GRANT ROLE ANALYST TO ROLE DBA;GRANT ROLE DBA TO ROLE SYSADMIN;

Next, give base privileges to the DBA roles:

USE ROLE SYSADMIN;GRANT CREATE DATABASE ON ACCOUNT TO ROLE DBA;GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE DBA;

Create Sandbox Users

Reference:

Create some “play users” assigned by specific developers and proceed as intended. Once you have the AD or OKTA integration completed, the developers could be assigned to the appropriate roles and the “play users” can be dropped. You’d be able to make some progress made without causing any interruptions. This is possible since the database objects are owned by roles rather than users.

Here’s how you would create those users:

USE ROLE SECURITYADMIN;CREATE USER SGOODMANPASSWORD = ‘password1234~’EMAIL = ‘SGOODMAN@LOSPOLLOS.COMMUST_CHANGE_PASSWORD = TRUE;

Use the same statement to create the other accounts from the user table above for the other Los Pollos users.

Assign Roles to Play Users

Reference:

USE ROLE SECURITYADMIN;GRANT ROLE SECURITYADMIN TO USER SGOODMAN;GRANT ROLE DBA TO USER HSCHRADER;GRANT ROLE DEVELOPER TO USER JPINKMAN;GRANT ROLE ANALYST TO USER GGUS;

In addition to granting roles to users, take the extra step of setting the default role for these users as outlined below:

Reference:

USE ROLE SECURITYADMIN;ALTER USER SGOODMAN SET DEFAULT_ROLE = SECURITYADMIN;ALTER USER HSCHRADER SET DEFAULT_ROLE = DBA;ALTER USER JPINKMAN SET DEFAULT_ROLE = DEVELOPER;ALTER USER GGUS SET DEFAULT_ROLE = ANALYST;

Simple enough! Go ahead and inform the users to login into Snowflake and change their password. After they do that, they can interact with the sample databases that Snowflake provides, review documentation, and also use the existing warehouse DEMO_WH as a starting point for issuing select against the sample databases.

This is a good stopping point for Day 1 and sets you up nicely for Day 2.

Day 2 (Open the Gates to Snowflake)

Creating Snowflake Warehouses

Start by defining some basic warehouses that your users will have access to initially. In the future, you could always create warehouses for larger sizes and assign users accordingly.

Reference:

USE ROLE DBA;CREATE WAREHOUSE PLAY_XS WITHWAREHOUSE_SIZE = XSMALLMAX_CLUSTER_COUNT = 2SCALING_POLICY = ECONOMYAUTO_SUSPEND = 120ALTER RESUME = TRUECOMMENT = ‘Use this as the default warehouse for the development initially.’;CREATE WAREHOUSE PLAY_SMALL WITHWAREHOUSE_SIZE = SMALLMAX_CLUSTER_COUNT = 2SCALING_POLICY = ECONOMYAUTO_SUSPEND = 120ALTER RESUME = TRUECOMMENT = ‘Use this as the warehouse if you want to improve query performance.’;

Once the warehouse has been created, grant usage to the warehouse for the various roles:

USE ROLE DBA;GRANT USAGE, OPERATE ON WAREHOUSE PLAY_XS TO ROLE LOADER;GRANT USAGE, OPERATE ON WAREHOUSE PLAY_XS TO ROLE VISUALIZER;GRANT USAGE, OPERATE ON WAREHOUSE PLAY_SMALL TO ROLE LOADER;GRANT USAGE, OPERATE ON WAREHOUSE PLAY_SMALL TO ROLE VISUALIZER;

Since the grants were assigned to base roles, they will be inherited by DBA, DEVELOPER, ANALYST via role privilege inheritance. You should also lock down the modification of Snowflake provided warehouses.

REVOKE MODIFY ON WAREHOUSE DEMO_WH FROM ROLE PUBLIC;REVOKE MODIFY ON WAREHOUSE LOAD_WH FROM ROLE PUBLIC;

Set the warehouse default by user as below:

USE ROLE SECURITYADMIN;ALTER USER SGOODMAN SET DEFAULT_WAREHOUSE = PLAY_XS;ALTER USER HSCHRADER SET DEFAULT_WAREHOUSE = PLAY_XS;ALTER USER JPINKMAN SET DEFAULT_WAREHOUSE = PLAY_XS;ALTER USER GGUS SET DEFAULT_WAREHOUSE = PLAY_XS;

Creating Snowflake Databases

We start by defining a sandbox database, in which the users can start their initial activities. The database is purposely defined as TRANSIENT.

Reference:

USE ROLE DBA;CREATE OR REPLACE TRANSIENT DATABASE SANDBOX_PLAYDB;GRANT USAGE ON DATABASE SANDBOX_PLAYDB TO ROLE LOADER;GRANT USAGE ON DATABASE SANDBOX_PLAYDB TO ROLE VISUALIZER;

Creating Snowflake Schemas

USE ROLE DBA;CREATE OR REPLACE TRANSIENT SCHEMA LAUNDROMAT;Define the grants as follows:GRANT USAGE ON SCHEMA LAUNDROMAT TO ROLE LOADER;GRANT USAGE ON SCHEMA LAUNDROMAT TO ROLE VISUALIZER;GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA LAUNDROMAT TO ROLE LOADER;GRANT SELECT ON FUTURE TABLES IN SCHEMA LAUNDROMAT TO ROLE VISUALIZER;GRANT ALL PRIVILEGES ON FUTURE VIEWS IN SCHEMA LAUNDROMAT TO ROLE LOADER;GRANT SELECT ON FUTURE VIEWS IN SCHEMA LAUNDROMAT TO ROLE VISUALIZER;GRANT ALL PRIVILEGES ON FUTURE STAGES IN SCHEMA LAUNDROMAT TO ROLE LOADER;GRANT ALL PRIVILEGES ON FUTURE FILE FORMATS IN SCHEMA LAUNDROMAT TO ROLE LOADER;

For the rest of Day 2, I recommend focusing on the following activities:

What’s Next

It would be great to hear if this helped you and what your next set of moves is with Snowflake!

Need Snowflake Cloud Data Warehousing and Migration Assistance?

How does Snowflake compare to other data warehouses? Our technical experts have implemented over 250 cloud/data projects in the last 3 years and conducted unbiased, detailed analyses across 34 business and technical dimensions, ranking each cloud data warehouse.

To listen in on a casual conversation about all things data engineering and the cloud, check out Hashmap’s podcast Hashmap on Tap as well on Spotify, Apple, Google, and other popular streaming apps.

Other Tools and Content You Might Like

Feel free to share on other channels and be sure and keep up with all new content from Hashmap here.

Venkat Sekar is Regional Director for Hashmap Canada and is an architect and consultant providing Data, Cloud, IoT, and AI/ML solutions and expertise across industries with a group of innovative technologists and domain experts accelerating high-value business outcomes for our customers.

HashmapInc

Innovative technologists and domain experts helping…

Hashmap

Written by

Hashmap

Innovative technologists and domain experts accelerating the value of Data, Cloud, IIoT/IoT, and AI/ML for the community and our customers http://hashmapinc.com

HashmapInc

Innovative technologists and domain experts helping accelerate the value of Data, Cloud, IIoT/IoT, and AI/ML for the community and our clients by creating smart, flexible and high-value solutions and service offerings that work across industries. http://hashmapinc.com

Hashmap

Written by

Hashmap

Innovative technologists and domain experts accelerating the value of Data, Cloud, IIoT/IoT, and AI/ML for the community and our customers http://hashmapinc.com

HashmapInc

Innovative technologists and domain experts helping accelerate the value of Data, Cloud, IIoT/IoT, and AI/ML for the community and our clients by creating smart, flexible and high-value solutions and service offerings that work across industries. http://hashmapinc.com

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store