Author: Ishani Aryan
Agile methodology is followed by industries for developing pipelines and solutions in data warehousing.In agile there are several teams involved and since each team might operate in a different environment, we need to set up multiple environments to provide smooth operations .
Ideally we there should be three environments i.e. PROD, DEV and QA but we could make it concise if we choose to have two environments(DEV & PROD) instead and have QA as a custom role for testers to perform Quality assurance in each environment.
4 simple steps to set up your environment
1. Clone Production database
Design PROD database and then use snowflake zero copy cloning feature to create DEV
Have a non-PROD environment as transient to reduce time travel cost and eliminate fail safe.
2. Environment Specific Warehouse
Create environment specific warehouses for each type of workload and grant warehouse to each corresponding role
Size each warehouse differently based on the type of workload
3. Environment Specific Role
Design custom roles for specific purposes and grant only privileges which are required to perform that task.
Follow RBAC privileges should be assigned to roles which in turn should be assigned to users.
All custom roles should be assigned to SYSADMIN as per best practice and custom roles that have access to account level objects should be assigned directly to ACCOUNTADMIN.
To achieve an isolated environment ,the PROD role should not have access to DEV and vice versa.
4. Resource Monitor
For each environment setup separate resource monitor for each warehouse to monitor cost
And one resource monitor at account level to control credit consumption for all warehouse in account including cloud services
It’s really easy to set up multiple environments in Snowflake as it provides a convenient way to monitor or separate costs.