This guide will show you how to securely connect SYNQ to your Snowflake instance.
We need this information so we collect metadata about your tables.
To be able to finish this guide, you’ll need the following:
→ Access to modify your Snowflake configuration as account admin
⏱️ Estimated time to finish: 10 minutes.
Data we collect
For the automated data anomaly testing we collect the following:
- Number of rows in every table in a given dataset
- Timestamp of the last change of data in all tables in a given dataset
SYNQ accesses data via dedicated xsmall warehouse which helps you isolate our workload from the rest of your production data. We query data as a dedicated SYNQ user with a dedicated role to clearly define access level policies. Data is collected every 30 minutes.
Synq should be expected to consume $0.0003 (see warehouses overview) x 48 (every 30 minutes) x 10 (10s per check) = 0.14 credits per day.
Before you execute your scripts you will need several key variables:
warehouse_name that is the name of the warehouse you will create for SYNQ (e.g. SYNQ_WH)database_name as an identifier of database (or multiple) you want to monitor
The following setup does several separate steps:
- It creates dedicated
username and role for SYNQ
- It creates
xsmall data warehouse that we can use to query your metadata
- Grant necessary privileges to SYNQ user/role so we can pull query history and monitor the target database
Setup a user and 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.
We recommend that you create user name and a warehouse dedicated to SYNQ.
Password authentication
In the code, remember to replace:
<user_password> with a password<warehouse_name> with the name you want your SYNQ’s warehouse to have
-- Configuration
set user_name='SYNQ';
set user_password='<user_password>';
set warehouse_size='XSMALL';
set warehouse_name='<warehouse_name>';
set role_name='SYNQ';
-- Set role for grants
USE ROLE ACCOUNTADMIN;
-- Create warehouse for SYNQ's monitoring workload
CREATE WAREHOUSE IF NOT EXISTS identifier($warehouse_name)
warehouse_size=$warehouse_size
auto_resume = true
auto_suspend = 5
initially_suspended=true
;
-- Create the role SYNQ will use
CREATE ROLE IF NOT EXISTS identifier($role_name);
-- Create SYNQ's user and grant access to role
CREATE USER IF NOT EXISTS identifier($user_name) PASSWORD=$user_password DEFAULT_ROLE=$role_name;
GRANT ROLE identifier($role_name) TO USER identifier($user_name);
-- Grant permissions to use the new warehouse
GRANT OPERATE, USAGE, MONITOR ON WAREHOUSE identifier($warehouse_name) TO ROLE identifier($role_name);
-- Grant privileges to allow access to query history
GRANT IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" TO ROLE identifier($role_name);
Key-pair authentication
In the code, remember to replace:
<rsa_public_key> with a key generated by SYNQ<warehouse_name> with the name you want your SYNQ’s warehouse to have
-- Configuration
set user_name='SYNQ';
set rsa_public_key='<user_password>';
set warehouse_size='XSMALL';
set warehouse_name='<warehouse_name>';
set role_name='SYNQ';
-- Set role for grants
USE ROLE ACCOUNTADMIN;
-- Create warehouse for SYNQ's monitoring workload
CREATE WAREHOUSE IF NOT EXISTS identifier($warehouse_name)
warehouse_size=$warehouse_size
auto_resume = true
auto_suspend = 5
initially_suspended=true
;
-- Create the role SYNQ will use
CREATE ROLE IF NOT EXISTS identifier($role_name);
-- Create SYNQ's user and grant access to role
CREATE USER IF NOT EXISTS identifier($user_name) RSA_PUBLIC_KEY=$rsa_public_key DEFAULT_ROLE=$role_name;
GRANT ROLE identifier($role_name) TO USER identifier($user_name);
-- Grant permissions to use the new warehouse
GRANT OPERATE, USAGE, MONITOR ON WAREHOUSE identifier($warehouse_name) TO ROLE identifier($role_name);
-- Grant privileges to allow access to query history
GRANT IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" TO ROLE identifier($role_name);
Grant access to monitored database
We recommend setting access on Schema level, as long as it is aligned with your permissions management strategy.
Applying schema level future grants in a Snowflake account where there are only database future grants can break existing roles! From Snowflake’s docs:
“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. An important point to note here is that as long as there is a SCHEMA level future grants, ALL DATABASE levels will be ignored, even for the roles that are NOT defined in the SCHEMA level future grants.”
If you use schema grants follow Option 1, if you use database grants follow Option 2.
Option 1 (Recommended): Grant access to all schemas
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. This script should be run in the same Worksheet as 1. Setup user and warehouse
Replace <database_name> with the name of your database.
set database_name='<database_name>';
-- Grant metadata privileges to database to be monitored
GRANT USAGE,MONITOR ON DATABASE identifier($database_name) TO ROLE identifier($role_name);
GRANT USAGE,MONITOR ON ALL SCHEMAS IN DATABASE identifier($database_name) TO ROLE identifier($role_name);
USE DATABASE identifier($database_name);
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($database_name, $role_name);
Option 2: Grant access to a database
This script should be run in the same Worksheet as 1. Setup user and warehouse
Replace <database_name> with the name of your database.
set database_name='<database_name>';
-- 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);
Once you’ve run successfully created the warehouse, database, and SYNQ role, input the following data in SYNQ
Integration name
For example, snowflake
Account name
You can find it under Admin > Accounts when you highlight the link icon (🔗).
Database
The name of the database you created earlier in the guide
Role, username, and password
The role name, username, and password you created for the SYNQ role earlier in the guide
Warehouse
The name of the warehouse you created earlier in the guide
