Skip to main content

Snowflake Authentication with dbt in Conveyor IDEs with SSO

Authentication to Snowflake accounts with Single Sign-On (SSO) can present challenges within a Conveyor IDE.

To address this challenge, we've introduced the dbt-conveyor-snowflake adapter. This adapter is a lightweight extension of the standard dbt-snowflake adapter, and simplifies the authentication process to Snowflake within a Conveyor IDE.

Snowflake Configuration​

To enable user authentication from within a Conveyor IDE, set up a new security integration in Snowflake with the following SQL statement:

CREATE SECURITY INTEGRATION CONVEYOR
    TYPE = OAUTH
    ENABLED = TRUE
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = 'PUBLIC'
    OAUTH_REDIRECT_URI = 'https://app.conveyordata.com/api/v2/ide/callback'
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE
    OAUTH_REFRESH_TOKEN_VALIDITY = 86400;

Following this, retrieve the OAuth Client ID and secret using the command:

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('CONVEYOR');

Save the OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET for use in the next step.

Using the dbt-conveyor-snowflake adapter​

First you need to install the [dbt-conveyor-snowflake] adapter:

pip install dbt-conveyor-snowflake

After installation modify your dbt profiles.yml file to include a new output configuration:

default:
  target: conveyor
  outputs:
    conveyor:
      type: conveyorsnowflake
      account: YOUR_SNOWFLAKE_ACCOUNT
      database: YOUR_DATABASE
      warehouse: YOUR_WAREHOUSE
      schema: YOUR_SCHEMA
      threads: 1

      # Conveyor uses OAuth authentication
      authenticator: oauth
      oauth_client_id: OAUTH_CLIENT_ID
      oauth_client_secret: OAUTH_CLIENT_SECRET

Refer to the dbt Snowflake documentation for specific values to fill in for account, database, etc. The crucial part is replacing OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET with the values obtained from Snowflake.

Using your new connection​

Once the setup is complete, execute dbt run. This should automatically open a new browser tab for Snowflake authentication. After authentication, dbt run should seamlessly execute.