Snowflake Setup

Before You Start

Complete the technical onboarding with Catalog by granting access to your Snowflake metadata and running a couple of tests.

You'll need one of the following top-level roles on Snowflake to create the Catalog user:

SECURITYADMIN
ACCOUNTADMIN
SYSADMIN

If your configuration allows, you can use different roles.

What Catalog Does with Its Access

Your Snowflake instance contains a SNOWFLAKE database. This database is system defined, read-only, and contains only metadata. It's similar to PG-catalog in classic PostgreSQL databases.

Catalog only has access to the SNOWFLAKE database. It runs queries on the tables of the ACCOUNT_USAGE schema. Catalog only accesses the metadata, not the data itself.

For more information on the database SNOWFLAKE and privileges, review the Snowflake documentation:

Queries Ran by the Catalog User

Catalog runs the following queries to retrieve your metadata.

Catalog queries

Get Databases:

SELECT
    database_id,
    database_name,
    database_owner,
    is_transient,
    comment,
    last_altered,
    created,
    deleted
FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES

Get Schemas:

SELECT
    schema_id,
    schema_name,
    catalog_id as database_id,
    catalog_name as database_name,
    schema_owner,
    is_transient,
    comment,
    last_altered,
    created,
    deleted
FROM SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA

Get Tables:

SELECT
    table_id,
    table_name,
    table_schema_id AS schema_id,
    table_schema AS schema_name,
    table_catalog_id AS database_id,
    table_catalog AS database_name,
    table_owner,
    table_type,
    is_transient,
    row_count,
    bytes,
    comment,
    created,
    last_altered,
    deleted
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES
WHERE deleted is null

Get Columns:

SELECT
    column_id,
    column_name,
    table_id,
    table_name,
    table_schema_id AS schema_id,
    table_schema AS schema_name,
    table_catalog_id AS database_id,
    table_catalog AS database_name,
    ordinal_position,
    column_default,
    is_nullable,
    data_type,
    maximum_cardinality,
    character_maximum_length,
    character_octet_length,
    numeric_precision,
    numeric_precision_radix,
    numeric_scale,
    datetime_precision,
    interval_type,
    interval_precision,
    comment,
    deleted
FROM SNOWFLAKE.ACCOUNT_USAGE.COLUMNS

Get Users:

SELECT
    name,
    created_on,
    deleted_on,
    login_name,
    display_name,
    first_name,
    last_name,
    email,
    has_password,
    disabled,
    snowflake_lock,
    default_warehouse,
    default_namespace,
    default_role,
    bypass_mfa_until,
    last_success_login,
    expires_at,
    locked_until_time,
    password_last_set_time,
    comment
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS

Get Queries:

SELECT
    query_id,
    -- dropping INSERT values
    IFF(
        query_type = 'INSERT',
        REGEXP_REPLACE(query_text, 'VALUES (.*)', 'DEFAULT VALUES'),
        query_text
    ) AS query_text,
    database_id,
    database_name,
    schema_id,
    schema_name,
    query_type,
    session_id,
    user_name,
    role_name,
    warehouse_id,
    warehouse_name,
    execution_status,
    error_code,
    error_message,
    CONVERT_TIMEZONE('UTC', start_time) as start_time,
    CONVERT_TIMEZONE('UTC', end_time) as end_time,
    total_elapsed_time,
    bytes_scanned,
    percentage_scanned_from_cache,
    bytes_written,
    bytes_written_to_result,
    bytes_read_from_result,
    rows_produced,
    rows_inserted,
    rows_updated,
    rows_deleted,
    rows_unloaded,
    bytes_deleted,
    partitions_scanned,
    partitions_total,
    compilation_time,
    execution_time,
    queued_provisioning_time,
    queued_repair_time,
    queued_overload_time,
    transaction_blocked_time,
    release_version,
    is_client_generated_statement
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE TRUE
    AND DATE(CONVERT_TIMEZONE('UTC', start_time)) = DATEADD(DAY,-1,CURRENT_DATE)
    AND execution_status = 'SUCCESS'
    AND query_type NOT IN ('SHOW', 'USE', 'ROLLBACK', 'DESCRIBE', 'ALTER_SESSION')

Step 1: Whitelist Catalog IP on Snowflake

This step is only needed if you've set up network policies, meaning your Snowflake instance only accepts connections from specific IPs. Review the Snowflake network policies documentation for more information.

Here are the Catalog fixed IPs for the whitelist:

For instances on app.us.castordoc.com: 34.42.92.72
For instances on app.castordoc.com: 35.246.176.138

Review Snowflake's instructions on how to whitelist an IP address.

Step 2: Create and Test the Catalog User on Snowflake

Enter credentials in the Catalog App interface. This section walks you through creating the user, role, and warehouse, then testing the setup.

Run the Complete Script

If you prefer to run all the SQL at once instead of step-by-step, use the following script. It includes all commands from Steps 2.1 through 2.7 and the test queries. Skip the GRANT APPLY TAG command if you're using Snowflake Standard Edition.

Complete script Steps 2.1 through 2.7

-- Create a dedicated role
USE ROLE SECURITYADMIN;
CREATE ROLE METADATA_VIEWER_ROLE;
GRANT ROLE METADATA_VIEWER_ROLE TO ROLE SYSADMIN;

-- Skip the following line if using Snowflake Standard Edition
GRANT APPLY TAG ON ACCOUNT TO ROLE METADATA_VIEWER_ROLE;

-- Create dedicated warehouse
USE ROLE SYSADMIN;
CREATE WAREHOUSE METADATA_WH
WITH WAREHOUSE_SIZE = SMALL
  AUTO_SUSPEND = 59
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'metadata reader warehouse';

-- Grant usage on the created warehouse to the created role
GRANT USAGE ON WAREHOUSE METADATA_WH TO ROLE METADATA_VIEWER_ROLE;

-- Grant usage on Snowflake shared database to the created role
USE ROLE ACCOUNTADMIN;
-- It's highly likely that you need to use this role
-- to grant access to this specific database
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE METADATA_VIEWER_ROLE;

-- Create a Catalog user
USE ROLE SECURITYADMIN;
CREATE OR REPLACE USER CATALOG
    LOGIN_NAME = CATALOG
    DEFAULT_ROLE = METADATA_VIEWER_ROLE
    DEFAULT_WAREHOUSE = METADATA_WH;

-- Grant the Catalog user the dedicated role
GRANT ROLE METADATA_VIEWER_ROLE TO USER CATALOG;

-- Test the Catalog user
USE ROLE METADATA_VIEWER_ROLE;

SHOW WAREHOUSES;
-- You must see the warehouse METADATA_WH as the default one

SHOW ROLES;
-- You must see the role METADATA_VIEWER_ROLE as the current and default one

SELECT
    database_id,
    database_name
FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES
WHERE NOT is_transient
    AND deleted IS NULL;
-- This query must return all databases that exist on your Snowflake instance

2.1 Create a Dedicated Role

Create a role called METADATA_VIEWER_ROLE. This role inherits the public role from your instance and won't have any rights initially. This role will be assigned to the Catalog user.

USE ROLE SECURITYADMIN;

CREATE ROLE METADATA_VIEWER_ROLE;
GRANT ROLE METADATA_VIEWER_ROLE TO ROLE SYSADMIN;

2.2 Grant Access to Snowflake Object Tagging

Snowflake Standard Edition

Skip this step if you're using Snowflake Standard Edition.

The APPLY TAG permission is required to retrieve your Snowflake Tags. Review the Snowflake documentation on Object Tagging for more information.

USE ROLE SECURITYADMIN;

GRANT APPLY TAG ON ACCOUNT TO ROLE METADATA_VIEWER_ROLE;

2.3 Create a Dedicated Warehouse

The warehouse size must be small at least. Catalog only retrieves your data model and queries a few times a day and the auto-suspend setup is on. This size is needed because the tables within the ACCOUNT_USAGE schema aren't always optimized by Snowflake.

USE ROLE SYSADMIN;

CREATE WAREHOUSE METADATA_WH
WITH WAREHOUSE_SIZE = SMALL
  AUTO_SUSPEND = 59
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'metadata reader warehouse';

2.4 Grant Usage on the New Warehouse to New Role

Add rights to the role you created in Step 2.1. This role is granted usage rights to the new warehouse you created in Step 2.3.

GRANT USAGE ON WAREHOUSE METADATA_WH TO ROLE METADATA_VIEWER_ROLE;

2.5 Grant Usage on Snowflake Shared Database to the Created Role

Add further rights to the role you created in Step 2.1. The following grants usage of the database SNOWFLAKE.

You must use a role that can grant usage to this database. The role ACCOUNTADMIN always can. Feel free to pick another one based on your role configuration.

USE ROLE ACCOUNTADMIN;
-- It's highly likely that you need to use the ACCOUNTADMIN role
-- to grant access to this specific database

GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE METADATA_VIEWER_ROLE;

2.6 Create the Catalog User on Snowflake

Create a user called CATALOG.

USE ROLE SECURITYADMIN;
-- You may need to use ACCOUNTADMIN
CREATE OR REPLACE USER CATALOG
    LOGIN_NAME = CATALOG
    DEFAULT_ROLE = METADATA_VIEWER_ROLE
    DEFAULT_WAREHOUSE = METADATA_WH;

2.7 Grant the Catalog User the Dedicated Role

Grant the Catalog user the role you created in Step 2.1.

GRANT ROLE METADATA_VIEWER_ROLE TO USER CATALOG;

2.8 Test the Catalog User

The Catalog user should now be correctly set up. Before you give Catalog the credentials, run a test to make sure everything works.

Use the created role and run the following statements.

Check Warehouse:

You must see the warehouse METADATA_WH as the default one.

USE ROLE METADATA_VIEWER_ROLE;

SHOW WAREHOUSES;

Check Role:

You must see the role METADATA_VIEWER_ROLE as the current and default one.

SHOW ROLES;

Check Databases:

This query must return all databases that exist on your Snowflake instance.

SELECT
    database_id,
    database_name
FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES
WHERE NOT is_transient
    AND deleted IS NULL;

Step 3: Create a Key Pair

Review the Snowflake documentation about Key Pair Authentication for more details.

3.1 Create Private Key

Open the terminal on your computer and generate a private key without a passphrase using the following command:

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

3.2 Create Public Key

Generate the public key using the private key you created above with the following command:

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

The key generated will be in the following format:

-----BEGIN PUBLIC KEY-----
MIIBIj...
-----END PUBLIC KEY-----

3.3 Assign Public Key to the Catalog User

Execute an ALTER USER command to add the public key to the Catalog user. Insert the public key without its delimiters in the command. Here is an example:

ALTER USER CATALOG SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Step 4: Add New Credentials in Catalog App

Admin Access Required

You must be a Catalog admin to complete this step.

You can now enter the newly created credentials in the Catalog App integrations.

Go to Settings > Integrations.
Click Snowflake Add.
Add the following credentials.
1. Account field, enter the Account Locator of your Snowflake. This value can be found in the hostname, AWS Private Link, or Azure Private Link endpoint for your instance. Here is an example: example.us-west-1
2. Username field, put the LOGIN_NAME of the user created.
3. Private Key, paste it in the Private Key field using the following format:
```
-----BEGIN PRIVATE KEY-----
<PRIVATE_KEY>
-----END PRIVATE KEY-----
```

Account Format

Don't add .snowflakecomputing.com in the Account form. Only include the account and region.

Before You Start​

What Catalog Does with Its Access​

Queries Ran by the Catalog User​

Step 1: Whitelist Catalog IP on Snowflake​

Step 2: Create and Test the Catalog User on Snowflake​

2.1 Create a Dedicated Role​

2.2 Grant Access to Snowflake Object Tagging​

2.3 Create a Dedicated Warehouse​

2.4 Grant Usage on the New Warehouse to New Role​

2.5 Grant Usage on Snowflake Shared Database to the Created Role​

2.6 Create the Catalog User on Snowflake​

2.7 Grant the Catalog User the Dedicated Role​

2.8 Test the Catalog User​

Step 3: Create a Key Pair​

3.1 Create Private Key​

3.2 Create Public Key​

3.3 Assign Public Key to the Catalog User​

Step 4: Add New Credentials in Catalog App​