Snowflake

Instructions for connecting a Snowflake data warehouse to AirOps

🌐

Important

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 IPS address over your database port.

To setup a new database connection, go to ⚙️ Settings —> Warehouse Setup —> Add Source +

Add the following details:

  • 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 you account identifier is en54071.us-east-2.aws.
    • You can also your Snowflake account ID by typing CURRENT_ACCOUNT() into a Snowflake worksheet.
  • 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!
  • Password - password for the above user.
  • Warehouse - the Snowflake warehouse that AirOps will connect to.
  • Database - database within the above Snowflake Warehouse.

Setup user and role

In order to add a Snowflake database as a Data Source on AirOps, we recommend using an existing user, or creating a user with read access to the tables, views, and schemas you would like to access from AirOps.

If you're creating a new user, you can follow the below instructions:

1. Create AirOps role

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

2. Create AirOps user and assign roles

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;