Snowflake

Connect to a Snowflake data warehouse in AirOps

Remember to allowlist our IP Address 52.71.87.39

If your database is protected by a firewall, remember to allow inbound access to AirOPs IP address over your database port.

1. Account Identifier - the ID of your Snowflake Account.

   • If your Snowflake URL is https://**[en54071.us-east-2.aws](http://en54071.us-east-2.aws)**.snowflakecomputing.com/, then your account identifier is en54071.us-east-2.aws.

   • You can also find your Snowflake account ID by typing CURRENT_ACCOUNT() into a Snowflake worksheet and running it.

2. User - the Snowflake user that AirOps will connect to the database with. It is best practice to create a new user for AirOps (instructions below), but any user with SELECT privileges can be used. Snowflake usernames are case sensitive.

3. Password - password for the above user.

4. Warehouse - the Snowflake warehouse that AirOps will connect to.

5. Database - database within the above Snowflake Warehouse.

Setup User and Role (for New Users)

To add a Snowflake database as a Data Source on AirOps, use an existing user or create a user with read access to the tables, views, and schemas you would like to access from AirOps:

1. Create AirOps Role

CREATE ROLE IF NOT EXISTS AIROPS_ROLE COMMENT = "Airops default role";

2. Create AirOps User and Assign Role

CREATE USER AIROPS_USER
	password = '<secure-password>'
	first_name = 'AirOps'
	last_name = 'User'
	default_warehouse = '<warehouse>' 
	default_namespace = '<database>.<schema>'
	default_role = AIROPS_ROLE;

GRANT ROLE AIROPS_ROLE TO USER AIROPS_USER;
GRANT ROLE AIROPS_ROLE TO ROLE SYSADMIN;

3. Grant USAGE privileges to AirOps role

GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE AIROPS_ROLE;

GRANT USAGE ON DATABASE "<database>" TO ROLE AIROPS_ROLE;

GRANT USAGE ON ALL SCHEMAS IN DATABASE "<database>" TO ROLE AIROPS_ROLE;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE "<database>" TO ROLE AIROPS_ROLE;

GRANT SELECT ON ALL TABLES IN DATABASE "<database>" TO ROLE AIROPS_ROLE;
GRANT SELECT ON FUTURE TABLES IN DATABASE "<database>" TO ROLE AIROPS_ROLE;

GRANT SELECT ON ALL VIEWS IN DATABASE "<database>" TO ROLE AIROPS_ROLE;
GRANT SELECT ON FUTURE VIEWS IN DATABASE "<database>" TO ROLE AIROPS_ROLE;