Connect Snowflake

Connect Roadway to Snowflake

Data Warehouse Requirements

1. Log in to the Snowflake web interface.

2. Execute the following SQL. These commands create a roadway user, role, warehouse and schema and grants read and write permissions on this schema.

Copy/*
Create Database Objects:

Note that we use explicit CREATE statements with no additional clauses, which 
prevents us from overwriting any existing objects of the same name. In the event you already have objects with these names, change the names in this script accordingly, but notify us of these changes.
 */
CREATE USER roadway_user
		PASSWORD = <some password>
		DEFAULT_ROLE = 'roadway_role'
		DEFAULT_WAREHOUSE = 'roadway_warehouse'
		DEFAULT_NAMESPACE = 'your_db.roadway'
		EMAIL = '[email protected]'
;
CREATE SCHEMA <your_db>.roadway;
CREATE WAREHOUSE roadway_warehouse
		AUTO_SUSPEND = 1
		WAREHOUSE_SIZE = XSMALL -- default to most conservative size, but change according to data volume and usage
;
CREATE ROLE roadway_role;

-- grant usages to roadway_role
GRANT USAGE ON DATABASE <your_db> TO ROLE roadway_role;
GRANT USAGE ON SCHEMA <your_db>.roadway TO ROLE roadway_role;
GRANT USAGE ON WAREHOUSE roadway_warehouse TO ROLE roadway_role;

-- grant limited read/write privileges to roadway_role within roadway schema
GRANT SELECT ON ALL VIEWS IN SCHEMA <your_db>.roadway TO ROLE roadway_role;
GRANT SELECT ON ALL TABLES IN SCHEMA  <your_db>.roadway TO ROLE roadway_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <your_db>.roadway TO ROLE roadway_role;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <your_db>.roadway TO ROLE roadway_role;
GRANT CREATE TABLE, CREATE VIEW ON SCHEMA <your_db>.roadway TO ROLE roadway_role;

-- grant roadway_role to roadway_user
GRANT ROLE roadway_role TO USER roadway_user;

3. Extend the above commands with readonly permissions on other databases and schemas according to your warehouse structure, e.g. GRANT SELECT ON ALL TABLES IN SCHEMA revenue_db.stripe TO ROLE roadway_role.

4. Ensure the role has permissions to view and create as needed.

Copysql
Copy code
GRANT SELECT, INSERT ON ALL TABLES IN schema your_schema TO [email protected];