Managing Multiple Environments in Snowflake

5 steps to becoming a Snowflake pro.

Photo by Aditya Vyas on Unsplash

Different Needs for Different Users

If your company has been using Snowflake for your cloud data, you are hardly alone. After they had the biggest software IPO in history in September, everyone was talking about Snowflake and cloud data warehousing. If your company is new to the product, you may just be getting started. This blog post is about how to set up multiple environments within Snowflake. But first let’s talk about how your company uses data warehouses.

Not every organization will use Snowflake the same way. For some companies, they only store a subset of their data in the warehouse, and they use that data sporadically (for monthly or quarterly reporting). For other companies, Snowflake is forming the central hub for all of their data, and it gets used for marketing, data science, business intelligence, really all of the company’s needs. At Equinox Media, we fall into the latter category.

We also follow an Agile methodology for developing pipelines and solutions in our data warehouse in Snowflake. Since there are several teams involved in this Agile development, and since each team might operate in a different environment, we need to set up multiple environments for our data warehouse. If you know Agile, you’ll be familiar with how our teams operate when developing, say, a new report:

We have separate, isolated environments for development, testing, and production. Having multiple environments meets our needs, but maybe the needs of your organization are different, and you would be happy with just a single environment. How can you decide? There are a number of factors:

The Case for a Single Environment

If your organization meets some or all of the following criteria, a single environment would most likely be preferred:

• You don’t follow a development methodology (such as Agile, waterfall, spiral, or any other SDLC model involving multiple phases), and thus may not need separate development or test environments.
• You have very few users, perhaps just a few developers and users of the warehouse, so you may want to keep the environment plan simple. Development is quick, and users can navigate around without having to muck about with roles and warehouses.
• You have very few data sources, and you do not foresee your data warehouse growing, so you may not want to spend a lot of time worrying about setting up multiple environments, only to not use them.
• You don’t have a somewhat experienced DBA. While it sounds silly to need DBA for a cloud service like Snowflake, in reality you do need someone to take on a pseudo DBA role to monitor and manage credit usage, performance tuning, security, cloning, etc. Having multiple environments creates more overhead.

How to Set Up Multiple Environments in Snowflake

Great! You’ve decided to split up your Snowflake into different environments, let’s go over the 5 steps you’ll need to take in order to accomplish what we set up at Equinox Media:

1. Clone your production database

• Snowflake’s zero-copy cloning makes it easy to create a copy of your environment without actually copying any of your data
• I recommend having non-PROD environments be transient and have 1 day of time-travel (transient objects can only have 0 or 1 day of time-travel)

How it appears after you clone your prod db

2. Set up environment specific access roles

• To achieve isolated environments, we need to set up roles that will allow or not allow users to access a database
• PROD roles will not allow the users access to the DEV database, and vice versa
• The code below shows schema-level roles since we wanted to restrict what types of objects go into landing vs data warehouse vs reporting:

• Repeat the above for each schema and database, you should end up with a structure that looks somewhat like this:

3. Set up environment specific functional roles

Access roles will be granted to the functional roles, and functional roles will be granted to the end users. Here are some examples of functional roles for different teams:

As a best practice, be sure to maintain role hierarchy by assigning these functional roles to the higher level SYSADMIN role

With functional roles added, your structure should look something like:

4. Set up Warehouses

Depending on how you would like to monitor and manage credit consumption in Snowflake, you may want to have a single warehouse for each workload across all environments, or split up the warehouse for each environment

Single Warehouse

Create a single warehouse across all environments for each type of workload and grant warehouse to all corresponding roles. This is easier to manage, but harder to monitor and to separate out the costs. Here is an example:

Environment Specific Warehouses

You could instead create environment specific warehouses for each type of workload and grant warehouse to each corresponding role as shown below. This requires more management, but it is a clearer way to monitor and separate costs, and this is the approach we took at Equinox Media:

5. Grant Functional Roles

That’s it! You can now grant the environment specific functional roles to your users and start developing away:

Conclusion

Snowflake makes it relatively easy to set up multiple environments for your company. This supports the development methodology your company uses in a way that makes it easy to monitor and separate costs. If you are looking to set up multiple environments, I hope this made it easier for you.

Bonus Material — Multiple Intra-Environment Databases

The ability to zero-copy clone a full database copy within seconds using virtually no storage is unprecedented, letting us expand our thinking about how we develop in data warehouses.

Two approaches we’ve tried are:

1. feature/bug-specific DEV environments (shown below)
2. developer-specific DEV environments (DB_JANEDOE_DEV, DB_JOHNSMITH_DEV, DB_SARAHCONNOR_DEV, etc.)

Multiple DEV environments allow developers to:

• make and test their changes in isolation, not worrying about other changes happening in the DEV environment, or if their data will be wiped due to a PROD to DEV restore
• promote to UAT and battle-test all changes in unison
• promote everything to PROD when ready

Hopefully this inspires you to think differently about data warehouse development. Let me know if you’ve come up with any other creative solutions using Snowflake’s capabilities!