Connecting Snowflake

Overview

We are using this: https://docs.snowflake.com/en/user-guide/oauth-custom.html

Creating a Snowflake OAuth Integration

Create a Snowflake OAuth integration using the CREATE SECURITY INTEGRATION (Snowflake OAuth) command.

create or replace security integration <integration_name>
  type = oauth
  enabled = true
  oauth_client = custom
  oauth_client_type = 'CONFIDENTIAL'
  oauth_redirect_uri = 'https://greatquestion.co/users/auth/snowflake/callback'
  oauth_allow_non_tls_redirect_uri = false
  oauth_issue_refresh_tokens = true
  oauth_refresh_token_validity = 86400;

Note: Only account administrators (users with the ACCOUNTADMIN role) or a role with the global CREATE INTEGRATION privilege can execute this SQL command.

You can then run these two commands to extract the information necessary to connect to snowflake in Great Question:

desc integration <integration_name>;

This you run to get the URL, which will look something like

https://..snowflakecomputing.com

select system$show_oauth_client_secrets(<integration_name>);

This one you run to get the OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET

After you have all the info, you can go to User Menu > Account > Integrations, add the information and connect to Snowflake, using your credentials.

Once you are connected you will see this:

Let's break this screen down:

Warehouse, Database and Table (required)

This is where your data lives inside Snowflake. Add the values to tell us where to get the data from.

Only import contacts with gq_import tag

In case you want to restrict access from specific records in the table you chose, you can add a boolean column called gq_import and the records with false won't be imported into Great Question.

When a candidate is deleted from Snowflake

If a previously imported record is removed from Snowflake, the candidate will not be deleted automatically from our Great Question database. This is a limitation of how our integration currently works. However, if you want to automatically delete records from Great Question, you can add a boolean column is_deleted to Snowflake and select what type of behavior you want when the record is imported to Great Question.

Syncing schedule

The candidates will be synced every 24 hours at midnight PST, and you can trigger a sync manually at the candidates page

Custom Attributes

All columns from Snowflake will be available in Great Question. If you want to be able to use any specific column, you need to create a candidate attribute for it. In order to do that you go to Account > Candidate attributes > New attribute

Note: The name of the attribute must match the column name from Snowflake.