Snowflake Setup

The Snowflake Synapse enables data synchronization between Snowflake schema and Syncari entities using the Snowflake API. After the Synapse is Authenticated, a user can Activate the Snowflake Synapse, which enables Syncari to replicate the schema of Snowflake and make it available within the Unified Data Model. Using the Sync Studio, a user can map Snowflake objects to Syncari entities, and Snowflake fields to Syncari fields.

If your Snowflake security requires you to whitelist an IP you can find that here.

Snowflake Information

You will need the following information from Snowflake:

• Account Name
• Warehouse Name
• Database Name
• Schema Name
• User Role (By default, PUBLIC will be used)

After you login to your Snowflake subscription, these steps will help you locate the information you need for the Synapse Setup.

1. Account Name
2. Warehouse Name
3. Database Name
4. Schema Name

User Role Setup

If the user is already created, the details can be found in Admin -> User & Roles. If not. the following queries can be used to create a new user role or it can be done in the Admin UI. 

• Create a new role that will be used to manage these permissions.

CREATE ROLE new_role_name;

• Assign the newly created role to users who require these permissions.

GRANT ROLE new_role_name TO USER syncariuser;

• Grant usage on the schema, which allows the role to access the schema but does not permit viewing or manipulating data in its tables.

GRANT USAGE ON SCHEMA DATABASE.SCHEMA TO ROLE new_role_name;

• Grant permissions for selecting, inserting, updating, and deleting data from all tables in the schema. This covers the full range of CRUD (create, read, update, delete) operations for existing tables.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA DATABASE.SCHEMA TO ROLE new_role_name;

OAuth Role Setup

Snowflake has deprecated username password authentication on 09/30/2024. So the recommended authentication method is OAuth. Please follow the following steps in Snowflake to setup OAuth.

• Create OAuth Integration. The integration name should be all upper case.

CREATE OR REPLACE SECURITY INTEGRATION SYNCARIOAUTHINTEGRATION
TYPE=OAUTH
ENABLED=TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://app.syncari.com/oauth/authorize' 
OAUTH_ISSUE_REFRESH_TOKENS = TRUE;

• Grant usage on the created Integration to the syncari user

GRANT USAGE ON INTEGRATION SYNCARIOAUTHINTEGRATION TO USER syncariuser;

• Grant usage on the Integration to the syncari role

GRANT USAGE ON INTEGRATION SYNCARIOAUTHINTEGRATION TO new_role_name;

• Run the following query to get the credentials. Copy values of OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('SYNCARIOAUTHINTEGRATION');

Synapse Setup

Step 1: Start by logging in to your Syncari subscription

1. Navigate to Synapses
2. Find Snowflake in the Synapse Library
3. Drag the Snowflake Synapse to the canvas

Step 2: Configure your Synapse parameters

1. Enter [Synapse Name]
2. Enter [Account Name]
3. Enter [Warehouse Name]
4. Enter [Database Name]
5. Enter [Schema Name]
6. Enter [User Role]
7. Enter [User Role]
8. Enter [Endpoint] - This should be of format https://<ACCOUNT-NAME>.snowflakecomputing.com. To confirm, the following query can be executed and in the results, check the url prefix for OAUTH_AUTHORIZATION_ENDPOINT
   

   1. DESCRIBE SECURITY INTEGRATION SYNCARIOAUTHINTEGRATION

9. For [Authentication Method], select OAuth
10. Click "Next"

[OPTIONAL] Enable Syncari to automatically create entities in Snowflake

Most customers use Snowflake as a source Synapse and therefore would NOT set this option.

However, the "Instantiate with Syncari entities" option enables all the existing entities in Syncari to be created as tables in Snowflake. This feature is useful if you want the schema from the Syncari Unified Data Model to be replicated in Snowflake. 

Talk to one of our Onboarding and Support Team members if you would like any clarification or a recommendation.

Step 3: Define your authentication parameters:

1. Enter [Client Id] - Value for OAUTH_CLIENT_ID
2. Enter [Client Secret] - Value for OAUTH_CLIENT_SECRET
3. Click "Authenticate"
4. You will be redirected to Snowflake for login and to provide consent to Syncari for DB access. Once you're done, you will be redirected back to Syncari
5. Click "Next"

Step 4: Activate your Synapse

1. Click "Make Synapse Active"
2. Click "Close"

Congratulations! You have completed the setup of Snowflake.

If you receive any errors message after clicking Authenticate, confirm each of the parameters in the previous steps are configured properly. If the error persists, contact one of our Onboarding and Support team members.

What's Next?

Checkout this article to learn how to designate the Id and Watermark fields for Snowflake Entities.