Snowflake Deployment Quick Start
When deploying a new Snowflake instance these are some of the steps that I typically perform to stand up the instance (e.g. clean up default objects, create new objects, roles, stages and warehouses) and manage resources (e.g. reduce default warehouse size, set resource monitors and reduce the auto-suspend timing on your warehouses.)
Let’s start with a bit of housekeeping by removing some of the sample objects and data that Snowflake provides by default. There is no harm in skipping this step but something to keep in mind is that Snowflake’s sample warehouses are fairly beefy and may be more expensive to use that you need.
USE ROLE ACCOUNTADMIN;-- Clean up sample objects and data.
DROP DATABASE IF EXISTS demo_db;
DROP DATABASE IF EXISTS util_db;
DROP DATABASE IF EXISTS snowflake_sample_data;
DROP WAREHOUSE "DEMO_WH";
DROP WAREHOUSE "LOAD_WH";
Next, we override some default settings and set the timezone to UTC and override the default behavior for the TIMESTAMP data type. Snowflake supports TIMESTAMP_NTZ, TIMESTAMP_LTZ and TIMESTAMP_TZ.
USE ROLE ACCOUNTADMIN;-- Global account-level configuration.
ALTER ACCOUNT SET CLIENT_ENCRYPTION_KEY_SIZE = 256;
ALTER ACCOUNT SET TIMEZONE = 'UTC';
ALTER ACCOUNT SET TIMESTAMP_TYPE_MAPPING = TIMESTAMP_TZ;-- Create a network policy that will whitelist IP addresses that can access the Snowflake instance.
CREATE OR REPLACE NETWORK POLICY whitelist ALLOWED_IP_LIST=(
'12.34.567.890',
'12.34.567.890',
'12.34.567.890'
);-- Activate network policy.
ALTER ACCOUNT SET NETWORK_POLICY = "whitelist";
Now we can create some databases and schemas.
USE ROLE SYSADMIN;-- Create databases and schemas.
CREATE DATABASE IF NOT EXISTS dataops;
CREATE SCHEMA IF NOT EXISTS dataops.inbound;
CREATE SCHEMA IF NOT EXISTS dataops.outbound;
Snowflake has an incredible feature called Time Travel that I like to configure at the database level. Depending on your Snowflake edition you can Time Travel up to 90 days in the past.
USE ROLE ACCOUNTADMIN;-- Configure time travel data retention period.
ALTER DATABASE dataops SET data_retention_time_in_days = 30;
Time to create some objects that are more unique to Snowflake; let’s create an internal stage, a virtual warehouse (the Snowflake equivalent of a compute cluster) and a resource monitor.
USE ROLE ACCOUNTADMIN;-- Create an internal stage we can upload files to for bulk loading into our tables.
CREATE STAGE IF NOT EXISTS dataops.public.staging;-- Create an XS virtual warehouse that auto-suspends after 120 seconds of inactivity.
CREATE WAREHOUSE dataops WITH WAREHOUSE_SIZE = "XSMALL" WAREHOUSE_TYPE = "STANDARD" AUTO_SUSPEND = 120 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 1 SCALING_POLICY = "STANDARD";-- Create a resource monitor that alerts at 75% of quota usage and immediately suspends at 100%. Adjust accordingly.
CREATE RESOURCE MONITOR dataops WITH CREDIT_QUOTA = 100 TRIGGERS ON 100 PERCENT DO SUSPEND_IMMEDIATE ON 75 PERCENT DO NOTIFY;-- Add the resource monitor to the virtual warehouse.
ALTER WAREHOUSE "dataops" SET RESOURCE_MONITOR = "dataops";
Now let’s create some additional roles and users. We want to set the default context and objects for the user and prompt them to change their password when they log in for the first time.
USE ROLE ACCOUNTADMIN;-- Create new roles (without any privileges).
CREATE ROLE DATAENGINEER COMMENT = "A role for our data engineers.";
CREATE ROLE ANALYST COMMENT = "A role for our data analysts.";-- Create new users and set their default context.
CREATE USER sterling PASSWORD = "Snowflake123" DEFAULT_NAMESPACE = "dataops.public" DEFAULT_ROLE = "DATAENGINEER" DEFAULT_WAREHOUSE = "DATAOPS" MUST_CHANGE_PASSWORD = TRUE;
Almost done. Now we just need to take care of grants and permissions. These can get verbose because we have to spell out privileges for each schema. Snowflake doesn’t support any sort of GRANT ALL ON ALL TABLES IN ALL SCHEMAS language at this time.
USE ROLE ACCOUNTADMIN;-- Users
GRANT ROLE dataengineer TO USER sterling;-- Roles
GRANT ROLE analyst TO ROLE dataengineer;-- Virtual Warehouses
GRANT USAGE ON WAREHOUSE dataops TO ROLE dataengineer;
GRANT USAGE ON WAREHOUSE dataops TO ROLE analyst;-- Data Ops DB
GRANT USAGE ON DATABASE dataops TO ROLE dataengineer;
GRANT ALL ON ALL SCHEMAS IN DATABASE dataops TO ROLE dataengineer;
GRANT ALL ON ALL TABLES IN SCHEMA dataops.inbound TO ROLE dataengineer;
GRANT ALL ON ALL VIEWS IN SCHEMA dataops.inbound TO ROLE dataengineer;
GRANT ALL ON FUTURE TABLES IN SCHEMA dataops.inbound TO ROLE dataengineer;
GRANT ALL ON FUTURE VIEWS IN SCHEMA dataops.inbound TO ROLE dataengineer;
GRANT ALL ON ALL TABLES IN SCHEMA dataops.outbound TO ROLE dataengineer;
GRANT ALL ON ALL VIEWS IN SCHEMA dataops.outbound TO ROLE dataengineer;
GRANT ALL ON FUTURE TABLES IN SCHEMA dataops.outbound TO ROLE dataengineer;
GRANT ALL ON FUTURE VIEWS IN SCHEMA dataops.outbound TO ROLE dataengineer;
GRANT READ, WRITE ON STAGE dataops.public.staging TO ROLE dataengineer;
That’s it! You should now be well along the path to standing up and managing a new Snowflake instance for your organization.
