BankOrg Docs

In progress

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

Create the warehouse

Create the warehouse for dbt, DBT_WH, as SYSADMIN (script 01_create_warehouse.sql).

Snowflake
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).

Snowflake
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.

Snowflake
USE ROLE SYSADMIN;

GRANT USAGE, OPERATE ON WAREHOUSE DBT_WH TO ROLE DBT_ROLE;
Snowflake
-- 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.

Snowflake
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.

Snowflake
GRANT ROLE DBT_ROLE TO USER DBT_USER;

Verify

Snowflake
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.

Output of DESC USER DBT_USER.