Setting Up Snowflake Integration with Qatalog

This short guide will walk you through setting up and connecting your Snowflake account with Qatalog.

Prerequisites

• An active Snowflake account with ACCOUNTADMIN role privileges.
• A Qatalog Enterprise account with admin privileges.

Step 1: Snowflake Setup

Before connecting Snowflake to Qatalog, you’ll need to ensure that OAuth is set up in your Snowflake account:

1. Log in to your Snowflake account as an account administrator.
2. Open a new SQL Worksheet in your Snowflake Dashboard.
3. Execute the following SQL command to create a new OAuth integration: 

CREATE SECURITY INTEGRATION MY_QATALOG_CLIENT

  TYPE = OAUTH

  ENABLED = TRUE

  OAUTH_CLIENT = CUSTOM

  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'

  OAUTH_REDIRECT_URI = 'https://qatalog.com/integrations/snowflake/grant'

  OAUTH_ISSUE_REFRESH_TOKENS = TRUE

  OAUTH_REFRESH_TOKEN_VALIDITY = 86400;

Note: You can customize the OAUTH_REFRESH_TOKEN_VALIDITY and the client name (MY_QATALOG_CLIENT) if needed. All other parameters should remain as shown.

4. After creating the integration, retrieve the necessary information: 

DESC SECURITY INTEGRATION MY_QATALOG_CLIENT;

From the output, note down:

• The Base URL: Find the AUTH_AUTHORIZATION_ENDPOINT and remove the trailing /oauth/authorize path.
• The OAUTH_CLIENT_ID
  
  

5. Retrieve the client secret: 

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('MY_QATALOG_CLIENT');

You will get two secrets. Note down either one.

6. Set up permissions for the non-admin user that will be used to connect Qatalog: 

GRANT USAGE ON DATABASE [db] TO ROLE [your-role];

GRANT USAGE ON SCHEMA [db].[schema] TO ROLE [your-role];

GRANT SELECT ON ALL TABLES IN SCHEMA [db].[schema] TO ROLE [your-role];

Replace [db], [schema], and [your-role] with your actual database, schema, and role names.

Step 2: Connect Snowflake to Qatalog

1. Log in to Qatalog
2. Click the plus icon (+) underneath the search bar to access the "Add a Connection" screen.
3. Select "Snowflake" from the list of available integrations.
4. Click "Connect" to open the Snowflake connection modal.
5. Fill in the required Snowflake details:
   • Base URI: Your Snowflake account base URL from Step 1
   • Client ID: The Client ID you noted from Step 1
   • Client Secret: The Client Secret you noted from Step 1
6. Click "Connect" to initiate the OAuth flow.
7. You will be redirected to Snowflake to authorize the connection. Log in with your Snowflake credentials if prompted.
8. Grant the requested permissions to allow Qatalog to access your Snowflake data.
9. After successful authorization, you will be redirected back to Qatalog.

Step 3: Verify the Connection

1. Once redirected back to Qatalog, you should see a confirmation message that Snowflake has been successfully connected.
2. Navigate to your integrations or connections dashboard to verify that Snowflake appears as an active integration.

Troubleshooting

If you encounter any issues during the setup process:

• Ensure your Snowflake account has the necessary permissions to create OAuth clients and roles.
• Ensure you're using a non-admin Snowflake account to connect. The following roles are not allowed to connect: ACCOUNTADMIN, ORGADMIN, SECURITYADMIN.
• Double-check that the Client ID and Client Secret are correctly copied from Snowflake.
• Verify that the user you're using to connect has DEFAULT_WAREHOUSE and DEFAULT_NAMESPACE set:

DESCRIBE USER your_user;

• Verify that the Base URI matches your Snowflake account URL exactly.
• Ensure the non-admin user has the necessary permissions on the databases and schemas you want to query.
• If the connection fails, check Snowflake's audit logs for any error messages or failed login attempts.

For further assistance, please contact our support team at support@qatalog.com.