Snowflake

Connect Snowflake step by step: the read role, the five ways to sign in, and what is read.

Convalesce reads Snowflake through one role and one user that you create for it. The connect screen writes the SQL for both from your own answers.

You choose how that user signs in: a password, a key pair, OAuth with a client secret, OAuth with a certificate, or an OAuth token.

Convalesce is a hosted service, so it connects to your tool over the internet. Nothing is installed on your side.

Before you start

Have these ready and the rest takes a few minutes:

Your account identifier, a warehouse the reads can run on, and the databases you want read.
A user with ACCOUNTADMIN, or one holding MANAGE GRANTS, to run one script.
For OAuth: an application registered with Microsoft Entra ID or Okta.

Connect it

In Convalesce, open Integrations, choose Snowflake, and follow the steps. Each one is shown below as it looks on screen, with what it asks for and anything to copy and run.

The steps depend on one choice: Choose how to sign in. Pick yours here, and every step, picture and script below follows it.

Simplest option, fine for evaluation; for production most teams move to one of the others.

Step 1 of 10: Name it

What to call this connection, and the deployment it belongs to.

The "Name it" step of the connect screen

What it asks for	Needed	What to enter
Name	Yes	How it is listed in Convalesce. Something that says which one it is, if there will be more than one. For example, `Orders database`.
Deployment	Yes	Which environment this is. Choose the same one as the pipelines that write to it, so both name its tables alike. Choose one of: Production, Staging, Development, Test, Quality assurance, User acceptance, Pre-production, Sandbox.
Instance name	Optional	Only when you connect two of these in the same deployment, such as two production servers: it keeps their tables apart. Leave it empty otherwise. For example, `eu1`.

Step 2 of 10: Name your account

Account, warehouse, role and user.

The "Name your account" step of the connect screen

Add a Snowflake integration and enter your account identifier (xy12345, xy12345.us-east-2.aws, and so on), warehouse, and role.

The warehouse is the compute the metadata queries run on, and the role is the one the SQL below creates.

What it asks for	Needed	What to enter
Account identifier	Yes	For example, `xy12345.us-east-2.aws`.
Warehouse	Yes	For example, `COMPUTE_WH`.
Role to create	Yes	The role the SQL below creates and grants to. For example, `convalesce_role`.
Databases to read	Yes	Add each database by name; `show databases` lists them. The script below grants on every one you add. Snowflake has no single grant that covers every database. For example, `ANALYTICS`.
User to create	Yes	The Snowflake account the SQL below creates, and the one this connection signs in as. If the user already exists, name it here and drop the `create user` line. For example, `convalesce_user`.

Step 3 of 10: Choose how to sign in

Password, key pair or OAuth.

The "Choose how to sign in" step of the connect screen

Five ways to authenticate the read connection, set via authentication_type in the recipe.

External browser sign-in (SSO) is not offered: it opens a browser for somebody to log in, which a scheduled connection cannot do unattended.

Step 4 of 10: Set a password

The password the reading user signs in with.

The "Set a password" step of the connect screen

The password below goes into the create user line of the SQL in the next step, and is what we store. Generate one, or type your own.

What it asks for	Needed	What to enter
Password for `svc`	Yes	Stored encrypted the moment you enter it, and shown to no one afterwards.

Step 5 of 10: Provision the read role

Create the reading role and user, and grant them.

The "Provision the read role" step of the connect screen

Run this as ACCOUNTADMIN, or as a user holding MANAGE GRANTS.

operate only matters to start a suspended warehouse; skip it if yours auto-resumes or stays running. If you only need metadata from part of a database, grant usage on individual schemas instead of the whole database.

references gives the shape of a table without the right to read a row; the select grants below it are what let rows be read. Leave the select lines out to connect with shapes only: reading can be turned on later from the connection's Settings tab, which shows those lines again.

This grant lets Convalesce read rows as well as the shape of your tables. While it investigates a failure the agent may run small read-only queries to confirm a cause: capped, with personal columns masked, never stored, and sent to the AI model. You can switch that off for this connection on its Settings tab once it is connected, without changing the grant.

Everything here is the floor: databases, schemas, tables, views, streams.

Run in Snowflake

create or replace role READER;

grant operate, usage on warehouse WH to role READER;

grant usage on database RAW to role READER;
grant usage on all schemas in database RAW to role READER;
grant usage on future schemas in database RAW to role READER;

grant select on all streams in database RAW to role READER;
grant select on future streams in database RAW to role READER;

-- Table/view metadata without profiling or classification:
grant references on all tables in database RAW to role READER;
grant references on future tables in database RAW to role READER;
grant references on all views in database RAW to role READER;
grant references on future views in database RAW to role READER;

-- To let Convalesce read rows (profiling, and queries while it investigates), grant select too.
-- Leave these two lines out to connect with shapes only:
grant select on all tables in database RAW to role READER;
grant select on future tables in database RAW to role READER;

-- Dynamic tables need monitor, not select, for their DDL:
grant monitor on all dynamic tables in database RAW to role READER;
grant monitor on future dynamic tables in database RAW to role READER;

grant usage on database ANALYTICS to role READER;
grant usage on all schemas in database ANALYTICS to role READER;
grant usage on future schemas in database ANALYTICS to role READER;

grant select on all streams in database ANALYTICS to role READER;
grant select on future streams in database ANALYTICS to role READER;

-- Table/view metadata without profiling or classification:
grant references on all tables in database ANALYTICS to role READER;
grant references on future tables in database ANALYTICS to role READER;
grant references on all views in database ANALYTICS to role READER;
grant references on future views in database ANALYTICS to role READER;

-- To let Convalesce read rows (profiling, and queries while it investigates), grant select too.
-- Leave these two lines out to connect with shapes only:
grant select on all tables in database ANALYTICS to role READER;
grant select on future tables in database ANALYTICS to role READER;

-- Dynamic tables need monitor, not select, for their DDL:
grant monitor on all dynamic tables in database ANALYTICS to role READER;
grant monitor on future dynamic tables in database ANALYTICS to role READER;

create user svc display_name = 'Convalesce'
  password = '<password>' default_role = READER
  default_warehouse = WH;
grant role READER to user svc;

Step 6 of 10: Grant what else you want captured (optional)

Optional: lineage, usage, tags, tasks, pipes and more.

The "Grant what else you want captured" step of the connect screen

Each of these is optional. Choose Yes for what you want read, and the SQL below carries its grant. imported privileges is the only way to get lineage, usage statistics or tags, and there is no finer-grained privilege: it is all-or-nothing on the whole shared SNOWFLAKE database.

If imported privileges isn't something your security policy allows, that's fine: everything else still works, you just won't get lineage, usage, or tags.

Pipes have no on all pipes in database form: Snowflake rejects it with "Bulk grant on objects of type PIPE to ROLE is restricted". One monitor execution grant on the account covers every pipe and task instead, existing and future, and has to be granted by ACCOUNTADMIN.

To stay object-scoped instead, grant future pipes and name the existing ones one at a time. show pipes in database lists them, though it only returns pipes your current role can already see.

If your Snowflake account restricts inbound connections by IP, add a network policy scoped to svc rather than loosening account-wide rules, and allow 34.66.85.47, the address Convalesce connects from.

What it asks for	Needed	What to enter
Tags	Optional	Off unless you choose Yes. Read through the first grant below. Choose one of: Yes, No.
Tasks	Optional	Off unless you choose Yes. Choose one of: Yes, No.
Pipes	Optional	Off unless you choose Yes. Choose one of: Yes, No.
Stages	Optional	Off unless you choose Yes. Choose one of: Yes, No.
Streamlit apps	Optional	Off unless you choose Yes. Choose one of: Yes, No.

Run in Snowflake

-- Lineage, usage statistics and tags: access to the shared ACCOUNT_USAGE
-- views. It is one grant on the whole shared SNOWFLAKE database.
grant imported privileges on database snowflake to role READER;

-- Streamlit apps
grant usage on all streamlits in database RAW to role READER;
grant usage on future streamlits in database RAW to role READER;

grant usage on all streamlits in database ANALYTICS to role READER;
grant usage on future streamlits in database ANALYTICS to role READER;

-- Stages
grant usage on all stages in database RAW to role READER;
grant usage on future stages in database RAW to role READER;

grant usage on all stages in database ANALYTICS to role READER;
grant usage on future stages in database ANALYTICS to role READER;

-- Tasks
grant monitor on all tasks in database RAW to role READER;
grant monitor on future tasks in database RAW to role READER;

grant monitor on all tasks in database ANALYTICS to role READER;
grant monitor on future tasks in database ANALYTICS to role READER;

-- Pipes: Snowflake has no `on all pipes in database` form, so one
-- account-level grant covers every pipe, existing and future:
grant monitor execution on account to role READER;

-- Or, to keep the grant object-scoped, cover future pipes and name the
-- existing ones one at a time:
grant monitor on future pipes in database RAW to role READER;
-- grant monitor on pipe RAW.<your-schema>."<your-pipe>" to role READER;

grant monitor on future pipes in database ANALYTICS to role READER;
-- grant monitor on pipe ANALYTICS.<your-schema>."<your-pipe>" to role READER;

Step 7 of 10: Choose what is read (optional)

Optional: narrow it to some schemas and tables.

The "Choose what is read" step of the connect screen

Everything the credential can see is read unless you narrow it here. List the schemas and tables you want, the ones to leave out, or both.

What it asks for	Needed	What to enter
Schemas to read	Optional	Add each one as the schema's name. A `` stands for any part of a name, as in `STG_`. Leave this empty to read all schemas. For example, `PUBLIC`.
Schemas to skip	Optional	Written the same way. Anything added here is skipped even if it is also added above.
Tables to read	Optional	Add each one as `database.schema.table`. A `` stands for any part of a name, as in `ANALYTICS.PUBLIC.ORDERS`. Leave this empty to read all tables. For example, `ANALYTICS.PUBLIC.ORDERS*`.
Tables to skip	Optional	Written the same way. Anything added here is skipped even if it is also added above.

Step 8 of 10: Test the connection

Check Convalesce can reach it with what you entered.

The "Test the connection" step of the connect screen

The test runs on the same worker a real run would, with the recipe exactly as it will be saved, so it fails the way a run would.

Step 9 of 10: Choose how often

How often Convalesce reads it.

The "Choose how often" step of the connect screen

Step 10 of 10: Review and connect

Check everything, then save the connection.

The "Review and connect" step of the connect screen

Network

If your Snowflake account restricts inbound connections by IP, add a network policy scoped to convalesce_user rather than loosening account-wide rules, and allow 34.66.85.47, the address Convalesce connects from.

Settings

What the connect screen asks for

Input	On the step	Needed
Name	Name it	Yes
Deployment	Name it	Yes
Instance name	Name it	Optional
Account identifier	Name your account	Yes
Warehouse	Name your account	Yes
Role to create	Name your account	Yes
Databases to read	Name your account	Yes
User to create	Name your account	Yes
Password for `svc`	Set a password	Yes
Tags	Grant what else you want captured	Optional
Tasks	Grant what else you want captured	Optional
Pipes	Grant what else you want captured	Optional
Stages	Grant what else you want captured	Optional
Streamlit apps	Grant what else you want captured	Optional
Schemas to read	Choose what is read	Optional
Schemas to skip	Choose what is read	Optional
Tables to read	Choose what is read	Optional
Tables to skip	Choose what is read	Optional
Private key	Add the key pair	Yes
Passphrase	Add the key pair	Optional
Identity provider	Add the OAuth application	Yes
Authority URL	Add the OAuth application	Yes
Client ID	Add the OAuth application	Yes
Scopes	Add the OAuth application	Yes
Client secret	Add the OAuth application	Yes
Encoded certificate	Add the OAuth application	Yes
Encoded private key	Add the OAuth application	Yes
OAuth token	Paste the token	Yes

Set for you

These are the same on every connection. The connect screen does not ask for them.

What it means	Setting
Names are matched whatever their casing.	`convert_urns_to_lowercase: true`
How tables feed each other is read.	`include_table_lineage: true`
Tables are read.	`include_tables: true`
Views are read.	`include_views: true`
Row counts and sizes are read for each table.	`profiling.enabled: true`
Only whole-table figures are read, never values from individual columns.	`profiling.profile_table_level_only: true`
Something that is no longer there is marked as removed.	`stateful_ingestion.enabled: true`

Troubleshooting

The test says it could not reach it. If a network policy limits who may sign in, it has to allow 34.66.85.47. See Network access.
The test says the role or warehouse does not exist. Run the script from Provision the read role as ACCOUNTADMIN, with the names you typed on the first step.
No lineage or usage appears. Run the first grant on Grant what else you want captured: it is what opens Snowflake's usage views.
OAuth sign-in fails for Okta. Use OAuth, client credentials, with the Snowflake user's login name set to the app's client ID.