This guide will show you how to securely connect Coalesce Quality 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
Coalesce Quality accesses data via a dedicated xsmall warehouse, which helps you isolate our workload from the rest of your production data. We query data as a dedicated user with a dedicated role to clearly define access level policies. Data is collected every 30 minutes.
This 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 — the name of the warehouse you will create (e.g. SYNQ_WH)database_name as an identifier of database (or multiple) you want to monitor
- It creates a dedicated
username and role for Coalesce Quality
- It creates
xsmall data warehouse that we can use to query your metadata
- Grant necessary privileges to the dedicated user and 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 creating a dedicated user name and warehouse.
Password authentication
In the code, remember to replace:
<user_password> with a password<warehouse_name> with the name you want the dedicated 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 the 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 dedicated role
CREATE ROLE IF NOT EXISTS identifier($role_name);
-- Create the dedicated 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
Coalesce Quality supports two key-pair authentication methods:
- Managed Key-pair: The key pair is generated and managed for you
- User Managed Key-pair: You generate and provide your own private key (supports both encrypted and unencrypted keys)
Option A: Managed Key-pair
When configuring the integration, select “Managed Key-pair” and a public key will be generated for you. Copy this public key and use it in the setup below.
In the code, remember to replace:
<rsa_public_key> with the key generated in the previous step<warehouse_name> with the name you want the dedicated 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 the 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 dedicated role
CREATE ROLE IF NOT EXISTS identifier($role_name);
-- Create the dedicated 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);
Option B: User Managed Key-pair
Both encrypted and unencrypted private keys in PKCS#8 PEM format are supported. If you use an encrypted key, you’ll need to provide the passphrase when configuring the integration.
Generate a new key pair
Snowflake requires a minimum 2048-bit RSA private key in PKCS#8 PEM format.
Generate an unencrypted private key:
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8
Extract the public key
After generating your private key, extract the public key:
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
Configure Snowflake with your public key
Remove the header and footer from the public key file (-----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----), then use the remaining content as <rsa_public_key> in the setup script:
-- Configuration
set user_name='SYNQ';
set rsa_public_key='<your_public_key_content>';
set warehouse_size='XSMALL';
set warehouse_name='<warehouse_name>';
set role_name='SYNQ';
-- Set role for grants
USE ROLE ACCOUNTADMIN;
-- Create warehouse for the 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 dedicated role
CREATE ROLE IF NOT EXISTS identifier($role_name);
-- Create the dedicated 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);
Configure the integration
In the integration settings:
- Select “User Managed Key-pair” as the authentication method
- Paste your private key (including the
-----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY----- headers)
- If your key is encrypted (starts with
-----BEGIN ENCRYPTED PRIVATE KEY-----), provide the passphrase
Keys in PKCS#1 format (-----BEGIN RSA PRIVATE KEY-----) are not supported by Snowflake. If you have a PKCS#1 key, convert it to PKCS#8 format using:openssl pkcs8 -topk8 -inform PEM -in rsa_key.pem -out rsa_key.p8 -nocrypt
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 all dynamic tables' + schemaAndRoleSuffix});
snowflake.execute({ sqlText: 'grant SELECT on future tables' + schemaAndRoleSuffix});
snowflake.execute({ sqlText: 'grant SELECT on future views' + schemaAndRoleSuffix});
snowflake.execute({ sqlText: 'grant SELECT on future dynamic tables' + 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);
grant SELECT on all dynamic tables in database identifier($database_name) to role identifier($role_name);
grant SELECT on future dynamic tables in database identifier($database_name) to role identifier($role_name);
Grant access to Snowflake tasks (Optional)
If you want to monitor Snowflake tasks, you need to grant MONITOR privileges on the specific tasks you want to observe. This allows us to collect task execution history and metadata.
Replace <database_name> and <task_name> with your actual database and task names:
-- Grant MONITOR privilege on specific tasks
GRANT MONITOR ON TASK identifier($database_name).PUBLIC.<task_name> TO ROLE identifier($role_name);
-- To grant MONITOR on all tasks in a schema (if needed)
GRANT MONITOR ON ALL TASKS IN SCHEMA identifier($database_name).PUBLIC TO ROLE identifier($role_name);
-- To grant MONITOR on future tasks in a schema (if needed)
GRANT MONITOR ON FUTURE TASKS IN SCHEMA identifier($database_name).PUBLIC TO ROLE identifier($role_name);
The MONITOR privilege on tasks allows viewing task execution history, status, and metadata but does not allow executing or modifying the tasks. This is required for task monitoring and anomaly detection.After granting these privileges, you must also enable task monitoring in the integration settings. In the integration configuration screen, make sure to enable “Fetch tasks and their executions” to start collecting task data.
APPLY TAG privilege. Without this privilege, Snowflake’s GET_DDL function replaces tag names and values with #UNKNOWN_TAG='#UNKNOWN_VALUE' placeholders.
-- Grant ability to read tags from GET_DDL output
GRANT APPLY TAG ON ACCOUNT TO ROLE identifier($role_name);
This grant is only needed if you use Snowflake tags on your tables/views and want SYNQ to use them for filtering or monitor rules. SYNQ will show a warning during ingestion if it detects unknown tag placeholders in your metadata, so you can decide at that point whether to apply this grant.The APPLY TAG privilege allows reading tag assignments but does not grant the ability to modify data or other security policies. See the Snowflake GET_DDL documentation for more details. Network Configuration
If your Snowflake instance is behind a firewall or has network access controls, you may need to whitelist our IP addresses. See IP Whitelist for the complete list of IP addresses by region. 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 earlier in the guide
Warehouse
The name of the warehouse you created earlier in the guide
