Setup Script for Snowflake DW for use with dbt

snowflake_dbt_setup.sql

use role accountadmin;

create or replace role loader;
create or replace role transformer;
create or replace role reporter;
create or replace role looker_role;
grant role loader to role sysadmin;
grant role transformer to role sysadmin;
grant role reporter to role sysadmin;
grant role looker_role to role sysadmin;
create user looker_user password = '<enter password here>';
grant role looker_role to user looker_user;

create user mark_dbt password = '<enter password here>';
grant role transformer to user mark_dbt;

use role sysadmin;


/*
CREATE WAREHOUSE loading 
WITH WAREHOUSE_SIZE = 'XSMALL'
WAREHOUSE_TYPE = 'STANDARD' 
AUTO_SUSPEND = 60 
AUTO_RESUME = TRUE 
MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 1 
SCALING_POLICY = 'STANDARD';
*/

CREATE OR REPLACE WAREHOUSE transforming 
WITH WAREHOUSE_SIZE = 'XSMALL'
WAREHOUSE_TYPE = 'STANDARD' 
AUTO_SUSPEND = 60 
AUTO_RESUME = TRUE 
MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 1 
SCALING_POLICY = 'STANDARD';

CREATE OR REPLACE WAREHOUSE reporting 
WITH WAREHOUSE_SIZE = 'XSMALL'
WAREHOUSE_TYPE = 'STANDARD' 
AUTO_SUSPEND = 60 
AUTO_RESUME = TRUE 
MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 1 
SCALING_POLICY = 'STANDARD';

CREATE OR REPLACE WAREHOUSE looking 
WITH WAREHOUSE_SIZE = 'XSMALL'
WAREHOUSE_TYPE = 'STANDARD' 
AUTO_SUSPEND = 60 
AUTO_RESUME = TRUE 
MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 1 
SCALING_POLICY = 'STANDARD';

-- grant usage on warehouse loading to role loader;
grant usage on warehouse transforming to role transformer;
grant usage on warehouse reporting to role reporter;
alter user looker_user set default_role = looker_role default_warehouse = 'looking';
alter user mark_dbt set default_role = transformer default_warehouse = 'looking';


create or replace database raw;
grant ownership on database raw to role loader;
grant ownership on schema public to role loader;
grant usage on database raw to role transformer;
grant usage on schema raw.public to role transformer;

create or replace database analytics;
grant ownership on database analytics to role transformer;
grant ownership on schema public to role transformer;
grant usage on database analytics to role reporter;
grant usage on database analytics to role looking;