Snowflake

Snowflake is a multi-cloud data warehouse optimized for analytics workloads and requiring little maintenance. Metaplane monitors the data within your Snowflake instance so you can be the first to know if potential data bugs.

Provisioning Access

Create role, user, warehouse

As a super user, execute the following SQL commands to create a read-only role, a user assigned to that role, and a warehouse for that role.

Make sure to generate a secure password and store it securely — you'll save it into Metaplane later.

-- Create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
set role_name = 'METAPLANE_ROLE';
set user_name = 'METAPLANE_USER';
set user_password = 'changethispassword'; -- Change this password
set warehouse_name = 'METAPLANE_WH';
set database_name = 'DATABASE';  -- Change this database name to your database

-- Change role for user / role steps
use role accountadmin;

-- Create role for metaplane
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;

-- Create a user for metaplane
create user if not exists identifier($user_name)
password = $user_password
default_role = $role_name
default_warehouse = $warehouse_name;

grant role identifier($role_name) to user identifier($user_name);

-- Create a warehouse for metaplane
create warehouse if not exists identifier($warehouse_name)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 5
auto_resume = true
initially_suspended = true
max_concurrency_level = 30
statement_timeout_in_seconds = 300
statement_queued_timeout_in_seconds = 1200;

-- Grant metaplane role access to warehouse
grant USAGE on warehouse identifier($warehouse_name) to role identifier($role_name);

-- Grant access to query history
grant imported privileges on database "SNOWFLAKE" to role identifier($role_name);

Provide access to tables/views

Option 1. Provide read-only privileges to relevant schemas (preferred)

The following stored procedure iterates through each schema in a given database, then grants access to each table in a given schema. This stored procedure should be run as follows each time a new schema is created. Be sure to replace DB_NAME and METAPLANE_ROLE with the relevant database and role names.

create or replace procedure grantFutureAccess(databaseName string, roleName string)
  returns string not null
  language javascript
  as
  $$
  var schemaResultSet = snowflake.execute({ sqlText: 'SELECT SCHEMA_NAME FROM ' + '"' + DATABASENAME + '"' + ".INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME != 'INFORMATION_SCHEMA';"});
    
  var numberOfSchemasGranted = 0;
  while (schemaResultSet.next()) {
    numberOfSchemasGranted += 1;
    var schemaAndRoleSuffix = ' in schema "' + DATABASENAME + '"."' + 
    schemaResultSet.getColumnValue('SCHEMA_NAME') + '" to role ' + ROLENAME + ';'

    snowflake.execute({ sqlText: 'grant USAGE on schema "' + DATABASENAME + '"."' +  
    schemaResultSet.getColumnValue('SCHEMA_NAME') + '" to role ' + ROLENAME + ';'});
    snowflake.execute({ sqlText: 'grant SELECT on all tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on all views' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on future tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on future views' + schemaAndRoleSuffix});
  }
  
  return 'Granted access to ' + numberOfSchemasGranted + ' schemas';
  $$
;

grant USAGE on database identifier($database_name) to role identifier($role_name);
call grantFutureAccess('DB_NAME', 'METAPLANE_ROLE');

Alternatively, execute the following query for each relevant schema:

-- Read-only access to specific schemas (CHANGE THIS)
set schema_name = 'DATABASE_NAME.SCHEMA_NAME';
grant USAGE on database identifier($database_name) to role identifier($role_name);
grant USAGE on schema identifier($schema_name) to role identifier($role_name);
grant SELECT on all tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on future tables in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on all views in schema identifier($schema_name) to role identifier($role_name);
grant SELECT on future views in schema identifier($schema_name) to role identifier($role_name);

Alternative option: Provide read-only privileges to entire database

Schema-level grants are preferred because if other roles have future grants on schemas, tables in those schemas may not appear in Metaplane. This is due to schema-level grants taking precedence in Snowflake:

When future grants are defined at both the database and schema level, the schema level grants take precedence over the database level grants, and the database level grants are ignored.

If no other roles have future grants on schemas, permissions can be granted to Metaplane on the database-level through:

-- Read-only access to database
grant USAGE on database identifier($database_name) to role identifier($role_name);
grant USAGE on all schemas in database identifier($database_name) to role identifier($role_name);
grant USAGE on future schemas in database identifier($database_name) to role identifier($role_name);
grant SELECT on all tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on future tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on all views in database identifier($database_name) to role identifier($role_name);
grant SELECT on future views in database identifier($database_name) to role identifier($role_name);

Adding Snowflake source

To add a source, navigate to the Connections page and click the Snowflake button. A modal will prompt you for the credentials you just created, then allow you to save the connection for testing:


Whitelist Metaplane IPs

If your organization uses a whitelist to manage Snowflake access, Metaplane will only access your Snowflake through the following IPs:

  • 52.22.202.169
  • 34.199.237.80
  • 54.227.153.130
  • 34.230.219.122
  • 44.197.96.121
  • 34.206.79.174
  • 107.22.42.246

As list: 52.22.202.169, 34.199.237.80, 54.227.153.130, 34.230.219.122,44.197.96.121, 34.206.79.174, 107.22.42.246

Using UI

  1. Click Account > Policies
  1. Click + Create... then enter Name, Allowed IP Addresses, and Comment

Using SQL

CREATE NETWORK POLICY METAPLANE_CONNECTOR
    ALLOWED_IP_LIST=('52.22.202.169','34.199.237.80','54.227.153.130','34.230.219.122', '44.197.96.121', '34.206.79.174', '107.22.42.246')
    BLOCKED_IP_LIST=()
COMMENT='Static IPs used by the Metaplane Connector';

What’s Next
Did this page help you?