Integrate Incorta with Snowflake for Seamless Data Ingestion

[amit_kothari]

Overview

Incorta’s new Snowflake data destination enables you to push curated, enriched, or transformed datasets directly into Snowflake.

Supported Incorta Versions: 2024.7.5 and above

Configuration

To enable the Snowflake data destination, a set of configurations must be completed both in Snowflake and Incorta:

1. Ensure the Snowflake connector is installed in Incorta.
2. Configure a storage integration in Snowflake to allow it to access the tenant folder in Incorta.
3. Create a user in Snowflake with a role that has the following privileges:
1. USAGE access on the target warehouse and target database.
2. CREATE SCHEMA privilege on the target database.
3. USAGE access on the external stage linked to the storage integration, with LIST and READ access to the tenant folder in Incorta.
4. If you're using OAuth authentication to connect from Incorta to Snowflake, you'll also need to configure OAuth settings appropriately.
5. Create the data destination in Incorta

The following section provides detailed instructions for configuring Snowflake and setting up the data destination in Incorta.

Configure a storage integration in Snowflake

1. Get the Incorta GCS/ADLS/AWS bucket path from Incorta CMC's tenant tab (login as admin) or  raise a Incorta support ticket
2. Find the Snowflake service account for your Snowflake
3.  Raise a Incorta support ticket and ask them to give the Snowflake service account for that cluster "snowFlakeIngest" role on the Incorta gcs cluster bucket
4. Login to Snowflake as a user with the ACCOUNTADMIN role
5. Create a storage integration that points to Incorta’s tenant folder. Here is a sample command for GCS:

CREATE STORAGE INTEGRATION "STG1_INTEGRATION_NAME" TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = GCS ENABLED = TRUE STORAGE_ALLOWED_LOCATIONS = ('gcs:/<host>/Tenants/default/source');

1. Create an external Stage in an existing or a new database. Here is a sample command for GCS in the database called INCORTAWH and schema PUBLIC:

USE INCORTAWH;

CREATE OR REPLACE STAGE "EXT_STAGE_NAME1” URL = 'gcs://<host>/Tenants/default/source' STORAGE_INTEGRATION = "STG1_INTEGRATION_NAME"; 

1. Get the snowflake service account using this command (GCS)

DESC STORAGE INTEGRATION "STG1_INTEGRATION_NAME";

Refer to Snowflake documentation for details on how to configure an integration for different cloud storage services.

• Configuring an integration for Google Cloud Storage
• Configuring a Snowflake storage integration to access Amazon S3
• Configuring an Azure container for loading data

Create a Snowflake user 

Create a non-admin Snowflake user (e.g., INCORTA_INGEST) or use an existing one. Assign the user a role that has the required privileges (create a new role if necessary). This user will later be used in Incorta to access Snowflake.

Required Privileges:

• USAGE access on the target warehouse and database
• USAGE access on the stage and its schema
• CREATE SCHEMA on the target database
• If you are using an existing target schema, the Snowflake user’s role must be granted ALL PRIVILEGES on the respective schema.

For example, if that user role is called INCORTA_INGEST, then run these grants:

OAuth authentication Configuration

1. Create a Snowflake security integration. Here is a sample command:

GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE INCORTA_INGEST;
GRANT USAGE ON Database INCORTAWH TO ROLE INCORTA_INGEST;
GRANT CREATE SCHEMA ON DATABASE INCORTAWH TO ROLE INCORTA_INGEST;
GRANT USAGE ON SCHEMA INCORTAWH.PUBLIC TO ROLE INCORTA_INGEST;
GRANT USAGE ON STAGE PUBLIC.EXT_STAGE_NAME1 TO ROLE INCORTA_INGEST;
GRANT USAGE ON INTEGRATION STG1_INTEGRATION_NAME TO ROLE INCORTA_INGEST;

CREATE OR REPLACE SECURITY INTEGRATION "INCORTA_OAUTH"
ENABLED = TRUE
TYPE = OAUTH
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://cloud.incorta.com/api/gdrive'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;

1. To get the client ID and client secret, run this sample command (replace INCORTA_OAUTH with the correct name) : 

DESC SECURITY INTEGRATION "INCORTA_OAUTH";
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS( 'INCORTA_OAUTH' );

Create the data destination in Incorta

1. Ensure the Snowflake connector is installed in Incorta
• Go to Incorta’s marketplace
• Open the Connectors tab
• Make sure that the Snowflake connector is installed
2. In Snowflake, under View Account Details, get the JDBC URL
3. In Incorta, navigate to Data->Data Destination, create a new Snowflake data Destination, and configure using the below properties. 
• Get the snowflake jdbc URL in Admin → Accounts in the Snowflake UI — hover over your account and there's a direct copy option for the account URL. This is needed for the connection string.
• If OAuth is selected as the Authentication method, then add the client ID and secret and click on Authorize. A snowflake screen will appear, login using the non-admin you had created, eg, INCORTA_INGEST
• Make sure that browser pop-ups are allowed for the Authorize action
• Here is a sample screenshot of the Snowflake Data Destination:

• Please refer to the Incorta documentation to understand all the properties of a Snowflake data destination.

1. Test the Connection and save the data destination.
2. Set a data destination for a physical schema that will push tables to Snowflake. 

• ​​In the Navigation bar, select Schema
• In the Action bar, select Settings (gear icon) → Set Data Destination
• In the Set Schema Destination for <SCHEMA_NAME> dialog:
• Select the Schema Destination 
• Enter Target Schema Name, which is the table name you want to create in the data destination. The default value is schema name in Incorta
• Load the schema and check the tables in Snowflake