Snowflake DBA-101; Deploying Standardized, Fully Functional Databases

This article provides both a guide and script for creating and deploying standardized, fully functional, Snowflake DATABASEs. A complete script is described. The script is available at https://github.com/Jeffreymjacobs/DBA-101-Scripts

The following standard architecture is used for each database. This is the basic architecture used for creating ELVT architectures for my clients.

The resulting Snowflake objects have the following features:

1. Consistent naming standards. All objects have an appropriate suffix, e.g. _DB, _ROLE, etc.
2. The Snowflake objects are “encapsulated”, i.e. no shared resource or roles, with exception of the administration role being granted to SYSADMIN.
3. Basic Role Based Access (RBAC)

The following Snowflake objects and privileges are created with example names:

1. Three ROLEs:
2. SALES_DEV_ADMIN_ROLE — the owner of the DATABASE with full privileges
3. SALES_DEV_DML_ROLE — has DML privileges on the tables, but no DDL privileges
4. SALES_DEV_ANALYST_ROLE — has SELECT and UDF USAGE privileges on VIEWs and UDFs only, including all FUTURE VIEWs and UDFs
5. A DATABASE, SALES_DEV_DB.
6. Three SCHEMAs are created with the following names. Note that they are not qualified with the DATABASE name:
7. PHYSICAL_SCHEMA — contains physical tables
8. VIEW_SCHEMA — contains “presentation” VIEWs for each physical table, e.g. mapping physical COLUMN names to desired BI presentation names
9. BI_VIEW_SCHEMA — contains more complex VIEWs and UDFs, e.g. JOINs between presentations VIEWs, aggregations, etc.
10. Two WAREHOUSEs. Additional warehouses may be need for loading and TASK execution; SERVERLESS TASKs are recommended, but are still in preview at this time.
11. SALES_DEV_WH_SMALL — a small warehouse
12. SALES_DEV_WH_XSM — an extra small warehouse
13. All appropriate USAGE privileges

Note that the scriptis totally idempotent, with the exception of the CREATE DATABASE statement. This can be very useful when cloning DATABASE and SCHEMA or when re-defining roles and privileges on legacy databases.

Let’s walk through the script. We start with a header section that defines session variables for all of the objects to be created. The only two variables that are needed are APP_AREA_NAME and DB_TYPE.

SET APP_AREA_NAME ='SALES'; SET APP_AREA_NAME =''; -- set your business area name, e.g; MARKETING SET DB_TYPE = ''; -- set your database type name, e.g. DEV, QA, etc
SET DB_NAME= CONCAT($APP_AREA_NAME,'_',$DB_TYPE,'_DB');
SET VIEW_SCHEMA_NAME = CONCAT($DB_NAME,'.','SFORCE_VIEW_SCHEMA'); SET PHYSICAL_SCHEMA_NAME = CONCAT($DB_NAME,'.','SFORCE_PHYSICAL_SCHEMA');
SET BI_VIEW_SCHEMA_NAME = CONCAT($DB_NAME,'.','BI_VIEW_SCHEMA');
SET ADMIN_ROLE = CONCAT($APP_AREA_NAME,'_',$DB_TYPE,'_ADMIN_ROLE'); SET ANALYST_ROLE = CONCAT($APP_AREA_NAME,'_',$DB_TYPE,'_ANALYST_ROLE');
SET DML_ROLE = CONCAT($APP_AREA_NAME,'_',$DB_TYPE,'_DML_ROLE');
SET DW_XSM = CONCAT($APP_AREA_NAME,'_',$DB_TYPE,'_XSM_WH');
SET DW_SMALL = CONCAT($APP_AREA_NAME,'_',$DB_TYPE,'_SM_WH'); 
SET CURRENT_USER = CURRENT_USER();

Now that we have all session variables set, the next step is to create all of the roles using SECURITYADMIN and ACCOUNTADMIN role:

USE ROLE SECURITYADMIN;
-- Grant ADMIN_ROLE, ANALYST_ROLE and DML_ROLE
-- to CURRENT_USER
CREATE ROLE IF NOT EXISTS IDENTIFIER($ADMIN_ROLE);
GRANT ROLE IDENTIFIER($ADMIN_ROLE) TO USER IDENTIFIER($CURRENT_USER);
CREATE ROLE IF NOT EXISTS IDENTIFIER($ANALYST_ROLE);
GRANT ROLE IDENTIFIER($ANALYST_ROLE) TO USER IDENTIFIER($CURRENT_USER);
CREATE ROLE IF NOT EXISTS IDENTIFIER($DML_ROLE);
-- GRANT EXECUTE TASK ON ACCOUNT
USE ROLE ACCOUNTADMIN;
GRANT EXECUTE TASK, EXECUTE MANAGED TASK ON ACCOUNT TO ROLE IDENTIFIER($ADMIN_ROLE);

Now create the database using SYSADMIN and change the owner to SALES_DEV_ADMIN_ROLE. This avoids overloading SYSADMIN with too many privileges, which can lead to unintended changes.

-- CREATE DATABASE and GRANT OWNERSHIP to ADMIN_ROLE
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS IDENTIFIER($DB_NAME);
GRANT OWNERSHIP ON DATABASE IDENTIFIER($DB_NAME) TO ROLE IDENTIFIER($ADMIN_ROLE);

Next, we create the WAREHOUSEs and GRANT USAGE to the various roles.

--CREATE WAREHOUSEs and GRANT USAGE
USE ROLE SYSADMIN;
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($DW_XSM) WITH WAREHOUSE_SIZE = 'XSMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 600 AUTO_RESUME = TRUE;
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($DW_SMALL) WITH WAREHOUSE_SIZE = 'SMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;
GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_XSM) TO ROLE IDENTIFIER($ADMIN_ROLE);
GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_XSM) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_XSM) TO ROLE IDENTIFIER($DML_ROLE);
GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_SMALL) TO ROLE IDENTIFIER($ADMIN_ROLE);
GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_SMALL) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT USAGE ON WAREHOUSE IDENTIFIER($DW_SMALL) TO ROLE IDENTIFIER($DML_ROLE);

Next, we create the SCHEMA. Note that these do not have fully qualified names. This avoids the issues of having to fully qualify code; the SCHEMA is dependent on the current database of the session. Queries, VIEWs etc. are portable accross DATABASEs.

-- CREATE SCHEMAs
USE ROLE IDENTIFIER($ADMIN_ROLE);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($PHYSICAL_SCHEMA_NAME);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($VIEW_SCHEMA_NAME);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($BI_VIEW_SCHEMA_NAME);

Next, we grant all the necessary privileges on the DATABASE, SCHEMAs and any objects that might already exist.

-- GRANT USAGE ON DATABASE
GRANT USAGE ON DATABASE IDENTIFIER($DB_NAME) TO ROLE IDENTIFIER($DML_ROLE);
GRANT USAGE ON DATABASE IDENTIFIER($DB_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT USAGE ON DATABASE IDENTIFIER($DB_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
-- GRANT USAGE ON View schemas to $ANALYST_ROLE
GRANT USAGE ON SCHEMA IDENTIFIER($VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT USAGE ON SCHEMA IDENTIFIER($BI_VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
-- Set up the DML user to access any existing tables, not required on empty database but good practice 
GRANT USAGE ON SCHEMA IDENTIFIER($PHYSICAL_SCHEMA_NAME) TO ROLE IDENTIFIER($DML_ROLE);
GRANT SELECT,INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN  SCHEMA  IDENTIFIER($PHYSICAL_SCHEMA_NAME)TO  ROLE IDENTIFIER($DML_ROLE);
-- Set up Analyst roles for SELECT, UDF Usage and SELECT from STREAMs, not required on empty database, but good practice
GRANT SELECT ON ALL VIEWS IN  SCHEMA IDENTIFIER($VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT SELECT ON ALL VIEWS IN  SCHEMA IDENTIFIER($BI_VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT USAGE ON ALL FUNCTIONS IN  SCHEMA IDENTIFIER($VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT USAGE ON ALL FUNCTIONS IN  SCHEMA IDENTIFIER($BI_VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT SELECT ON ALL STREAMS IN SCHEMA IDENTIFIER($PHYSICAL_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);

Next, GRANT FUTURE privileges on appropriate objects.

USE ROLE SECURITYADMIN;
GRANT SELECT ON FUTURE VIEWS IN  SCHEMA IDENTIFIER($VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT SELECT ON FUTURE VIEWS IN  SCHEMA IDENTIFIER($BI_VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA IDENTIFIER($VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA IDENTIFIER($BI_VIEW_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT SELECT ON FUTURE STREAMS IN SCHEMA IDENTIFIER($PHYSICAL_SCHEMA_NAME) TO ROLE IDENTIFIER($ANALYST_ROLE);
GRANT SELECT ON FUTURE STREAMS IN SCHEMA IDENTIFIER($PHYSICAL_SCHEMA_NAME) TO ROLE IDENTIFIER($ADMIN_ROLE);
GRANT SELECT ON FUTURE STREAMS IN SCHEMA IDENTIFIER($PHYSICAL_SCHEMA_NAME) TO ROLE IDENTIFIER($DML_ROLE);

Finally, we grant the admin role to SYSADMIN and leave the user who ran the script in the ADMIN_ROLE.

Copyright © 2022, Jeffrey M. Jacobs