While setting up Initial Snowflake Environment, single vs multiple Accounts- Architecture Best practices

You decided Snowflake to be your new cloud data warehouse. You are tasked with designing the system. Now you have a crucial decision to make. Whether to choose multiple accounts/URLs for each environment (DEV, IT, QV, PROD) or using one account and building out separate DEV, TEST, and PROD databases? For example:

• DEV = http://mydevaccount.east-us-2.azure.snowflakecomputing.com (SourceSystem.Schema.Tables)
• TEST = http://mytestaccount.east-us-2.azure.snowflakecomputing.com (SourceSystem.Schema.Tables)
• PROD = http://myprodaccount.east-us-2.azure.snowflakecomputing.com (SourceSystem.Schema.Tables)

Versus

• Single Environment = http://mysnowflakeaccount.east-us-2.azure.snowflakecomputing.com

with different database names:

• Dev_SourceSystem.Schema.Tables
• Test_SourceSystem.Schema.Tables
• Prod_SourceSystem.Schema.Tables

Why have different URLs (Accounts) for each environment ?

Pro: Easy DevOps - Code deployments are easier. No need to change the database names for deploying to each environment as the database name can remain the same and the environments are isolated

Pro: Accidental Deletion - Since the environments are isolated from each other, users will have to login to separate URLs, and they may not confuse one with another

Con:Complexity - Difficult to setup, white list all the IP, setting up deployment workflows, managing security, managing billing. Everything gets complex and redundant. You have to repeat the same for all environments

Advantages of having different Databases and one Account (Recommended):

Pro: Simplicity - it's easier to have everything in one account.  If you use a nice structure in your RBAC model, there isn't a difference as far as isolating these environments. Leveraging RBAC correctly to completely isolate environments within a single account would also take care of avoiding any accidents

Pro: Easy DevOps - If you then make sure that your scripts for DevOps, ETL, etc. are all referencing schema only (no database references), then migrating DDL, DML, etc. is as easy as it would be in separate accounts

Pro: To make use of Snowflake's features - One of the best features of Snowflake is Zero-Copy Clones for your testing lifecycle. This is only available within a single account. If you use separate accounts, you will need to copy your data from one environment to the next (duplicating or tripling your storage costs and a large time-consumer and credit-consumer). Zero-copy clones allow for a near instant snapshot of your data to a different environment

nVector

posted on 09 Dec 19