BankOrg Data Platform / Environment Setup
Create Snowflake Service User
Create the DBT_ROLE, DBT_WH warehouse and DBT_USER service user that dbt connects with.
dbt connects to Snowflake as a dedicated service user, DBT_USER, authenticated with a key pair and no password.
Prerequisites
- The public key from Generate RSA Key Pair
Create the warehouse
Create the warehouse for dbt, DBT_WH, as SYSADMIN (script 01_create_warehouse.sql).
USE ROLE SYSADMIN;
CREATE WAREHOUSE IF NOT EXISTS DBT_WH
WITH WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE;Create the role
Create DBT_ROLE as SECURITYADMIN and grant it to SYSADMIN, so the role hierarchy stays under it (script 02_create_role_and_user.sql).
USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS DBT_ROLE;
GRANT ROLE DBT_ROLE TO ROLE SYSADMIN;Grant privileges to the role
DBT_ROLE can use and operate the warehouse. Read access to the RAW tables and to the load log is granted at the end of 11_create_raw_db.sql, when PRD_RAW is created.
USE ROLE SYSADMIN;
GRANT USAGE, OPERATE ON WAREHOUSE DBT_WH TO ROLE DBT_ROLE;-- Read access to RAW
GRANT USAGE ON DATABASE PRD_RAW TO ROLE DBT_ROLE;
GRANT USAGE ON SCHEMA PRD_RAW.CORE_BANKING TO ROLE DBT_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA PRD_RAW.CORE_BANKING TO ROLE DBT_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA PRD_RAW.CORE_BANKING TO ROLE DBT_ROLE;
-- Read access to the load log (OPS)
GRANT USAGE ON SCHEMA PRD_RAW.OPS TO ROLE DBT_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA PRD_RAW.OPS TO ROLE DBT_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA PRD_RAW.OPS TO ROLE DBT_ROLE;Create the user
Create the user as SECURITYADMIN. Replace the placeholder with the body of bankorg_dbt.pub, without the BEGIN and END lines.
USE ROLE SECURITYADMIN;
CREATE USER IF NOT EXISTS DBT_USER
TYPE = SERVICE
DEFAULT_ROLE = DBT_ROLE
DEFAULT_WAREHOUSE = DBT_WH
RSA_PUBLIC_KEY = '<paste public key here>';TYPE = SERVICE marks a non-interactive user that cannot sign in with a password.
Assign the role
Grant the role to the user so dbt can use it.
GRANT ROLE DBT_ROLE TO USER DBT_USER;Verify
DESC USER DBT_USER;RSA_PUBLIC_KEY_FP is populated and HAS_KEYPAIR is true, which confirms the public key is registered. The default warehouse and role are DBT_WH and DBT_ROLE.