Skip to main content

​
Snowflake Connector

​
Overview

This document details the secure Snowflake connector setup for a fictitious organization AcmeCorp’s use of the system. The setup involves creating a dedicated read-only role (e.g., AIDNN_READONLY_ROLE) and granting it the necessary permissions for production databases (e.g., ACMECORP_PROD). You can use any role name and choose any database(s) you wish. The role is then assigned to the service user and granted access to the designated analysis warehouse. The system will use this setup to run analyses on all specified Snowflake databases. A Personal Access Token (PAT) will be created for the service user and role combination for authentication. Please note: This is one of the ways to set up this access which we recommend. However, depending upon your specific environment, your Snowflake admin would be able to guide you appropriately.

​
Role Creation and Database Permissions

The following SQL commands establish the dedicated read-only role and grant it necessary USAGE and SELECT permissions on the ACMECORP_PROD database, including permissions on future objects to ensure the system maintains read access as the schema evolves. 
-- Optional: Check if grants already exist for the role
SHOW GRANTS ON ROLE AIDNN_READONLY_ROLE;

-- Create the dedicated read-only role
CREATE ROLE AIDNN_READONLY_ROLE;

-- Grant USAGE on the ACMECORP_PROD database
GRANT USAGE ON DATABASE ACMECORP_PROD TO ROLE AIDNN_READONLY_ROLE;

-- Grant USAGE on all existing schemas in the ACMECORP_PROD database
GRANT USAGE ON ALL SCHEMAS IN DATABASE ACMECORP_PROD TO ROLE AIDNN_READONLY_ROLE;

-- Grant SELECT on all existing tables in the ACMECORP_PROD database
GRANT SELECT ON ALL TABLES IN DATABASE ACMECORP_PROD
    TO ROLE AIDNN_READONLY_ROLE;

-- Grant SELECT on all existing views in the ACMECORP_PROD database
GRANT SELECT ON ALL VIEWS IN DATABASE ACMECORP_PROD
    TO ROLE AIDNN_READONLY_ROLE;

-- Grant USAGE on FUTURE schemas created in the ACMECORP_PROD database
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ACMECORP_PROD
    TO ROLE AIDNN_READONLY_ROLE;

-- Grant SELECT on FUTURE tables created in the ACMECORP_PROD database
GRANT SELECT ON FUTURE TABLES IN DATABASE ACMECORP_PROD
    TO ROLE AIDNN_READONLY_ROLE;

-- Grant SELECT on FUTURE views created in the ACMECORP_PROD database
GRANT SELECT ON FUTURE VIEWS IN DATABASE ACMECORP_PROD
    TO ROLE AIDNN_READONLY_ROLE;

​
Creating the Service User

Create a service account user to be used by the system as follows: 
CREATE USER AIDNN_SERVICE_USER
  PASSWORD = 'StrongPassword123!';
Assign network policy to allow access for this user: 
ALTER USER AIDNN_SERVICE_USER SET NETWORK_POLICY = 'ALLOW_ALL_POLICY';

​
Granting Role to the Service User

Assign the AIDNN_READONLY_ROLE to the service account user: 
-- Grant the role to the service user
GRANT ROLE AIDNN_READONLY_ROLE TO USER AIDNN_SERVICE_USER;

​
Warehouse Access

Grant USAGE privilege on the analysis warehouse so the role can execute queries: 
-- Grant USAGE on the analysis warehouse to the read-only role
GRANT USAGE ON WAREHOUSE ANALYSIS_WH TO ROLE AIDNN_READONLY_ROLE;

​
Personal Access Token (PAT) Creation

The final step is to generate a Personal Access Token (PAT) for the AIDNN_SERVICE_USER using the AIDNN_READONLY_ROLE. This PAT will be configured in the connector application for secure authentication. Action Required: Create the PAT for AIDNN_SERVICE_USER and AIDNN_READONLY_ROLE. This PAT can be generated programmatically as well: 
ALTER USER AIDNN_SERVICE_USER ADD PAT aidnn_readonly_pat
    ROLE_RESTRICTION = 'AIDNN_READONLY_ROLE'
    DAYS_TO_EXPIRY = 30
    COMMENT = 'PAT for readonly access for Aidnn service user';

​
Setting up the Snowflake Connector

To setup a Snowflake connector, once you’re within the workstation from Home, choose Connectors from the left side panel as shown in the image below: Connectors in left panel Once you’re within the Connectors page, on the top right within the main window you will see Add New Connector: Add New Connector button Upon choosing Add New Connector, the screen will take you to the page with list of available connectors: Available connectors To include a Snowflake connector, hover over Snowflake and you will see Add as shown in the below image: Snowflake connector Add button Choose Add and it takes you to the Add New Snowflake Service page: Add New Snowflake Service page

​
Connector Configuration

On the New Snowflake Service page, the Name field provides a text box for entering the connector name. This value can be any user-defined name and will be displayed as the connector name after setup is complete. You can also include a description about the connector to provide more context, however it is optional. The second part of the page collects all the details related to the connection: Credential Configuration

​
General Fields

  • Name (required): Provide a name for the connector.
  • Description (optional): Add a description to provide more context about the connector.

​
Credential Configuration

  • Username: Enter your Snowflake username associated with the account that will be used to authenticate the connection.
  • Account: Enter your Snowflake account identifier (for example: xy12345.us-east-1). This identifies the Snowflake account the connector will connect to.
  • Warehouse: Specify the Snowflake virtual warehouse to be used for query execution.
  • Password: Enter the password for the specified Snowflake user account (this may be a Personal Access Token provided by your Snowflake Admin).
  • Advanced Configuration Options (optional): Typically not required unless your username and password/PAT is configured to only use a specific role, database, etc. If so, ask your Snowflake Admin for these details.

​
Testing the Connection

After entering the credentials, click Test Connector to verify that the connector can successfully authenticate and access the Snowflake account. This test checks:
  • Account validity
  • User credentials
  • Warehouse access
  • Required permissions
Any errors will be surfaced before proceeding. Test Connector A successful test connector might look similar to the one shown below: Successful test result

​
Adding the Connector

After a successful test, click Add to create and save the new connector: Add connector After the connector is created, the connector details page is shown, displaying all configuration information. The workflow section appears at the bottom of the page. Click Sync now (as highlighted in the image below) to start syncing and include the database: Connector details with Sync now

​
Syncing Metadata

Within the Sync Metadata section, select the database you want to synchronize. This determines which database metadata will be fetched and made available through the connector during the sync process: Sync Metadata section After selecting the desired database, click Sync now in the bottom-left corner of the screen. This action initiates the synchronization process, allowing the selected database to be ingested and made available through the connector: Sync now button Once synchronization is initiated:
  • The process enters the workflow state
  • The sync status displays current progress
  • You can monitor the workflow to see what steps are being executed
  • After completion, the status updates to Completed
  • The connector becomes available for use
Completed sync status

​
Managing Connectors

​
Archive a Connector

To archive an existing connector, navigate to the Connector page and select the relevant connector name. In the connector details view, click the three-dot (more options) menu located at the top right of the screen and select Archive. This action archives the entire connector and removes it from active use. Archive connector

​
Support

Please reach out to your Snowflake Admin or support@isotopes.ai for more details if you face any issues.