Snowflake - Coalesce Quality Documentation

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

We read metadata from your Snowflake account using the dedicated role described below. Depending on which features you enable in the integration, we ingest:

Catalog — databases, schemas, tables, views, materialized views, dynamic tables, semantic views, streams, comments, column types, tags.
Stages (opt-in grant) — SHOW STAGES for each named stage the role can see: its type, URL, cloud, region and storage integration. With query logs enabled, the COPY INTO statements that load a table from a stage or unload one to it become lineage between the stage and the table.
Apps, notebooks, warehouses and pipes (opt-in) — SHOW STREAMLITS, SHOW NOTEBOOKS, SHOW PIPES, SHOW CORTEX SEARCH SERVICES and SHOW WAREHOUSES for each object the role can see: a Streamlit app’s title and warehouse, a notebook’s warehouses, a pipe’s COPY statement and the stage it loads from, a Cortex Search service’s query and warehouse, and a warehouse’s size, type and auto-suspend.
SQL definitions — GET_DDL output for tables, views, materialized views, dynamic tables, semantic views, streams, stored procedures, and user-defined functions. Used for lineage and to detect references between objects.
Constraints — primary keys (via SHOW PRIMARY KEYS) and clustering keys. Foreign keys, uniques, and checks are read from GET_DDL when present.
Table metrics (opt-in) — row counts and last-modified timestamps for anomaly detection.
Query logs (opt-in) — SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY for performance, cost, and lineage signals.
Tasks (opt-in) — SHOW TASKS plus TASK_HISTORY for execution outcomes.
Stored procedures and UDFs — SHOW PROCEDURES / SHOW USER FUNCTIONS plus GET_DDL for SQL, JavaScript, Python, Java, and Scala languages (automatic when the role has USAGE on them).
Table-level lineage (opt-in) — via SNOWFLAKE.CORE.GET_LINEAGE on Enterprise+ editions.

Metadata vs. data access. The baseline grants below give Coalesce Quality access to your schema and DDL, never to row data. The SELECT privileges on tables/views/etc. are required so the role can call SHOW and GET_DDL (Snowflake gates object visibility behind any object privilege), but Coalesce Quality only issues data-reading queries when you enable the corresponding opt-in feature in the integration UI — Fetch table metrics, Fetch query logs, Fetch table lineage, or SQL test execution against the audit table. With every opt-in disabled, the integration runs metadata commands only.

We use a dedicated XSMALL warehouse to isolate our workload from your production compute, and a dedicated user and role with the minimum required privileges. Metadata is collected on a schedule (typically 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

The following setup does several separate steps:

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.

Use key-pair authentication. Snowflake is retiring single-factor password sign-in: every human user must enrol in MFA, and service users lose password authentication altogether. Coalesce Quality connects unattended and cannot supply a second factor, so a password connection fails with error 394508 once your account reaches that stage. The scripts below create the user as TYPE = SERVICE, which is exempt from MFA and accepts key-pair authentication only.If you already connect with a password, follow Password authentication is deprecated by Snowflake to move the existing user to key-pair without recreating it.

Key-pair authentication

For more details on Snowflake’s key-pair authentication, see the official Snowflake documentation.

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 save it. Coalesce Quality then generates the key pair and shows the public half under Key pair authentication at the bottom of the integration’s page. Copy it 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='<rsa_public_key>';
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 = 60
initially_suspended=true
;

-- Create the dedicated role
CREATE ROLE IF NOT EXISTS identifier($role_name);

-- Create the dedicated service user and grant access to role.
-- TYPE = SERVICE exempts it from Snowflake's MFA enforcement.
CREATE USER IF NOT EXISTS identifier($user_name) TYPE = SERVICE 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);

CREATE USER IF NOT EXISTS leaves an existing user untouched, public key included. If the user already exists, attach the key with ALTER USER instead — see moving an existing user to key-pair.

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

Generate an encrypted private key (recommended for better security):

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8

When prompted, enter a passphrase to encrypt the private key. You’ll need to provide this passphrase when configuring the integration.

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 = 60
initially_suspended=true
;

-- Create the dedicated role
CREATE ROLE IF NOT EXISTS identifier($role_name);

-- Create the dedicated service user and grant access to role.
-- TYPE = SERVICE exempts it from Snowflake's MFA enforcement.
CREATE USER IF NOT EXISTS identifier($user_name) TYPE = SERVICE 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

Rotating a managed key pair

A managed key pair (Option A) is replaced from Settings → Integrations → (your Snowflake integration) → Key pair authentication, at the bottom of the page. Generating a replacement changes nothing until you activate it, so the integration keeps connecting with the key in use meanwhile.

Press Generate a new key pair. The new public key appears under New public key, waiting to be used.
Add that public key to the Snowflake user the integration connects as, without replacing the key in use. Follow Snowflake’s key-pair rotation guide.
Press Start using the new key pair.
Remove the old public key from the Snowflake user, as the same guide describes.

Add the new public key on the Snowflake user before you activate it. Activating first leaves the integration unable to connect until you do.

To drop a replacement you decided against, press Discard it. A user-managed key pair (Option B) is rotated on your side: follow Snowflake’s key-pair rotation guide, then paste the new private key into the integration.

Password authentication (deprecated)

Snowflake is removing password authentication for service users as part of its single-factor password deprecation. A password connection works only while your account still permits TYPE = LEGACY_SERVICE users, and stops with error 394508 when it no longer does. Prefer key-pair authentication above; if you must start with a password, plan to move the user to key-pair.

In the code, remember to replace:

<user_password> with a password that meets your account’s password policy
<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 = 60
initially_suspended=true
;

-- Create the dedicated role
CREATE ROLE IF NOT EXISTS identifier($role_name);

-- Create the dedicated user and grant access to role.
-- LEGACY_SERVICE is the only user type Snowflake still signs in with a
-- password alone, and it is being retired.
CREATE USER IF NOT EXISTS identifier($user_name) TYPE = LEGACY_SERVICE 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);

Password authentication is deprecated by Snowflake

If the integration stops connecting, or a new one never does, with

394508 (08004): Failed to authenticate: MFA with TOTP is required. To authenticate, provide both your password and a current TOTP passcode.

(or 394509, 390197, 250001 Multi-factor authentication is required for this account), Snowflake has enforced its single-factor password deprecation on the user the integration signs in as. Every human user (TYPE = PERSON, or no type) must now present a second factor, and password authentication is being removed from LEGACY_SERVICE users. Coalesce Quality connects unattended and cannot enter a TOTP code, so no password will work. Being able to log into Snowflake yourself does not help: your own login satisfies MFA interactively, the integration cannot. Service users (TYPE = SERVICE) are exempt from MFA and authenticate with a key pair. The fix is to move the integration’s user to key-pair authentication; it does not need to be recreated.

Move an existing user to key-pair

In Coalesce Quality, open Settings → Integrations → (your Snowflake integration), set Authentication method to Coalesce Quality managed key-pair and save. The public key appears under Key pair authentication at the bottom of the page; copy it. (To bring your own key instead, pick User managed key-pair and generate one as in Option B.)

In Snowflake, as ACCOUNTADMIN or a role with OWNERSHIP on the user, attach the key to the existing user:

set user_name='SYNQ';
set rsa_public_key='<rsa_public_key>';

USE ROLE ACCOUNTADMIN;
ALTER USER identifier($user_name) SET RSA_PUBLIC_KEY=$rsa_public_key;

Back in Coalesce Quality, press Test connection. Once it succeeds, remove the password and mark the user as a service user so it stays outside MFA enforcement:
ALTER USER identifier($user_name) UNSET PASSWORD; ALTER USER identifier($user_name) SET TYPE = SERVICE;

Do not re-run the setup script for this. It creates the user with CREATE USER IF NOT EXISTS, which skips a user that already exists, so the public key is never attached and the connection keeps failing with the same error.

If the integration signs in as a person

If the integration was set up with someone’s own Snowflake login rather than a dedicated user, do not try to exempt that login from MFA. Create a dedicated service user with the key-pair setup above, grant it the integration’s role, and update the Username on the integration to the new user.

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. Run it in the same Worksheet as the Setup a user and warehouse step above so the $role_name session variable is still set. 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);
GRANT USAGE,MONITOR ON FUTURE 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 materialized views' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on all dynamic tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on all streams' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on all semantic views' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant REFERENCES on all semantic views' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant USAGE on all procedures' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant USAGE on all functions' + 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 materialized views' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on future dynamic tables' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on future streams' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant SELECT on future semantic views' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant REFERENCES on future semantic views' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant USAGE on future procedures' + schemaAndRoleSuffix});
    snowflake.execute({ sqlText: 'grant USAGE on future functions' + 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);

MATERIALIZED VIEW, STREAM, and SELECT ON DYNAMIC TABLE grants are silently ignored on Snowflake editions that do not support those object types — the grant statements still succeed, so the procedure is safe to run on Standard edition.

Semantic views need both SELECT and REFERENCES. SELECT lets the role list them via SHOW SEMANTIC VIEWS and query them via SEMANTIC_VIEW(sv DIMENSIONS … METRICS …). REFERENCES is required separately so GET_DDL('SEMANTIC_VIEW', …) returns the body — without it, the catalog scrape can see the asset but cannot read its definition. Snowflake gates this differently from every other object kind, where SELECT / USAGE cover both visibility and GET_DDL.

Option 2: Grant access to a database

Run this in the same Worksheet as the Setup a user and warehouse step above. Replace <database_name> with the name of your database.

set database_name='<database_name>';

-- Read-only access to database
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);
grant USAGE, MONITOR 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 materialized views in database identifier($database_name) to role identifier($role_name);
grant SELECT on future materialized 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 SELECT on all streams in database identifier($database_name) to role identifier($role_name);
grant SELECT on future streams in database identifier($database_name) to role identifier($role_name);
-- Semantic views: SELECT enables SHOW + SEMANTIC_VIEW(...) querying;
-- REFERENCES is required separately for GET_DDL('SEMANTIC_VIEW', ...).
grant SELECT on all semantic views in database identifier($database_name) to role identifier($role_name);
grant SELECT on future semantic views in database identifier($database_name) to role identifier($role_name);
grant REFERENCES on all semantic views in database identifier($database_name) to role identifier($role_name);
grant REFERENCES on future semantic views in database identifier($database_name) to role identifier($role_name);
grant USAGE on all procedures in database identifier($database_name) to role identifier($role_name);
grant USAGE on future procedures in database identifier($database_name) to role identifier($role_name);
grant USAGE on all functions in database identifier($database_name) to role identifier($role_name);
grant USAGE on future functions 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>, <schema_name>, and <task_name> with your actual database, schema, and task names before running:

-- Grant MONITOR privilege on a specific task
GRANT MONITOR ON TASK <database_name>.<schema_name>.<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 <database_name>.<schema_name> TO ROLE identifier($role_name);

-- To grant MONITOR on future tasks in a schema (if needed)
GRANT MONITOR ON FUTURE TASKS IN SCHEMA <database_name>.<schema_name> 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.

Grant access to Snowflake tags (Optional)

If your Snowflake objects use tags and you want Coalesce Quality to read them (e.g. to filter tables or deploy monitors based on tag values), you need to grant the 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 Coalesce Quality to use them for filtering or monitor rules. Coalesce Quality 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.

Grant access to stages (Optional)

Coalesce Quality shows each named stage as an asset, with the location it points at, and the lineage between the stage and the tables COPY INTO loads from it or unloads to it (this needs query logs enabled). Snowflake lists a stage only to a role holding a privilege on it, so stages need a grant of their own:

-- External stages in every schema of the database, now and in the future
GRANT USAGE ON ALL STAGES IN DATABASE identifier($database_name) TO ROLE identifier($role_name);
GRANT USAGE ON FUTURE STAGES IN DATABASE identifier($database_name) TO ROLE identifier($role_name);

Unlike the baseline grants, USAGE on an external stage also lets the role read the files in it, through LIST @stage or SELECT … FROM @stage. Coalesce Quality only runs SHOW STAGES, but grant it only if that access is acceptable, or grant it on the specific stages you want to see:

GRANT USAGE ON STAGE <database_name>.<schema_name>.<stage_name> TO ROLE identifier($role_name);

USAGE applies to external stages only. An internal stage is listed only to a role with READ on it, which is read access to its files, and it has no external location to show, so we do not ask for it. The user stage (@~) and table stages (@%table) are not objects of their own and never appear.If your schemas use schema-level future grants (Option 1 above), use GRANT USAGE ON FUTURE STAGES IN SCHEMA … per schema instead, for the reason explained there.

Grant access to apps, notebooks, warehouses and pipes (Optional)

With Fetch apps, notebooks, warehouses and pipes enabled, Coalesce Quality shows each Streamlit app, notebook, warehouse, pipe and Cortex Search service as an asset: an app under its title, with the warehouse it runs on; a pipe with the stage it reads and the table it loads; a Cortex Search service with the tables its query reads. Snowflake lists each of them only to a role holding a privilege on it:

-- Streamlit apps and Cortex Search services, now and in the future
GRANT USAGE ON ALL STREAMLITS IN DATABASE identifier($database_name) TO ROLE identifier($role_name);
GRANT USAGE ON FUTURE STREAMLITS IN DATABASE identifier($database_name) TO ROLE identifier($role_name);
GRANT USAGE ON ALL CORTEX SEARCH SERVICES IN DATABASE identifier($database_name) TO ROLE identifier($role_name);
GRANT USAGE ON FUTURE CORTEX SEARCH SERVICES IN DATABASE identifier($database_name) TO ROLE identifier($role_name);

-- Pipes created from now on. Snowflake refuses a grant on all existing pipes
-- at once, so grant those one by one.
GRANT MONITOR ON FUTURE PIPES IN DATABASE identifier($database_name) TO ROLE identifier($role_name);
GRANT MONITOR ON PIPE <database_name>.<schema_name>.<pipe_name> TO ROLE identifier($role_name);

-- Each warehouse you want to see
GRANT MONITOR ON WAREHOUSE <warehouse_name> TO ROLE identifier($role_name);

MONITOR lets the role see a pipe’s status and a warehouse’s load, not change them. USAGE on a Streamlit app lets the role open the app, and on a Cortex Search service lets it query the service; Coalesce Quality only lists them.Snowflake takes no grant on a notebook, so a notebook is listed only when the integration’s role owns it.If your schemas use schema-level future grants (Option 1 above), use the … IN SCHEMA … form of the future grants per schema instead, for the reason explained there.

Stored procedures and user-defined functions

Coalesce Quality automatically discovers stored procedures and UDFs using SHOW PROCEDURES IN DATABASE / SHOW USER FUNCTIONS IN DATABASE and fetches each object’s DDL via GET_DDL. All languages are supported (SQL, JavaScript, Python, Java, Scala). The grants in Option 1 / Option 2 above already include the required USAGE on procedures and functions — no extra steps are needed. Procedures and UDFs show up as first-class assets in Coalesce Quality and participate in lineage whenever they are referenced by tables, views, or tasks.

Personal access (user OAuth)

In addition to the workspace credentials above, you can let each Coalesce Quality member authenticate as themselves against Snowflake. Queries then run under the member’s own Snowflake account and respect the grants, masking policies, and row-level security configured on that user. See Data Warehouse Access for the full model and when to enable it. To enable personal access, an account admin creates a Snowflake security integration of type OAUTH once, then pastes the client ID and secret into the Snowflake integration’s edit page in Coalesce Quality.

Create the security integration

USE ROLE ACCOUNTADMIN;

CREATE SECURITY INTEGRATION COALESCE_QUALITY_USER_OAUTH
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://app.synq.io/auth/dwh-oauth/callback'
  OAUTH_ALTERNATE_REDIRECT_URIS = ('https://app.us.synq.io/auth/dwh-oauth/callback', 'https://app.au.synq.io/auth/dwh-oauth/callback')
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 7776000   -- 90 days
  BLOCKED_ROLES_LIST = ('ACCOUNTADMIN','ORGADMIN','SECURITYADMIN');

Keep only the redirect URI(s) for the Coalesce Quality region(s) your members use. The EU app is https://app.synq.io, the US app is https://app.us.synq.io and the AU app is https://app.au.synq.io — pass one as OAUTH_REDIRECT_URI and any others in OAUTH_ALTERNATE_REDIRECT_URIS. No trailing slash. BLOCKED_ROLES_LIST prevents members from minting OAuth tokens for high-privilege account roles. Add any other roles your security policy reserves for break-glass use.

Fetch the client credentials

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('COALESCE_QUALITY_USER_OAUTH');

The function returns a JSON object with OAUTH_CLIENT_ID, OAUTH_CLIENT_SECRET, and OAUTH_CLIENT_SECRET_2 (the second secret exists for rotation; either can be used). Paste the client ID and one of the secrets into Settings → Integrations → (your Snowflake integration) → Warehouse access → Personal credentials in Coalesce Quality.

No user mapping is required. When a member consents, they sign in to Snowflake on the standard authorize page, and the access token is issued for that Snowflake user. If your account uses SSO/SAML, the consent page transparently redirects through the IdP. Coalesce Quality stores per-user access/refresh tokens; queries from that member then run as the Snowflake user they signed in as.

Optional: restrict which roles members can request

Each member can pass a role parameter when consenting (Coalesce Quality forwards it as scope=session:role:<role>). If you want to constrain the set of roles reachable through this OAuth app, use ALLOWED_ROLES_LIST:

ALTER SECURITY INTEGRATION COALESCE_QUALITY_USER_OAUTH
  SET ALLOWED_ROLES_LIST = ('ANALYST', 'READER');

ALLOWED_ROLES_LIST is a whitelist that complements BLOCKED_ROLES_LIST (blacklist) above. Leave it empty to allow any role the member is granted on Snowflake, minus the blocked ones.

Databases from shares

When an account creates a database from a share (CREATE DATABASE … FROM SHARE), Snowflake records only the share it came from, not the database in the account that provides it. To link the tables of such a database to their source tables, Coalesce Quality needs to know which database each share was made from. You give it that in the integration of the account that receives the share, under Advanced → Databases from shares. The values come from SHOW SHARES, run in the account that provides the share:

USE ROLE ACCOUNTADMIN; -- or the role that owns the shares
SHOW SHARES;

Use the rows of kind OUTBOUND. Each one gives the three values the integration asks for:

`SHOW SHARES` column	Field in the integration
`owner_account`	Owner account
`name`	Share name
`database_name`	Shared database

The easiest way is Fill from SHOW SHARES: paste the whole result, as copied from Snowsight, SnowSQL or the Snowflake CLI, and Coalesce Quality suggests the shares it found in it. Check each suggestion, correct anything it read wrong, and untick the shares this account doesn’t import. Nothing is saved until you save the integration. You can also add each share by hand.

The integration role cannot run SHOW SHARES for you. Snowflake lists outbound shares only to a role with the IMPORT SHARE privilege, and then only the shares that role owns, so ACCOUNTADMIN is usually the only role that sees all of them. Running it from a stored procedure with owner’s rights is not allowed either. That’s why the values are entered in the integration rather than read by it.

A share without an entry is skipped: its database is still monitored, but its tables are not linked to the tables in the providing account. Add the new share here whenever the providing account starts sharing another database.

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.

Once you’ve successfully created the warehouse, database, and dedicated role, input the following data in the Coalesce Quality UI.

Input data in the UI

Integration name

For example, snowflake

Account name

You can find it under Admin > Accounts when you highlight the link icon (🔗). Both formats work:

<orgname>-<accountname> (preferred, e.g. acme-prod1)
<locator>.<region> (legacy, e.g. xy12345.eu-west-1)

Do not include https:// or a trailing slash. A trailing .snowflakecomputing.com is stripped and the value is lowercased automatically, so either acme-prod1 or acme-prod1.snowflakecomputing.com will work.

Databases

The names of the databases you granted access to earlier in the guide. You can monitor multiple databases with the same integration.

Role, username, and authentication

The role name and username you created earlier in the guide, and the authentication method that matches how you set the user up: a key pair (recommended), or the password for a LEGACY_SERVICE user while your Snowflake account still permits one.

Warehouse

The name of the warehouse you created earlier in the guide.

Audit table FQN (optional)

Fully-qualified table name (e.g. MY_DB.PUBLIC.QUALITY_SQL_TEST__AUDIT) where Coalesce Quality will write SQL test execution results. The integration creates the table automatically if it does not yet exist. Leave empty if you do not run SQL tests.

ACCOUNT_USAGE database name (optional)

Defaults to SNOWFLAKE. Override only if your account has the SNOWFLAKE share imported under a non-default name.

Optional flags

Fetch query logs — enables query history ingestion from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY. You can optionally restrict the set of source databases.
Fetch Snowflake tasks — discovers Snowflake Tasks (SHOW TASKS). Select a subset of databases or leave empty for all.
Fetch task executions from ACCOUNT_USAGE.TASK_HISTORY — when on, centralized task history is used (up to 45-minute lag). When off, INFORMATION_SCHEMA.TASK_HISTORY is queried per database (lower lag, more warehouse time).
Task execution fetch interval (minutes) — minimum 5.
Fetch table lineage — uses SNOWFLAKE.CORE.GET_LINEAGE for table-level lineage (Enterprise+). Optionally restrict to a subset of databases.
Fetch apps, notebooks, warehouses and pipes — with every catalog refresh, also lists Streamlit apps, notebooks, warehouses, pipes and Cortex Search services. Needs the grants in Grant access to apps, notebooks, warehouses and pipes.
Don’t use GET_DDL for table schema collection — enable only if GET_DDL causes warehouse pressure; Coalesce Quality will fall back to parsing query logs.

​Data we collect

​Setup a user and warehouse

​Key-pair authentication

​Option A: Managed Key-pair

​Option B: User Managed Key-pair

Generate a new key pair

Extract the public key

Configure Snowflake with your public key

Configure the integration

​Rotating a managed key pair

​Password authentication (deprecated)

​Password authentication is deprecated by Snowflake

​Move an existing user to key-pair

​If the integration signs in as a person

​Grant access to monitored database

​Option 1 (Recommended): Grant access to all schemas

​Option 2: Grant access to a database

​Grant access to Snowflake tasks (Optional)

​Grant access to Snowflake tags (Optional)

​Grant access to stages (Optional)

​Grant access to apps, notebooks, warehouses and pipes (Optional)

​Stored procedures and user-defined functions

​Personal access (user OAuth)

​Create the security integration

​Fetch the client credentials

​Optional: restrict which roles members can request

​Databases from shares

​Network Configuration

​Input data in the UI

​Integration name

​Account name

​Databases

​Role, username, and authentication

​Warehouse

​Audit table FQN (optional)

​ACCOUNT_USAGE database name (optional)

​Optional flags

Data we collect

Setup a user and warehouse

Key-pair authentication

Option A: Managed Key-pair

Option B: User Managed Key-pair

Rotating a managed key pair

Password authentication (deprecated)

Password authentication is deprecated by Snowflake

Move an existing user to key-pair

If the integration signs in as a person

Grant access to monitored database

Option 1 (Recommended): Grant access to all schemas

Option 2: Grant access to a database

Grant access to Snowflake tasks (Optional)

Grant access to Snowflake tags (Optional)

Grant access to stages (Optional)

Grant access to apps, notebooks, warehouses and pipes (Optional)

Stored procedures and user-defined functions

Personal access (user OAuth)

Create the security integration

Fetch the client credentials

Optional: restrict which roles members can request

Databases from shares

Network Configuration

Input data in the UI

Integration name

Account name

Databases

Role, username, and authentication

Warehouse

Audit table FQN (optional)

ACCOUNT_USAGE database name (optional)

Optional flags