Data Applications → Configure Incorta Data Delivery for Snowflake

About Incorta Data Delivery for Snowflake

Following is the high-level process Incorta Data Delivery process for Snowflake

Create a Snowflake Destination
Assign a destination to a Physical Schema
Load the physical schema to push data to Destination

Create a Snowflake Data Destination

When you create an BigQuery Data Destination, you are able to seamlessly ingest physical schema tables from Incorta to your BigQuery instance.

Here are the steps:

Find the Incorta Storage Bucket
Create an integration in Snowflake
Find the Snowflake Service Account / Grant Access
Create an external stage
Get Snowflake connection info
Configure the Data Destination
Set a Data Destination for a Physical Schema
Load data to the Data Destination

Find the Incorta Storage Bucket

For data to be transacted from Incorta to Snowflake, you must get Snowflake's service account and grant it access to Incorta. If you are on Incorta Cloud, please contact support to provide the storage bucket.

Go to the CMC, log in as admin
Go to clusters, select the cluster and view tenants
Under the Path Column select Show
- Example: gcs://{cluster name}/Tenants/default/source
To test the connection, select Test Connection.
To save, select Ok.

Create an integration in Snowflake

In Snowflake, run the following command to create a storage integration:

  CREATE STORAGE INTEGRATION {NAME}
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = "{Provider}"
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ("{GCS BUCKET PATH}");

Example

  CREATE STORAGE INTEGRATION INCORTA_INTEGRATION_SOURCE
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = "GCS"
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ("{storage bucket from previous step}");

Find the Snowflake Service Account / Grant Access

Next, find the service account being used by the Snowflake integration.

Run the command:

DESC STORAGE INTEGRATION {Integration name from previous step};

Example:

DESC STORAGE INTEGRATION INCORTA_INTEGRATION_SOURCE;

As a result of the statement, get the service account information from the results. In GCP, it would be the value associated with STORAGE_GCP_SERVICE_ACCOUNT.

Now, submit a ticket Incorta to grant the role snowFlakeIngest on the bucket. You can use the following as a request:

Please grant the following service account {service account} the following role snowFlakeIngest access to the following bucket {gcs://ic-CLUSTER-bucket} to be able to ingest to snowflake.

Create an exernal stage

In Snowflake, create or set the database in which you would like to create the external stage.

CREATE DATABASE "{database name}}";

Whether pre-existing or newly created, set the database:

USE DATABASE "{database name}";

Run the following to create the external stage

CREATE OR REPLACE STAGE "EXT_STAGE_NAME" URL = '{GCS BUCKET}' STORAGE_INTEGRATION = "{INTEGRATION NAME}";

Example:

CREATE OR REPLACE STAGE "EXT_STAGE_NAME" URL = 'gcs://{cluster}}/Tenants/default/source' STORAGE_INTEGRATION = "INCORTA_INTEGRATION_SOURCE";

Get Snowflake connection info

In Snowflake go to Profile → Connect a tool to Snowflake
In the account details view, select Connectors/Drivers
In the dropdown, select JBDC Connection String
Select a warehouse or database and copy the connection string. Note that the connection string can end with ‘.com’

Configure the Data Destination

After completing all previous pre-requisite steps, proceed to create a Snowflake Data Destination

Go to the Data tab
Select +New > Add Data Destination
Using information from the above stems, configure the properties of the Snowflake Destination.

Set a Data Destination for a Physical Schema

Set a data destination for physical schemas that will push tables to Snowdflake. Here are the steps:

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 a Schema Destination or select + New Schema Data Destination to create a new one.
Optionally, enter Target Schema Name, which is the table name you want to create in the the data destination. The default value is schema name in Incorta.
Note
If the schema name already exists in the destination, the newly sent schema will overwrite the existing one.
Optionally, enter a value in the Target Table Prefix to add as a prefix to the table names.
Select which tables should be sent to the Destination
Select Done.

A Snowflake icon will appear on the left of the schema name to indicate the Data Destination is set for the schema.

Load data to the Data Destination

When you load your schema using full or incremental load, Incorta sends the schema to the data destination automatically. Hence, adding a new stage to the load job details viewer: Send to Destination. For more details, refer to the Load Job Details Viewer.

Content