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

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 ProfileConnect 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

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.