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
- Example:
- 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_STAGESTORAGE_PROVIDER = "{Provider}"ENABLED = TRUESTORAGE_ALLOWED_LOCATIONS = ("{GCS BUCKET PATH}");
Example
CREATE STORAGE INTEGRATION INCORTA_INTEGRATION_SOURCETYPE = EXTERNAL_STAGESTORAGE_PROVIDER = "GCS"ENABLED = TRUESTORAGE_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.
NoteIf 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.